Why does Python's sqlite3 module not respect the order of positional parameters?-CodePudding

Recently I ran across the following peculiar behavior, that can be explained by the following code sample:

import sqlite3, platform

insert = (10, "today")
db = sqlite3.connect(":memory:")
db.execute("CREATE TABLE t (number, string)")
db.execute("INSERT INTO t (string, number) VALUES ($2, $1)", insert)
select = db.execute("SELECT number, string FROM t").fetchone()

print(f"python version: {platform.python_version()}")
print(f"sqlite version: {sqlite3.sqlite_version}, module: {sqlite3.version}")
print(f"insert: {insert}")
print(f"select: {select}")

python version: 3.10.5
sqlite version: 3.38.5, module: 2.6.0
insert: (10, 'today')
select: ('today', 10)

I would expect the select to come out the same way as the insert. The only explanation for this behavior that I can come up with, is that the numeric value of the parameter arguments are being ignored. Why does Python ignore the ordering?

CodePudding user response：

You're mixing up two different ways of using parameters here:

db.execute("INSERT INTO t (string, number) VALUES ($2, $1)", insert)

If you reverse ($2, $1) as ($1, $2), you will get the same result.

However, if you use this:

db.execute("INSERT INTO t (string, number) VALUES (?2, ?1)", insert)

Then you get what you expected.

The $-format is for named parameters, e.g.:

db.execute("INSERT INTO t (string, number) VALUES ($s, $n)", {'s': 'today', 'n': 10})

Have a read of https://www.sqlite.org/lang_expr.html#varparam