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