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

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

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

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.

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.

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 :)

Slightly related: #52443

pythongh-99953: Purge mention of numeric param style from sqlite3 docs

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

yup, this is what it looked like was happening :) however, at the same time when you pass parameters to execute as a sequence, they somehow were being lined up with those named parameters. I'm not sure to what degree sqlite itself or the python driver is doing this, that is, silently adapting a sequence of non-named parameters to a statement using named style, but it would be nice if that didn't pass silently. But if it means having to newly parse SQL to figure it out, as I also suspect, then definitely dont get into that. I would not mind an implementation detail like this being noted in a sidebox however that is optional.

Just FYI, this comment sounds like it was generated by ChatGPT...

gh-99953: Purge mention of numeric param style from sqlite3 docs (#10…

pythongh-99953: Purge mention of numeric param style from sqlite3 docs (

however, at the same time when you pass parameters to execute as a sequence, they somehow were being lined up with those named parameters. I'm not sure to what degree sqlite itself or the python driver is doing this, that is, silently adapting a sequence of non-named parameters to a statement using named style, but it would be nice if that didn't pass silently.

Yep, it is unfortunate that this particular case is silently allowed. And by the way, it is the sqlite3 extension module, not SQLite, that has created that particular mess.

But if it means having to newly parse SQL to figure it out, as I also suspect, then definitely dont get into that. I would not mind an implementation detail like this being noted in a sidebox however that is optional.

Definitely! See #100668. I suggest we fix that doc issue first, and then consider how we can mitigate the case where the named style is used and a tuple, list, or non-dict-sequence is supplied.

gh-99953: Purge mention of numeric param style from sqlite3 docs (GH-…

Navigation Menu

Repository navigation

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

Linked PRs

Metadata

Assignees

Labels

Projects

Milestone

Relationships

Development

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

Description

Linked PRs

Activity

zzzeek commented on Dec 2, 2022

zzzeek commented on Dec 3, 2022

erlend-aasland commented on Dec 30, 2022

erlend-aasland commented on Dec 30, 2022

erlend-aasland commented on Dec 30, 2022

zzzeek commented on Dec 31, 2022

CAM-Gerlach commented on Dec 31, 2022

erlend-aasland commented on Jan 1, 2023

Metadata

Metadata

Assignees

Labels

Projects

Milestone

Relationships

Development

Issue actions