Wow, a lot of discussion to read :-)
I'll start with some comments on the more important things.
On 21.05.2013 19:24, Daniele Varrazzo wrote:
> On Tue, 2013-05-21 at 17:32 +0100, Vernon D. Cole wrote:
>> My conclusion: Auto switching is easy, practical, and has years of
>> precedent history. DB-api 3 should use it as the default action.
-1 on making this the default for the reasons I already stated
earlier on.
We can make it a possible paramstyle value, but not the default.
Whether it works or not depends a lot on the supported paramstyles
that the auto-detection can detect and even then the algorithm
can run into problems when e.g. seeing a parameter object that
implements both the mapping and sequence protocol, accidental
use of (unresolved) Python formatting characters in a SQL
statement, etc.
Note that not all Python types are associated with an
abstract base class, e.g. take a user defined class that
implements the mapping or sequence protocol, but doesn't
inherit from the ABCs.
Also note that the DB-API 2.0 has so far mandated that
.__getitem__() be used for lookups, so that both sequences
and mappings can be used - even for positional paramstyles.
I'm not sure many database modules implement this.
We might want to require ABC inheritance to be used for
parameter sequences/mappings in DB-API 3.0 to resolve
this issue.
> I'd love that, but please take your time to review the ambiguous cases
> and the consequences in usability of the specific grammar I'd chosen for
> qmarkpg 0.1, which implicitly demanded the query to:
>
> - have literal ? escaped as ??, : not escaped if args is a sequence
> - have literal : escaped as ::, ? not escaped if args is a mapping
> - don't escape neither ? nor : if no param is passed
>
> It doesn't feel very ergonomic. Have you got something better in mind?
I agree that we need to think more carefully about the definition
of the paramstyles if we want to make them standard in DB-API 3.0.
However, escaping doesn't strike me as one of the important
details, since I have yet to find an example where you'd
actually need escaping :-)
First of all, SQL string literals should not be subject to
marker parsing. They must be skipped and care must be taken
to make sure that the '-quoting is being respected ('' -> ').
* qmark case: question marks don't appear outside SQL string
literals, so you don't need to escape them - they will
always refer to binding markers
* named case: it should be enough to make sure that the colon
of a named marker is not preceded by another colon
(negative look-behind). If it is, the potential substring
is not a named marker, e.g. "abc::integer < 3" is not a named
marker, "abc < :integer" is a named marker
(please correct me, if I'm missing an important use case)
For other paramstyles, such as the format and pyformat ones,
you probably need to apply the standard "%%" escaping, since
"%" is sometimes used in SQL as operator (e.g. MySQL).
However, I think we can leave those deliberately underspecified
in DB-API 3.0 to both encourage use of one of the mandatory
paramstyles, and to allow database module authors to continue
exposing natively used paramstyles.
Something we do need to address in a paramstyle spec for
qmark and named is SQL comments. Perhaps easiest would be
to disallow them in SQL statements passed to .execute*().
PS: mxODBC 3.2 implements both styles and does conversion from
named to qmark. When choosing named style, we explicitly disallow
question marks to appear in the SQL statement to detect errors
early and give proper error messages to the user, rather than
some obscure "too few parameters for statement".
--
Marc-Andre Lemburg
eGenix.com
Professional Python Services directly from the Source (#1, May 21 2013)
>>> Python Projects, Consulting and Support ... http://www.egenix.com/
>>> mxODBC.Zope/Plone.Database.Adapter ... http://zope.egenix.com/
>>> mxODBC, mxDateTime, mxTextTools ... http://python.egenix.com/
________________________________________________________________________
::::: Try our mxODBC.Connect Python Database Interface for free ! ::::::
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
http://www.egenix.com/company/contact/
_______________________________________________
DB-SIG maillist - DB-SIG@python.org
http://mail.python.org/mailman/listinfo/db-sig
