CODEWITHSUNDEEP
pythonmysqlvariables
tomstephens89
tomstephens89

Reputation: 123

Python variable in SQL statement?

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

Upvotes: 0

Views: 3689

Answers (1)

arodriguezdonaire
arodriguezdonaire

Reputation: 5562

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

Upvotes: 4

Related Questions