Django: Using named parameters on a raw SQL query

Question

I'm trying to execute a raw query that is built dynamically. To assure that the parameters are inserted in the valid position I'm using named parameters.

This seems to work for Sqlite without any problems. (all my tests succeed) But when I'm running the same code against MariaDB it fails...

A simple example query:

 SELECT u.* 
    FROM users_gigyauser AS u
  WHERE u.email like :u_email
    GROUP BY u.id
    ORDER BY u.last_login DESC
  LIMIT 60 OFFSET 0

Parameters are:

 {'u_email': '%test%'}

The error I get is a default syntax error as the parameter is not replaced. I tried using '%' as an indicator, but this resulted in SQL trying to parse

%u[_email]

and that returned a type error.

I'm executing the query like this:

raw_queryset = GigyaUser.objects.raw(
    self.sql_fetch, self._query_object['params']
)

Or when counting:

cursor.execute(self.sql_count, self._query_object['params'])

Both give the same error on MariaDB but work on Sqlite (using the ':' indicator)

Now, what am I missing?

using %(u_email) as stated in the docs (docs.djangoproject.com/en/1.8/topics/db/sql) results in a "incomplete format " error btw — Arron
– Arron, Commented Mar 30, 2016 at 14:22
TNX!!!!! If you put this comment as a answer, it can be marked as answer. — Arron
– Arron, Commented Mar 30, 2016 at 15:02

yedpodtrzitko · Accepted Answer · 2016-03-30 15:05:09Z

12

edit:

The format needs to have s suffix as following:

%(u_email)s

edited Mar 30, 2016 at 15:05

answered Mar 30, 2016 at 14:22

yedpodtrzitko

9,4392 gold badges49 silver badges47 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Arron Over a year ago

There is a reason for not using the ORM,.. and it is a good one. I'm not afraid for sql-injection... so that is not a problem either.

Arron Over a year ago

Django orm is nice for the simple object related database queries. When however your database structure becomes more advanced and you need to generate exact reports/selections things go bad. I need to generate specialized selections based on a dynamic json object.. generated by a javascript tool

Jack Scandall · Accepted Answer · 2020-07-17 21:39:10Z

2

If you are using SQLite3, for some reason syntax %(name)s will not work. You have to use :name syntax instead if you want to pass your params as {"name":"value"} dictionary.

It's contrary to the documentation, that states the first syntax should work with all DB engines.

Heres the source of the issue: https://code.djangoproject.com/ticket/10070#comment:18

answered Jul 17, 2020 at 21:39

Jack Scandall

4124 silver badges11 bronze badges

Collectives™ on Stack Overflow

Django: Using named parameters on a raw SQL query

2 Answers 2

2 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related