Python MySQL Dynamic Variables in Update Statement

Question

I am using python, pymysql and MYSQL. This code looks for an existing row with data in it. Eg, Symbol = AGL, Date = 20200629, Enterprise_Value = 12345.

If I find an existing row, I want to update it. The code cycles through variables called stat_name and stats.

Stat_Name holds the column names and stats are the data values that will be stored in the rows.

The SELECT COUNT portion of the code works. However, when it gets to the UPDATE {table} section, it errors out with this message:

pymysql.err.ProgrammingError: (1064, "You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax
to use near ''Enterprise_Value' = '12345' WHERE Symbol = 'AGL' AND Date = '20200629'' at line 1")

I've pasted the current version of the code below:

dbname = "mydb"
var_table = "asx"

var_date = '20200629'
symbol = 'AGL'
dict_stats = {'Enterprise_Value' : '12345', 'EBITDA' : '67890'}

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

    my_cursor = conn.cursor()

    #Find whether the row already exists.
    select_word_temp = ("SELECT COUNT(*) from {table} WHERE Symbol = %s AND Date = %s")
    select_word = str(select_word_temp)
    data_word = (symbol, var_date)
    my_cursor.execute(select_word.format(table=var_table), data_word)
    result = my_cursor.fetchone()
    row_count =  my_cursor.rowcount
    print(result)
    if row_count != 0:
        #The looks to be a syntax problem in this area.
        update_word_temp = ("UPDATE {table} SET %s = %s WHERE Symbol = %s AND Date = %s")
        update_word = str(update_word_temp)
        update_data_word = (stat_name, stat, symbol, var_date)
        my_cursor.execute(update_word.format(table=var_table), update_data_word)

    conn.commit()
    conn.close()

for stat_name in dict_stats:
    print(stat_name)
    stat = dict_stats[stat_name]
    print(stat)
    funPopulateTables(symbol, var_date, stat_name, stat)

I found this article, which looks useful, but I haven't been able to get it to work using their suggested syntax as yet. Python MYSQL update statement

Any help would be greatly appreciated. Thanks in advance.

Answer 1

Thanks for the advice. I did end up using that syntax and managed to get it working. I've pasted the updated code below.

if row_count != 0:
    #Fixed syntax in the line below
    update_word_temp = str("UPDATE {table} SET ") + stat_name + str(" = %s WHERE Symbol = %s AND Date = %s")
    print(update_word_temp)
    update_word = str(update_word_temp)
    #Updated the variables in the line below
    update_data_word = (stat, symbol, var_date)
    print(update_data_word)
    my_cursor.execute(update_word_temp.format(table=var_table), update_data_word)

Answer 2

I came across this thread as I was trying to do the same thing, but the solution you came up with introduces a mysql injection vulnerability. See little bobby tables for more information about that \s Instead, you could make a unique string for each sql statement in your object that explicitly sets each column. This isn't too bad with 4 elements in the array like your example, but could get difficult with more.

dbname = "mydb"
var_table = "asx"

var_date = '20200629'
symbol = 'AGL'
dict_stats = {'Enterprise_Value' : '12345', 'EBITDA' : '67890'}

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

    my_cursor = conn.cursor()

    #Find whether the row already exists.
    select_word_temp = ("SELECT COUNT(*) from {table} WHERE Symbol = %s AND Date = %s")
    select_word = str(select_word_temp)
    data_word = (symbol, var_date)
    my_cursor.execute(select_word.format(table=var_table), data_word)
    result = my_cursor.fetchone()
    row_count =  my_cursor.rowcount
    print(result)
    if row_count != 0:
        if (stat_name == "Enterprise_Value"):
          update_word = ("UPDATE {table} SET Enterprise_Value = %s WHERE Symbol = %s AND Date = %s")
        elif (stat_name == "12345"):
          update_word = ("UPDATE {table} SET 12345 = %s WHERE Symbol = %s AND Date = %s")
        elif (stat_name == "EBITDA"):
          update_word = ("UPDATE {table} SET EBITDA = %s WHERE Symbol = %s AND Date = %s")
        elif (stat_name == "67890"):
          update_word = ("UPDATE {table} SET 67890 = %s WHERE Symbol = %s AND Date = %s")
        else:
          return "ERROR " + stat_name + " not in " + str(dict_stats)
        update_data_word = (stat, symbol, var_date)
        my_cursor.execute(update_word.format(table=var_table), update_data_word)

    conn.commit()
    conn.close()