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)