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...
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)?
If you would like to refer to this comment somewhere else in this project, copy and paste the following link:
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.
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)
If you would like to refer to this comment somewhere else in this project, copy and paste the following link:
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
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.
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)?
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
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)