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](https://stackoverflow.com/questions/52036566/python-sqlalchemy-format-daterange-object), 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: https://docs.sqlalchemy.org/en/latest/dialects/postgresql.html#sqlalchemy.dialects.postgresql.ranges.RangeOperators — Ilja Everilä, Dec 12 '18 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, Dec 12 '18 at 15:14

Ilja Everilä · Accepted Answer · 2018-12-12T19:50:38.327

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.

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.` — Roman, Dec 12 '18 at 15:31
Ah true, unsurprisingly `datetime.strptime` produces `datetime` objects, not `date` objects. Added the call to `date()` in order to match the types. — Ilja Everilä, Dec 12 '18 at 15:32
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. — Roman, Dec 12 '18 at 15:36

Python postgreSQL sqlalchemy query a DATERANGE column

1 Answers1

Linked