Python postgreSQL sqlalchemy queries the DATERANGE column

I

HAVE A BOOKING SYSTEM AND I SAVE THE DATE RANGE OF MY BOOKING IN THE DATERANGE COLUMN:

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

I

already know that I can use booked_date.lower to access the actual date or booked_date.upper

For example I’m doing 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 date range. For example, I want to view all bookings between January 1, 2018 and January 10, 2018.

It’s usually simple because dates can be compared like this: date <= other date

BUT IF I DO IT WITH 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 using useful range operators, looks like there are better options to do what I want, but for that I need to create one somehow Range variable, but Python can’t do this. So I’m still confused.

In my database, my dateRange column entry looks like this:

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

Edit

I’m trying to use native SQL instead of sqlalchemy, but I don’t 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) + ')')

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()

This can be achieved using the “range contained in” operator <@. To pass the correct operand, you must create psycopg2.extras.DateRange , representing 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 psycopg2.extras.Range type. The SQLAlchemy range column type does not provide such information, such as your error status.

If you want to use the original SQL and pass a date range, you can use the same DateRange object or pass the value:

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:

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 – use placeholders as always. It should avoid any possibility of SQL injection; The only thing that can happen is that the text is invalid for the syntax of daterange. Alternatively, you can pass the boundary to 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’s easier to use the Range type with Psycopg2 and let them handle the details.