On 09.04.2023 15:13, Mike Bayer wrote:
It's my understanding that a lot of executemany() implementations will
actually run the given statement once per each element in the sequence
of parameters. such as, it creates a prepared statement handle for
the statement, then runs each parameter set. a driver that works this
way can document that executemany() is guaranteed to invoke in that
order, because it's a driver-determined behavior, not the database.
a driver that packages all the parameter sets into a single
concatenated statement of course has a different story, they can't
guarantee this order.
but in both cases it can be documented and in the former case it can
be documented as the order being guaranteed also.
The DB-API wording is vague in this respect, mostly because the Python
driver cannot guarantee whether the statements actually get executed in
the same order as the parameters are passed to the method.
Some databases optimize such bulk executes to gain performance (e.g.
reorder INSERTs to make them match partitions) or seemingly randomize
executes (e.g. when running against a cluster to spread load).
I agree that we should probably clearly state this in PEP 249 using your
text:
"""
The order in which parameters are processed by .executemany() should not
be assumed to be in the same order as the parameters were given, since
the underlying database drivers or backends may optimize the way the
operations are processed.
"""
Related to this: databases often also don't necessarily guarantee that
INSERT order is stable, i.e. a SELECT will return the rows in the same
order they were added or in primary key order. Many do, but esp. cluster
ones typically don't. When writing code trying to work with different
backends, this can easily create problems, if you're not aware. I was
once bitten by this a long time ago, when running tests against a
Terraform data warehouse.
On Sun, Apr 9, 2023, at 5:19 AM, Tony Locke wrote:
My initial thought is that the driver must pass the executemany
parameters on to the server without changing the order of the
parameters. Maybe that should be explicitly stated in the spec. Once
the server has the parameters then I think the behaviour becomes DBMS
dependent. I guess my philosophy is that the humble driver should just
faithfully pass things back and forth while interfering as little as
possible.
On Fri, 7 Apr 2023 at 19:09, Mike Bayer <mike...@zzzcomputing.com> wrote:
>
> OK that's two "sure, let's absolve ourselves of this problem"
responses :)
>
> any opinion on executemany() being less useful if this requirement
is not established, and/or encouraging DBAPI authors to at least
*document* this themselves and maybe *prefer* maintaining ordering ?
>
>
>
> On Fri, Apr 7, 2023, at 1:54 PM, Erlend Egeberg Aasland wrote:
>
> On Fri, 7 Apr 2023 at 19:15, Mike Bayer <mike...@zzzcomputing.com>
wrote:
>
>
> […] 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". […]
>
>
> Sounds good to me.
>
> Erlend
>
>
>
> _______________________________________________
> DB-SIG maillist - DB-SIG@python.org
> https://mail.python.org/mailman/listinfo/db-sig
_______________________________________________
DB-SIG maillist -DB-SIG@python.org
https://mail.python.org/mailman/listinfo/db-sig
--
Marc-Andre Lemburg
eGenix.com
Professional Python Services directly from the Experts (#1, Apr 10 2023)
Python Projects, Coaching and Support ... https://www.egenix.com/
Python Product Development ... https://consulting.egenix.com/
________________________________________________________________________
::: We implement business ideas - efficiently in both time and costs :::
eGenix.com Software, Skills and Services GmbH Pastor-Loeh-Str.48
D-40764 Langenfeld, Germany. CEO Dipl.-Math. Marc-Andre Lemburg
Registered at Amtsgericht Duesseldorf: HRB 46611
https://www.egenix.com/company/contact/
https://www.malemburg.com/
_______________________________________________
DB-SIG maillist - DB-SIG@python.org
https://mail.python.org/mailman/listinfo/db-sig
