Re: intent of "numeric" paramstyle, wrt actual numbers not numerically ordered?
"Mike Bayer" <[email protected]> Fri, 02 Dec 2022 12:38:58 -0500
| Newsgroups | gmane.comp.python.db |
|---|---|
| Message-ID | <[email protected]> |
--===============5228031188804557442==
Content-Type: multipart/alternative;
boundary=d06f5cf55203460e824d7a8d70752c5a
--d06f5cf55203460e824d7a8d70752c5a
Content-Type: text/plain
On Fri, Dec 2, 2022, at 12:14 PM, Marc-Andre Lemburg wrote:
> 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).
>
thanks, that confirms what I thought, for our own testing we will test with that style.
I'm supplying a Connection subclass for our tests that use SQLite just for testing numeric format, which fixes the parameters into something it expects, which is, just a dictionary with those numbers as names, since it supports "named" format too.
> 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
>
on our end we are adapting some non-DB-API modules like asyncpg to DB-API, or something very close to that. asyncpg uses PostgreSQL's native bound format which is "numbered" with dollar signs instead of colons. as long as "numbered" is in pep-249 there's always the chance that some other new database will rely upon 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 don't understand what they mean by "the DB-API does not allow multiple values for the paramstyle attribute", unless they meant, it's not a tuple of all possible types. SQLite supports statements with any of those formats (with the exception of this issue). I'm not sure if I'm recalling correctly but it seems like some DBAPIs want you to actually assign to .paramstyle to change the paramstyle in use (seems safer than just auto-detecting).
> --
> 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/
>
>
--d06f5cf55203460e824d7a8d70752c5a
Content-Type: text/html
<!DOCTYPE html><html><head><title></title><style type="text/css">p.MsoNormal,p.MsoNoSpacing{margin:0}</style></head><body><div><br></div><div><br></div><div>On Fri, Dec 2, 2022, at 12:14 PM, Marc-Andre Lemburg wrote:<br></div><blockquote type="cite" id="qt" style=""><div class="qt-moz-cite-prefix">On 02.12.2022 17:34, Mike Bayer wrote:<br></div><blockquote type="cite" cite="mid:[email protected]"><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></blockquote><div><br></div><div>thanks, that confirms what I thought, for our own testing we will test with that style.<br></div><div><br></div><div>I'm supplying a Connection subclass for our tests that use SQLite just for testing numeric format, which fixes the parameters into something it expects, which is, just a dictionary with those numbers as names, since it supports "named" format too. <br></div><div><br></div><blockquote type="cite" id="qt" style=""><p>The only DB-API compatible module I know which does implement
this, is the old Informix one: <a class="qt-moz-txt-link-freetext" href="https://informixdb.sourceforge.net/manual.html#binding-parameters">https://informixdb.sourceforge.net/manual.html#binding-parameters</a><br></p></blockquote><div><br></div><div>on our end we are adapting some non-DB-API modules like asyncpg to DB-API, or something very close to that. asyncpg uses PostgreSQL's native bound format which is "numbered" with dollar signs instead of colons. as long as "numbered" is in pep-249 there's always the chance that some other new database will rely upon it...<br></div><div><br></div><div><br></div><blockquote type="cite" id="qt" style=""><blockquote type="cite" cite="mid:[email protected]"><div>The sqlite3 docs have this to say
(<a class="qt-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>):<br></div></blockquote><dl class="qt-py qt-data"><dt class="qt-sig qt-sig-object qt-py" id="qt-sqlite3.paramstyle"><span class="qt-sig-prename qt-descclassname"><span class="qt-pre">sqlite3.</span></span><span class="qt-sig-name qt-descname"><span class="qt-pre">paramstyle</span></span><a class="qt-headerlink" href="https://docs.python.org/3/library/sqlite3.html#sqlite3.paramstyle" title="Permalink to this definition"></a><br></dt><dd><p>String constant stating the type of parameter marker
formatting expected by
the <code class="qt-xref qt-py qt-py-mod qt-docutils qt-literal qt-notranslate"><span class="qt-pre">sqlite3</span></code> module. Required by the
DB-API. Hard-coded to <code class="qt-docutils qt-literal qt-notranslate"><span class="qt-pre">"qmark"</span></code>.<br></p><div class="qt-admonition qt-note"><p class="qt-admonition-title">Note<br></p><p>The <code class="qt-xref qt-py qt-py-mod qt-docutils qt-literal qt-notranslate"><span class="qt-pre">sqlite3</span></code> module supports <code class="qt-docutils qt-literal qt-notranslate"><span class="qt-pre">qmark</span></code>, <code class="qt-docutils qt-literal qt-notranslate"><span class="qt-pre">numeric</span></code>,
and <code class="qt-docutils qt-literal qt-notranslate"><span class="qt-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="qt-docutils qt-literal qt-notranslate"><span class="qt-pre">paramstyle</span></code> attribute.<br></p></div></dd></dl></blockquote><div><br></div><div>I don't understand what they mean by "the DB-API does not allow multiple values for the paramstyle attribute", unless they meant, it's not a tuple of all possible types. SQLite supports statements with any of those formats (with the exception of this issue). I'm not sure if I'm recalling correctly but it seems like some DBAPIs want you to actually assign to .paramstyle to change the paramstyle in use (seems safer than just auto-detecting).<br></div><div><br></div><div><br></div><blockquote type="cite" id="qt" style=""><div>-- <br></div><pre class="qt-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="qt-moz-txt-link-freetext" href="https://www.egenix.com/">https://www.egenix.com/</a>
>>> Python Product Development ... <a class="qt-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="qt-moz-txt-link-freetext" href="https://www.egenix.com/company/contact/">https://www.egenix.com/company/contact/</a>
<a class="qt-moz-txt-link-freetext" href="https://www.malemburg.com/">https://www.malemburg.com/</a>
<br></pre></blockquote><div><br></div></body></html>
--d06f5cf55203460e824d7a8d70752c5a--
--===============5228031188804557442==
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
--===============5228031188804557442==--