[DB-SIG] Two sample implementations of ['named', 'qmark'] auto switch (a report)

Daniele Varrazzo daniele.varrazzo at gmail.com
Tue May 21 19:24:16 CEST 2013

On Tue, 2013-05-21 at 17:32 +0100, Vernon D. Cole wrote:

> Daniele uses pure autoswitching.  His only reference to paramstyle is when
> it is defined.  I quote:
> >>>paramstyle = 'named' # or qmark? whatever.
> The heart of his algorithm is as follows (I will cut out details)..
> > from collections import Mapping, Sequence
> >
> > ...clip...
> > def convert_params(query, args):
> >     """
> >     Convert a qmark query into "format" or a named query into "pyformat".
> >
> >     I'm not sure it is possible to disambiguate the two query styles, I'm not
> >     even sure anybody has tried showing it is possible or the contrary. So we
> >     try to infer from the args which type of arguments the query has.
> >     """
> >     if args is None:
> >         # no placeholder here
> >         return query
> >
> >
> >     elif isinstance(args, Mapping):
> >             ...clip...
> >         return RE_NAMED.sub(sub_mapping, query)
> >     elif isinstance(args, Sequence):
> >
> >             ...clip...
> >         return RE_QMARK.sub(sub_sequence, query)
> >     else:
> >         raise TypeError('expected a sequence or mapping argument')
> >

This was the code from qmarkpg 0.1, which was supporting both qmark and
named. Note a fundamental flaw: if args is a sequence it unescapes ??
into ?; if it is a mapping it unescapes :: into :. But if it is None it
doesn't unescape anything, i.e. the kind of nuisance Michael was
reporting about psycopg not doing %% -> % if no param is passed.

Because of this shortcoming I've dropped the mapping support altogether
from qmarkpg 0.2, because qmark is sufficient for the use in a framework
looking for uniformity more than flexibility and because so I can
unescape ?? -> ? in any situation - whether I got params or not.

> >  A quick check of the psycopg2 documentation reveals that, indeed, it does
> not care whether you hand it 'format' or 'pyformat' queries: it
> autoswitches.  It has done that for years.    Daniele just hands off the
> autoswitch to it.

Yes, but with a fundamental difference: psycopg switches according to
the placeholder found in the query, it doesn't look at the type of the
param; i.e. the algorithm used is something like:

- find the first % in the query string
- take the following char
- is it a '%'?
  - add a single '%' to the query
- is it an 's'?
  - we are dealing with a sequence
  - escape and add args[0] to the query
- is it an '('?
  - we are dealing with a mapping
  - parse the name until the ')s'
  - escape and add args[name] to the query
- everything else is an error

and goes on but giving error if a mix of %s and %(name)s is found. It is
a stripped-down version of the string % operator (which indeed can take
no problem either a tuple or a mapping on the RHS).

It works spectacularly fine but only because the placeholders are not
ambiguous: looking at the char after each % in the query I can tell
unambiguously if I am in format, pyformat or error state.

I am not sure you can do the same with the qmark/named pair: this is the
heart of my observation that format/pyformat are a superior combination
w.r.t. parseability and it's not easy so write an adapter supporting
qmark/named *together* (whereas supporting only one of them is
straightforward). Really, it's not like I don't like to code :)

> (I admired his use of collections.Mapping, and immediately copied it. Made
> my code one line longer for the import.)
> My conclusion:  Auto switching is easy, practical, and has years of
> precedent history. DB-api 3 should use it as the default action.

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?


More information about the DB-SIG mailing list