[DB-SIG] Context manager support for cursors

[Mike Bayer]

On Thu, Jul 26, 2018 at 1:03 PM, Daniele Varrazzo
<daniele.varrazzo at gmail.com> wrote:
> 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?

I'll give you the ? issue but for :name if you want spaces it comes
out as :"my name", e.g. quote the identifier like any other.


>
> 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".

of course.  if every DBAPI has to use the same single or two
paramstyles and the rules for escaping etc. are very clear and
unambiguous, that is all we need.

> 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.

no kidding.  but if you have just two paramstyles, one is positional
and one named, then you can assume the paramstyle from execute("", {})
vs. execute("", []).


>
>
> -- Daniele