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?
Advertisement
Answer
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