CODEWITHSUNDEEP
pythonflask-sqlalchemy
Harry
Harry

Reputation: 121

Flask-sqlalchemy query datetime intervals

I have defined a table with flask-sqlalchemy. Displayed below.

class Notes(db.Model):
    id = db.Column(db.Integer, primary_key=True)
    notes = db.Column(db.Text, nullable=False)
    user_id = db.Column(db.Integer, db.ForeignKey('user.id'), nullable=False)
    added_at = db.Column(db.DateTime, default=db.func.now())

@staticmethod
def newest(num):
    return Notes.query.order_by(desc(Notes.added_at)).limit(num)

I'm attempting to write a query that is to replace and already existing direct query, which looks like this.

select notes,user,added_at from notes where added_at >= now() - INTERVAL 8 HOUR;

However based on the documentation that I can find, I'm not able to find a method to do the same. I'm able to make simpler queries, but I'm struggling to recreate what's pretty simple in sql itself.

I'm more than willing to read some documentation surrounding it, wasn't able to precisely nail that down either. Any direction you could provide would be awesome.

Upvotes: 12

Views: 27433

Answers (5)

exhuma
exhuma

Reputation: 21727

SQLAlchemy automatically adapts Python timedelta instances to correct interval queries. To use them you will need to rewrite your query in such a way that either the left-hand-side or right-hand-side of the comparison operator is an interval. That way it becomes comparable with timedeltas.

You query currently reads like this:

WHERE added_at >= now() - INTERVAL 8 HOUR

On both sides of the comparator >= you have absolute timestamps. And this is difficult to translate to SQLAlchemy. You can rewrite it like this:

WHERE now() - added_at >= INTERVAL 8 HOUR

This gives you intervals on both sides. This is now pretty easy to translate to SQLAlchemy:

from datetime import timedelta
from sqlalchemy import func

class Notes(db.Model):
    @staticmethod
    def newest(num):
        query = Notes.query.filter(
            func.now() - Notes.added_at >= timedelta(hours=8)
        )
        return query.order_by(desc(Notes.added_at)).limit(num)

Compared to the existing answers, this...

  • ... ensures that you use DB-internal timestamps (as opposed to using datetime.now() from Python)
  • ... does not require any type-casting
  • ... does not use string-concatenations
  • ... does not use raw text()

Upvotes: 2

André C. Andersen
André C. Andersen

Reputation: 9375

The following should also work:

from sqlalchemy import func
from sqlalchemy.dialects.postgresql import INTERVAL
from sqlalchemy.sql.functions import concat

Notes.query\
    .filter(
        Notes.added_at >= (func.now() - func.cast(concat(8, ' HOURS'), INTERVAL))
    )\
    .limit(num)

It has the nice property that 8 can be replaced with a value from inside the database, e.g., if you joined in another table with dynamic intervals. I gave this answer also here.

Upvotes: 20

TheGrimmScientist
TheGrimmScientist

Reputation: 2897

I always have Python's datetime library get me the "now" and "8 hours ago", then just do a filter using the datetimes:

from datetime import datetime, timedelta

now = datetime.now()
eight_hours_ago = now - timedelta(hours=8)

Notes.query.filter(Notes.added_at > eight_hours_ago).filter(Notes.added_at < now).all()

Upvotes: 15

jackotonye
jackotonye

Reputation: 3853

Can also try

from sqlalchemy import func, text

@staticmethod
def newest(num):
    return Notes.query.filter(Notes.added_at >= (func.date_sub(func.now(),  text('INTERVAL  8 HOUR')).order_by(desc(Notes.added_at)).limit(num)

OR

from datetime import datetime, timedelta
from dateutil import tz 

@staticmethod
def newest(num):
    recent = datetime.now(tz=tz.tzlocal()) - timedelta(hours=8)

    return Notes.query.filter(Notes.added_at >= recent).order_by(desc(Notes.added_at)).limit(num)

Upvotes: 2

metatoaster
metatoaster

Reputation: 18908

You can try something like 

Notes.query.order_by(desc(Notes.added_at)).filter(
    Notes.added_at >= text('NOW() - INTERVAL 8 HOURS').limit(num)

As I only use pure sqlalchemy I tested this out with this syntax:

>>> from sqlalchemy import text
>>> # s is a standard sqlalchemy session created from elsewhere.
>>> print s.query(Notes).order_by(desc(Notes.added_at)).filter(
...     Notes.added_at >= text('NOW() - INTERVAL 8 HOURS'))
SELECT notes.id AS notes_id, notes.notes AS notes_notes, notes.added_at AS notes_added_at 
FROM notes 
WHERE notes.added_at >= NOW() - INTERVAL 8 HOURS ORDER BY notes.added_at DESC

Reason for using text for that section is simply because NOW() and INTERVAL usage is not consistent across all sql implementations (certain implementations require the use of DATEADD to do datetime arithmetic, and while sqlalchemy does support the Interval type it's not really well documented, and also on my brief testing with it it doesn't actually do what you needed (using example from this answer, for both sqlite and MySQL). If you intend to use the SQL backend as an ordered (but dumb) data store you can just construct the actual query from within Python, perhaps like so:

q = s.query(Notes).order_by(desc(Notes.added_at)).filter(
    Notes.added_at >= (datetime.utcnow() - timedelta(3600 * 8))
)

Some people dislike this as some databases (like postgresql) can deal with datetime better than Python (such as timedelta is ignorant of leap years, for instance).

Upvotes: 3

Related Questions