Python postgreSQL sqlalchemy 查询 DATERANGE 列

我有一个预订系统，并将预订日期范围保存在“日期范围”列中：

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

我已经知道我可以通过以下方式访问实际日期booked_date.lower or booked_date.upper

例如我在这里这样做：

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

现在我需要按给定的日期范围过滤我的预订。例如，我想查看 2018 年 1 月 1 日至 2018 年 1 月 10 日之间的所有预订。

通常很简单，因为日期可以这样比较：date <= other date

但如果我用 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()

我收到错误：

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

EDIT

我找到了一个sheet https://www.postgresql.org/docs/9.3/functions-range.html使用有用的范围运算符，看起来有更好的选择来完成我想做的事情，但为此我需要以某种方式创建一个range variable，但 python 不能这样做。所以我还是很困惑。

在我的数据库中，按日期范围列条目如下所示：

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

EDIT

我正在尝试使用本机 SQL 而不是 sqlalchemy，但我不明白如何创建日期范围对象。

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

查询

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

可以使用“范围包含于”运算符来实现<@。为了传递正确的操作数，您必须创建一个实例psycopg2.extras.DateRange http://initd.org/psycopg/docs/extras.html#psycopg2.extras.DateRange，它代表一个 PostgresqldaterangePython 中的值：

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

注意属性lower and upper是的一部分psycopg2.extras.Range http://initd.org/psycopg/docs/extras.html#psycopg2.extras.Range类型。 SQLAlchemy 范围列类型不提供此类信息，如您的错误所述。

如果您想使用原始 SQL 并传递日期范围，您可以使用相同的DateRange对象也可以传递值：

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

你也可以手动构建文字 https://www.postgresql.org/docs/current/rangetypes.html#RANGETYPES-IO， 如果你想：

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

诀窍是在 Python 中构建文字并将其作为单个值传递 - 一如既往地使用占位符。它应该避免任何 SQL 注入的可能性；唯一可能发生的事情是文字具有无效的语法daterange。或者，您可以将边界传递给范围构造函数 https://www.postgresql.org/docs/current/rangetypes.html#RANGETYPES-CONSTRUCT:

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

总而言之，使用 Psycopg2 更容易Range类型并让他们处理细节。