Python postgreSQL sqlalchemy query a DATERANGE column

Question

I have a booking system and I save the booked daterange in a DATERANGE column:

booked_date = Column(DATERANGE(), nullable=False)

I already know that I can access the actual dates with booked_date.lower or booked_date.upper

For example I do this here:

for bdate in room.RoomObject_addresses_UserBooksRoom: 
    unaviable_ranges['ranges'].append([str(bdate.booked_date.lower),\
    str(bdate.booked_date.upper)])

Now I need to filter my bookings by a given daterange. For example I want to see all bookings between 01.01.2018 and 10.01.2018.

Usually its simple, because dates can be compared like this: date <= other date

But if I do it with the DATERANGE:

the_daterange_lower = datetime.strptime(the_daterange[0], '%d.%m.%Y')
the_daterange_upper = datetime.strptime(the_daterange[1], '%d.%m.%Y')

bookings = UserBooks.query.filter(UserBooks.booked_date.lower >= the_daterange_lower,\
UserBooks.booked_date.upper <= the_daterange_upper).all()

I get an error:

AttributeError: Neither 'InstrumentedAttribute' object nor 'Comparator' object associated with UserBooks.booked_date has an attribute 'lower'

EDIT

I found a sheet with useful range operators and it looks like there are better options to do what I want to do, but for this I need somehow to create a range variable, but python cant do this. So I am still confused.

In my database my daterange column entries look like this:

[2018-11-26,2018-11-28)

EDIT

I am trying to use native SQL and not sqlalchemy, but I dont understand how to create a daterange object.

bookings = db_session.execute('SELECT * FROM usersbookrooms WHERE booked_date && [' + str(the_daterange_lower) + ',' + str(the_daterange_upper) + ')')

Remember that the actual range objects in Python are a psycopg2 feature, so if you need to produce such for use as values in queries, read their docs. Also if using SQLAlchemy instead of raw SQL, check their range operator support: docs.sqlalchemy.org/en/latest/dialects/… — Ilja Everilä
– Ilja Everilä, Commented Dec 12, 2018 at 15:11
I know this. The question you linked to is also my question. Maybe I missed something, so I will reread everything. But I think .lower and .upper should work in my case. — Roman
– Roman, Commented Dec 12, 2018 at 15:14

Ilja Everilä · Accepted Answer · 2018-12-12 19:50:38Z

5

The query

the_daterange_lower = datetime.strptime(the_daterange[0], '%d.%m.%Y')
the_daterange_upper = datetime.strptime(the_daterange[1], '%d.%m.%Y')

bookings = UserBooks.query.\
    filter(UserBooks.booked_date.lower >= the_daterange_lower,
           UserBooks.booked_date.upper <= the_daterange_upper).\
    all()

could be implemented using "range is contained by" operator <@. In order to pass the right operand you have to create an instance of psycopg2.extras.DateRange, which represents a Postgresql daterange value in Python:

the_daterange_lower = datetime.strptime(the_daterange[0], '%d.%m.%Y').date()
the_daterange_upper = datetime.strptime(the_daterange[1], '%d.%m.%Y').date()

the_daterange = DateRange(the_dateranger_lower, the_daterange_upper)

bookings = UserBooks.query.\
    filter(UserBooks.booked_date.contained_by(the_daterange)).\
    all()

Note that the attributes lower and upper are part of the psycopg2.extras.Range types. The SQLAlchemy range column types do not provide such, as your error states.

If you want to use raw SQL and pass date ranges, you can use the same DateRange objects to pass values as well:

bookings = db_session.execute(
    'SELECT * FROM usersbookrooms WHERE booked_date && %s',
    (DateRange(the_daterange_lower, the_daterange_upper),))

You can also build literals manually, if you want to:

bookings = db_session.execute(
    'SELECT * FROM usersbookrooms WHERE booked_date && %s::daterange',
    (f'[{the_daterange_lower}, {the_daterange_upper})',))

The trick is to build the literal in Python and pass it as a single value – using placeholders, as always. It should avoid any SQL injection possibilities; only thing that can happen is that the literal has invalid syntax for a daterange. Alternatively you can pass the bounds to a range constructor:

bookings = db_session.execute(
    'SELECT * FROM usersbookrooms WHERE booked_date && daterange(%s, %s)',
    (the_daterange_lower, the_daterange_upper))

All in all it is easier to just use the Psycopg2 Range types and let them handle the details.

edited Dec 12, 2018 at 19:50

answered Dec 12, 2018 at 15:25

Ilja Everilä

53.4k9 gold badges138 silver badges142 bronze badges

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

3 Comments

Roman Over a year ago

Thanks for the hint with the DateRange. I am still having issues, but I think I can move forward now. I have an issue with timezones, because I get:

sqlalchemy.exc.ProgrammingError: (psycopg2.ProgrammingError) function daterange(timestamp without time zone, timestamp without time zone, unknown) does not exist LINE 3: WHERE usersbookrooms.booked_date <@ daterange('2019-01-14T00...                                             ^ HINT:  No function matches the given name and argument types. You might need to add explicit type casts.

Ilja Everilä Over a year ago

Ah true, unsurprisingly datetime.strptime produces datetime objects, not date objects. Added the call to date() in order to match the types.

Roman Over a year ago

Dont this and it works now. un_daterange = DateRange(the_daterange_lower.date(), the_daterange_upper.date()). Thanks a lot, you helped me a lot a few times already.

Collectives™ on Stack Overflow

Python postgreSQL sqlalchemy query a DATERANGE column

1 Answer 1

3 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related