sqlite3's support for "numeric" paramstyle does not appear to honor the actual numbers with positional parameters

[zzzeek]

We're attempting to get some test support for "numeric" paramstyle, which while unnecessary for sqlite3, is similar to the paramstyle used by a very widely used, non-pep-249 library asyncpg.

anyway, I don't think sqlite3 is interpreting "numeric" correctly when the numbers are not ordered. If we consider numbers like ":3, :4, :2, :1" etc. to just be more interesting looking question marks (like "?, ?, ?, ?"), that's certainly easy but it seems to defeat the purpose of "numeric" parameters, where we would assume the number refers to the position of an entry in the parameter list.

if indeed this is wrong and it's a bug (I'm going to ping the DBAPI SIG list with this, to get their notion of intent), I fully expect that sqlite3 probably cant change things at this point, but just want to understand indeed what the intent of "numeric" paramstyle is.

Demo below:

import sqlite3


conn = sqlite3.connect(":memory:")

cursor = conn.cursor()

cursor.execute(
    """
    create table my_table(
        a varchar,
        b varchar,
        c varchar,
        d varchar,
        e varchar
    )
"""
)


cursor.execute(
    """
    insert into my_table(a, b, c, d, e) values ('a', 'b', 'c', 'd', 'e')
"""
)


cursor.execute(
    """
    select count(*) from my_table where a=? and b=? and c=? and d=? and e=?
""",
    ("a", "b", "c", "d", "e"),
)
assert cursor.fetchone() == (1, )


cursor.execute(
    """
    select count(*) from my_table where a=:1 and b=:2 and c=:3 and d=:4 and e=:5
""",
    ("a", "b", "c", "d", "e"),
)
assert cursor.fetchone() == (1, )

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")  # <--- fails

    #("a", "b", "c", "d", "e"),   # <--- succeeds, which is wrong

    # {"3": "a", "4": "b", "1": "c", "2": "e", "5": "d"} # <--- succeeds, but this is not "numeric" paramstyle

)
assert cursor.fetchone() == (1, )

Linked PRs

• gh-99953: Purge mention of numeric param style from sqlite3 docs #100630
• [3.11] gh-99953: Purge mention of numeric param style from sqlite3 docs (GH-100630) #100669
• [3.10] gh-99953: Purge mention of numeric param style from sqlite3 docs (GH-100630) #100670

[zzzeek]

Here's the discussion: https://mail.python.org/pipermail/db-sig/2022-December/006363.html

[24rr]

The numeric paramstyle in the Python Database API (DBAPI) specifies that parameters in an SQL statement should be represented by numbers. This is in contrast to the other paramstyles, which use a different syntax to specify parameters (such as qmark, which uses ? to represent parameters, or format, which uses %s).

In the numeric paramstyle, the numbers refer to the position of the parameter in the parameter sequence. This means that if you have a statement like this:

SELECT * FROM my_table WHERE a = :1 AND b = :2

then the first parameter in the parameter sequence will be used for the a column, and the second parameter will be used for the b column. The order of the parameters is important, and they should be specified in the same order as they appear in the SQL statement.

In your example, the :3, :4, :1, :5, and :2 parameters in the SELECT statement are out of order. This means that the values in the parameter sequence will not be used in the expected way, and the query will not return the expected results.

If you want to use the numeric paramstyle, you should make sure that the parameters in your SQL statement are in the same order as the values in the parameter sequence. Alternatively, you can use a different paramstyle that does not rely on the order of the parameters, such as the qmark paramstyle.

[zzzeek]

The numeric paramstyle in the Python Database API (DBAPI) specifies that parameters in an SQL statement should be represented by numbers. This is in contrast to the other paramstyles, which use a different syntax to specify parameters (such as qmark, which uses ? to represent parameters, or format, which uses %s).

In the numeric paramstyle, the numbers refer to the position of the parameter in the parameter sequence. This means that if you have a statement like this:

SELECT * FROM my_table WHERE a = :1 AND b = :2

then the first parameter in the parameter sequence will be used for the a column, and the second parameter will be used for the b column. The order of the parameters is important, and they should be specified in the same order as they appear in the SQL statement.

In your example, the :3, :4, :1, :5, and :2 parameters in the SELECT statement are out of order. This means that the values in the parameter sequence will not be used in the expected way, and the query will not return the expected results.

Hey there -

sorry, then sqlite3 should raise an error. This is a broken feature and as it's too late to fix it, SQLite should note in the documentation that it's not really working as one would expect.

Refer to the thread here for guidance (that's pep-249's author): https://mail.python.org/pipermail/db-sig/2022-December/006364.html . "numeric" paramstyle is certainly not used by anyone but as sqlite3 mentions it in the docs and has marginal support it, both the docs and the library should be explicit about this unexpected restriction.

If you want to use the numeric paramstyle, you should make sure that the parameters in your SQL statement are in the same order as the values in the parameter sequence. Alternatively, you can use a different paramstyle that does not rely on the order of the parameters, such as the qmark paramstyle.

It should be obvious that "numeric" paramstyle is useless if the numbers have to be in exact numerical order.

[24rr]

The numeric paramstyle in the Python Database API (DBAPI) specifies that parameters in an SQL statement should be represented by numbers. This is in contrast to the other paramstyles, which use a different syntax to specify parameters (such as qmark, which uses ? to represent parameters, or format, which uses %s).
In the numeric paramstyle, the numbers refer to the position of the parameter in the parameter sequence. This means that if you have a statement like this:

SELECT * FROM my_table WHERE a = :1 AND b = :2

then the first parameter in the parameter sequence will be used for the a column, and the second parameter will be used for the b column. The order of the parameters is important, and they should be specified in the same order as they appear in the SQL statement.
In your example, the :3, :4, :1, :5, and :2 parameters in the SELECT statement are out of order. This means that the values in the parameter sequence will not be used in the expected way, and the query will not return the expected results.

Hey there -

sorry, then sqlite3 should raise an error. This is a broken feature and as it's too late to fix it, SQLite should note in the documentation that it's not really working as one would expect.

Refer to the thread here for guidance (that's pep-249's author): https://mail.python.org/pipermail/db-sig/2022-December/006364.html . "numeric" paramstyle is certainly not used by anyone but as sqlite3 mentions it in the docs and has marginal support it, both the docs and the library should be explicit about this unexpected restriction.

If you want to use the numeric paramstyle, you should make sure that the parameters in your SQL statement are in the same order as the values in the parameter sequence. Alternatively, you can use a different paramstyle that does not rely on the order of the parameters, such as the qmark paramstyle.

It should be obvious that "numeric" paramstyle is useless if the numbers have to be in exact numerical order.

You are correct - the numeric paramstyle in the Python DBAPI is essentially useless if the parameters have to be in a specific order. This is because most SQL statements will have multiple parameters, and it is not always possible or practical to specify them in a specific order. The qmark and format paramstyles, on the other hand, do not rely on the order of the parameters, and are therefore more flexible and useful in most cases.

[erlend-aasland]

Hi Mike, and thanks for the report.

sorry, then sqlite3 should raise an error. This is a broken feature and as it's too late to fix it, SQLite should note in the documentation that it's not really working as one would expect.

+1 to everything you said there. It is too late to fix this; a documentation update is needed.

It should be obvious that "numeric" paramstyle is useless if the numbers have to be in exact numerical order.

Indeed.

[erlend-aasland]

Digging a little bit more into this, I think I now see where the confusion comes from. This is the set of SQLite placeholder templates:

• qmark: ?
• numeric: ?<number>
• named: :<name>
• named: @<name>
• named: $<name>

All of these work as expected in sqlite3. Here's an example using the SQLite numeric style:

import sqlite3
cx = sqlite3.connect(":memory:")
assert cx.execute("select ?1, ?2", ("a", "b")).fetchall() == [('a', 'b')]
assert cx.execute("select ?2, ?1", ("a", "b")).fetchall() == [('b', 'a')]

Now, this is the set of PEP-249 placeholder templates:

• qmark: ?
• numeric: :<number>
• named: :<name>
• format: %s
• pyformat: %(name)s

If you pass the PEP-249 numeric style to sqlite3, it is silently interpreted as SQLite named style.

The intersection of these two placeholder template sets is ? and :<name>, and those two only. The mention of the numeric style should be purged from the docs :)

[erlend-aasland]

Slightly related: #52443