bug in cursors.py in 1.2.4

tcghost
2013-02-12
2013-02-18
  • tcghost
    tcghost
    2013-02-12

    the following code produces an error:

    import MySQLdb
    db = MySQLdb.connect()
    c = db.cursor()
    c.executemany("INSERT INTO tmp (date, id) VALUES (DATE(NOW()), %s);", ((1, ), (1, )))

    TypeError: not all arguments converted during string formatting

    while removing the DATE() fixes it:

    import MySQLdb

    db = MySQLdb.connect()
    c = db.cursor()
    c.executemany("INSERT INTO tmp (date, id) VALUES (NOW(), %s);", ((1, ), (1, )))

    works fine.

    Seems to be related to the insert_values regex in cursors.py. Works fine in 1.2.3

     
  • Andy Dustman
    Andy Dustman
    2013-02-12

    It's really hard to deal with full SQL expressions as arguments to execute or executemany, since there can be any number of nested parenthesis, intermixed with string literals. 1.2.3 had different parsing bugs...

    What's probably needed is a real parser and not a regex. See http://stackoverflow.com/questions/5454322/python-how-to-match-nested-parentheses-with-regex for some solutions.

     
  • tcghost
    tcghost
    2013-02-16

    Hi Andy, thanks for your response, we worked around the issue in our code for now.

    Can you tell me the reason why you need to parse out the individual insert values so that I fully understand the issue?

    After looking at the code in cursors.py it seems that you try to get everything in between the parenthesis in "insert ... values (%s)" and then join that up with commas after calling db.literal() on each set of parameters for it to become "insert ... values (%s,%s,%s...)".

    In what case would you actually need to parse each individual argument in the SQL rather than just use the full argument set (ie, why would a simple r'\svalues\s*((.+))' not work)?

     
  • Andy Dustman
    Andy Dustman
    2013-02-18

    It's only needed so that executemany can turn the INSERT statement into a multi-row INSERT. The VALUES part is then repeated for each row being inserted, and a single statement is executed. The execute method doesn't try to pick apart the query at all.

    http://dev.mysql.com/doc/refman/5.5/en/insert.html

     
  • Andy Dustman
    Andy Dustman
    2013-02-18

    Also, r'\svalues*((.+))' can match other things (potentially things in string literals) an may fail to match the entire VALUES clause, specifically because of expressions which may have nested parenthesis or string literals, and also it's legal to use dictionary-type string interpolation, i.e. VALUES (%(key1)s, %(key2)s)