Python variable in SQL statement?

Question

I am trying to pass a variable to an SQL statement which I will eventually use in an iterator in order to process a list of key values and store in a CSV, however I am having trouble getting the variable into the statement?

Here is my code:

import MySQLdb as mdb
from MySQLdb import cursors
import csv
con = mdb.connect('172.16.7.50', 'root', 'abcd2014', 'templog')
tablename = 'pitemp'
with con:
 cursor = con.cursor()
 cursor.execute("SELECT temp, time FROM %s", (tablename,))
fid = open('new.csv','w')
writer = csv.writer(fid, delimiter=',')
writer.writerow([ i[0] for i in cursor.description ]) # heading row
writer.writerows(cursor.fetchall())
print 'finished!'

I have tried a selection of different bracket combinations as found on stack overflow but they all result in the following error:

Traceback (most recent call last):
File "/home/tom/PycharmProjects/TomsSQL2CSV/sql2csv.py", line 11, in <module>
cursor.execute("SELECT temp, time FROM %s", (vari,))
File "/home/tom/.local/lib/python2.7/site-packages/MySQLdb/cursors.py", line 205, in execute
self.errorhandler(self, exc, value)
File "/home/tom/.local/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 ''pitemp'' at line 1")

• Stiffo: cursor.execute("SELECT temp, time FROM %s", (tablename,)) Is that an extra comma after tablename ?
• deceze: @Stiffo Yes, because that's a tuple.
• Stiffo: If it's a tuple, that will result in extra ' ' characters, resulting in a broken SQL statement, wont it?
• tomstephens89: It looks like extra '' are being added in the error... Any ideas?
• Steve Jessop: @Stiffo: "SELECT temp, time FROM %s" and (tablename,) are two different parameters to execute(), and that method doesn't apply the % operator between the two of them. execute() takes a tuple even if there's only one value to substitute. Actually "SELECT temp, time FROM %s" % (tablename,) wouldn't have parentheses in the result, because the % operator treats tuples specially, but it's not relevant either way :-)

Answer 1

You should be using '?' for parameter bindings in your sql string not python format specifiers (you are after all writing sql here not python).

cursor.execute("SELECT temp, time FROM ?", (tablename,))

• Matthias: Are you sure you can use the table name as a parameter?
• tomstephens89: Everywhere I have read says that both ? %s are usable depending on what driver I use?
• anrodon: You're using SQL parameters, not Python parameters, if you use %s MySQL will read it like that and ignore your parameter, the way to tell MySQL that you want to put a parameter there is with '?'. You can use %s for Python parameters but no for SQL parameters
• anrodon: try this: cursor.execute("SELECT temp, time FROM ?", [tablename])
• anrodon: @tomstephens89 was its useful for you? Tell me please so then I'll can update the answer for future people with the same doubt.