8000 allow no whitespaces between `VALUES` and `(` when do bulk insert/replace by uwydoc · Pull Request #597 · PyMySQL/PyMySQL · GitHub
[go: up one dir, main page]
Skip to content

allow no whitespaces between VALUES and ( when do bulk insert/replace #597

New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and 8000 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
Merged
merged 2 commits into from
Nov 9, 2017
Merged

allow no whitespaces between VALUES and ( when do bulk insert/replace #597

merged 2 commits into from
Nov 9, 2017

Conversation

uwydoc
Copy link
Contributor
@uwydoc uwydoc commented Aug 30, 2017 •
edited
Loading

the regular expression used to check whether bulk insert/replace is possible is as follows:

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

notice the last \s+ in the first part of the regular expression requires whitespaces between VALUES and left-parenthesis, which means queries like insert into user values(%s, %s) would not be considered bulk-insertable. although the demo sql query in the comment of executemany has a space between VALUES and (, i do think this restriction could be relaxed.

This gotcha is so trivial and would cost some time to realize. It actually costed me some time to find out why the performance of a relatively simple program dropped by more than 6 times after switching from MySQLdb to PyMySQL.

The fix is quite simple, just replace the \s+ with \s* in the first part.

NOTE: ported from PyMySQL/mysqlclient@354dcb5

@methane
Copy link
Member
methane commented Aug 30, 2017

Please copy this commit:
PyMySQL/mysqlclient@354dcb5

@uwydoc
Copy link
Contributor Author
uwydoc commented Sep 5, 2017

@methane done and thanks.

@methane methane mentioned this pull request Sep 15, 2017
Closed
@methane methane merged commit ed5efda into PyMySQL:master Nov 9, 2017
@github-actions github-actions bot locked as resolved and limited conversation to collaborators Aug 2, 2021
Sign up for free to subscribe to this conversation on GitHub. Already have an account? Sign in.
Reviewers
No reviews
Assignees
No one assigned
Labels
None yet
Projects
None yet
Milestone
No milestone
Development

Successfully merging this pull request may close these issues.
2 participants
@uwydoc @methane
0