[Django]-Django Queryset Filter Missing Quotes

by

6👍

✅

This issue is quite interesting, it seems to happen with SQLite only. It’s known here: https://code.djangoproject.com/ticket/14091 and in the docs.

So basically the query might not be wrong, but when you get the query back with Django it looks wrong:

>>> test_list = ['test1', 'test2', 'test3', 'test4', 'test5']
>>> cls.objects.filter(next_task__test__in=test_list).query.__str__()

SELECT ...
FROM ...
WHERE "job"."next_task_id" IN (test1, test2, test3, test4, test5);

Work around: if you really think the query is wrong, then provide more quote for the list, something like:

>>> test_list = ["'test1'", "'test2'", "'test3'", "'test4'", "'test5'"]
>>> cls.objects.filter(next_task__test__in=test_list).query.__str__()

SELECT ...
FROM ...
WHERE "job"."next_task_id" IN ('test1', 'test2', 'test3', 'test4', 'test5');

I would rely on the standard one anyway, the work around above is too hackish.

👤Hieu Nguyen

1👍

I really like the answer from @Andrey-St, but a colleague pointed out that this makes a round trip to the database to do the work. So instead, we changed it to just grab the formatted query from the cursor.

 def stringify_queryset(qs):
     sql, params = qs.query.sql_with_params()
     with connection.cursor() as cursor:
         return cursor.mogrify(sql, params)

(We’re using psycopg2 for Postgres — I am not sure if mogrify() is available on other DB engines).

👤Alan Porter

0👍

def stringify_queryset(qs: QuerySet) -> str:
    sql, params = qs.query.sql_with_params()
    with connection.cursor() as cursor:
        cursor.execute('EXPLAIN ' + sql, params)
        raw_sql =  cursor.db.ops.last_executed_query(cursor, sql, params)
    raw_sql = raw_sql[len('EXPLAIN '):]
    return raw_sql

(taken from https://code.djangoproject.com/ticket/17741)

👤Andrey St

Leave a comment