Good idea!
A statement to the effect that it is DBMS (and potentially driver) dependent
seems a good clarification.
Chris
From: DB-SIG <db-sig-bounces+chris.clark=actian....@python.org> On Behalf Of
Mike Bayer
Sent: Friday, April 7, 2023 10:15 AM
To: db-sig@python.org
Subject: [DB-SIG] documenting whether or not the seq_of_parameters to
executemany is expected to be run in order given
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 :)
_______________________________________________
DB-SIG maillist - DB-SIG@python.org
https://mail.python.org/mailman/listinfo/db-sig
