Python PostgreSQL sqlalchemy查询DATERANGE列

问题描述

我有一个预订系统，我将预订的日期范围保存在DATERANGE列中:

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

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

我已经知道我可以使用 booked_date.lower 或 booked_date.upper

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

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

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.

通常很简单，因为可以像这样比较日期: date< =其他日期

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

但是如果我用DATERANGE做到这一点:

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

我得到一个错误:

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

编辑

我找到了工作表，其中包含有用的范围运算符，它看起来像有更好的选择来做我想做的事情，但是为此，我需要以某种方式创建一个 range变量，但是python无法做到这一点.所以我还是很困惑.

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.

在数据库中，我的daterange列条目如下所示:

In my database my daterange column entries look like this:

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

编辑

我正在尝试使用本机SQL，而不是sqlalchemy，但我不了解如何创建daterange对象.

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) + ')')

推荐答案

查询

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 ，它表示Python中的Postgresql daterange 值:

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

请注意，属性 lower 和 upper 是 psycopg2.extras.Range 类型.SQLAlchemy范围列类型不提供这种类型，例如您的错误状态.

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.

如果要使用原始SQL和传递日期范围，则也可以使用相同的 DateRange 对象传递值:

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

如果您还可以手动构建文字想:

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

诀窍是像往常一样使用占位符在Python中构建文字并将其作为单个值传递.它应该避免任何SQL注入的可能性；唯一可能发生的是，文字对于 daterange 具有无效的语法.或者，您可以将边界传递给范围构造器:

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

总而言之，仅使用Psycopg2 Range 类型并让它们处理细节会更容易.

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

这篇关于Python PostgreSQL sqlalchemy查询DATERANGE列的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！