使用 Postgresql 使 Sqlalchemy 在过滤器中使用日期
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/7075828/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Make Sqlalchemy Use Date In Filter Using Postgresql
提问by PythonWolf
I'm trying to perform the following query in Sqlalchemy.
我正在尝试在 Sqlalchemy 中执行以下查询。
Select * from "Mytable" where Date(date_time_field) = "2011-08-16";
I have tried several methods. Some here on SO. But none seems "realistic" since some do Casts Other String Formating ?!?! and not a plain simple Date() ( By Date i Mean the Postgresql Date not the Python One)at the ORM Field.
我尝试了几种方法。这里有一些关于 SO。但似乎没有一个是“现实的”，因为有些人会转换其他字符串格式？！？！而不是ORM 字段中简单的 Date() （按日期我指的是 Postgresql 日期而不是 Python 日期）。
Is it possible at the ORM level to declare a Simple Date() around the queried field ?
是否可以在 ORM 级别在查询字段周围声明 Simple Date() ？
Best Regards António
最好的问候安东尼奥
回答by PythonWolf
Using @Ants Aasma Comment.
使用@Ants Aasma 评论。
And to Keep it clean for any web search.
并保持任何网络搜索的清洁。
from sqlalchemy import Date, cast
from datetime import date
my_data = session.query(MyObject).\
filter(cast(MyObject.date_time,Date) == date.today()).all()
Thank you all who tried to solve this problem :)
谢谢所有试图解决这个问题的人:)
回答by Zuzia Hartleb
Native SQL functions can be invoked using using funcmodule
可以使用 usingfunc模块调用本机 SQL 函数
from sqlalchemy import func
from datetime import date
my_data = session.query(MyObject).filter(
func.date(MyObject.date_time) == date.today()
).all()
Calling func.date
打电话 func.date
from sqlalchemy import select, func
print select([func.date('2004-10-19 10:23:54')])
will produce following SQL:
将产生以下 SQL：
SELECT date(:date_2) AS date_1
You can also declare your own shortcuts to SQL functions:
您还可以声明自己的 SQL 函数快捷方式：
from sqlalchemy.sql.functions import GenericFunction
from sqlalchemy.types import DateTime
class convert_tz(GenericFunction):
"""
Sqlalchemy shortcut to SQL convert timezone function
:param DateTime datetime
:param str from_tz: The timezone the datetime will be converted from
:param str to_tz: The timezone the datetime will be converted from
:returns: Datetime in another timezone
:rtype: DateTime or None if timezones are invalid
"""
type = DateTime
Used like:
像这样使用：
from sqlalchemy import select, func
print select([func.convert_tz(func.now(), '+00:00', '-05:00')])
It will generate following SQL:
它将生成以下 SQL：
SELECT convert_tz(now(), :param_1, :param_2) AS convert_tz_1
回答by Turkesh Patel
in postgreSQL if date_time_field is one field of your table then quer must like this. Select * from Mytable where date_time_field = '2011-08-16'
在 postgreSQL 中，如果 date_time_field 是表的一个字段，那么 quer 必须喜欢这个。Select * from Mytable where date_time_field = '2011-08-16'
