Python -基于日期筛选SQL查询

Question

我正在尝试构建一个SQL查询，该查询将根据系统日期(对过去7天内完成的所有销售的查询)进行筛选：

import datetime
import pandas as pd
import psycopg2

con = p.connect(db_details)
cur = con.cursor()

df = pd.read_sql("""select store_name,count(*) from sales 
     where created_at between datetime.datetime.now() - (datetime.today() - timedelta(7))""",con=con)

我犯了个错误

psycopg2.NotSupportedError: cross-database references are not implemented: datetime.datetime.now

Answer 1

将Python语法混合到SQL查询中。SQL是由数据库解析和执行的，而不是由Python执行的，而且数据库对datetime.datetime.now()、datetime.date()或timedelta()一无所知！您看到的特定错误是由于您的Python代码被解释为SQL，并且datetime.datetime.now引用了datetime数据库中datetime表的now列，这是一个跨数据库引用，而psycopg2不支持涉及多个数据库的查询。

相反，使用SQL参数将值从Python传递到数据库。使用SQL中的占位符显示数据库驱动程序的值应该放在哪里：

params = {
    # all rows after this timestamp, 7 days ago relative to 'now'
    'earliest': datetime.datetime.now() - datetime.timedelta(days=7),
    # if you must have a date *only* (no time component), use
    # 'earliest': datetime.date.today() - datetime.timedelta(days=7),
}
df = pd.read_sql("""
     select store_name,count(*) from sales 
     where created_at >= %(latest)s""", params=params, con=con)

这使用 parameters documentation定义的占位符，其中%(latest)s引用params字典中的latest键。驱动程序直接支持datetime.datetime()实例。

请注意，我还修复了7天前的表达式，并将BETWEEN语法替换为>=；如果没有第二个日期，您就不会在两个日期之间查询值，所以使用>=将列限制为给定日期的日期或之后的日期。

Answer 2

datetime.datetime.now()不是一个适当的SQL语法，因此不能由read_sql()执行。我建议使用计算当前时间的正确的SQL语法，或者为每个datetime.datetime.now()和datetime.today() - timedelta(7)创建变量，并在字符串中替换它们。

编辑:不要遵循第二个建议。见下面Martijn Pieters的评论。

Answer 3

也许您应该删除SQL中的Python代码，在python中计算日期，然后使用strftime函数将它们转换为字符串。

然后，您将能够在SQL查询中使用它们。