[DB-SIG] intent of "numeric" paramstyle, wrt actual numbers not numerically ordered?

[Marc-Andre Lemburg]

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/
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <https://mail.python.org/pipermail/db-sig/attachments/20221202/d6bba76c/attachment.html>