Bug #69675 executemany fails when combined with built in functions such as now()
Submitted: 4 Jul 2013 22:56 Modified: 11 Jul 2013 17:13
Reporter: Mikael Rapp Email Updates:
Status: Closed Impact on me:
None 
Category:Connector / Python Severity:S3 (Non-critical)
Version:1.0.11 OS:Any
Assigned to: Geert Vanderkelen CPU Architecture:Any
Tags: executemany python connector

[4 Jul 2013 22:56] Mikael Rapp
Description:
executemany() fails if used with complex statements.

The regexp pattern (RE_SQL_INSERT_VALUES in cursor.py) used to fetch the VALUES(..) fields fails if it contains a built in function.

INSERT INTO log (ts, event, ip) VALUES(NOW(), "error"; "127.0.0.1")

Tested on Win7, Py 3.3
Additional keywords for the search engines: fails exception hasNone m.group(1) 

How to repeat:
mySQLCon = mysql.connector.connect(....)
cursor = mySQLCon.cursor()

stmnt = "INSERT INTO log (timestamp, event, ip) VALUES(NOW(), %s , %s)"

values = [
   ("error1", "127.0.0.1"),
   ("error2", "127.0.0.1"),
]

cursor.executemany(stmnt, value) #Fails

Suggested fix:
If the pattern isn't matched there should at least be a graceful exception with information about the limitations in the function(row 427@cursor.py in "_batch_insert")

Better solution 
Update (row 33 in cursor.py): 
RE_SQL_INSERT_VALUES = re.compile(
    r'VALUES\s*(\(\s*(?:%(?:\(.*\)|)s\s*(?:,|)\s*)+\))',
    re.I | re.M)
to cover more cases

Could probably be tricky to accommodate all complex cases that SQL allows for (nested functions, sub queries etc) but the most generic ones should be possible to cover with a more robust Regexp.
[8 Jul 2013 7:41] Geert Vanderkelen
Verified with 1.0 and 1.1.
[8 Jul 2013 7:43] Geert Vanderkelen
Mikael,
Thanks for reporting this problem. We'll definitely have to give a better error message or simply drop to single inserts.
[8 Jul 2013 8:01] Mikael Rapp
Just to add a use-case for the solution: If you drop to do iterative singe inserts  the user/developer should be notified of this (maybe though an exception that can be overridden with a config setting?). 

In my case i used the batch function to access a remote SQL server over a high latency connection. 
If the function name indications a batch behavior and the performance is that of multiple single queries( in may case over 100x performance diff) it can cause the developer to start optimizing the wrong things or worse, looking into completely different approaches when batch inserts does not live up to expectations.

Thanks for quick response, 
//Mikael
[8 Jul 2013 9:33] Geert Vanderkelen
Thanks for the suggestions.
I've revamped the regular expression(s) and made it much easier.
We're still testing some use cases, but the whole thing should work without any new option or exceptions. Well, it will hopefully work the way it was intended to work :)
[11 Jul 2013 17:13] Paul Dubois
Noted in 1.0.12, 1.1.1 changelogs.

The MySQLCursor.executemany() method raised an exception when a SQL 
function was used as a column value when executing an INSERT
statement.