Ahmed Al-haddad Ahmed Al-haddad - 1 year ago 55
Python Question

What is the correct way to use .format when passing other variables in the select query for MySQL?

What is the correct way to use .format when I want to pass other variables in the select query for MySQL?

For example I have this query that I can't figure out how to pass all the parameters together correctly

a = "c9" # this is for %s
b = "TEMP" # this is for the first {}
c = "1 hour" # this is for the last {}
c.execute("SELECT Client, {}, Date_Time from data where Client = %s and Date_Time > DATE_SUB(NOW(), INTERVAL {}".format(b,a,c)) # This doesn't work


I also tried many other variations but they didn't work as well, I am still getting the
1064
error. So, what's the correct way?!

Answer Source

Try this:

>>> a = "c9" # this is for %s
>>> b = "TEMP" # this is for the first {}
>>> c = "1 hour" # this is for the last {}
>>> sql = "SELECT Client, {}, Date_Time from data where Client = \"{}\" and Date_Time > DATE_SUB(NOW(), INTERVAL {})".format(b,a,c)
>>> sql
'SELECT Client, TEMP, Date_Time from data where Client = "c9" and Date_Time > DATE_SUB(NOW(), INTERVAL 1 hour)'
>>>
>>> cur = db.cursor()
>>> cur.execute(sql)  
>>> data = cur.fetchall()   
>>> cur.close()