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

Question

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?!

@RushyPanchal http://www.inmotionhosting.com/support/website/database-troubleshooting/error-1064 — Ahmed Al-haddad, May 01 '16 at 19:30
You miss a right parentheses `)`. It should be `DATE_SUB(NOW(), INTERVAL {}` **)**. Pls refer to my answer for the detailed description. — SparkAndShine, May 01 '16 at 19:31

SparkAndShine · Accepted Answer · 2016-05-02T12:22:42.940

2

In you case,

miss a right parenthesis );
mix up the usage in str.format and format % values

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

edited May 02 '16 at 12:22

answered May 01 '16 at 19:14

SparkAndShine

17,001
22
90
134

Thank you sir. This worked like a boss. Why did we need the `\"{}\"` for the second variable tho? – Ahmed Al-haddad May 01 '16 at 19:36
`Client = {}` will be `Client=c9`. I suppose `c9` should be a string. But wait, you miss a `=`? `Client == "C9"`? – SparkAndShine May 01 '16 at 19:38
Oh now I get it. No no the query only needs on `=` so `Client = \"{}\"` is actually correct – Ahmed Al-haddad May 01 '16 at 19:48

score 1 · Answer 2 · edited May 23 '17 at 11:52

You want to use a single formatting system - {} is a newer style formatting whereas %s is older.

Change this

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

to

c.execute("SELECT Client, {}, Date_Time from data where Client = {} and Date_Time > DATE_SUB(NOW(), INTERVAL {}".format(b,a,c))

You can also used named fields:

c.execute("SELECT Client, {field}, Date_Time from data where Client = {client} and Date_Time > DATE_SUB(NOW(), INTERVAL {interval}".format(
    field = b, client = a, interval = c))

However, it's not always the best idea to use string interpolation (i.e formatting) in queries, because of SQL injection attacks and other exploits that can occur. You need to sanitize the user data before passing it into the query – libraries conforming to the DB API should do this for you. See this answer for a more concrete example.

Thank you sir. I tried your answer first but I had error 1054: `Unknown column 'c9' in 'where clause'`. The other one worked for me. — Ahmed Al-haddad, May 01 '16 at 19:35

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

2 Answers2