classification
Title: Rename & explain sqlite3.Cursor.execute 'parameters' param
Type: behavior Stage: patch review
Components: Documentation, Library (Lib) Versions: Python 3.3, Python 3.4, Python 2.7
process
Status: open Resolution:
Dependencies: Superseder:
Assigned To: docs@python Nosy List: Rosuav, docs@python, erlendaasland, ghaering, goodmami, r.david.murray, terry.reedy
Priority: normal Keywords: patch

Created on 2014-01-23 10:42 by terry.reedy, last changed 2021-03-23 21:59 by erlendaasland.

Pull Requests
URL Status Linked Edit
PR 25003 open erlendaasland, 2021-03-23 21:56
Messages (8)
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 Egeberg Aasland (erlendaasland) * Date: 2021-03-23 21:59
Terry, I've created GH-25003 based on your suggested changes.
History
Date User Action Args
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