Passing lists or tuples as arguments in django raw sql

Question

I have a list and want to pass thru django raw sql.

Here is my list

region = ['US','CA','UK']

I am pasting a part of raw sql here.

results = MMCode.objects.raw('select assigner, assignee from mm_code where date between %s and %s and country_code in %s',[fromdate,todate,region])

Now it gives the below error, when i execute it in django python shell

Traceback (most recent call last):
File "<console>", line 1, in <module>
File "/usr/local/lib/python2.6/dist-packages/django/db/models/query.py", line 1412, in __iter__
query = iter(self.query)
File "/usr/local/lib/python2.6/dist-packages/django/db/models/sql/query.py", line 73, in __iter__
self._execute_query()
File "/usr/local/lib/python2.6/dist-packages/django/db/models/sql/query.py", line 87, in _execute_query
self.cursor.execute(self.sql, self.params)
File "/usr/local/lib/python2.6/dist-packages/django/db/backends/util.py", line 15, in execute
return self.cursor.execute(sql, params)
File "/usr/local/lib/python2.6/dist-packages/django/db/backends/mysql/base.py", line 86, in execute
return self.cursor.execute(query, args)
File "/usr/lib/pymodules/python2.6/MySQLdb/cursors.py", line 166, in execute
self.errorhandler(self, exc, value)
File "/usr/lib/pymodules/python2.6/MySQLdb/connections.py", line 35, in defaulterrorhandler
raise errorclass, errorvalue
DatabaseError: (1064, "You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ')' at line 1")

I have tried by passing the tuple also but there is no use. Can some one help me.

Thanks Vikram

Can you give us the sql query string your app is trying to execute? — niktrs, Aug 01 '11 at 07:07
This is my sql query which it was executing correctly and it is retrieving results. `select assigner, assignee from mm_code where date between '2011-07-21' and '2011-07-31' and country_code in ('US','UK','CA')` I have tried so many combinations but always giving error :-( — vkrams, Aug 01 '11 at 07:19
Yes.. the above query executes in Django Shell up to date condition but when I give the country_code condition, its giving errors. Please help me — vkrams, Aug 01 '11 at 07:25
Try using(changed char [ to ( ): MMCode.objects.raw('select assigner, assignee from mm_code where date between %s and %s and country_code in %s',(fromdate,todate,region)) — niktrs, Aug 02 '11 at 09:08
Using a tuple '()' over a list '[]' wouldn't make any difference. In fact the official [documentation](https://docs.djangoproject.com/en/dev/topics/db/sql/#passing-parameters-into-raw) uses a list for passing parameters. — Pannu, Aug 02 '11 at 12:38
I'm trying to make the same thing work but with id:s. For now I have to divide all the queries and run them one by one. Not a massive job but it doesnt feel quite DRY. — Gesias, Oct 17 '13 at 12:37

score 39 · Answer 1 · edited Jul 01 '14 at 00:15

For PostgreSQL at least, a list/tuple parameter is converted into an array in SQL, e.g.

ARRAY['US', 'CA', 'UK']

When this is inserted into the given query, it results in invalid SQL -

SELECT assigner, assignee FROM mm_code
WHERE date BETWEEN '2014-02-01' AND '2014-02-05'
AND country_code IN ARRAY['US', 'CA', 'UK']

However, the 'in' clause in SQL is logically equivalent to -

SELECT assigner, assignee FROM mm_code
WHERE date BETWEEN %s AND %s
AND country_code = ANY(%s)

... and when this query is filled with the parameters, the resulting SQL is valid and works -

SELECT assigner, assignee FROM mm_code
WHERE date BETWEEN '2014-02-01' AND '2014-02-05'
AND country_code = ANY(ARRAY['US', 'CA', 'UK'])

I'm not sure if this works in the other databases though, and whether or not this changes how the query is planned.

I can confirm this solution works in 2022 with PostgreSQL 10 and Django 3.2 — Nathan Karasch, Mar 09 '22 at 19:46
If anyone like me looks for the opposite it works by _negating_: `NOT country_code = ANY(ARRAY['US', 'CA', 'UK'])` — Pierre Lemée, Mar 01 '23 at 18:21

score 19 · Accepted Answer · answered Sep 20 '13 at 18:09

Casting the list to a tuple does work in Postgres, although the same code fails under sqlite3 with DatabaseError: near "?": syntax error so it seems this is backend-specific. Your line of code would become:

results = MMCode.objects.raw('select assigner, assignee from mm_code where date between %s and %s and country_code in %s',[fromdate,todate,tuple(region)])

I tested this on a clean Django 1.5.1 project with the following in bar/models.py:

from django.db import models

class MMCode(models.Model):
    assigner = models.CharField(max_length=100)
    assignee = models.CharField(max_length=100)
    date = models.DateField()
    country_code = models.CharField(max_length=2)

then at the shell:

>>> from datetime import date
>>> from bar.models import MMCode
>>> 
>>> regions = ['US', 'CA', 'UK']
>>> fromdate = date.today()
>>> todate = date.today()
>>> 
>>> results = MMCode.objects.raw('select id, assigner, assignee from bar_mmcode where date between %s and %s and country_code in %s',[fromdate,todate,tuple(regions)])
>>> list(results)
[]

(note that the query line is changed slightly here, to use the default table name created by Django, and to include the id column in the output so that the ORM doesn't complain)

score 4 · Answer 3 · edited Jul 08 '21 at 15:04

4

This is not a great solution, because you must make sure your "region" values are correctly escaped for SQL. However, this is the only thing I could get to work with Sqlite:

sql = ('select assigner, assignee from mm_code '
    'where date between %%s and %%s and country_code in %s' % (tuple(region),))
results = MMCode.objects.raw(sql, [fromdate,todate])

edited Jul 08 '21 at 15:04

djvg

11,722
5
72
103

answered Nov 03 '11 at 22:34

Dave

3,171
1
25
23

score 3 · Answer 4 · edited May 23 '17 at 10:30

I ran into exactly this problem today. Django has changed (we now have RawSQL() and friends!), but the general solution is still the same.

According to https://stackoverflow.com/a/283801/532513 the general idea is to explicitly add the same numbers of placeholders to your SQL string as there are elements in your region array.

Your code would then look like this:

sql = 'select assigner, assignee from mm_code where date between %s and %s and country_code in ({0})'\
      .format(','.join([%s] * len(region)))
results = MMCode.objects.raw(sql, [fromdate,todate] + region)

Your sql string would then first become ... between %s and %s and country_code in (%s, %s, %s) ... and your params would be effectively [fromdate, todate, 'US', 'CA', 'UK']. This way, you allow the database backend to correctly escape and potentially encode each of the country codes.

score -2 · Answer 5 · answered Aug 01 '11 at 07:34

-2

Well i'm not against raw sql but you can use: MMCode.objects.filter(country_code__in=region, date__range=[fromdate,todate])

hope this helps.

answered Aug 01 '11 at 07:34

Pannu

2,547
2
23
29

Well my query is very big one I have just pasted a subquery here. So ORM is not useful in my case and I am not good at ORM :-) – vkrams Aug 01 '11 at 11:04
Since your raw sql worked in django shell, it probably has to do with your list. Try to debug and find the sql query formed at run time. – Pannu Aug 01 '11 at 12:00
@Pannu the issue was not what SQL query to write, issue is to pass arguments to raw sql, ORM may not be perfomant in some cases. – Shedrack Jul 30 '22 at 02:07

Passing lists or tuples as arguments in django raw sql

5 Answers5

Linked