Passing Lists or Tuples as Arguments in Django Raw SQL

Passing lists or tuples as arguments in django raw sql

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)

Raw SQL Query using a list parameter fails

According to SQL notation for IN() this could be IN ('2', '4').
So you could try something like:

~'WHERE P.id IN {0}'.format(tuple(your_list))~ << don't.

EDIT:

Warning

Do not use string formatting on raw queries or quote placeholders in
your SQL strings!

Following Django documentation about it, you can use:

products = Product.objects.raw(
    'SELECT DISTINCT ON(I.product_id) P.id, P.name, P.desc, C.name AS ram, I.image '
    'FROM products_product AS P '
    'LEFT JOIN categories AS RPC ON P.id = RPC.product_id '
    'LEFT JOIN company AS CP ON P.company_id = CP.id '
    'LEFT JOIN category AS C ON RPC.category_id = C.id '
    'LEFT JOIN image AS I ON I.product_id = P.id '
    'WHERE P.id IN %s', params=[your_list])

How to pass a list of values for an `in` SQL clause in Django

According to the Django docs on raw(),

params is a list or dictionary of parameters. You’ll use %s placeholders in the query string for a list... Such placeholders will be replaced with parameters from the params argument.

Conveniently, raw queries are very easy to test in a django shell. If you simply input your raw() query, it will print a RawQuerySet with your resulting query:

>>> from django.contrib.auth.models import User
>>> pk_list = (1, 3, 6)
>>> User.objects.raw('SELECT * FROM auth_user WHERE id IN %s', params=[pk_list])
<RawQuerySet: 'SELECT * FROM auth_user WHERE id IN (1, 3, 6)'>

As you can see, whatever is in params is placed into the raw query. In your example, you need to change my_list to a tuple (or else it will look like "IN [1,2,3]") and change your params input to a list (params=[my_list, my_status]).

Django Raw SQL give me TypeError not enough arguments

The real problem is that you're passing in a list to params, but then you're trying to call repr on the result (I only know this because I got the same problem when running it in ipython). What you need to do is pass in a tuple:

counters = Counter.objects.raw("""
        SELECT id, name FROM building_counter c
        INNER JOIN scope_scope_buildings ssb 
                ON c.building_id = ssb.building_id
                AND ssb.scope_id = %s
        WHERE  energy_id = %s
        AND parent_id is not NULL
        AND type = 'C'
       """, (self.id, energy_id))

Or you can apply this patch to your django source and it'll turn these into tuples when you're in the shell.

If you don't use the shell for raw queries often, you can just ignore this though, since the rest of django handles list params just fine.

Django filter multiple columns with a list of tuples

Yes, but only* using the where parameter of extra: https://docs.djangoproject.com/en/dev/ref/models/querysets/#django.db.models.query.QuerySet.extra

Something like this should do:

Simple.objects.extra(where=['(a,b) in %s' % your_list])

*If you create a custom database type you should be able to define custom operators, so... might be able to work around it. I'll google a bit :)

Passing param to DB .execute for WHERE IN... INT list

Edit: If you think this answer circumvents the built-in protections against SQL-injection attack you're mistaken; look more closely.

Testing with pg8000 (a DB-API 2.0 compatible Pure-Python interface to the PostgreSQL database engine):

This is the recommended way to pass multiple parameters to an "IN" clause.

params = [3,2,1]
stmt = 'SELECT * FROM table WHERE id IN (%s)' % ','.join('%s' for i in params)
cursor.execute(stmt, params)

Full example:

>>> from pg8000 import DBAPI
>>> conn = DBAPI.connect(user="a", database="d", host="localhost", password="p")
>>> c = conn.cursor()
>>> prms = [1,2,3]
>>> stmt = 'SELECT * FROM table WHERE id IN (%s)' % ','.join('%s' for i in prms)
>>> c.execute(stmt,prms)
>>> c.fetchall()
((1, u'myitem1'), (2, u'myitem2'), (3, u'myitem3'))

give parameter(list or array) to in operator - python, sql

The idea is to have a query like this one:

cursor.execute("SELECT ... IN (%s, %s, %s)", (1, 2, 3))

where each %s will be substituted by elements in your list. To construct this string query you can do:

placeholders= ', '.join(['%s']*len(article_ids))  # "%s, %s, %s, ... %s"
query = 'SELECT name FROM table WHERE article_id IN ({})'.format(placeholders)

finally

cursor.execute(query, tuple(article_ids))

---

Related Topics