Passing parameters to raw()

[Roman Klesel]

Hello,


the docs say:

"""
Passing parameters into raw()

If you need to perform parameterized queries, you can use the params
argument to raw():

>>> lname = 'Doe'
>>> Person.objects.raw('SELECT * FROM myapp_person WHERE last_name = %s', [lname])

params is a list of parameters. You’ll use %s placeholders in the
query string (regardless of your database engine); they’ll be replaced
with parameters from the params list.
"""

howerver this woks just fine and I see no reason why this should not be done:

>>> param = dict(lname = 'Doe')
>>> qs = Person.objects.raw('SELECT * FROM myapp_person WHERE last_name = %(lname)s', param)

however still this fails:

>>> repr(qs)
/home/user/vpy/dev/lib/python2.7/site-packages/django/db/models/query.pyc
in __repr__(self)
1530
1531 def __repr__(self):
-> 1532 return "<RawQuerySet: %r>" % (self.raw_query %
tuple(self.params))
1533
1534 def __getitem__(self, k):

TypeError: format requires a mapping

If no one objects, I could write a patch to the code and the docs that
implements the functionality.

Regards Roman

[Shai Berger]

Hi Roman,

On Wednesday 22 May 2013, Roman Klesel wrote:
>
> howerver this woks just fine and I see no reason why this should not be done:
> >>> param = dict(lname = 'Doe')

> >>> qs = Person.objects \

> >>> .raw('SELECT * FROM myapp_person WHERE last_name = %(lname)s', param)
>

This currently works almost by chance -- it depends on the database access
library's support for 'pyformat' parameter style; support which, luckily, is
there in most supported databases. The Oracle backend, specifically, does not
currently support this; see ticket #10070[0]. Also, there was very recently a
related discussion on this list[1].

> however still this fails:
> >>> repr(qs)
>
> /home/user/vpy/dev/lib/python2.7/site-packages/django/db/models/query.pyc
> in __repr__(self)
> 1530
> 1531 def __repr__(self):
> -> 1532 return "<RawQuerySet: %r>" % (self.raw_query %
> tuple(self.params))
> 1533
> 1534 def __getitem__(self, k):
>
> TypeError: format requires a mapping
>
> If no one objects, I could write a patch to the code and the docs that
> implements the functionality.
>

It would be nice if you could add this note to ticket #10070 -- I don't
believe the current patches against it address it.

Thanks,
Shai.

[0] https://code.djangoproject.com/ticket/10070
[1] https://groups.google.com/d/topic/django-developers/c1t3zwKMMmk/discussion

[Russell Keith-Magee]

Hi Roman, 

Sounds reasonable to me. Off the top of my head, I'm not certain how many of the database backends support the pyformat paramstyle, but given that at least some of them do, I can't see why the __repr__ of .raw() shouldn't support it.

Yours,

Russ Magee %-)

[VernonCole]

Officially (according to PEP-249) you are not supposed to be able to do that (pass a dictionary  of parameters), but PostgreSQL does it as an extension to the spec.  SQLite and ms-sql (both of which use format converters to send your queries out with '?' where you put the '%s') do NOT support it.  Shai is supplying a patch for Oracle (which uses a different format conversion) to support it.

 
There is a very noisy discussion taking place right now on the DB-SIG about the design of a future db-api version 3.  I am suggesting that we support automatic switching (similar to what you want to use here) but at the moment, I am loosing, and I do not expect that it will happen.  So you can do that now, on some databases, but do not expect it to be either universal or future proof.  I would advise sticking with a list of parameters for your present development efforts.

[Russell Keith-Magee]

On Thu, May 23, 2013 at 2:19 PM, VernonCole <verno...@gmail.com> wrote:

Officially (according to PEP-249) you are not supposed to be able to do that (pass a dictionary  of parameters), but PostgreSQL does it as an extension to the spec. 

Are you sure about this? 

http://www.python.org/dev/peps/pep-0249/#paramstyle

lists 5 officially supported paramstyle formats, including pyformat, and "numeric", "named" and "pyformat" are specifically listed as preferred formats (ironically, leaving qmark and format, the most common options in practice, as less preferred by the standard).

 

Yours,

Russ Magee %-)

[Roman Klesel]

Oh wow!

I didn't expect to enter such difficult terrain ... ;-)

From PEP-249 I understand that pyformat is encouraged and supported.

I also read throug the discussion on the sig-db mailing list. As far
as I understand you (Veron and many others) are trying to settle on
what will be in the specs of v3.0 of the python db interface. And as
far as I can see the opinions vary from qmark only to everything that
has ever been used.

The current situation appears to be like that: The different suppliers
of db interfaces provide either all paramstyles or a subset of them.
qmark and named are provided by all of them (maybe not).

I would like to suggest, that we not try to sort this out in on the
django side. Rather pass through the current situation and leave it up
to the user what he/she want's to do. Still it might be good to point
this out in the docs and suggest something that at the moment looks
most future prove.

Regards Roman

[VernonCole]

Understand that this will not affect django for the next several releases.  Probably django 2.0.  The transition from db-api 2 to db-api 3 will be a lot like the transition from Python 2 to Python 3.  You will have to import a different module to get the different behavior, and today's api-2 interface will linger around for innumerable years to come.

The reason that 'format' and 'pyformat' have fallen into disfavor is that they *look* like the %s and %(identifier)s used by the string "%" operator, but they don't quite *work* like them.  That causes no small amount of confusion and trouble -- such as having to use %%s sometimes but not others.  I need not explain to this group, you have all been bitten by it, or will be.  Someday, some poor human will, I suspect, have to look at every occurrence of %s in the entire django code base and decide whether to replace it with '?' because it is going to an SQL query, or with "{}" because it will be a string format operation. The only examples of %s still left will be in templates, and in ADO connection strings.  [Having heard myself say that, I may decide to write a .format() method for Python 2.5 so that I can keep them out of ADO connection strings -- so that that problem would never appear.  Must give us pause.]
 
Expect % as a format specifier in an SQL query to go away ... someday ... but not today, and not tomorrow.
--
Vernon Cole

On Wednesday, May 22, 2013 3:37:55 AM UTC-6, roman wrote: