Python and MySQLdb: substitution of table resulting in syntax error

Question

I need to dynamically change tables and variables from time to time, so I wrote a python method like this:

    selectQ ="""SELECT * FROM  %s WHERE %s = %s;""" 
    self.db.execute(selectQ,(self.table,self.columnSpecName,idKey,))
    return self.db.store_result()

However this results in a syntax error exception. I tried debugging it so I printed the variables in the method and filled them in manually, and that worked. So I am not sure what I am doing wrong ?

Is it because I try to use a substitute for a table ?

Also how do I debug mysqldb so it prints the substituted query as a string ?

score 11 · Accepted Answer · answered Feb 22 '12 at 12:09

11

Parameter substitution in the DB API is only for values - not tables or fields. You'll need to use normal string substitution for those:

selectQ ="""SELECT * FROM  %s WHERE %s = %%s;""" % (self.table,self.columnSpecName)
self.db.execute(selectQ,(idKey,))
return self.db.store_result()

Note that the value placeholder has a double % - this is so that it's left alone by the initial string substitution.

answered Feb 22 '12 at 12:09

Daniel Roseman

567,968
59
825
842

If I understand correctly it will substitute the %s with a string and it will just drop off a % sign of %%s ? – Lucas Kauffman Feb 22 '12 at 12:14
ow btw, apperently you need to typecast the selectQ after substitution to string, but that might be because I also used some integers. – Lucas Kauffman Feb 22 '12 at 12:31
Is this documented somewhere? In a query like `SHOW COLUMNS FROM Table`, it's no unreasonable to think of `Table` as a parameter. (But +1 for the answer. This is a hard question to search for.) – Joshua Taylor Nov 21 '14 at 15:48

score 1 · Answer 2 · edited May 26 '12 at 09:47

1

Here is a full working example

def rtnwkpr(tick, table, col):

    import MySQLdb as mdb
    tickwild = tick + '%'       
    try:
        con = mdb.connect(host, user, password, db);
        cur = con.cursor()
        selectq = "SELECT price FROM %s WHERE %s LIKE %%s;" % (table, col)
        cur.execute(selectq,(tickwild))
        return cur.fetchall()

edited May 26 '12 at 09:47

Martijn Pieters

963,270
265
3,804
3,187

answered May 25 '12 at 21:43

Peter Rogers

9
2

def inswk(table, total, date, tick): import MySQLdb as mdb import sys con = None try: con = mdb.connect(host, user, password, db); cur = con.cursor() selectq = """INSERT INTO %s (price, date, ticker) VALUES (%%s, %%s, %%s)""" % (table) cur.execute(selectq,(total, date, tick)) – Peter Rogers May 29 '12 at 21:38

score 0 · Answer 3 · answered Feb 22 '12 at 12:10

0

You'll have to use string substitution to add the table and column names, the driver will only handle parameters.

Ed: NM, Daniel answered faster and more completely

answered Feb 22 '12 at 12:10

Fredrik Håård

2,739
1
23
32

score -3 · Answer 4 · answered Feb 22 '12 at 12:02

Did you mean to write:

selectQ = """SELECT * FROM %s WHERE %s = %s;""" % (self.table,self.columnSpecName,idKey) #maybe the idkey should be self.idkey? don't know the rest of the code

self.db.execute(selectQ)

and this is just a mistake with string formatting?

Btw why do you write explicit SQL for this kind of work? better use magical sqlalchemy for python sql manipulation..

Python and MySQLdb: substitution of table resulting in syntax error

4 Answers4

Linked

Related