The documents officially state “named” as the paramstyle, but with cx_Oracle we often use the numeric style with executemany so that we can insert from lists of tuples. In this context, the useful “feature” of the numeric paramstyle is that you don’t need to transform your data into a dictionary.
https://cx-oracle.readthedocs.io/en/latest/api_manual/module.html#cx_Oracle.paramstyle I haven’t tried binding the same value multiple times or using the placeholders in different orders, though. From: DB-SIG <db-sig-bounces+jostev=bgs.ac...@python.org> On Behalf Of Marc-Andre Lemburg Sent: 02 December 2022 17:14 To: Mike Bayer <mike...@zzzcomputing.com>; DB-SIG <db-sig@python.org> Subject: Re: [DB-SIG] intent of "numeric" paramstyle, wrt actual numbers not numerically ordered? 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 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/ This email and any attachments are intended solely for the use of the named recipients. If you are not the intended recipient you must not use, disclose, copy or distribute this email or any of its attachments and should notify the sender immediately and delete this email from your system. UK Research and Innovation (UKRI) has taken every reasonable precaution to minimise risk of this email or any attachments containing viruses or malware but the recipient should carry out its own virus and malware checks before opening the attachments. UKRI does not accept any liability for any losses or damages which the recipient may sustain due to presence of any viruses.
_______________________________________________ DB-SIG maillist - DB-SIG@python.org https://mail.python.org/mailman/listinfo/db-sig
