django and paramstyle: what's the actual story?

[VernonCole]

I noticed in a recent post that there is an outstanding patch for Oracle for support of 'named' paramstyle.

I also found, during my morning's research on the question, that there has been at least one patch so that django will play nice with 'pyformat' paramstyle.

I got involved with this entire question when I back-ported Adam Vandenberg's paramstyle conversion code into the development fork of adodbapi.  It appeared to me, and seems to be widely believed in the community (enough so that I have stated it as a fact) that "django blindly expects 'format' paramstyle."  Not only django-mssql, but also the sqlite adapter convert incoming queries from 'format' into 'qmark' before handing it off to the SQL engine.  My revision to adodbapi permits the programmer to SET paramstyle (to one of 'named', 'qmark', or 'format') and the adapter does the conversion.  The version now in development adds the capability of making that selection using a keyword argument to the 'connect' call.  The patched version of the django-mssql back end just sets that keyword and goes on its merry way.

Recent traffic on the Python database discussion list has floated the idea that it is time for an upgrade to PEP 249, making a new db-api version 3.0 with a new PEP.  The consensus opinion is running toward paring the available paramstyle options down to two, and forcing adapter authors to support both.  The two winners were 'qmark' and 'named'.  My latest input to that effort was a plea that 'format' must be kept because of the widespread use of it in existing code -- and I specifically mentioned django.  But was I wrong?

I am not sure whether I am sounding a warning, or asking a question.  If a programmer is using the ORM, the whole question of paramstyle confusion is unimportant -- the ORM takes care of it.  To a programmer who makes raw SQL calls, it is all important.  Do I make a parameter list, or a parameter dictionary? 

What is the feeling of this group?  Should I maintain my activism that %s 'format' paramstyle be maintained into the future?  Or should I be helping the django community to prepare for a new future where one must (or may?) choose between a parameter list with "?" or a parameter mapping with ":paramname"?  Personally, I do not really like the '%s' style would be happy if it goes away.  What do you think?
--
Vernon Cole

[mjl Martin J. Laubach]

  As for the death of '%s' style -- yes please, with sprinkles on top. It is totally violating the principle of least astonishment as anyone will expect '%s' to expand to a parameter string; yet that's not what it does, it magically adds quotes and whatnot.

  The other two variants are widely understood.

        mjl

[Aymeric Augustin]

On 16 mai 2013, at 09:29, VernonCole <verno...@gmail.com> wrote:

What is the feeling of this group?  Should I maintain my activism that %s 'format' paramstyle be maintained into the future?  Or should I be helping the django community to prepare for a new future where one must (or may?) choose between a parameter list with "?" or a parameter mapping with ":paramname"?  Personally, I do not really like the '%s' style would be happy if it goes away.  What do you think?

The consequences for Django sound rather benign to me. I wouldn't worry too much.

If there's a new version of the DB API that standardize parameter style, I'll most likely advocate following it.

I also dislike the '%s' style because it interacts badly with string interpolation as soon as you try displaying the query, for instance in DebugCursorWrapper.

-- 

Aymeric.

[Shai Berger]

On Thursday 16 May 2013, VernonCole wrote:
> I noticed in a recent post that there is an outstanding patch for Oracle
> for support of 'named' paramstyle.
>

As the author of that patch, I should probably clarify that it is a "format"
style -- %(name)s -- not a "named" -- :name -- style.

AFAIK, that style is currenly supported by all core django backends (well,
except Oracle, of course); I know our code which uses it has been successfully
run against SQLite and Postgres, and I suspect django-pyodbc also supports it.
It has its problems, both with respect to Python string interpolation and to
SQL's pattern operator, but AFAIK it is the most common in use.

[VernonCole]

Shai:

  I think that you are showing how rotten this whole "paramstyle" mess is: the thing you are describing is, IIUC, "pyformat" paramstyle.  "named" uses a ":name" SQL statement syntax, and expects a mapping of parameters.  My understanding was that Oracle expected that one.  I am only an egg.

Here's my understanding:

'qmark' uses '?' inside SQL statements, and expects parameters in a sequence, to be applied in order.

'format' uses '%s' inside SQL statements, and expects parameters in a sequence, to be applied in order.

'numeric' uses ":n" (where 'n' is an integer), and expects parameters in a sequence, to be applied by index number 'n'.

'named' uses ":name" inside SQL statements, and expects parameters in a mapping, to be applied by name.

'pyformat' uses "%(name)s", and expects parameters in a mapping.

The intent of PEP-249 is that the ORM should look at the value of adapter.paramstyle and use that format, whatever it happens to be.

Clearly, that is not happening.  Should it be put on the To-do list for some future version of django,  at least to supporting the two expected future winners in the parmstyle competition ('named', 'qmark')?
--
Vernon Cole

[Shai Berger]

Hi Vernon and all,

On Friday 17 May 2013, VernonCole wrote:
> Shai:
>
> I think that you are showing how rotten this whole "paramstyle" mess is:
> the thing you are describing is, IIUC, "pyformat" paramstyle. "named" uses
> a ":name" SQL statement syntax, and expects a mapping of parameters. My
> understanding was that Oracle expected that one. I am only an egg.
>

Oracle (as in, its C libraries) indeed expects named (":name") parameters, and
so (AFAIK) does cx_Oracle.

> Here's my understanding:
>
[...]

>
> 'pyformat' uses "%(name)s", and expects parameters in a mapping.
>
> The intent of PEP-249 is that the ORM should look at the value of
> adapter.paramstyle and use that format, whatever it happens to be.
>

As you mentioned in your first message, we are talking here about raw selects,
and not ORM-written queries. I hope we can make those standard in the sense
that standard SQL can be written cross-database; I have almost no preference
among the competing paramstyles, as long as some form where parameters are
passed in a mapping and referenced by name is supported.

For the record, I am currently interested in having the same code run against
SQLite, PostgreSQL, Oracle and MSSQL.

> Clearly, that is not happening. Should it be put on the To-do list for
> some future version of django, at least to supporting the two expected
> future winners in the parmstyle competition ('named', 'qmark')?

As far as I know, all current core backends support 'format' ("%s") and
'pyformat' ("%(name)s"), except Oracle/pyformat (which my patch fixes). Oracle
is unique among the core backends in not passing the arguments received to the
interface library as received, but reconstructing its own params structure --
mostly on account of cx_Oracle supporting only 'named' paramstyle.

Have fun,
Shai.