classification
Title: Rename & explain sqlite3.Cursor.execute 'parameters' param
Type: behavior Stage: resolved
Components: Documentation, Library (Lib) Versions: Python 3.10, Python 3.9
process
Status: closed Resolution: fixed
Dependencies: Superseder:
Assigned To: docs@python Nosy List: Rosuav, berker.peksag, docs@python, erlendaasland, ghaering, goodmami, miss-islington, r.david.murray, terry.reedy
Priority: normal Keywords: patch

Created on 2014-01-23 10:42 by terry.reedy, last changed 2021-04-14 13:28 by berker.peksag. This issue is now closed.

Pull Requests
URL Status Linked Edit
PR 25003 merged erlendaasland, 2021-03-23 21:56
PR 25402 merged miss-islington, 2021-04-14 12:29
Messages (10)
msg208908 - (view) Author: Terry J. Reedy (terry.reedy) * (Python committer) Date: 2014-01-23 10:42
"execute(sql[, parameters])
    Executes an SQL statement. The SQL statement may be parametrized (i. e. placeholders instead of SQL literals). The sqlite3 module supports two kinds of placeholders: question marks (qmark style) and named placeholders (named style)."

Experimental facts based on experiments with the code example in the doc, using 3.4.b2: 'parameters' is a single subscriptable collection parameter, sequence or dict, that might be called seq_dict. It is positional only, so whatever name is used is a dummy. Only one placeholder style can be used in a given SQL statement string. If question marks are used, seq_dict must be a sequence. If names are used, seq_dict can be either a sequence or dict or subclass thereof. A UserDict is treated as a sequence and raises KeyError(0).

Possible text that encompasses the above, replacing the last sentence:

"A statement may use one of two kinds of placeholders: question marks (qmark style) or named placeholders (named style). For qmark style, seq_dict must be a sequence. For named style, it can be either a sequence or dict instance. Len(seq_dict) must match the number of placeholders."

After cleaning up the test file, I will verify on 2.7 and upload.
msg208926 - (view) Author: R. David Murray (r.david.murray) * (Python committer) Date: 2014-01-23 14:04
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.
msg208953 - (view) Author: Chris Angelico (Rosuav) * Date: 2014-01-23 15:10
Small quibble: The last sentence capitalizes a Python built-in, which is confusing ("Len(seq_dict) must match..."). Tweak of grammar to have it not at the beginning of the sentence: "Either way, len(seq_dict) must match...".
msg209020 - (view) Author: Terry J. Reedy (terry.reedy) * (Python committer) Date: 2014-01-23 23:26
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.
msg209034 - (view) Author: R. David Murray (r.david.murray) * (Python committer) Date: 2014-01-24 04:18
I don't know anything about the current relationship between the external project and the stdlib version.  In the (small) changes I've been part of, we have maintained what is in the stdlib without reference to the external project.
msg387088 - (view) Author: Michael Wayne Goodman (goodmami) * Date: 2021-02-16 05:14
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.
msg387089 - (view) Author: Michael Wayne Goodman (goodmami) * Date: 2021-02-16 05:17
Sorry, typo in my last statement. I did *not* verify if the behavior is the same with earlier/later versions.
msg389411 - (view) Author: Erlend E. Aasland (erlendaasland) * (Python triager) Date: 2021-03-23 21:59
Terry, I've created GH-25003 based on your suggested changes.
msg391073 - (view) Author: Berker Peksag (berker.peksag) * (Python committer) Date: 2021-04-14 12:28
New changeset 3386ca0b36327afeef8d7eff277b2aed1030c08d by Erlend Egeberg Aasland in branch 'master':
bpo-20364: Improve sqlite3 placeholder docs (GH-25003)
https://github.com/python/cpython/commit/3386ca0b36327afeef8d7eff277b2aed1030c08d
msg391077 - (view) Author: Berker Peksag (berker.peksag) * (Python committer) Date: 2021-04-14 13:28
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/95e4431804587a0c9d464bb7b3d5f3057bbeaccd
History
Date User Action Args
2021-04-14 13:28:33berker.peksagsetstatus: open -> closed
stage: patch review -> resolved
resolution: fixed
versions: + Python 3.9, Python 3.10, - Python 2.7, Python 3.3, Python 3.4
2021-04-14 13:28:15berker.peksagsetmessages: + msg391077
2021-04-14 12:29:16miss-islingtonsetnosy: + miss-islington
pull_requests: + pull_request24135
2021-04-14 12:28:58berker.peksagsetnosy: + berker.peksag
messages: + msg391073
2021-03-23 21:59:28erlendaaslandsetmessages: + msg389411
2021-03-23 21:56:21erlendaaslandsetkeywords: + patch
nosy: + erlendaasland
pull_requests: + pull_request23761
2021-02-16 05:17:04goodmamisetmessages: + msg387089
2021-02-16 05:14:34goodmamisetnosy: + goodmami
messages: + msg387088
2016-03-27 18:26:25berker.peksaglinkissue18691 superseder
2014-01-24 04:18:42r.david.murraysetmessages: + msg209034
2014-01-23 23:26:58terry.reedysetnosy: + ghaering
messages: + msg209020
2014-01-23 15:10:26Rosuavsetnosy: + Rosuav
messages: + msg208953
2014-01-23 14:04:45r.david.murraysetnosy: + r.david.murray
messages: + msg208926
2014-01-23 10:42:59terry.reedycreate