Home:ALL Converter>Python MySQL Dynamic Insert - Column Name as Variable, Values as Variables

Python MySQL Dynamic Insert - Column Name as Variable, Values as Variables

Ask Time:2020-06-29T20:53:09         Author:JiggidyJoe

Json Formatter

I am trying to create a function that populates a MySQL table. The function receives several variables as inputs:


The value of Stat_Name changes as the code runs, eg Enterprise_Value, Profit_Margin, EBITDA - these are the column names within the table. Stat_Value is the data that goes into the rows.

This code requires one Column Name as a variable, and three values as variables.

I found this previous article which looks promising, but I haven't been able to make it work as of yet. dynamic mysql insert statement in python

I have pasted my attempt at the code below:

dbname = "mydb"
var_table = "exc"

symbol = 'ABC'
var_date = '20200629'
stat_name = 'Enterprise_Value'
stat_value = '12345'

def funPopulateTables(symbol, var_date, stat_name, stat_value):
    conn = pymysql.connect(host="localhost", user="root", passwd=mypass, db=dbname)

    my_cursor = conn.cursor()

    query_placeholders = ', '.join(['%s'] * len(stat_value))
    query_columns = ', '.join(stat_name)

    add_word = ("INSERT INTO {table} " "(Symbol, Date, (%s)) " "VALUES (%s, %s, %s)") %(query_columns, query_placeholders)
    data_word = (symbol, var_date, stat_value)
    my_cursor.execute(add_word.format(table=var_table), data_word)


funPopulateTables(symbol, var_date, stat_name, stat_value)

Any help would be greatly appreciated. Thanks in advance.

Author:JiggidyJoe,eproduced under the CC 4.0 BY-SA copyright license with a link to the original source and this disclaimer.
Link to original article:https://stackoverflow.com/questions/62638605/python-mysql-dynamic-insert-column-name-as-variable-values-as-variables