Rename & explain sqlite3.Cursor.execute 'parameters' param

[terryjreedy]

BPO	20364
Nosy	@terryjreedy, @bitdancer, @Rosuav, @berkerpeksag, @goodmami, @miss-islington, @erlend-aasland
PRs	bpo-20364: Improve sqlite3 placeholder docs #25003 [3.9] bpo-20364: Improve sqlite3 placeholder docs (GH-25003) #25402

^{Note: these values reflect the state of the issue at the time it was migrated and might not reflect the current state.}

Show more details

GitHub fields:

assignee = None
closed_at = <Date 2021-04-14.13:28:33.094>
created_at = <Date 2014-01-23.10:42:59.107>
labels = ['type-bug', 'library', '3.9', '3.10', 'docs']
title = "Rename & explain sqlite3.Cursor.execute 'parameters'  param"
updated_at = <Date 2021-04-14.13:28:33.092>
user = 'https://github.com/terryjreedy'

bugs.python.org fields:

activity = <Date 2021-04-14.13:28:33.092>
actor = 'berker.peksag'
assignee = 'docs@python'
closed = True
closed_date = <Date 2021-04-14.13:28:33.094>
closer = 'berker.peksag'
components = ['Documentation', 'Library (Lib)']
creation = <Date 2014-01-23.10:42:59.107>
creator = 'terry.reedy'
dependencies = []
files = []
hgrepos = []
issue_num = 20364
keywords = ['patch']
message_count = 10.0
messages = ['208908', '208926', '208953', '209020', '209034', '387088', '387089', '389411', '391073', '391077']
nosy_count = 9.0
nosy_names = ['terry.reedy', 'ghaering', 'r.david.murray', 'docs@python', 'Rosuav', 'berker.peksag', 'goodmami', 'miss-islington', 'erlendaasland']
pr_nums = ['25003', '25402']
priority = 'normal'
resolution = 'fixed'
stage = 'resolved'
status = 'closed'
superseder = None
type = 'behavior'
url = 'https://bugs.python.org/issue20364'
versions = ['Python 3.9', 'Python 3.10']

[terryjreedy]

"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.

[bitdancer]

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.

[Rosuav]

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...".

[terryjreedy]

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.

[bitdancer]

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.

[goodmami]

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.

[goodmami]

Sorry, typo in my last statement. I did *not* verify if the behavior is the same with earlier/later versions.

[erlend-aasland]

Terry, I've created python/issues-test-cpython#25003 based on your suggested changes.

[berkerpeksag]

New changeset 3386ca0 by Erlend Egeberg Aasland in branch 'master':
bpo-20364: Improve sqlite3 placeholder docs (GH-25003)
3386ca0

[berkerpeksag]

New changeset 95e4431 by Miss Islington (bot) in branch '3.9':
bpo-20364: Improve sqlite3 placeholder docs (GH-25003)
95e4431