Bug #99576 MySQLCursorPrepared.executemany() not using multiple-row INSERT syntax

• Submitted: 14 May 2020 16:54
• Modified: 22 Jul 2022 15:40
• Reporter: Martin Humphries
• Status: Not a Bug
• Category: Connector / Python
• Severity: S3 (Non-critical)
• Version: 8.0.20
• OS: Windows
• 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 2022 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')