Bug #75824 executemany() should work with INSERT IGNORE
Submitted: 9 Feb 2015 9:24 Modified: 9 Feb 2015 11:12
Reporter: Takashi Ichii Email Updates:
Status: Verified Impact on me:
None 
Category:Connector / Python Severity:S3 (Non-critical)
Version:2.0.3 OS:Any
Assigned to: CPU Architecture:Any

[9 Feb 2015 9:24] Takashi Ichii
Description:
RE_SQL_INSERT_STMT doesn't match INSERT IGNORE and so executemany() doesn't work with it.
As a result, we can only get the last statement by cur.statement.

How to repeat:
>>> import mysql.connector
>>> conn = mysql.connector.connect()
>>> cur = conn.cursor()
>>> query = 'INSERT INTO foo (id) VALUES (%s)'
>>> params = [[1], [2]]
>>> cur.executemany(query, params)
>>> cur.statement
u'INSERT INTO foo (id) VALUES (1),(2)'
>>> query = 'INSERT IGNORE INTO foo (id) VALUES (%s)'
>>> params = [[1], [2]]
>>> cur.executemany(query, params)
>>> cur.statement
u'INSERT IGNORE INTO foo (id) VALUES (2)'

Suggested fix:
Following works with just only INSERT IGNORE, but needs more care
about such as comments.

--- ./cursor.py.orig      2015-01-23 10:00:04.000000000 +0900
+++ ./cursor.py    2015-02-09 18:20:06.206720246 +0900
@@ -38,7 +38,7 @@
     r'''\s*ON\s+DUPLICATE\s+KEY(?:[^"'`]*["'`][^"'`]*["'`])*[^"'`]*$''',
     re.I | re.M | re.S)
 RE_SQL_INSERT_STMT = re.compile(
-    r"({0}|\s)*INSERT({0}|\s)*INTO.+VALUES.*".format(SQL_COMMENT),
+    r"({0}|\s)*INSERT({0}|\s)*(?:IGNORE\s+)?INTO.+VALUES.*".format(SQL_COMMENT),
     re.I | re.M | re.S)
 RE_SQL_INSERT_VALUES = re.compile(r'.*VALUES\s*(\(.*\)).*', re.I | re.M | re.S)
 RE_PY_PARAM = re.compile(b'(%s)')
[9 Feb 2015 11:12] MySQL Verification Team
Hello Takashi Ichii,

Thank you for the report.

Thanks,
Umesh
[9 Feb 2015 11:12] MySQL Verification Team
//

[root@cluster-repo mysql-5.6.24]# rpm -qa|grep -i mysql-connector
mysql-connector-python-2.0.3-1.el6.noarch

[root@cluster-repo mysql-5.6.24]# python
Python 2.6.6 (r266:84292, Jan 22 2014, 01:49:05)
[GCC 4.4.7 20120313 (Red Hat 4.4.7-4)] on linux2
Type "help", "copyright", "credits" or "license" for more information.
>>> import mysql.connector
>>> con = mysql.connector.connect(user='root', password='',host='127.0.0.1',port=15000,database='test')
>>> cur = con.cursor()
>>> query = 'INSERT INTO foo (id) VALUES (%s)'
>>> params = [[1], [2]]
>>> cur.executemany(query, params)
>>> cur.statement
u'INSERT INTO foo (id) VALUES (1),(2)'
>>> query = 'INSERT IGNORE INTO foo (id) VALUES (%s)'
>>> params = [[1], [2]]
>>> cur.executemany(query, params)
>>> cur.statement
u'INSERT IGNORE INTO foo (id) VALUES (2)'
[13 Dec 2017 15:16] N S
I'm also having this issue. I've tried adding "(?:IGNORE\s+)?" to the regex in version 2.1.7 and it solves the issue.
[13 Dec 2017 15:21] N S
allow-insert-ignore-in-executemany

Attachment: allow-insert-ignore-in-executemany.patch (text/x-patch), 704 bytes.