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.

You can't parameterize column names. You'll need to sanitize any user input (I.e. check it matches an existing column) and format it in to the query — roganjosh
– roganjosh, Commented Jul 2, 2020 at 9:27
I managed to parameterise the column name as per this link: stackoverflow.com/questions/62638605/… — JiggidyJoe
– JiggidyJoe, Commented Jul 2, 2020 at 10:34
Does this answer your question? Python MySQL Dynamic Insert - Column Name as Variable, Values as Variables — roganjosh
– roganjosh, Commented Jul 2, 2020 at 11:30

JiggidyJoe · Accepted Answer · 2020-07-02 11:46:25Z

0

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)

answered Jul 2, 2020 at 11:46

JiggidyJoe

5192 gold badges9 silver badges22 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

draxiom · Accepted Answer · 2022-03-12 22:41:49Z

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()

Collectives™ on Stack Overflow

Python MySQL Dynamic Variables in Update Statement

2 Answers 2

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related