Django: Using named parameters on a raw SQL query

Question

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

This seem 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 a indicator, but this resulted in sql trying to parse

%u[_email]

and that returned a type error.

I'm executing the query like:

 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?

Answer 1

edit:

The format needs to have s suffix as following:

%(u_email)s

Answer 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