| Bug #75824 | executemany() should work with INSERT IGNORE | ||
|---|---|---|---|
| Submitted: | 9 Feb 2015 9:24 | Modified: | 16 Nov 2022 18:00 |
| Reporter: | Takashi Ichii | Email Updates: | |
| Status: | Closed | Impact on me: | |
| Category: | Connector / Python | Severity: | S3 (Non-critical) |
| Version: | 2.0.3 | OS: | Any |
| Assigned to: | CPU Architecture: | Any | |
[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.
[6 May 2021 2:08]
Dan Smythe
A simple workaround for this, since that regex allows "sql comments" is to use a mysql comment for your IGNORE keyword, such as: INSERT /*! IGNORE */ INTO foo (id) VALUES (%s) This will get the regex to fire and your bulk insert statements to generate with the IGNORE keyword and without having to modify the connector source.
[7 Nov 2022 21:35]
Nuno Mariz
Posted by developer: Thank you for your contribution.
[16 Nov 2022 18:00]
Philip Olson
Posted by developer: Fixed as of the upcoming MySQL Connector/Python 8.0.32 release, and here's the proposed changelog entry from the documentation team: Not all parameters were added to the INSERT statement when using INSERT IGNORE with cursor.executemany(). Thanks to Takashi Ichii for the contribution. Thank you for the bug report.
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)')