Python execute mysql query with dynamic column names-CodePudding

I have python code to update a mysql table

cur.execute("SELECT * FROM wpkv_newsletter")

count =1
for row in cur.fetchall():
    list_num=count! 1
    col_name='list_' str(list_num)
   
    query='''UPDATE wpkv_newsletter SET %s = 1 WHERE id = %s'''
    
    cur.execute(query,(col_name.replace(" ' " , " "),count))
  
    count =count 1

But I got this error

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 ''list_2' = 1 WHERE id = 1' at line 1")

Obliviously the query it executed was

UPDATE wpkv_newsletter SET 'list_1'= 1 WHERE id = 1;

MySQL cannot accept a column name with '' quotation mark. How can I execute the query in Python 3.8 without quotation mark?

CodePudding user response：

Query parameters are always treated as string values.

You cannot use a query parameter for an identifier (e.g. a column name).

Therefore you must format the SQL query with the column name as a fixed part of the query string, not as a dynamic parameter.

col_name='list_' str(list_num)

query=f'''UPDATE wpkv_newsletter SET {col_name} = 1 WHERE id = %s'''

This carries some risk of SQL injection if the variable comes from an untrusted source. But in your case, the variable for the column name is under the control of your code, so it's safe.

CodePudding user response：

I got it work with this way

    col_name='list_' str(list_num)
   
    query='UPDATE wpkv_newsletter SET '  col_name    '=1 WHERE id = %s' 
   
    cur.execute(query,(count))