Python challenging string encoding

Question

I have the following list of providers (in Russian):

providers = [u'\u041e\u041e\u041e "\u041a\u0432\u0430\u0440\u0442\u0430\u043b 
 \u041b\u0435\u043e\u043f\u043e\u043b\u0438\u0441"', 
 u'\u0426\u0435\u043d\u0442\u0440\u0430\u043b']

These are obviously in unicode. Previously, to do a SQL SELECT, I was doing:

providers = tuple([str(item) for item in providers])
sql += " WHERE provider IN {} GROUP BY date ORDER BY date ASC".format(repr(providers))
cursor.execute(sql,)

Now, since the list items are in unicode, I run into a UnicodeEncodeError.

How would I correctly do this sql statement?

Answer 1

You should not use .format() to include values in a sql query. Use sql parameters instead:

sql += " WHERE provider IN ({}) GROUP BY date ORDER BY date ASC".format(', '.join(['%s'] * len(providers)))
cursor.execute(sql, providers)

where providers is the original list.

The idea is to generate a SQL query with the in test using SQL parameter syntax matching the number of providers in your list: WHERE provider in (%s, %s) ... for a two-provider list. Yes, the MySQLdb sql parameter syntax echoes the old-style python formatting syntax, but is not the same thing.