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?
asked Dec 7, 2012 at 20:34
David542
112k211 gold badges584 silver badges1.1k bronze badges
1 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.
answered Dec 7, 2012 at 20:36
Martijn Pieters
1.1m326 gold badges4.2k silver badges3.5k bronze badges
Sign up to request clarification or add additional context in comments.
Comments
default