Passing lists or tuples as arguments in django raw sql

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_codeWHERE 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_codeWHERE date BETWEEN %s AND %sAND 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_codeWHERE 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.

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 modelsclass 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)

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.