Django query filter, passing a date results in an invalid SQL syntax

Question

z = Pokes.objects.filter(
    pokestiming__when_start__lte=datetime.now(), 
    pokestiming__when_end__gte=datetime.now(),
    pokestiming__next_poke__lte=datetime.now(),
    poke_deleted=0,
    poke_operating=1,
)

That's a query I'm using. It selects any Poke object, with a PokesTiming object (foreign key) within the date ranges. It's basically a start date and expiry date.

Problem is, passing datetime.now(), and even though it seems to be the exact same syntax in the django docs, the SQL result is:

SELECT [fieldnames] FROM pokes INNER JOIN pokes_timing ON (pokes.id = pokes_timing.poke_id) WHERE (pokes.poke_deleted = 0 AND pokes_timing.when_start <= 2010-02-01 00:00:00 AND pokes_timing.next_poke <= 2010-02-01 13:50:48 AND pokes_timing.when_end >= 2010-02-01 13:50:48 AND pokes.poke_operating = 1 ) LIMIT 21

That is, the dates passed are just dates, not wrapped in string quotes, which results in an error and no results. That query ^ was retrieved by this method

I'm sure I'm missing something silly

Models:

class Pokes(models.Model):
    poke_title = models.CharField(max_length=135)
    poke_text = models.TextField()
    poke_email = models.CharField(max_length=450)
    poke_email_verified = models.IntegerField(null=True, blank=True)
    poke_key = models.CharField(max_length=30)
    poke_operating = models.IntegerField(default=0)
    poke_deleted = models.IntegerField(default=0)

    class Meta:
        db_table = u'pokes'

    def __unicode__(self):
        return self.poke_title

class PokesTiming(models.Model):
    poke = models.ForeignKey(Pokes)
    interval_min = models.IntegerField(null=True, default=30)
    interval_max = models.IntegerField(null=True, default=120)
    when_start = models.DateTimeField()
    when_end = models.DateTimeField()
    last_run = models.DateTimeField()
    is_single = models.IntegerField(default=0)
    next_poke = models.DateTimeField()

    class Meta:
        db_table = u'pokes_timing'

    def __unicode__(self):
        return self.poke

Bah - Must have just been me. It seems to work through Django now (the test data I have must have been wrong - it returns a result now) but the raw SQL query doesn't work. I guess Django does something to it before it's executed? Either way it seems to be working.

db.connection.queries shows me the same thing, but it works just fine (running the query does return the correct results). what db engine are you using, and what error are you getting when you run the query? — Ofri Raviv
– Ofri Raviv, Commented Feb 1, 2010 at 6:29
I'm using MySQL, and it returns the following: 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 '00:00:00 AND pokes_timing .... It works if the datetime part is quoted, but if I just pass datetime.now(), it doesn't add the quotes. — damnitshot
– damnitshot, Commented Feb 1, 2010 at 8:34
Can you show the model definitions for PokesTiming and Pokes? — Felix Kling
– Felix Kling, Commented Feb 1, 2010 at 9:47
I find it strange that you call datetime.now() 3 times, but get different results (look in the query - one of the times is 00:00:00 and the other 2 are not). maybe you should assign the results of datetime.now() to some variable and pass that to the filters? — Ofri Raviv
– Ofri Raviv, Commented Feb 1, 2010 at 13:35
That would have been when I attempted to use datetime.now().date() instead, just to see if it worked. I also added a response below. — damnitshot
– damnitshot, Commented Feb 1, 2010 at 16:59

tar · Accepted Answer · 2013-11-07 02:54:33Z

3

There is a bug report for this issue that was closed as invalid because there is no existing way for Django to get the actual query sent to the database.

#17612 - SQLite, filter, datefield, datetime no quotes around date in sql query

You can see that this limitation has been mentioned in the official documentation at this point.

Databases - Parameters not quoted in connection.queries

answered Nov 7, 2013 at 2:54

tar

1,5661 gold badge21 silver badges35 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Andrey Shamakhov · Accepted Answer · 2016-08-19 08:28:44Z

2

There is a solution (from https://code.djangoproject.com/ticket/17741):

import datetime
from django.db import connection
from myapp.models import Entry
today_entry_list = Entry.objects.filter(post_date=datetime.date.today())
sql, params = today_entry_list.query.sql_with_params()
cursor = connection.cursor()
cursor.execute('EXPLAIN ' + sql, params)
print(cursor.db.ops.last_executed_query(cursor, sql, params)[8:])

answered Aug 19, 2016 at 8:28

Andrey Shamakhov

3425 silver badges12 bronze badges

Comments

Bite code · Accepted Answer · 2011-12-22 09:26:32Z

0

Django will always cast all field to the proper SQL syntaxe, which you probably don't when you do it manually. Hence your error.

answered Dec 22, 2011 at 9:26

Bite code

601k118 gold badges310 silver badges336 bronze badges

Collectives™ on Stack Overflow

Django query filter, passing a date results in an invalid SQL syntax

3 Answers 3

Comments

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related