Sorry to resurrect an old bug, but I've also found the docs lacking and I can fill in some gaps with some experimental results. Setup:
>>> import sqlite3
>>> conn = sqlite3.connect(':memory:')
>>> conn.execute('CREATE TABLE foo (x INTEGER, y INTEGER, z INTEGER)')
<sqlite3.Cursor object at 0x7f67257a79d0>
When the parameters is a sequence, the named placeholders can be repeated. There should be as many parameters as unique placeholders:
>>> conn.execute('INSERT INTO foo VALUES (:1, :2, :1)', (4, 5))
<sqlite3.Cursor object at 0x7f850a990a40>
>>> conn.execute('SELECT * FROM foo').fetchall()
[(4, 5, 4)]
Using numeric named placeholders is misleading, because they don't correspond to the indices in the parameters sequence. The following inserts (6, 7, 6), not (7, 6, 7):
>>> conn.execute('INSERT INTO foo VALUES (:2, :1, :2)', (6, 7))
<sqlite3.Cursor object at 0x7f850a990a40>
>>> conn.execute('SELECT * FROM foo').fetchall()
[(4, 5, 4), (6, 7, 6)]
So it is probably better to stick to non-numeric names:
>>> conn.execute('INSERT INTO foo VALUES (:a, :a, :a)', (8,))
<sqlite3.Cursor object at 0x7f850a990a40>
>>> conn.execute('SELECT * FROM foo').fetchall()
[(4, 5, 4), (6, 7, 6), (8, 8, 8)]
When the number of parameters is not the same as the number of unique placeholders, an sqlite3.ProgrammingError is raised:
>>> conn.execute('INSERT INTO foo VALUES (:1, :2, :1)', (4, 5, 6))
Traceback (most recent call last):
File "<stdin>", line 1, in <module>
sqlite3.ProgrammingError: Incorrect number of bindings supplied. The current statement uses 2, and there are 3 supplied.
Question mark placeholders may be mixed with named placeholders. Each question mark uses the next "unclaimed" parameter, which then cannot be reused.
>>> conn.execute('INSERT INTO foo VALUES (:a, ?, :a)', (1, 2))
<sqlite3.Cursor object at 0x7f850a990ab0>
>>> conn.execute('SELECT * FROM foo').fetchall()
[(4, 5, 4), (6, 7, 6), (8, 8, 8), (1, 2, 1)]
As mentioned by R. David Murray and Terry J. Reedy above, when the parameters are given as a dict, extra items are ignored and no error is raised:
>>> conn.execute('INSERT INTO foo VALUES (:a, :b, :a)', {'a': 3, 'b': 4, 'c': 5})
<sqlite3.Cursor object at 0x7f850a990ab0>
>>> conn.execute('SELECT * FROM foo').fetchall()
[(4, 5, 4), (6, 7, 6), (8, 8, 8), (1, 2, 1), (3, 4, 3)]
Disclaimer: I tested the above statements on Python 3.8.5. I did verify if the behavior is the same with earlier/later versions, and I don't know if this is intentional behavior or some undiscovered bug. |