Opened 8 years ago

Last modified 16 months ago

#10070 new Bug

Named parameters not working on raw sql queries with sqlite

Reported by: Matias Surdi Owned by: nobody
Component: Database layer (models, ORM) Version: 1.0
Severity: Normal Keywords:
Cc: shai@…, mike@…, Joseph Gordon Triage Stage: Accepted
Has patch: yes Needs documentation: no
Needs tests: yes Patch needs improvement: no
Easy pickings: no UI/UX: no

Description (last modified by Ramiro Morales)

The following code shows the problem when using sqlite:

$ python manage.py shell
Python 2.5.2 (r252:60911, Oct  5 2008, 19:29:17) 
[GCC 4.3.2] on linux2
Type "help", "copyright", "credits" or "license" for more information.
(InteractiveConsole)
>>> from django.db import connection
>>> c = connection.cursor()
>>> c.execute("select name from inventory_host where id=%(id)s",{'id':'1'})
Traceback (most recent call last):
  File "<console>", line 1, in <module>
  File "/usr/lib/python2.5/site-packages/django/db/backends/util.py", line 19, in execute
    return self.cursor.execute(sql, params)
  File "/usr/lib/python2.5/site-packages/django/db/backends/sqlite3/base.py", line 167, in execute
    query = self.convert_query(query, len(params))
  File "/usr/lib/python2.5/site-packages/django/db/backends/sqlite3/base.py", line 179, in convert_query
    return query % tuple("?" * num_params)
TypeError: format requires a mapping
>>> 
>>> import django
>>> django.VERSION
(1, 0, 2, 'final', 0)
$ sqlite3 --version
3.5.9

When using Mysql or Postgres, that works (not tested by me, but by others on django-users).

Attachments (2)

svnpatch.diff (1.3 KB) - added by Matias Surdi 8 years ago.
allow dict as parameters
base.diff (1.9 KB) - added by anonymous 7 years ago.
Another approach using try and a pseudo dictionary

Download all attachments as: .zip

Change History (17)

comment:1 Changed 8 years ago by Ramiro Morales

Description: modified (diff)

(description formatting)

comment:2 Changed 8 years ago by Ramiro Morales

Adding info from the django-users thread:

SQLite and Oracle backends don't support this. In fact, Oracle used to have this and it was deleted in [9418] in response to a ticket (#9478) that reported it was faulty with this commit message "Removed support for passing params as dicts in the oracle backend. Wasn't documented, didn't work, isn't necessary".

The relevant docs (http://docs.djangoproject.com/en/dev/topics/db/sql/#topics-db-sql) don't state clearly if using named parameter marker plus a mapping is supported or not, so maybe this is a documentation issue?.

comment:3 Changed 8 years ago by Ian Kelly

The main reason for removing it from Oracle was that it never worked in the first place, nobody seemed to be using it, and it didn't seem worthwhile to maintain at the time. If there is demand to add this as an explicitly documented feature, I'd be happy to put it back in.

comment:4 Changed 8 years ago by Matias Surdi

I'm attaching a patch for SQLite.

With this patch, now the parameters can be passed as a dict, and the query can contain %(parameter)s as with mysql or postgres.

Changed 8 years ago by Matias Surdi

Attachment: svnpatch.diff added

allow dict as parameters

comment:5 Changed 8 years ago by Alex Gaynor

Triage Stage: UnreviewedDesign decision needed

comment:6 Changed 8 years ago by Ivan Giuliani

Component: UncategorizedDatabase layer (models, ORM)
Needs tests: set

Changed 7 years ago by anonymous

Attachment: base.diff added

Another approach using try and a pseudo dictionary

comment:7 Changed 7 years ago by ke1g.nh@…

I've attached a different fix (http://code.djangoproject.com/attachment/ticket/10070/base.diff) for this (though similar in places to msurdi's). Sorry about the annonymous user on the attachement, but I didn't see how to combine it with this.

It should be faster in the apparently more common case of supplying a sequence, since the only extra overhead is the try block setup.

Also, since the text replacement is done by the format operator itself, I'm more confident that the result will be correct, where I have to worry about missing the fact that a percent sign is quoted when using the regular expression replace approach of the original patch.

A hint if anyone doesn't find the colonifier to be obvious: the format operator only cares that its right operand has a getitem method. (Tested with python versions 2.4 and 2.6.)

This now allows my PostgreSQL developed mapping queries to work with sqlite3 (Django-1.0.3, python 2.6).

Sadly, I have no test.py code to offer.

Bill

comment:8 Changed 6 years ago by Chris Beaven

Severity: Normal
Type: Bug

comment:9 Changed 5 years ago by Aymeric Augustin

Easy pickings: unset
Triage Stage: Design decision neededAccepted
UI/UX: unset

Based on Ramiro's and Ian's input in the first comments, there's no fundamental reason not to support named parameters on Oracle and SQL, it's just a matter of writing a solid implementation.

comment:10 in reply to:  2 Changed 4 years ago by Shai Berger

Cc: shai@… added
Has patch: set

Replying to ramiro:

In fact, Oracle used to have this and it was deleted in [9418] in response to a ticket (#9478)

For future reference, the relevant ticket is #9408. The code that was removed indeed did not really support named parameters (except by names "arg0", "arg1" etc.).

Pull Request 411 supports named parameters for real. No tests provided (it still isn't a documented feature as far as I know...). I can do the documentation and add tests if this is deemed required.

comment:11 Changed 4 years ago by anonymous

I think at very least this should be mentioned explicitly in the docs.

comment:12 Changed 4 years ago by Mike Fogel

Cc: mike@… added

comment:13 Changed 4 years ago by rupa108

I started a related discussion here https://groups.google.com/forum/#!topic/django-developers/ts14HX9Yz0I

named parameters do currently work with some db interfaces but the code breaks on the repr() method of a RawQuerySet:

param = dict(lname = 'Doe')
qs = Person.objects.raw('SELECT * FROM myapp_person WHERE last_name = %(lname)s', param)
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
Last edited 16 months ago by Tim Graham (previous) (diff)

comment:14 Changed 3 years ago by Shai Berger <shai@…>

In d097417025e71286ad5bbde6e0a79caacabbbd64:

Support 'pyformat' style parameters in raw queries, Refs #10070

Add support for Oracle, fix an issue with the repr of RawQuerySet,
add tests and documentations. Also added a 'supports_paramstyle_pyformat'
database feature, True by default, False for SQLite.

Thanks Donald Stufft for review of documentation.

comment:15 Changed 17 months ago by Joseph Gordon

Cc: Joseph Gordon added
Note: See TracTickets for help on using tickets.
Back to Top