Python MySQLdb Syntax Error with seemingly correct syntax

Question

I'm trying to do a simple retrieval of a column named N_NUMBER in a MySQL table using the following code:

from os.path import join, dirname
from dotenv import load_dotenv
import MySQLdb    

TABLE_NAME = 'testmaster'

dotenv_path = join(dirname(__file__), '.env')
load_dotenv(dotenv_path) # loads the .env

dbName = os.environ.get('DB_NAME')
mydb = MySQLdb.connect(host=os.environ.get('DB_HOST'), user=os.environ.get('DB_USER'), passwd=os.environ.get('DB_PASS'), db=dbName)
cursor = mydb.cursor()

cursor.execute("SELECT N_NUMBER FROM %s", (TABLE_NAME,))

I continue to get the same Traceback every time, which is:

Traceback (most recent call last):
  File "updateMaster.py", line 101, in <module>
    cursor.execute("SELECT 'N_NUMBER' FROM %s", (TABLE_NAME,))
  File "/Library/Frameworks/Python.framework/Versions/2.7/lib/python2.7/site-packages/MySQLdb/cursors.py", line 205, in execute
    self.errorhandler(self, exc, value)
  File "/Library/Frameworks/Python.framework/Versions/2.7/lib/python2.7/site-packages/MySQLdb/connections.py", line 36, in defaulterrorhandler
    raise errorclass, errorvalue
_mysql_exceptions.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 ''testmaster'' at line 1")

I've also tried the syntax with pyformat style as recommended by the docs for the .execute() method, but still received the same error. The pyformat style looked like:

cursor.execute("SELECT N_NUMBER FROM %(table_name)s", {'table_name':TABLE_NAME})

When I try directly inserting the table name into the command string, the command works, leading me to suspect that it has something to do with the %s. What's going wrong?

You cannot parameterize a table name: http://stackoverflow.com/questions/15255694/python-mysqldb-execute-table-variable (same goes for column names as well). — alecxe, Jul 26 '16 at 13:35
Thank you! I knew this question was probably asked somewhere but I haven't been able to find it. — Ryan M, Jul 26 '16 at 13:39

score 2 · Accepted Answer · answered Jul 26 '16 at 13:35

2

You cannot parametrize table nor column names. While being cognisant of the possibility of SQL Injection attacks, instead do:

cursor.execute("SELECT N_NUMBER FROM {}".format(TABLE_NAME))

answered Jul 26 '16 at 13:35

mechanical_meat

163,903
24
228
223

Worked perfectly, thank you :) – Ryan M Jul 26 '16 at 13:39
Cheers, mate. Happy coding to you. – mechanical_meat Jul 26 '16 at 13:40

Python MySQLdb Syntax Error with seemingly correct syntax

1 Answers1