MySQL Bugs: #69675: executemany fails when combined with built in functions such as now()

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

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.

Verified with 1.0 and 1.1.

Mikael,
Thanks for reporting this problem. We'll definitely have to give a better error message or simply drop to single inserts.

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

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 :)

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.