Simple "CREATE" or "DROP TABLE" fails with PyMySQL and parameter replacement

Question

I'm basically using the following code to drop an existing table using Python 3.6 and PyMySQL, with a MySQL 5.7 database:

connection = pymysql.connect(
    host=host,
    user=username,
    password=password,
    db=database,
    charset='utf8mb4',
    cursorclass=pymysql.cursors.DictCursor
)

table_name = "video_playback_statistics"
sql = "DROP TABLE IF EXISTS %s"
params = (table_name,)

with connection.cursor() as cursor:
    cursor.execute(sql, params)

I get the following 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 ''video_playback_statistics'' at line 1"

I've successfully executed other, more complicated SQL statements with more parameters, but this simple one does not work.

When I run the SQL command without parametrization, it works fine:

cursor.execute('DROP TABLE IF EXISTS video_playback_statistics')

I've looked at the following questions:

• 1064, "You have an error in your SQL syntax;..." Python MySQL
• pymysql return error SQL syntax

But these kinds of queries seem to work fine.

Also I've seen this:

• Python MYSQL update statement

But here the table name is directly written into the string.

Answer 1

Apparently the parametrization of table names or column names is not possible, according to this comment. The adapter, for some reason, inserts two double quotes for the value, which causes a syntax error.

Two possible solutions would be:

1. Use backslash escaping for the parameter:

   sql = "DROP TABLE IF EXISTS `%s`"
   

2. Use Python string formatting:

   sql = "DROP TABLE IF EXISTS {}".format(table_name)
   

For values (e.g. in INSERT or WHERE statements), you should still use parametrization.