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 "", line 1, in 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/d…

Accepted Answer

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)

Passing lists or tuples as arguments in django raw sql

Solution

Problem

Related problems