imploding a list for use in a python MySQLDB IN clause
Though this question is quite old, thought it would be better to leave a response in case someone else was looking for what I wanted
Accepted answer gets messy when we have a lot of the params or if we want to use named parameters
After some trials
ids = [5, 3, ...] # list of ids
cursor.execute('''
SELECT
...
WHERE
id IN %(ids)s
AND created_at > %(start_dt)s
''', {
'ids': tuple(ids), 'start_dt': '2019-10-31 00:00:00'
})
Tested with python2.7
, pymysql==0.7.11
Use the list_of_ids
directly:
format_strings = ','.join(['%s'] * len(list_of_ids))
cursor.execute("DELETE FROM foo.bar WHERE baz IN (%s)" % format_strings,
tuple(list_of_ids))
That way you avoid having to quote yourself, and avoid all kinds of sql injection.
Note that the data (list_of_ids
) is going directly to mysql's driver, as a parameter (not in the query text) so there is no injection. You can leave any chars you want in the string, no need to remove or quote chars.
This appears to still be a problem with Python3 in 2021, as pointed out in the comment by Rubms to the answer by markk.
Adding about 9 lines of code to the method "_process_params_dict" in "cursor.py" in the mysql connector package to handle tuples solved the problem for me:
def _process_params_dict(self, params):
"""Process query parameters given as dictionary"""
try:
to_mysql = self._connection.converter.to_mysql
escape = self._connection.converter.escape
quote = self._connection.converter.quote
res = {}
for key, value in list(params.items()):
if type(value) is tuple: ### BEGIN MY ADDITIONS
res[key.encode()] = b''
for subvalue in value:
conv = subvalue
conv = to_mysql(conv)
conv = escape(conv)
conv = quote(conv)
res[key.encode()] = res[key.encode()] + b',' + conv if len(res[key.encode()]) else conv
else: ### END MY ADDITIONS
conv = value
conv = to_mysql(conv)
conv = escape(conv)
conv = quote(conv)
res[key.encode()] = conv
except Exception as err:
raise errors.ProgrammingError(
"Failed processing pyformat-parameters; %s" % err)
else:
return res