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")

cursor.execute("SELECT temp, time FROM %s", (tablename,)) Is that an extra comma after tablename ? — Stiffo
– Stiffo, Commented Jul 28, 2015 at 13:18
If it's a tuple, that will result in extra ' ' characters, resulting in a broken SQL statement, wont it? — Stiffo
– Stiffo, Commented Jul 28, 2015 at 13:22
It looks like extra '' are being added in the error... Any ideas? — tomstephens89
– tomstephens89, Commented Jul 28, 2015 at 13:26
@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 :-) — Steve Jessop
– Steve Jessop, Commented Jul 28, 2015 at 13:33

The6thSense · Accepted Answer · 2015-07-28 13:26:06Z

4

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,))

edited Jul 28, 2015 at 13:26

The6thSense

8,3459 gold badges38 silver badges67 bronze badges

answered Jul 28, 2015 at 13:23

anrodon

5,6211 gold badge29 silver badges51 bronze badges

Sign up to request clarification or add additional context in comments.

6 Comments

Matthias Over a year ago

Are you sure you can use the table name as a parameter?

tomstephens89 Over a year ago

Everywhere I have read says that both ? %s are usable depending on what driver I use?

anrodon Over a year ago

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 Over a year ago

try this: cursor.execute("SELECT temp, time FROM ?", [tablename])

anrodon Over a year ago

@tomstephens89 was its useful for you? Tell me please so then I'll can update the answer for future people with the same doubt.

|

Collectives™ on Stack Overflow

Python variable in SQL statement?

1 Answer 1

6 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

6 Comments

Your Answer

Sign up or log in

Post as a guest

Related