Bug #99576 MySQLCursorPrepared.executemany() not using multiple-row INSERT syntax
Submitted: 14 May 2020 16:54 Modified: 22 Jul 15:40
Reporter: Martin Humphries Email Updates:
Status: Not a Bug Impact on me:
None 
Category:Connector / Python Severity:S3 (Non-critical)
Version:8.0.20 OS:Windows
Assigned to: CPU Architecture:Any
Tags: executemany, MySQLCursorPrepared, prepared statement

[14 May 2020 16:54] Martin Humphries
Description:
MySql Server Version:  5.7.13
MySql Connector Version: 8.0.20
Python Version: 3.8.2
System: Windows 7 x64 SP1

Issue: MySQLCursorPrepared.executemany() not using multiple-row INSERT syntax.

From the documentation I expected to be able to use multiple-row syntax for a prepared statement, but I have been unable to achieve this.  I have been comparing the server 'GENERAL QUERY LOG' items to what I expected to see

MySQLCursor.executemany() Method:
"An optimization is applied for inserts: The data values given by the parameter sequences are batched using multiple-row syntax."
https://dev.mysql.com/doc/connector-python/en/connector-python-api-mysqlcursor-executemany...

How to repeat:
See attached python script.
[14 May 2020 16:56] Martin Humphries
Replicates the issue

Attachment: BugReport_MySQLCursorPrepared_Executemany.py (text/plain), 2.50 KiB.

[15 May 2020 10:16] MySQL Verification Team
Hello Martin Humphries,

Thank you for the report and feedback.

regards,
Umesh
[22 Jul 15:40] Nuno Mariz
Posted by developer:
 
This is the expected behaviour for prepared statements. Like you observed in your tests, the statement is prepared and then executed multiple times with the data provided.

# Prepare    INSERT INTO employees (first_name, hire_date) VALUES (?, ?)
# Reset stmt    
# Execute    INSERT INTO employees (first_name, hire_date) VALUES ('JaneForCurPrep', '2005-02-12')
# Reset stmt    
# Execute    INSERT INTO employees (first_name, hire_date) VALUES ('JoeForCurPrep', '2006-05-23')
# Reset stmt    
# Execute    INSERT INTO employees (first_name, hire_date) VALUES ('JohnForCurPrep', '2010-10-03')