CODEWITHSUNDEEP
pythondjangosqlitepython-3.xdjango-1.7
GergelyPolonkai
GergelyPolonkai

Reputation: 6421

Passing literal percent sign to QuerySet.extra()

I’d like to execute the following code:

 Booking.objects.filter(start_ts__isnull = False, end_ts__isnull = False).extra(select = {'amount': "strftime('%s', end_ts) - strftime('%s', start_ts)"})

However, in the shell, I get the following error:

Traceback (most recent call last):
  File "<console>", line 1, in <module>
  File "/home/polesz/Projects/duckbook/venv/lib/python3.4/site-packages/django/db/models/query.py", line 835, in extra
    clone.query.add_extra(select, select_params, where, params, tables, order_by)
  File "/home/polesz/Projects/duckbook/venv/lib/python3.4/site-packages/django/db/models/sql/query.py", line 1744, in add_extra
    entry_params.append(next(param_iter))
StopIteration

I have tried doubling the percent signs (%%s) and escaping them with a backslash (\%s), but neither helped. I also looked at this answer, but that solution doesn’t work with QuerySet.extra() (or I just miss the point).

What is the correct way to solve this?

Edit: I also tried to use select_params like this:

Booking.objects.filter(start_ts__isnull = False, end_ts__isnull = False).extra(select = {'amount': "strftime('%s', end_ts) - strftime(%s, start_ts)"}, select_params = ['%s', '%s'])

but regardless the usage of quote signs, the resulting query has \'%s\', which gives an SQL error, of course.

Upvotes: 2

Views: 603

Answers (1)

serTK
serTK

Reputation: 11

I had the exact same issue, and wanted to follow up here.

I got around this issue by using the .format method with double percent signs. For example, if you had a Foo model with date_field and wanted to perform a SQL method date_format(date_field, '%Y-%m'), you can do it in Django 1.7 ORM this way:

Foo.objects.extra(
    select={'formatted_date': 
        "date_format(date_field, '{param}')".format(param='%%Y-%%m')}
)

For your reference, there's a bug in Django 1.7 where passing literal %s to extra select directly was not allowed. This has since been fixed, and here's the link: https://code.djangoproject.com/ticket/23460

Upvotes: 1

Related Questions