Python MYSQL dynamic variables error with SQL syntax

Question

I wrote below function:

def update_record(col, new_value, date, finance_manager_table, connection):
    finance_manager_table.execute("""
       UPDATE financemanager
       SET %s = %s
       WHERE Date = %s
    """, (col, new_value, date))
    connection.commit()

Then I try to call the function with: dbc.update_record("Salary", "2500", "January/22", finance_manager_table, connection) and then experiencing the error:

mysql.connector.errors.ProgrammingError: 1064 (42000): 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 ''Salary' = 2500 WHERE Date = 'January/22'' at line 2

So it looks as it creates correct statement, but still somehow wrong.

I've removed 'col' and instead wrote "SET Salary = %s" which has worked.

Is it incorrect to write "SET %s = %s", or is there a problem with something else?

Thanks! Adam

the root cause of this issue is that parametrization has not ever supported table nor column-name string substitution. usually the way i would suggest this to be done is in two steps: 1) perform the string substition for the column name only in your case, then 2) pass that newly constructed query and the parameters to .execute(). There are a ton of other SO questions and answers about this. — mechanical_meat
– mechanical_meat, Commented Dec 28, 2021 at 14:03

Subhashis Pandey · Accepted Answer · 2021-12-28 13:58:43Z

1

Use Python 3's f-Strings: An Improved String Formatting Syntax

s = f"""
       UPDATE financemanager
       SET {col} = {new_value}
       WHERE Date = '{date}'
    """
finance_manager_table.execute(s)

answered Dec 28, 2021 at 13:58

Subhashis Pandey

1,5381 gold badge13 silver badges17 bronze badges

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

2 Comments

Adam Ziaja Over a year ago

Thanks! I didn't know this way of writing dynamic variables into a string. Worked perfectly!

Subhashis Pandey Over a year ago

please upvote if it helped.

Terry Spotts · Accepted Answer · 2021-12-28 14:11:41Z

You need to use one of Python's string formatting methods(f-string, .format) for the database object names(tables, columns, etc.) also a good idea to wrap them in backticks and use %s substitution of the .execute() method for values to prevent SQL injection:

def update_record(col, new_value, date, finance_manager_table, connection):
    finance_manager_table.execute(f"""
       UPDATE financemanager
       SET `{col}` = %s
       WHERE Date = %s
    """, (new_value, date))
    connection.commit()

or

def update_record(col, new_value, date, finance_manager_table, connection):
    finance_manager_table.execute("""
       UPDATE financemanager
       SET `{}` = %s
       WHERE Date = %s
    """.format(col), (new_value, date))
    connection.commit()

Nathan Roberts · Accepted Answer · 2021-12-28 14:00:06Z

0

I think the reason you were getting an error was because in the part of your query SET %s = %s, the first variable you were passing in was being interpreted as a string so it was surrounding it in single quotes. This was causing it not to be recognized as a column name. In other words it was turning that part of the query into SET 'Salary' = 2500

answered Dec 28, 2021 at 14:00

Nathan Roberts

8485 silver badges10 bronze badges

Collectives™ on Stack Overflow

Python MYSQL dynamic variables error with SQL syntax

3 Answers 3

2 Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

2 Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related