R. David Murray added the comment: I wonder if the fact that a sequence is accepted in named mode should actually be considered a bug and not documented. Also, is it really true that the number of items must match even in named mode? I think I remember passing a dict with extra elements, but I don't have that code handy to check. ---------- nosy: +r.david.murray _______________________________________ Python tracker <report@bugs.python.org> <http://bugs.python.org/issue20364> _______________________________________

Terry J. Reedy added the comment: I do not know what the intention was for sequences and named placeholders. Thinking of named tuples made me think it ok. The code might have a hint. Is sqlite3 code maintained here or elsewhere? The current docstring is just 'Executes a SQL statement.', and help gives no signature. .executemany is similar. I suspect the whole module needs better docstrings. You are correct about dicts and extra key:value pairs. I tried a UserDict with an extra pair and when I got sqlite3.ProgrammingError: Incorrect number of bindings supplied. The current statement uses 2, and there are 3 supplied I had not yet realized that they were treated as sequences, not dicts. So replace "Len(seq_dict)" with "The length of sequences". This solves Chris's point also. ---------- nosy: +ghaering _______________________________________ Python tracker <report@bugs.python.org> <http://bugs.python.org/issue20364> _______________________________________

Michael Wayne Goodman <goodman.m.w@gmail.com> added the comment: 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. ---------- nosy: +goodmami _______________________________________ Python tracker <report@bugs.python.org> <https://bugs.python.org/issue20364> _______________________________________

Change by Erlend Egeberg Aasland <erlend.aasland@innova.no>: ---------- keywords: +patch nosy: +erlendaasland nosy_count: 6.0 -> 7.0 pull_requests: +23761 pull_request: https://github.com/python/cpython/pull/25003 _______________________________________ Python tracker <report@bugs.python.org> <https://bugs.python.org/issue20364> _______________________________________

Berker Peksag <berker.peksag@gmail.com> added the comment: New changeset 3386ca0b36327afeef8d7eff277b2aed1030c08d by Erlend Egeberg Aasland in branch 'master': bpo-20364: Improve sqlite3 placeholder docs (GH-25003) https://github.com/python/cpython/commit/3386ca0b36327afeef8d7eff277b2aed103... ---------- nosy: +berker.peksag _______________________________________ Python tracker <report@bugs.python.org> <https://bugs.python.org/issue20364> _______________________________________

Berker Peksag <berker.peksag@gmail.com> added the comment: New changeset 95e4431804587a0c9d464bb7b3d5f3057bbeaccd by Miss Islington (bot) in branch '3.9': bpo-20364: Improve sqlite3 placeholder docs (GH-25003) https://github.com/python/cpython/commit/95e4431804587a0c9d464bb7b3d5f3057bb... ---------- _______________________________________ Python tracker <report@bugs.python.org> <https://bugs.python.org/issue20364> _______________________________________