On 02.12.2022 17:34, Mike Bayer wrote:
Does numeric paramstyle intend to support statements where the numbers
are not numerically ordered within the statement? For example:
select count(*) from my_table where a=:3 and b=:4 and c=:1 and d=:5
and e=:2
if so, what is the expected form of the positional tuple? Consider
this data:
insert into my_table(a, b, c, d, e) values ('a', 'b', 'c', 'd', 'e')
to match this row, if we assume the positional tuple's contents should
correspond to the numbers in the statement assuming 1-based ordering,
we would expect this statement to match the row:
cursor.execute(
"""select count(*) from my_table where a=:3 and b=:4 and c=:1 and
d=:5 and e=:2""",
("c", "e", "a", "b", "d")
)
PEP 249 is a bit vague on this, but the general understanding at the
time when this was added was that "numeric" .paramstyle defines the
numbers after the colon as referring to 1-based positions in the
parameter tuple (otherwise, binding the same value multiple times would
not work, which is the main "feature" of the numeric style).
The only DB-API compatible module I know which does implement this, is
the old Informix one:
https://informixdb.sourceforge.net/manual.html#binding-parameters
OTOH, if we did not expect the numbers to be significant, and they are
basically more interesting looking question marks where we dont care
about the number, we'd expect this to match:
cursor.execute(
"""select count(*) from my_table where a=:3 and b=:4 and c=:1 and
d=:5 and e=:2""",
("a", "b", "c", "d", "e"),
)
Apparently Python sqlite3 module, which has been in production for
decades at this point in billions of computers, seems to honor the
second form, and an issue search has not shown anyone ever noticing.
I've raised an issue at https://github.com/python/cpython/issues/99953
It would appear this might speak to the relative un-popularity of
"numeric" paramstyle, though that doesn't change my own process here,
which is to try to support it.
The sqlite3 docs have this to say
(https://docs.python.org/3/library/sqlite3.html#sqlite3.paramstyle):
sqlite3.paramstyle<https://docs.python.org/3/library/sqlite3.html#sqlite3.paramstyle>
String constant stating the type of parameter marker formatting
expected by the |sqlite3| module. Required by the DB-API. Hard-coded
to |"qmark"|.
Note
The |sqlite3| module supports |qmark|, |numeric|, and |named| DB-API
parameter styles, because that is what the underlying SQLite library
supports. However, the DB-API does not allow multiple values for the
|paramstyle| attribute.
I've never seen sqlite3 used with numeric style binding parameters.
--
Marc-Andre Lemburg
eGenix.com
Professional Python Services directly from the Experts (#1, Dec 02 2022)
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
