代码之家 › 专栏 › 技术社区 › edbras

如何使用sqlacalchemy根据计算的json值选择postgresql记录?

sqlalchemy postgresql python

edbras · 技术社区 · 6 年前

我有一个Postgres表,其中有两列用于查询:

start_date_detail: date 
flex: jsonb

flex字段示例:

{
    "communication": [
        {
            "remind_on": "mxmw@cxla.nl",
            "type": "email",
            "remind_date": -150
        },
        {
            "remind_on": "+31612345678",
            "type": "sms",
            "remind_date": -360
        }
    ]
}

我需要选择上周有提醒日期的所有记录,所以(伪代码):

(now() - 1-week) < (start_date_detail + remind_date) < now()

我怎么能用sqlacalchemy意识到这一点呢?

因为它是查询中的一个计算值,所以我不知道怎么做。在《Postgres》中,我提到了这一点:

SELECT * FROM time_item 
WHERE 
    (start_date_detail + INTERVAL '1 second' * (flex->'communication'->0->>'remind_date')::numeric <= NOW())
    OR (start_date_detail + INTERVAL '1 second' * (flex->'communication'->1->>'remind_date')::numeric <= NOW())

如何将其放入sqlacalchemy中?

还有一件事: 在上面的查询中,我将每个通信项添加到WHERE子句中。我怎样才能使这个更灵活呢?也就是说,我不需要为每个通信项都添加WHERE子句。

1 回复 | 直到 6 年前

Ilja Everilä 6 年前

你可以用 jsonb_array_elements() 将数组扩展到一组 jsonb 然后可以在谓词中使用的元素。通过使用 alias . 使用诸如

In [4]: class TimeItem(Base):
   ...:     __tablename__ = 'time_item'
   ...:     id = Column(Integer, primary_key=True)
   ...:     start_date_detail = Column(Date)
   ...:     flex = Column(JSONB)
   ...:

查询可能看起来像

In [39]: session.query(TimeItem).\
    ...:     select_from(TimeItem,
    ...:                 func.jsonb_array_elements(TimeItem.flex['communication']).
    ...:                     alias('comm')).\
    ...:     filter((TimeItem.start_date_detail +
    ...:             timedelta(seconds=1) *
    ...:             column('comm', type_=JSONB)['remind_date'].
    ...:                 astext.
    ...:                 cast(Integer)).
    ...:                     between(func.now() - timedelta(weeks=1),
    ...:                             func.now())).\
    ...:     all()

然后你可以根据你的需要调整谓词,我试着用你的例子来说明。 remind_date 被解释为偏移秒到 start_date_detail 与前一周相比。查询实体时,例如 TimeItem SQLAlchemy根据对象标识执行自己的重复数据消除,因此查询可以省略SQL端distinct,移动现有子查询表达式中的数组元素,或者类似的操作。

推荐文章

Arbaaz Ali · 暂停AWS RDS上的复制

1 年前

Nero · 我应该如何提取嵌套列表以保存到数据库?

2 年前

NextNightFlyer · 如何将参数传递给Python应用程序的Get请求?

2 年前

Zack Plauché · SQLAlchemy ORM:为什么是行[0]。属性,而不仅仅是行。属性选择结果

2 年前

Danil · 为什么pycharm没有看到sqlalchemy模块?

2 年前

HYBRID BEING · SQLAlchemy-具有特定列的反射表返回“无法在表上创建ForeignKeyConstraint”

2 年前

Glauberiano · 气流-尝试运行TaskInstance时从sqlalchemy引发的错误

2 年前

Manan Bordia · 带参数None的外键:Sqlalchemy

2 年前

edbras · 在Postgresql中的WHERE中使用JSON数组进行查询

6 年前

user3690132 · Python,SQLalchemy对象长度

6 年前