[DB-SIG] API 3.0 limiting paramstyle to ['named', 'qmark'] is okay. ('format' is not desirable)

[Daniele Varrazzo]

On Mon, May 20, 2013 at 8:31 PM, Chris Clark <Chris.Clark at actian.com> wrote:

Hi Chris, thanks for your summary, it has explained me a couple of things.

> We had a voting thread, for example
> http://mail.python.org/pipermail/db-sig/2007-June/005084.html (although
> there where earlier threads in 2006 April).

That's why I haven't found traces of the poll in my mail and just
trusted it: I wasn't subscribed yet to the dbsig at the time, and I
thought you were referring to some much more recent poll.


> For most drivers (for example, ODBC and JDBC) there is a single bind
> parameter indicator and it is '?', the driver implementer MUST implement it.
> The original Python dbi spec was (I think) a thin veneer over the native
> drivers that did not attempt to hide the DBMS parameter passing symbols.
> This appears to be the real sticking point, we have 2 use cases
>
> 1. "I want to write a python database application"
>      *
>
>        Some users want a single way that works with all backends. I'm
>        conveniently ignoring SQL dialect issues here, for a simple
>        "SELECT col1 from some_table where col2 = ?" SQL dialects are
>        not an issue. The ODBC (etc.) spec ignore SQL dialect issues too.
>
> 2. "I want to write a python <insert favorite DBMS here> database
>    application".
>      * they really want Python access to the database API that doesn't
>        get in the way. They are not concerned about other vendors.
>        There is nothing wrong with this approach for those users but
>        this isn't a good use case when support for other backends is
>        needed.

This is a very interesting split, and shows pretty much where all of
our disagreement comes from. As a driver developer I have the 2.-style
users in mind and want to use my driver directly and allow to use
whatever feature the database I support exposes. Michael thinking
about SQLAlchemy, Vernon thinking about Django focus on an 1.-style
usage, where they write a program using the driver and the final user
only uses the driver indirectly. From the latter point of view I agree
uniformity is more valuable than flexibility (e.g. to permit the same
driver to support both sequence and mapping arguments).

With uniformity in mind, I think the requirement in the title of this
thread should be further restricted rejecting the :named style too,
leaving indeed *only* qmark as supported paramstyle: a driver that
would attempt to be flexible and implement both would find an
ambiguous grammar to deal with, whereas leaving only the ? to juggle
is a much easier problem. I will soon update qmarkpg to only support
its eponymous paramstyle, dropping named.

It is now a trivial task to write a generalized version of qmarkpg,
taking a conforming dbapi2 driver and converting it into a driver
supporting qmark and only qmark, getting rid of any ambiguities. I
would call it qmarker. Ironically, looking to see if the name was
already taken in PyPI I've found:

https://pypi.python.org/pypi/sqlparams/

"""
sqlparams is a utility module for simplifying the use of SQL
parameters in queries. Some Python DB API 2.0 compliant modules only
support the ordinal qmark or format style parameters (e.g., pyodbc
only supports qmark). This utility module provides a helper class,
SQLParams, that is used to support named parameter styles such as
named, numeric and pyformat, and have them safely converted to the
desired ordinal style.
"""

He must be a frustrated 2.-style user :)

So I believe there is really no need for the spec to restrict
placeholder styles: you may come out with two flavours of the specs:
kinda dbapi3-human and dbapi3-orm, the latter demanding qmark only;
but because you can mechanically convert any placeholder to qmark in a
wrapper module I see no reason for the dbapi to rule on that and cut
out the realistic use cases of direct users of a dbapi module (at the
same time forcing an unworkable upgrade path). SQLAlchemy, Django,
etc. may just use psycopg2 as:

    import qmarker
    import psycopg2 as _psycopg2
    psycopg2 = qmarker.qmark_module(_psycopg2)
    # your magic here


-- Daniele