documenting whether or not the seq_of_parameters to executemany is expected to be run in order given
"Mike Bayer" <[email protected]> Fri, 07 Apr 2023 13:14:37 -0400
| Newsgroups | gmane.comp.python.db |
|---|---|
| Message-ID | <[email protected]> |
--===============8591525221098558709==
Content-Type: multipart/alternative;
boundary=a2f694f8af5842ce90c1d7b644977d86
--a2f694f8af5842ce90c1d7b644977d86
Content-Type: text/plain
hey list -
as $subject says, if we run:
cursor.executemany("insert into table (a, b) values (?, ?)", [(1, 1), (2, 2), (3, 3)])
it should be obvious that most people would *expect* that the three parameter sets given are INSERTed in the order that was given.
This could be an issue if perhaps the rows being inserted contained values that refer to previous rows via foreign key. Or if attempting to run an UPDATE, where we would like the order of rows UPDATEd to be deterministic, so that we can avoid deadlocks with other processes that may be UPDATEing some subset of those same rows in a different transaction.
it's obviously also an issue for developers expecting server-generated values to follow some sequence, however if you bring that use case up you will get a flock of lecturers scolding you for this suggestion, so let's ignore that use case (that is not my use case).
However pep-249 doesn't indicate this behavior one way or the other, that is, whether we should not expect this, or we should expect this, or that it's up to the DBAPI to tell us what to expect.
I bring this up because a common optimization for executemany of an INSERT is to rewrite the statement like this:
"INSERT INTO table (a, b) VALUES (1, 1), (2, 2), (3, 3)"
For example see what Pymysql does, using the regex at https://github.com/PyMySQL/PyMySQL/blob/885841f3fee416c222a75d83a81f74d3dcd71b51/pymysql/cursors.py#L5 to rewrite the statement here : https://github.com/PyMySQL/PyMySQL/blob/885841f3fee416c222a75d83a81f74d3dcd71b51/pymysql/cursors.py#L162
So it's also the case that most databases given the INSERT statement above will run the VALUES entries in that order, after all, why *wouldnt* they. But it turns out a similar statement run on MS SQL Server, using explicit table-valued entries in order, in some cases will actually insert the rows in some other order (optimizing for it seems like indexing of values in some way that relate to some foreign key constraint). My actual problem from there gets into that I'm also trying to use RETURNING , but that's not the scope of the question here.
The scope here is, should pep-249 add some verbiage: "the order in which parameters are processed by executemany() should not be assumed to be in the order the parameters were given". which IMO would be crazy. but if that's the reality, maybe it should be stated. I'd of course *prefer* if it were stated that executemany() should process the given params in the order given. But I'm not too optimistic about that :)
--a2f694f8af5842ce90c1d7b644977d86
Content-Type: text/html
Content-Transfer-Encoding: quoted-printable
<!DOCTYPE html><html><head><title></title><style type=3D"text/css">p.Mso=
Normal,p.MsoNoSpacing{margin:0}</style></head><body><div>hey list -<br><=
/div><div><br></div><div>as $subject says, if we run:<br></div><div><br>=
</div><div>cursor.executemany("insert into table (a, b) values (?, ?)", =
[(1, 1), (2, 2), (3, 3)])<br></div><div><br></div><div>it should be obvi=
ous that most people would *expect* that the three parameter sets given =
are INSERTed in the order that was given.<br></div><div><br></div><div>T=
his could be an issue if perhaps the rows being inserted contained value=
s that refer to previous rows via foreign key. O=
r if attempting to run an UPDATE, where we would like the order of rows =
UPDATEd to be deterministic, so that we can avoid deadlocks with other p=
rocesses that may be UPDATEing some subset of those same rows in a diffe=
rent transaction.<br></div><div><br></div><div>it's obviously also an is=
sue for developers expecting server-generated values to follow some sequ=
ence, however if you bring that use case up you will get a flock of lect=
urers scolding you for this suggestion, so let's ignore that use case (t=
hat is not my use case).<br></div><div><br></div><div>However pep-249 do=
esn't indicate this behavior one way or the other, that is, whether we s=
hould not expect this, or we should expect this, or that it's up to the =
DBAPI to tell us what to expect.<br></div><div><br></div><div>I bring th=
is up because a common optimization for executemany of an INSERT is to r=
ewrite the statement like this:<br></div><div><br></div><div>"INSERT INT=
O table (a, b) VALUES (1, 1), (2, 2), (3, 3)"<br></div><div><br></div><d=
iv>For example see what Pymysql does, using the regex at <a href=3D"http=
s://github.com/PyMySQL/PyMySQL/blob/885841f3fee416c222a75d83a81f74d3dcd7=
1b51/pymysql/cursors.py#L5">https://github.com/PyMySQL/PyMySQL/blob/8858=
41f3fee416c222a75d83a81f74d3dcd71b51/pymysql/cursors.py#L5</a> to rewrit=
e the statement here : <a href=3D"https://github.com/PyMySQL/PyMySQL/blo=
b/885841f3fee416c222a75d83a81f74d3dcd71b51/pymysql/cursors.py#L162">http=
s://github.com/PyMySQL/PyMySQL/blob/885841f3fee416c222a75d83a81f74d3dcd7=
1b51/pymysql/cursors.py#L162</a><br></div><div><br></div><div>So it's al=
so the case that most databases given the INSERT statement above will ru=
n the VALUES entries in that order, after all, why *wouldnt* they. =
But it turns out a similar statement run on MS SQL Server, using =
explicit table-valued entries in order, in some cases will actually inse=
rt the rows in some other order (optimizing for it seems like indexing o=
f values in some way that relate to some foreign key constraint). &=
nbsp; My actual problem from there gets into that I'm also trying to use=
RETURNING , but that's not the scope of the question here.<br></div><di=
v><br></div><div>The scope here is, should pep-249 add some verbiage: "t=
he order in which parameters are processed by executemany() should not b=
e assumed to be in the order the parameters were given". which IMO=
would be crazy. but if that's the reality, maybe it should be sta=
ted. I'd of course *prefer* if it were stated that executema=
ny() should process the given params in the order given. But=
I'm not too optimistic about that :)<br></div><div><br></div><div><br><=
/div><div><br></div></body></html>
--a2f694f8af5842ce90c1d7b644977d86--
--===============8591525221098558709==
Content-Type: text/plain; charset="us-ascii"
MIME-Version: 1.0
Content-Transfer-Encoding: 7bit
Content-Disposition: inline
_______________________________________________
DB-SIG maillist - [email protected]
https://mail.python.org/mailman/listinfo/db-sig
--===============8591525221098558709==--