Python PostgreSQL sqlalchemy查询DATERANGE列 [英] Python postgreSQL sqlalchemy query a DATERANGE column

问题描述

我有一个预订系统，我将预订的日期范围保存在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 是

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列的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！