[DB-SIG] documenting whether or not the seq_of_parameters to executemany is expected to be run in order given
Mike Bayer
mike_mp at zzzcomputing.com
Fri Apr 7 13:14:37 EDT 2023
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 :)
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <https://mail.python.org/pipermail/db-sig/attachments/20230407/b98792b1/attachment.html>
More information about the DB-SIG
mailing list