Automagically expanding a Python list with formatted output

Does anyone know if there's a way to automatically expand a list in Python, separated by commas? I'm writing some Python code that uses the MySQLdb library, and I'm trying to dynamically update a list of rows in a MySQL database with certain key values.

For instance, in the code below, I'd like to have the numeric values in the record_ids list expand into a SQL "IN" clause.

import MySQLdb
record_ids = [ 23, 43, 71, 102, 121, 241 ]

mysql = MySQLdb.connect(user="username", passwd="secret", db="apps")
mysql_cursor = mysql.cursor()

sqlStmt="UPDATE apps.sometable SET lastmod=SYSDATE() where rec_id in ( %s )"

mysql_cursor.execute( sqlStmt, record_ids )
mysql.commit()

Any help would be appreciated!

Answers


try:

",".join( map(str, record_ids) )

",".join( list_of_strings ) joins a list of string by separating them with commas

if you have a list of numbers, map( str, list ) will convert it to a list of strings


Further to the given answers, note that you may want to special case the empty list case as "where rec_id in ()" is not valid SQL, so you'll get an error.

Also be very careful of building SQL manually like this, rather than just using automatically escaped parameters. For a list of integers, it'll work, but if you're dealing with strings received from user input, you open up a huge SQL injection vulnerability by doing this.


I do stuff like this (to ensure I'm using bindings):

sqlStmt=("UPDATE apps.sometable SET lastmod=SYSDATE() where rec_id in (%s)"
    % ', '.join(['?' for n in record_ids]))

mysql_cursor.execute(sqlStmt, record_ids)
mysql.commit()

This works for all dynamic lists you want to bind without leaving you susceptible to SQL injection attacks.


Slightly different alternative to Dustin's answer:

sqlStmt=("UPDATE apps.sometable SET lastmod=SYSDATE() where rec_id in (%s)"
    % ', '.join(['?' * len(record_ids])))

Alternitavely, using replace:

sqlStmt="UPDATE apps.sometable SET lastmod=SYSDATE() where rec_id in " +
    record_ids.__str__().replace('[','(').replace(']',')')

Need Your Help

Input blur not working

javascript jquery input blur onblur

I am trying to add a .success class to an input field whenever I click out of the input field. However, when I click off of the input the .required class is still showing even though I have removed...

Collections.emptyList() returns a List<Object>?

java generics type-inference

I'm having some trouble navigating Java's rule for inferring generic type parameters. Consider the following class, which has an optional list parameter: