python MySQL connector and parameterized query

Question

Python 3.6 / MySQL 5.6

I am still pretty new to python though I've used MySQL in other coding languages for sometime. In a dev environment I want to drop all tables in a particular database. I could drop the database but the hosting provider locks down some MySQL controls so dropping "databases" is possible from the command line or code but creating them is only allowed through their management web panel. That is a time consuming pain. I can drop/create the tables from command line or code much easier.

I wrote a python script that I can call from a Makefile when i want to clean/restart the project.

import os
import mysql.connector.django

DBI = mysql.connector.connect(
    option_files=os.path.join(os.path.expanduser("~"), ".my.cnf"),
    option_groups="membersdev"
)

cursorFind = DBI.cursor(buffered=True)
cursorDrop = DBI.cursor(buffered=True)

query = """
select TABLE_NAME
from information_schema.TABLES
where TABLE_SCHEMA = %s
"""
cursorFind.execute(query, ('dev_site_org',))

query2 = "DROP TABLE IF EXISTS %s"

for tableName in cursorFind.fetchall():
    cursorDrop.execute(query2, tableName)

cursorDrop.close()
cursorFind.close()

DBI.close()

I am pretty sure that "query2" is syntactically correct with a parameter. I think that cursorDrop.execute(query2, tableName) is correct since tableName is a tuple; but, I keep getting the exception and stack trace:

Traceback (most recent call last):
  File "/home/me/.pyenv/versions/3.6.3/lib/python3.6/site-packages/mysql/connector/connection_cext.py", line 377, in cmd_query
    raw_as_string=raw_as_string)
_mysql_connector.MySQLInterfaceError: 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 ''My_First_Table'' at line 1

During handling of the above exception, another exception occurred:

Traceback (most recent call last):
  File "misc_scripts/db_delete.py", line 35, in <module>
    cursorDrop.execute(query2)
  File "/home/me/.pyenv/versions/3.6.3/lib/python3.6/site-packages/mysql/connector/cursor_cext.py", line 264, in execute
    raw_as_string=self._raw_as_string)
  File "/home/me/.pyenv/versions/3.6.3/lib/python3.6/site-packages/mysql/connector/connection_cext.py", line 380, in cmd_query
    sqlstate=exc.sqlstate)
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 ''My_First_Table'' at line 1

Is there something special I need to do to access the table name from the select result tuple? Do I have to order the queries or the executes differently? Is there a statement "prepare" that I am missing?

Can you try `cursorDrop.execute(query2, tableName[1:-1])`, that double `'` is really weird. — Ken Lee, May 04 '18 at 02:33
This is a duplicate of https://stackoverflow.com/questions/9394291/python-and-mysqldb-substitution-of-table-resulting-in-syntax-error/9394450#9394450 — Ilja Everilä, May 04 '18 at 04:46

georgexsh · Accepted Answer · 2018-05-06T16:18:28.360

In MySQL, schema objects, unlike SQL parameters, have a different quotation rule, the quote mark for schema object is the backtick (`):

An identifier may be quoted or unquoted. If an identifier contains special characters or is a reserved word, you must quote it whenever you refer to it. (Exception: A reserved word that follows a period in a qualified name must be an identifier, so it need not be quoted.) Reserved words are listed at Section 9.3, “Keywords and Reserved Words”.

...

The identifier quote character is the backtick (`):

you could modify your code like this:

query2 = "DROP TABLE IF EXISTS `%s`" 
...
    cursorDrop.execute(query2 % tableName)

see more on MySQL doc.

score 1 · Answer 2 · answered May 04 '18 at 04:41

Instead of using the Execute method of filling in the table name, use basic python string primitives construct the string for the DROP statement. This way you won't get extra quotes around the table name. (Such would give you a syntax error.) Then simply

cursorDrop.execute(query2)

Another issue: You need to do the equivalent of USE db_name after connecting and before performing the DROPs.

python MySQL connector and parameterized query

2 Answers2

Linked