SQLAlchemy在时隙中获取行 [英] Sqlalchemy get row in timeslot

问题描述

我有一个名为Appointment的模型，其中的列 datetime 是 DateTime 字段和 duration ，是 Integer 字段，以分钟为单位表示持续时间。现在，我要检查 func.now（）是否在约会的 datetime 和 datetime 和 duration

I have a model called Appointment which has the columns datetime which is a DateTime field and duration which is an Integer field and represents duration in minutes. Now I want to check if func.now() is between the datetime of the appointment and the sum of the datetime and duration

我目前正在尝试做这样，但我需要一个适用于PostgreSQL和SQLite的解决方案。

I am currently to try to do it this way, but I need a solution that will work for both PostgreSQL and SQLite.

current_appointment = Appointment.query.filter(
    Appointment.datetime.between(
        func.now(),
        func.timestampadd(
            'MINUTES', Appointment.duration, func.now()
            )
        )
    ).limit(1).one_or_none()

推荐答案

我认为对于sqlite和postgres，您都不能直接在ORM中执行此操作，但是sqlalchemy允许您使用自定义SQL构造和编译扩展。

I don't think you'll be able to do this directly in the ORM for both sqlite and postgres, but sqlalchemy lets you extend it in a cross-dialect way with Custom SQL Constructs and Compilation Extension.

此代码段可能不是确实是对的，因为我用一些不同的模型对其进行了破解并为此进行了翻译，但是我得到了一些非常接近的东西，可以正确地渲染postgres SQL：

This snippet might not be exactly right because I hacked at it with some different models and translated it over for this, but I got something very close to render the postgres SQL correctly:

from sqlalchemy import func
from sqlalchemy.sql import expression
from sqlalchemy.types import DateTime
from sqlalchemy.ext.compiler import compiles

class durationnow(expression.FunctionElement):
    type = DateTime()
    name = 'durationnow'

@compiles(durationnow, 'sqlite')
def sl_durationnow(element, compiler, **kw):
    return compiler.process(
        func.timestampadd('MINUTES', element.clauses, func.now())
    )

@compiles(durationnow, 'postgresql')
def pg_durationnow(element, compiler, **kw):
    return compiler.process(
        func.now() + func.make_interval(0, 0, 0, 0, 0, element.clauses)
    )

    # Or alternatively...
    # return "now() - make_interval(0, 0, 0, 0, 0, {})".format(compiler.process(element.clauses))
    # which is more in-line with how the documentation uses 'compiles'

通过类似的设置，您应该能够将原始查询转换为直接向SQL呈现的交叉查询，而不是在Python中进行持续时间计算：

With something like that set up you should be able to turn your original query into a cross-dialect one that renders to SQL directly instead of doing the duration computation in Python:

current_appointment = Appointment.query.filter(
    Appointment.datetime.between(
        func.now(),
        durationnow(Appointment.duration)
    ).limit(1).one_or_none()

这篇关于SQLAlchemy在时隙中获取行的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！