Python string formatting on MySQL query

Question

I'm trying to execute a query using two parameters, one is a list and the other is a number.

Here is my code:

cursor.execute("SELECT cod_art, price_EU, price_third_country 
 FROM lt_gamma_articles 
 WHERE cod_art in (%s) AND cod_store = %d 
 ORDER BY cod_art, timestamp" 
 % format_strings, tuple(cod_art_int), int(shop) )

I get this error:

TypeError: not enough arguments for format string

I think the error is in the string formatting, but I don't know how to format it correctly and I've been stuck for a while.

Answer 1

From the looks of your code, I'm gussing your basing it off of imploding a list for use in a python MySQLDB IN clause. Assuming that's the case, and format_strings is built similar to:

format_strings = ','.join(['%s'] * len(cod_art_int))

I would use .format to build the query string, and build up a list for the positional query parameters:

query_params = list(cod_art_int)
query_params.append(int(shop))
query_sql = """
 SELECT cod_art, price_EU, price_third_country 
 FROM lt_gamma_articles
 WHERE cod_art IN ({cod_art_list}) AND cod_store = %s 
 ORDER BY cod_art, timestamp
""".format(cod_art_list=format_strings)
cursor.execute(query_sql, query_params)

---

Explanation:

Say cod_art_int is this list of integer values:

cod_art_int = [10, 20, 30]

In order to use those values in the cod_art IN (...) part of the query, you need to add a %s for each one. That is done by:

format_strings = ','.join(['%s'] * len(cod_art_int))

Which can be broken down to:

step_one = ['%s'] * len(cod_art_int)
# ['%s', '%s', '%s']
format_strings = ','.join(step_one)
# '%s,%s,%s'

When you build the final query string, you replace {cod_art_list} with format_strings:

query_sql = """
 SELECT cod_art, price_EU, price_third_country 
 FROM lt_gamma_articles
 WHERE cod_art IN ({cod_art_list}) AND cod_store = %s 
 ORDER BY cod_art, timestamp
""".format(cod_art_list=format_strings)

And you get the query string:

SELECT cod_art, price_EU, price_third_country 
FROM lt_gamma_articles
WHERE cod_art IN (%s,%s,%s) AND cod_store = %s 
ORDER BY cod_art, timestamp

Then your query parameters will be safely substituted within the query to replace the %ss. You build up the parameter list to correspond to the %ss. Since cod_art IN (%s,%s,%s) is first, you add that to the list first, followed by the value for cod_store (int(shop) which I'm going to say is 456):

query_params = list(cod_art_int)
# [10, 20, 30] 
query_params.append(int(shop))
# [10, 20, 30, 456] 

In the end you execute the query with its parameters:

cursor.execute(query_sql, query_params)

• Albert S.: Bingo! It worked! Thank you very much! Do you have a source like a link or something for me to understand this ?
• ohmu: @AlbertS. I don't have a good link, but see my update to the answer.