8000 Regular expression in executemany doesn't support new alias syntax for INSERT ... ON DUPLICATE KEY UPDATE · Issue #898 · PyMySQL/PyMySQL · GitHub
[go: up one dir, main page]
Skip to content

Regular expression in executemany doesn't support new alias syntax for INSERT ... ON DUPLICATE KEY UPDATE #898

New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

Sign up for GitHub

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

Jump to bottom
8000
Open
grasslasts opened this issue Sep 23, 2020 · 6 comments · May be fixed by #1039
Open

Regular expression in executemany doesn't support new alias syntax for INSERT ... ON DUPLICATE KEY UPDATE #898

grasslasts opened this issue Sep 23, 2020 · 6 comments · May be fixed by #1039
Labels
Feature Request

Comments

@grasslasts
Copy link

Regular expression in executemany

RE_INSERT_VALUES = re.compile(
    r"\s*((?:INSERT|REPLACE)\b.+\bVALUES?\s*)" +
    r"(\(\s*(?:%s|%\(.+\)s)\s*(?:,\s*(?:%s|%\(.+\)s)\s*)*\))" +
    r"(\s*(?:ON DUPLICATE.*)?);?\s*\Z",
    re.IGNORECASE | re.DOTALL)

doesn't support new introduced in MySQL 8.0.19 alias syntax like

INSERT INTO t1 (a,b,c) VALUES (%s,%s,%s) AS new ON DUPLICATE KEY UPDATE c = new.a+new.b;

while old supported values function syntax is deprecated since 8.0.20 (https://dev.mysql.com/doc/refman/8.0/en/insert-on-duplicate.html)

INSERT INTO t1 (a,b,c) VALUES (%s,%s,%s) ON DUPLICATE KEY UPDATE c=VALUES(a)+VALUES(b);
@grasslasts
Copy link
Author
grasslasts commented Sep 24, 2020 •
edited
Loading

I suggest to add a non-capturing group (?:AS\s*[^\s]+\s*)? inside 3rd regexp group, so final version would be

RE_INSERT_VALUES = re.compile(
    r"\s*((?:INSERT|REPLACE)\s.+\sVALUES?\s+)"
    r"(\(\s*(?:%s|%\(.+\)s)\s*(?:,\s*(?:%s|%\(.+\)s)\s*)*\))"
    r"(\s*(?:AS\s*[^\s]+\s*)?(?:ON DUPLICATE.*)?);?\s*\Z",
    re.IGNORECASE | re.DOTALL)

I can make a PR if you agree.

@methane
Copy link
Member
methane commented Sep 25, 2020

I do not anything except bugfix in this year. Please wait a half year.

@grasslasts
Copy link
Author

Are you agree with suggested solution? Are you ready to accept pull requests now?

@grasslasts grasslasts linked a pull request Mar 21, 2022 that will close this issue
Open
@KelRicher
Copy link

Nice

@xy-olha
Copy link
xy-olha commented Nov 7, 2024

python 3.7.9/3.7.10, pymysql 0.9.3, MySQL 8.0.33,
RE_INSERT_VALUES in cursor.py matches soooooo slowly when using INSERT INTO ... VALUES (...) AS alias ON DUPLICATE KEY UPDATE xx.col = alias.col in executemany. Need this fix badly.

@notatallshaw-gts
Copy link
notatallshaw-gts commented Feb 11, 2025 •
edited
Loading
6750

FYI, I hit this issue migrating from SqlAlchemy 1.4 to 2.0 where PyMySQL was used as the driver, this is because SqlAlchemy 2.0 detects the version of MySQL DB and uses the newer syntax if the database version is high enough. The performance slowdown for my statements were between 10x and 100x.

Based on responses from SqlAlchemy maintainers I switched to mysqlclient as the driver, which from initial testing doesn't have this issue.

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Assignees
No one assigned
Labels
Feature Request
Projects
None yet
Milestone
No milestone
Development

Successfully merging a pull request may close this issue.

Add support for new alias syntax in executemany grasslasts/PyMySQL
5 participants
@methane @grasslasts @KelRicher @xy-olha @notatallshaw-gts
0