Re: intent of "numeric" paramstyle, wrt actual numbers not numerically ordered?
Marc-Andre Lemburg <[email protected]> Fri, 2 Dec 2022 18:14:21 +0100
| Newsgroups | gmane.comp.python.db |
|---|---|
| Organization | eGenix.com Software GmbH; http://www.egenix.com/ |
| Message-ID | <[email protected]> |
This is a multi-part message in MIME format.
--===============0956340682171431315==
Content-Type: multipart/alternative;
boundary="------------VqCjWSYfDIFSwjR2X9ZMp0g2"
Content-Language: en-US
This is a multi-part message in MIME format.
--------------VqCjWSYfDIFSwjR2X9ZMp0g2
Content-Type: text/plain; charset=UTF-8; format=flowed
Content-Transfer-Encoding: 8bit
On 02.12.2022 17:34, Mike Bayer wrote:
> Does numeric paramstyle intend to support statements where the numbers
> are not numerically ordered within the statement? For example:
>
> select count(*) from my_table where a=:3 and b=:4 and c=:1 and d=:5
> and e=:2
>
>
> if so, what is the expected form of the positional tuple? Consider
> this data:
>
> insert into my_table(a, b, c, d, e) values ('a', 'b', 'c', 'd', 'e')
>
> to match this row, if we assume the positional tuple's contents should
> correspond to the numbers in the statement assuming 1-based ordering,
> we would expect this statement to match the row:
>
> cursor.execute(
> """select count(*) from my_table where a=:3 and b=:4 and c=:1 and
> d=:5 and e=:2""",
> ("c", "e", "a", "b", "d")
> )
>
PEP 249 is a bit vague on this, but the general understanding at the
time when this was added was that "numeric" .paramstyle defines the
numbers after the colon as referring to 1-based positions in the
parameter tuple (otherwise, binding the same value multiple times would
not work, which is the main "feature" of the numeric style).
The only DB-API compatible module I know which does implement this, is
the old Informix one:
https://informixdb.sourceforge.net/manual.html#binding-parameters
> OTOH, if we did not expect the numbers to be significant, and they are
> basically more interesting looking question marks where we dont care
> about the number, we'd expect this to match:
>
> cursor.execute(
> """select count(*) from my_table where a=:3 and b=:4 and c=:1 and
> d=:5 and e=:2""",
> ("a", "b", "c", "d", "e"),
> )
>
> Apparently Python sqlite3 module, which has been in production for
> decades at this point in billions of computers, seems to honor the
> second form, and an issue search has not shown anyone ever noticing.
> I've raised an issue at https://github.com/python/cpython/issues/99953
>
> It would appear this might speak to the relative un-popularity of
> "numeric" paramstyle, though that doesn't change my own process here,
> which is to try to support it.
The sqlite3 docs have this to say
(https://docs.python.org/3/library/sqlite3.html#sqlite3.paramstyle):
sqlite3.paramstyle<https://docs.python.org/3/library/sqlite3.html#sqlite3.paramstyle>
String constant stating the type of parameter marker formatting
expected by the |sqlite3| module. Required by the DB-API. Hard-coded
to |"qmark"|.
Note
The |sqlite3| module supports |qmark|, |numeric|, and |named| DB-API
parameter styles, because that is what the underlying SQLite library
supports. However, the DB-API does not allow multiple values for the
|paramstyle| attribute.
I've never seen sqlite3 used with numeric style binding parameters.
--
Marc-Andre Lemburg
eGenix.com
Professional Python Services directly from the Experts (#1, Dec 02 2022)
>>> Python Projects, Coaching and Support ... https://www.egenix.com/
>>> Python Product Development ... https://consulting.egenix.com/
________________________________________________________________________
::: We implement business ideas - efficiently in both time and costs :::
eGenix.com Software, Skills and Services GmbH Pastor-Loeh-Str.48
D-40764 Langenfeld, Germany. CEO Dipl.-Math. Marc-Andre Lemburg
Registered at Amtsgericht Duesseldorf: HRB 46611
https://www.egenix.com/company/contact/
https://www.malemburg.com/
--------------VqCjWSYfDIFSwjR2X9ZMp0g2
Content-Type: text/html; charset=UTF-8
Content-Transfer-Encoding: 8bit
<html>
<head>
<meta http-equiv="Content-Type" content="text/html; charset=UTF-8">
</head>
<body>
<div class="moz-cite-prefix">On 02.12.2022 17:34, Mike Bayer wrote:<br>
</div>
<blockquote type="cite"
cite="mid:[email protected]">
<title></title>
<div>Does numeric paramstyle intend to support statements where
the numbers are not numerically ordered within the statement?
For example:<br>
</div>
<div><br>
</div>
<div>select count(*) from my_table where a=:3 and b=:4 and c=:1
and d=:5 and e=:2<br>
</div>
<div><br>
</div>
<div><br>
</div>
<div>if so, what is the expected form of the positional tuple?
Consider this data:<br>
</div>
<div><br>
</div>
<div>insert into my_table(a, b, c, d, e) values ('a', 'b', 'c',
'd', 'e')<br>
</div>
<div><br>
</div>
<div>to match this row, if we assume the positional tuple's
contents should correspond to the numbers in the statement
assuming 1-based ordering, we would expect this statement to
match the row:<br>
</div>
<div><br>
</div>
<div>cursor.execute(<br>
</div>
<div> """select count(*) from my_table where a=:3 and b=:4 and
c=:1 and d=:5 and e=:2""",<br>
</div>
<div> ("c", "e", "a", "b", "d")<br>
</div>
<div>)<br>
</div>
<div><br>
</div>
</blockquote>
<p>PEP 249 is a bit vague on this, but the general understanding at
the time when this was added was that "numeric" .paramstyle
defines the numbers after the colon as referring to 1-based
positions in the parameter tuple (otherwise, binding the same
value multiple times would not work, which is the main "feature"
of the numeric style).<br>
</p>
<p>The only DB-API compatible module I know which does implement
this, is the old Informix one:
<a class="moz-txt-link-freetext" href="https://informixdb.sourceforge.net/manual.html#binding-parameters">https://informixdb.sourceforge.net/manual.html#binding-parameters</a></p>
<blockquote type="cite"
cite="mid:[email protected]">
<div>OTOH, if we did not expect the numbers to be significant, and
they are basically more interesting looking question marks where
we dont care about the number, we'd expect this to match:<br>
</div>
<div><br>
</div>
<div>cursor.execute(<br>
</div>
<div> """select count(*) from my_table where a=:3 and b=:4 and
c=:1 and d=:5 and e=:2""",<br>
</div>
<div> ("a", "b", "c", "d", "e"),<br>
</div>
<div>)<br>
</div>
<div><br>
</div>
<div>Apparently Python sqlite3 module, which has been in
production for decades at this point in billions of computers,
seems to honor the second form, and an issue search has not
shown anyone ever noticing. I've raised an issue at <a
href="https://github.com/python/cpython/issues/99953"
moz-do-not-send="true" class="moz-txt-link-freetext">https://github.com/python/cpython/issues/99953</a>
<br>
</div>
<div><br>
</div>
<div>It would appear this might speak to the relative
un-popularity of "numeric" paramstyle, though that doesn't
change my own process here, which is to try to support it.<br>
</div>
</blockquote>
<p>The sqlite3 docs have this to say
(<a class="moz-txt-link-freetext" href="https://docs.python.org/3/library/sqlite3.html#sqlite3.paramstyle">https://docs.python.org/3/library/sqlite3.html#sqlite3.paramstyle</a>):</p>
<dl class="py data">
<dt class="sig sig-object py" id="sqlite3.paramstyle">
<span class="sig-prename descclassname"><span class="pre">sqlite3.</span></span><span
class="sig-name descname"><span class="pre">paramstyle</span></span><a
class="headerlink"
href="https://docs.python.org/3/library/sqlite3.html#sqlite3.paramstyle"
title="Permalink to this definition"></a></dt>
<dd>
<p>String constant stating the type of parameter marker
formatting expected by
the <code class="xref py py-mod docutils literal notranslate"><span
class="pre">sqlite3</span></code> module. Required by the
DB-API. Hard-coded to
<code class="docutils literal notranslate"><span class="pre">"qmark"</span></code>.</p>
<div class="admonition note">
<p class="admonition-title">Note</p>
<p>The <code class="xref py py-mod docutils literal
notranslate"><span class="pre">sqlite3</span></code>
module supports <code class="docutils literal notranslate"><span
class="pre">qmark</span></code>, <code class="docutils
literal notranslate"><span class="pre">numeric</span></code>,
and <code class="docutils literal notranslate"><span
class="pre">named</span></code> DB-API parameter styles,
because that is what the underlying SQLite library supports.
However, the DB-API does not allow multiple values for
the <code class="docutils literal notranslate"><span
class="pre">paramstyle</span></code> attribute.</p>
</div>
</dd>
</dl>
<p></p>
<p>I've never seen sqlite3 used with numeric style binding
parameters.</p>
--
<pre class="moz-signature" cols="72">Marc-Andre Lemburg
eGenix.com
Professional Python Services directly from the Experts (#1, Dec 02 2022)
>>> Python Projects, Coaching and Support ... <a class="moz-txt-link-freetext" href="https://www.egenix.com/">https://www.egenix.com/</a>
>>> Python Product Development ... <a class="moz-txt-link-freetext" href="https://consulting.egenix.com/">https://consulting.egenix.com/</a>
________________________________________________________________________
::: We implement business ideas - efficiently in both time and costs :::
eGenix.com Software, Skills and Services GmbH Pastor-Loeh-Str.48
D-40764 Langenfeld, Germany. CEO Dipl.-Math. Marc-Andre Lemburg
Registered at Amtsgericht Duesseldorf: HRB 46611
<a class="moz-txt-link-freetext" href="https://www.egenix.com/company/contact/">https://www.egenix.com/company/contact/</a>
<a class="moz-txt-link-freetext" href="https://www.malemburg.com/">https://www.malemburg.com/</a>
</pre>
</body>
</html>
--------------VqCjWSYfDIFSwjR2X9ZMp0g2--
--===============0956340682171431315==
Content-Type: text/plain; charset="us-ascii"
MIME-Version: 1.0
Content-Transfer-Encoding: 7bit
Content-Disposition: inline
_______________________________________________
DB-SIG maillist - [email protected]
https://mail.python.org/mailman/listinfo/db-sig
--===============0956340682171431315==--