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

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?

>Solution :

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

Leave a Reply