[issue20364] Rename & explain sqlite3.Cursor.execute 'parameters' param

[Berker Peksag]

Change by Berker Peksag :


--
resolution:  -> fixed
stage: patch review -> resolved
status: open -> closed
versions: +Python 3.10, Python 3.9 -Python 2.7, Python 3.3, Python 3.4

___
Python tracker 

___
___
Python-bugs-list mailing list
Unsubscribe: 
https://mail.python.org/mailman/options/python-bugs-list/archive%40mail-archive.com

[issue20364] Rename & explain sqlite3.Cursor.execute 'parameters' param

[Berker Peksag]

Berker Peksag  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/95e4431804587a0c9d464bb7b3d5f3057bbeaccd


--

___
Python tracker 

___
___
Python-bugs-list mailing list
Unsubscribe: 
https://mail.python.org/mailman/options/python-bugs-list/archive%40mail-archive.com

[issue20364] Rename & explain sqlite3.Cursor.execute 'parameters' param

[miss-islington]

Change by miss-islington :


--
nosy: +miss-islington
nosy_count: 8.0 -> 9.0
pull_requests: +24135
pull_request: https://github.com/python/cpython/pull/25402

___
Python tracker 

___
___
Python-bugs-list mailing list
Unsubscribe: 
https://mail.python.org/mailman/options/python-bugs-list/archive%40mail-archive.com

[issue20364] Rename & explain sqlite3.Cursor.execute 'parameters' param

[Berker Peksag]

Berker Peksag  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/3386ca0b36327afeef8d7eff277b2aed1030c08d


--
nosy: +berker.peksag

___
Python tracker 

___
___
Python-bugs-list mailing list
Unsubscribe: 
https://mail.python.org/mailman/options/python-bugs-list/archive%40mail-archive.com

[issue20364] Rename & explain sqlite3.Cursor.execute 'parameters' param

[Erlend Egeberg Aasland]

Erlend Egeberg Aasland  added the comment:

Terry, I've created GH-25003 based on your suggested changes.

--

___
Python tracker 

___
___
Python-bugs-list mailing list
Unsubscribe: 
https://mail.python.org/mailman/options/python-bugs-list/archive%40mail-archive.com

[issue20364] Rename & explain sqlite3.Cursor.execute 'parameters' param

[Erlend Egeberg Aasland]

Change by Erlend Egeberg Aasland :


--
keywords: +patch
nosy: +erlendaasland
nosy_count: 6.0 -> 7.0
pull_requests: +23761
pull_request: https://github.com/python/cpython/pull/25003

___
Python tracker 

___
___
Python-bugs-list mailing list
Unsubscribe: 
https://mail.python.org/mailman/options/python-bugs-list/archive%40mail-archive.com

[issue20364] Rename & explain sqlite3.Cursor.execute 'parameters' param

[Michael Wayne Goodman]

Michael Wayne Goodman  added the comment:

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

--

___
Python tracker 

___
___
Python-bugs-list mailing list
Unsubscribe: 
https://mail.python.org/mailman/options/python-bugs-list/archive%40mail-archive.com

[issue20364] Rename & explain sqlite3.Cursor.execute 'parameters' param

[Michael Wayne Goodman]

Michael Wayne Goodman  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)')


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))

>>> 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))

>>> 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,))

>>> 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 "", line 1, in 
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))

>>> 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})

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

___
___
Python-bugs-list mailing list
Unsubscribe: 
https://mail.python.org/mailman/options/python-bugs-list/archive%40mail-archive.com

[issue20364] Rename explain sqlite3.Cursor.execute 'parameters' param

[Terry J. Reedy]

New submission from Terry J. Reedy:

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.

--
assignee: docs@python
components: Documentation, Library (Lib)
messages: 208908
nosy: docs@python, terry.reedy
priority: normal
severity: normal
stage: patch review
status: open
title: Rename  explain sqlite3.Cursor.execute 'parameters'  param
type: behavior
versions: Python 2.7, Python 3.3, Python 3.4

___
Python tracker rep...@bugs.python.org
http://bugs.python.org/issue20364
___
___
Python-bugs-list mailing list
Unsubscribe: 
https://mail.python.org/mailman/options/python-bugs-list/archive%40mail-archive.com

[issue20364] Rename explain sqlite3.Cursor.execute 'parameters' param

[R. David Murray]

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 rep...@bugs.python.org
http://bugs.python.org/issue20364
___
___
Python-bugs-list mailing list
Unsubscribe: 
https://mail.python.org/mailman/options/python-bugs-list/archive%40mail-archive.com

[issue20364] Rename explain sqlite3.Cursor.execute 'parameters' param

[Chris Angelico]

Chris Angelico added the comment:

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

--
nosy: +Rosuav

___
Python tracker rep...@bugs.python.org
http://bugs.python.org/issue20364
___
___
Python-bugs-list mailing list
Unsubscribe: 
https://mail.python.org/mailman/options/python-bugs-list/archive%40mail-archive.com

[issue20364] Rename explain sqlite3.Cursor.execute 'parameters' param

[Terry J. Reedy]

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 rep...@bugs.python.org
http://bugs.python.org/issue20364
___
___
Python-bugs-list mailing list
Unsubscribe: 
https://mail.python.org/mailman/options/python-bugs-list/archive%40mail-archive.com

[issue20364] Rename explain sqlite3.Cursor.execute 'parameters' param

[R. David Murray]

R. David Murray added the comment:

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.

--

___
Python tracker rep...@bugs.python.org
http://bugs.python.org/issue20364
___
___
Python-bugs-list mailing list
Unsubscribe: 
https://mail.python.org/mailman/options/python-bugs-list/archive%40mail-archive.com