Please suggest me the right way to use python variable in sql query

Question

I want to run below code, But I am getting syntax error.

cur.execute("USE Test") # select the database
cur.execute("SHOW TABLES")

for (table_name,) in cur:
    print(table_name)
    trunc_table="truncate table %s"
    cur.execute(trunc_table, table_name)
    con.commit()

This might be related to https://stackoverflow.com/questions/49971952/python-mysql-connector-and-parameterized-query. Basically You probably have to use python's string methods to add the table name. Only "values" can be parameterized. — 7 Reeds, May 10 '18 at 18:03

score 0 · Accepted Answer · answered May 10 '18 at 18:05

Prepared statement parameters can only be used where SQL allows expressions. Table names are not expressions, they have to be literals in the query. You can use string formatting to substitute into the string.

trunc_table = "truncate table %s" % table_name
cur.execute(trunc_table)

Also, I think you need to use a different cursor to execute the second query while you're looping through the results of the first query. So before the loop, do:

cur2 = connection.cursor()
cur2.execute("USE Test")

and then use

cur2.execute(trunc_table)

in the loop. Another option would be to use cur.fetchall() to get all the rows first, then you can reuse the cursor.

for (table_name,) in cur.fetchall():

Please suggest me the right way to use python variable in sql query

1 Answers1