Passing lists or tuples as arguments in django raw sql

user_727495 0
Viewed 19829

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

tuples list django sql
5 Answers

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.

user_5325130
Related