Python and MySQLdb: substitution of table resulting in syntax error

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 ?

Answers:

Thank you for visiting the Q&A section on Magenaut. Please note that all the answers may not help you solve the issue immediately. So please treat them as advisements. If you found the post helpful (or not), leave a comment & I’ll get back to you as soon as possible.

Method 1

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.

Method 2

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

Method 3

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

Method 4

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..

All methods was sourced from stackoverflow.com or stackexchange.com, is licensed under cc by-sa 2.5, cc by-sa 3.0 and cc by-sa 4.0