On Fri, Dec 2, 2022, at 12:14 PM, Marc-Andre Lemburg wrote:
> 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).
>
thanks, that confirms what I thought, for our own testing we will test with
that style.
I'm supplying a Connection subclass for our tests that use SQLite just for
testing numeric format, which fixes the parameters into something it expects,
which is, just a dictionary with those numbers as names, since it supports
"named" format too.
> 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
>
on our end we are adapting some non-DB-API modules like asyncpg to DB-API, or
something very close to that. asyncpg uses PostgreSQL's native bound format
which is "numbered" with dollar signs instead of colons. as long as "numbered"
is in pep-249 there's always the chance that some other new database will rely
upon 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 don't understand what they mean by "the DB-API does not allow multiple values
for the paramstyle attribute", unless they meant, it's not a tuple of all
possible types. SQLite supports statements with any of those formats (with the
exception of this issue). I'm not sure if I'm recalling correctly but it seems
like some DBAPIs want you to actually assign to .paramstyle to change the
paramstyle in use (seems safer than just auto-detecting).
> --
> 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
