sqlalchemy日期作为原始sql中的字符串 [英] sqlalchemy date as string in raw sql

问题描述

请考虑下表：

class Employee(Base):
    __tablename__ = "t_employee"

    id = Column(Integer(20), Sequence('%s_id_seq' % __tablename__), primary_key=True)
    first_name = Column(String(30))
    last_name = Column(String(30))
    email = Column(String(50))
    start_date = Column(Date, default=datetime.now)
    end_date = Column(Date)

在sqlalchemy中如何在原始sql中选择使用字符串而不是日期？以下内容适用于mysql，但不适用于Oracle：

How do I select using strings instead of dates in a raw sql in sqlalchemy? The following works in mysql but not in Oracle:

session.query(Employee).\
    filter("end_date IS NULL OR end_date>='%s'" % datetime.now()).all()

最好的情况是，在处理Date或DateTime列时我可以使用字符串或日期（可互换）（我曾尝试过TypeDecorator无效）

The best scenario would be if I could use either string or date (interchangeably) whenever dealing with Date or DateTime columns (I have tried TypeDecorator to no avail)

请请注意，该问题涉及到原始 sql（我知道这可以使用谓词完成）...

Please notice that the question refers to raw sql (I know this can be done using predicates) ...

推荐答案

不要使用字符串格式将值插值到SQL中。对于 datetime 对象，默认字符串格式恰好适用于MySQL，但这只是偶然性和运气。

Don't interpolate values into SQL using string formatting. In the case of a datetime object, the default string format happens to work for MySQL, but that is just serendipity and luck.

在这种情况下，请不要使用原始SQL并让SQLAlchemy将datetime对象转换为后端数据库可以为您理解的内容：

In this case, don't use raw SQL and have SQLAlchemy translate the datetime object to something the backend database understands for you:

from sqlalchemy import or_

session.query(Employee).filter(
    or_(Employee.end_date == None, Employee.end_date >= datetime.now())
).all()

即使使用原始SQL，我也会有sqlalchemy生成该SQL并使用绑定参数：

Even when using raw SQL, I'd have sqlalchemy generate that SQL and use bind parameters:

from sqlalchemy.sql.expression import bindparam, column
from sqlalchemy.types import DateTime
from sqlalchemy import or_

dtnow = bindparam('dtnow', datetime.now(), DateTime)
end_date = column('enddate', DateTime)

session.query(Employee).\
    filter(or_(end_date == None, end_date >= dtnow)).all()

该筛选器表达式将转换为数据库后端的正确转义的SQL，无论后端是什么。当未设置后端时，表达式变为：

That filter expression is turned into properly escaped SQL for your database backend, whatever backend that may be. When no backend is set, the expression becomes:

>>> str(or_(end_date == None, end_date >= dtnow))
'enddate IS NULL OR enddate >= :dtnow'

在执行时，

和 datetime.now（）值将作为SQL参数传递给后端数据库游标。

and the datetime.now() value will be passed in as a SQL parameter to the backend database cursor when executing.

最后一种方法是使用 text（）类型：

A last resort is to use the text() type:

from sqlalchemy.sql.expression import bindparam, text

dtnow = bindparam('dtnow', datetime.now(), DateTime)
session.query(Employee).\
    filter(text('end_date is NULL or end_date >= :dtnow', bindparams=[dtnow])).all()

否则，我将避免将原始SQL和SQLAlchemy ORM完全混合使用。直接将原始SQL直接用于数据库连接：

I'd otherwise avoid mixing raw SQL and the SQLAlchemy ORM altogether. Use raw SQL directly with the database connection only:

conn = session.connection()
conn.execute(text('SELECT * FROM t_employee WHERE end_date IS NULL OR end_date>=:dtnow'),
   dtnow=datetime.now())

这篇关于sqlalchemy日期作为原始sql中的字符串的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！