[DB-SIG] Context manager support for cursors

[Daniele Varrazzo]

On Thu, Jul 26, 2018 at 2:57 PM, Mike Bayer <mike_mp at zzzcomputing.com> wrote:
> On Thu, Jul 26, 2018 at 9:45 AM, Daniele Varrazzo

>> Maybe it's easy enough to use the most generic one ("%s" and
>> "%(name)s"? "{}" and "{name}"?) and provide a conversion library to
>> help implementing drivers using whatever different format they have to
>> use?
>
> there are so many ways to make this super easy yet I still get
> complaints about people wanting to support some non-standard pet
> parameter style their driver uses (asyncpg uses $<position> or
> something like that).

It sounds like the Postgres server-side parameters
(https://www.postgresql.org/docs/current/static/libpq-exec.html#LIBPQ-PQPREPARE),
which is not listed in the DBAPI 2 paramstyle choices. But that's why
I think the approach of allowing the driver to pass-through
placeholders the way the underlying protocol/library wants is wrong,
from the PoV of designing something supposed to be portable.


> My votes remain at support ? and :name
> (solves your percent sign issue).

But it replaces it with two different issues. ? is a valid Postgres
operator for some data type (JSON), : is used in the cast operation,
value::type. Also, where does "name" ends? Can I have names with an
hyphen? Can I have another token concatenated after :name without
space?

If there is a mechanism of placeholders to replace with values there
must be a mechanism to escape placeholders: I don't think there's a
way around it. The approach "let's use this funny character which
nobody use" is short-sighted: that character *will* be used
eventually, and it's not up the driver's author to forbid it. The
assumption is already weak for an interface targeting a single system,
but it's entirely broken for something like DBAPI that would try to be
agnostic and allow for future drivers to be written. The chars used as
placeholder marker can also appear in comments and strings, so either
the query author will escape them or the driver will have to tokenize
the query to tell apart the ? and the : which are not parameters,
which is not trivial if the database syntax is not trivial (there are
nested strings, there are nested comments...): IMO not only it's not
the driver's task, but it's also a totally unnecessary complication of
a very simple matter.

The last time there was an approach to go past DBAPI 2 we crashed on
the placeholder style and didn't go further. I hope, if the matter is
open again, that we don't go down the same path. It's not "I like ?
more than $" bikeshedding: if the mechanism doesn't allow for escaping
placeholder chars it's just a broken system and a non-starter. We may
as well leave the thing to anarchy and allow drivers authors do expose
whatever the underlying protocol fancy, leaving the responsibility to
use the right format to SQLAlchemy and the Django ORM, which have to
address the different SQL syntax anyway.

I personally think the responsibility to mandate a single params style
sits square in the DBAPI field, because it is there exactly to allow
working with different protocols homogeneous. But if the DBAPI decides
to avoid this responsibility then at least it shouldn't mandate "they
should be one of these fews". The entire presence of the "paramstyle"
attribute is broken by the way: it doesn't consider drivers using more
than one (qmark and named, or format and pyformat), or to have
different formats for different connections (such as a "native" one
and a DBAPI one). I don't think the presence of the paramstyle
attribute is really useful in any scenario: you will still need to
read the documentation of the driver to be able to use it correctly.


-- Daniele