Ahmed Al-haddad Ahmed Al-haddad - 7 months ago 21
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

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