服务器端的SQLAlchemy datetime操作 [英] SQLAlchemy datetime operations on server side

问题描述

我有一张表，上面列出了预定的付款和过去的付款，我需要查找同一用户/合同在同一周内是否有两项收费.

I have a table with scheduled and past payments, and I need to find if there are any two charges made in the same week for the same user/contract.

select count(*) from charge as c1, charge as c2 
where c1.id_contract = c2.id_contract 
  and c1.status = 'SUCCESS' 
  and c2.status in ('SUCCESS', 'PENDING', 'WAITING') 
  and c1.id > c2.id and c2.due_time > (c1.due_time - interval 7 day);

我很难在sqlalchemy中重现此查询，主要是因为我找不到如何以数据库不可知的形式将MySQL的间隔"转换为SQLAlchemy.

I'm having a hard time at reproducing this query in sqlalchemy, mainly because I can't find how to translate MySQL's 'interval' to SQLAlchemy in a database agnostic form.

到目前为止，我想到了这个功能，它可以翻译所有内容，但可以翻译间隔:

So far I came up with this, which translates everything, but the interval:

db.session.query(Charge, OldCharge).filter(Charge.id_contract == OldCharge.id_contract, Charge.status=='WAITING', OldCharge.status.in_(('SUCCESS', 'PENDING')), Charge.id > OldCharge.id).count()

有什么想法吗?

推荐答案

因此，我最终编写了一个自定义的date_diff()表达式:

So, I ended up writing a custom date_diff() expression:

class date_diff(expression.FunctionElement):
    type = Integer()
    name = 'age'

@compiles(date_diff, 'default')
def _default_date_diff(element, compiler, **kw):  # pragma: no cover
    return "DATEDIFF(%s, %s)" % (compiler.process(element.clauses.clauses[0]),
                                 compiler.process(element.clauses.clauses[1]),
                                 )
@compiles(date_diff, 'mysql')
def _my_date_diff(element, compiler, **kw):  # pragma: no cover
    return "DATEDIFF(%s, %s)" % (compiler.process(element.clauses.clauses[0]),
                                 compiler.process(element.clauses.clauses[1]),
                                 )

@compiles(date_diff, 'sqlite')
def _sl_date_diff(element, compiler, **kw):    # pragma: no cover
    return "julianday(%s) - julianday(%s)" % (compiler.process(element.clauses.clauses[0]),
                                              compiler.process(element.clauses.clauses[1]),
                                              )

这篇关于服务器端的SQLAlchemy datetime操作的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！