Raw SQL parameters
Matias Surdi
I'm trying to run a sql query with parameters taken from a dict, here is
the relevant part of the code:
query = "select * from table where name='%(name)s'"
parameters = {'name':'valueofname'}
cursor = connection.cursor()
data = cursor.execute(query,parameters)
The error I get is:
TypeError: format requires a mapping
But, as far as I know (from PEP249) this should be possible.
Basically, what I need, is to pass the parameters values in a dict, and
not as a list or tuple as is shown in django docs.
Which is the correct way to accomplish this?
Thanks a lot.
Karen Tracey
Hi,
I'm trying to run a sql query with parameters taken from a dict, here is
the relevant part of the code:
query = "select * from table where name='%(name)s'"
Remove the single quotes around '%(name)s'. The backend will handle quoting, I expect the extra quotes are causing confusion somewhere.
Karen
Matias Surdi
I've also tried with:
cursor.execute("select * from table where
field=:value",{'value':'something'})
And I got the error:
"Type error: not all arguments converted during string formatting"
The curious thing here, is that the above query works perfect running it
directly through sqlite3.
Ramiro Morales
>
> The curious thing here, is that the above query works perfect running it
> directly through sqlite3.
>
From what I have seen by reading DB backend source code, Django cursor's
execute() method supports only the printf-like parmeter maker style with a list
or tuple of actual parameters.
If you want to use the pyformat parameter marking style (as described
in PEP 249),
you' ll need to use the native DB-API driver API as you've already discovered.
Regards,
--
Ramiro Morales
Karen Tracey
I didn't look at any code, I just tried a similar query on my own DB, using current 1.0.X branch code:
kmt@lbox:~/software/web/xword$ python manage.py shell
Python 2.5.1 (r251:54863, Jul 31 2008, 23:17:40)
[GCC 4.1.3 20070929 (prerelease) (Ubuntu 4.1.2-16ubuntu2)] on linux2
Type "help", "copyright", "credits" or "license" for more information.
(InteractiveConsole)
>>> from django.db import connection
>>> query = "SELECT * FROM Authors WHERE Author = %(name)s"
>>> parms = {'name': 'Manny Nosowsky'}
>>> cursor = connection.cursor()
>>> data = cursor.execute(query, parms)
>>> data
1L
>>> cursor.fetchall()
((4L, u'Manny Nosowsky', u'No', u''),)
>>>
>>>
That's using MySQL as the DB, so perhaps it is backend-specific? The only thing I needed to do to make the example shown originally work is remove the quotes (and use table/column/parm names that exist in my DB).
Karen
Ramiro Morales
>
> That's using MySQL as the DB, so perhaps it is backend-specific?
So it seems. the MySQL and PostgreSQL backend seem to (still?)
support it. But the Oracle crew removed it a while back:
http://code.djangoproject.com/changeset/9418
Regards,
--
Ramiro Morales
Matias Surdi
using.I'll try with mysql later.
Is this a bug? should it be reported as a ticket?
Karen Tracey
Yes, maybe it's just a problem with sqlite, which is te backend I'm
using.I'll try with mysql later.
Is this a bug? should it be reported as a ticket?
I see you opened ticket #10070, which is fine because I don't know the answer to whether it is a bug that some of the backends do not support this style of parameter passing. Django apparently doesn't use it internally, nor document support for it (http://docs.djangoproject.com/en/dev/topics/db/sql/ doesn't mention it) so it may be permissible variation in the backends, I don't know. Hopefully someone who knows more than I will respond in that ticket.
Karen
Matias Surdi
Malcolm Tredinnick
> Hi,
>
>
> I'm trying to run a sql query with parameters taken from a dict, here is
> the relevant part of the code:
> query = "select * from table where name='%(name)s'"
> parameters = {'name':'valueofname'}
> cursor = connection.cursor()
> data = cursor.execute(query,parameters)
>
>
> The error I get is:
>
> TypeError: format requires a mapping
>
>
> But, as far as I know (from PEP249) this should be possible.
Just to be accurate, PEP 249 says that a paramstyle of "pyformat" is one
possible value, which would permit the above sort of query. It does not
say that every conforming database wrapper is required to support it.
Although the PEP recommends supporting "numeric", "named" or pyformat"
styles of parameter markers, historically and practically, most database
wrappers have supported "format", although SQLite (and some other
databases that don't have backends in Django's core, although the exact
names escape me now) only supports "qmark".
Since you note later in the thread that you're using SQLite, that is the
cause of the confusion here. I don't think there's any bug.
Regards,
Malcolm
Ian
wrote:
> possible value, which would permit the above sort of query. It does not
> say that every conforming database wrapper is required to support it.
> Although the PEP recommends supporting "numeric", "named" or pyformat"
> styles of parameter markers, historically and practically, most database
> wrappers have supported "format", although SQLite (and some other
> databases that don't have backends in Django's core, although the exact
> names escape me now) only supports "qmark".
>
> Since you note later in the thread that you're using SQLite, that is the
> cause of the confusion here. I don't think there's any bug.
to using that style through the Django cursor wrapper is that the
backend will intercept the query, assume it's written in the "format"
style, and attempt to convert it to "qmark", raising an error when
that fails. I think this is not a bug, but a design question: should
Django support passing query parameters as mappings; if so, should the
backend pass the engine's native style through unchanged, or should we
standardize on "pyformat" (as we have already standardized on "format"
when the parameters are passed as a sequence)?
We should move this discussion to the developers list or the ticket.
Ian
Malcolm Tredinnick
If you want. Personally, I'm struggling to care about it that much. It's
simply not a blocker to any real functionality.
Malcolm