Python / SQLAlchemy format DateRange object

问题:

I am using the DateRange column type to save reservations for a booking system. My DB is PosgreSQL and I use Python 3 with Flask and SQLAlchemy.

Even though it should be best practice to use DateRange, I dont even find a documentation or anything helpful on how to work with these types.

The SQLAlchemy documentation has only 2 sentences written that such a column type exists.

A side note here: All resources, which I find only show that people usually use a start date and an end date. Is my approach with the DateRange bad?

I am currently successfully saving some date ranges into the DB. Now I need to pass all unavailable dates into the view in JSON format, so my custom calendar can use these days to block them. So I need to construct a fitting format. As datetime is not serializable I need to convert it to string.

My date range objects look like this atm.:

for bdate in room.RoomObject_addresses_UserBooksRoom:
    print (bdate.booked_date)

Output:

DateRange(datetime.date(2018, 8, 1), datetime.date(2018, 8, 9), '[)')
DateRange(datetime.date(2018, 8, 1), datetime.date(2018, 8, 10), '[)')
DateRange(datetime.date(2018, 9, 7), datetime.date(2018, 9, 14), '[)')

I would like to convert them into lists or tuples with this format %Y-%m-%d, so I would get this output:

['2018-08-01','2018-08-09']

I know how it is done with datetime objects but here I have no idea, and as mentioned above I was not able to find more information on this topic.

回答1:

SQLAlchemy documentation hints that the range types are a Psycopg feature, so looking at their documentation for the objects in question you will find that Range objects have attributes lower and upper that hold the bounds. With them it is straightforward to produce a list of tuples:

fmt = '%Y-%m-%d'
bookings = [(format(bdate.lower, fmt), format(bdate.upper, fmt))
            for bdate in room.RoomObject_addresses_UserBooksRoom]