RyMac RyMac - 1 year ago 90
SQL Question

Python MySQLdb Syntax Error with seemingly correct syntax

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?

Answer Source

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))
Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download