Mysql named placeholders in python used in the IN clause
Question: I prefer to use named placeholders when hacking MySQL bound python code, but
it seems that I can't get it just right with the `IN` clause. An example:

    
    
    con = MySQLdb.connect(db='test', user='test')
    cur = con.cursor()
    

Three too simple examples:

    
    
    # (#1) works fine, but I want placeholders.
    cur.execute( """ update test
                     set    i = 999
                     where  SNO in (1, 2) """)
    
    # (#2) works fine too, but still not enough placeholders.
    cur.execute( """ update test
                     set    i = %(i)s
                     where  SNO in (1, 2) """, {'i' : 999})
    
    # (#3) works, but did not pass the beauty check...
    cur.execute( """ update test
                     set    i = %(i)s
                     where  SNO in ( %(a)s, %(b)s ) """, {'i' : 99,
                                                          'a' : 1,
                                                          'b' : 2})
    

This is what I really want, but it fail with: **Operand should contain 1
column(s)**

    
    
    # (#4) This one fails with: _mysql_exceptions.OperationalError: (1241, 'Operand should contain 1 column(s)')
    cur.execute( """ update test
                     set    i = %(i)s
                     where  SNO in ( %(foo)s ) """, {'i'   : 999,
                                                     'foo' : [1, 2]})
    

Apparently I need _more magic_. It would be easy simply to move the problem to
the application, implementing a loop in python, but I would rather avoid that.

Ah yes, performance is important too.


Answer: `MySQLdb` already handles the escaping of a sequence for you:

    
    
    >>> con = MySQLdb.connect(db='test')
    >>> con.literal([1,2,3])
    ('1', '2', '3')
    >>> cur = con.cursor()
    >>> cur.execute("select * from test where id in %(foo)s", {'foo': [1,2,3]})
    3L
    >>> cur._executed
    "select * from test where id in ('1', '2', '3')"
    

So by removing the parenthesis around your placeholder it should work - but
only for sequences with more then one element, because a single element is
formatted like this:

    
    
    >>> con.literal([1])
    ('1',)
    

Inserted into a SQL query, the trailing comma makes it illegal SQL.

To work arount this, you could also define your own converter to convert a
custom type to the representation you like:

    
    
    import MySQLdb.converters
    conv = MySQLdb.converters.conversions.copy()
    
    class CustomList(list):
        def __init__(self, *items):
            super(CustomList, self).__init__(items)
    
    conv[CustomList] = lambda lst, conv: "(%s)" % ', '.join(str(item) for item in lst)
    
    con = MySQLdb.connect(db='test', conv=conv)
    cur = con.cursor()
    cur.execute('select * from test where id in %(foo)s', {'foo': CustomList(0, 1, 2)})
    print cur._executed
    
    
    
    select * from test where id in (0, 1, 2)
    

This way the quotes around the list items are gone.

It would also work to just replace the converter for `list`, but that would
change the behaviour for all lists and possibly introduce vulnerabilities. The
above way of formatting a list would not be safe for a list containing
strings, as it doesn't escape special characters.

To do that, you would have to recursively escape all items in the list:

    
    
    >>> ...
    >>> conv[list] = lambda lst, cv: "(%s)" % ', '.join(cv[type(item)](item, cv) for item in lst)
    >>> con = MySQLdb.connect(..., conv=conv)
    >>> con.literal([1, "it's working...", 2])
    "(1, 'it\\'s working...', 2)"
    

