Postgres日期类型输入语法错误：SQL引用类属性失败求助

核心问题 #

1. 语法错误：forecast函数中self.date_start = date_start)和self.date_end = (date_end)存在多余括号，且date_start、date_end并非该函数的参数，会触发NameError。
2. SQL变量未正确替换：原SQL语句使用{self.date_start}写法，但普通字符串不会自动解析变量，导致SQL执行时把{self.date_start}当作字符串传入，触发日期格式错误。
3. SQL注入风险：直接字符串拼接SQL语句存在安全隐患。

修复步骤 #

步骤1：修正函数内的语法错误 #

删除forecast函数中多余的两行代码：

# 删掉这两行错误代码
self.date_start = date_start)
self.date_end = (date_end)

同时确保Query类的__init__方法已正确初始化日期参数与数据库连接：

class Query:
    def __init__(self, date_start, date_end, connection):
        self.date_start = date_start
        self.date_end = date_end
        self.connection = connection  # 初始化数据库连接

步骤2：用参数化查询修复变量替换问题（推荐） #

参数化查询是数据库操作的最佳实践，既解决变量替换问题，又能避免SQL注入：

def forecast(self, provider: str, zone: str='Mainland'):
    self.df_forecasts = pd.DataFrame()
        
    fquery = """
    SELECT dp.name AS provider_name, lf.datetime_from AS date, fr.name AS run_name, lf.value AS value
    FROM load_forecasts lf
    INNER JOIN bidding_zones bz ON lf.zone_id = bz.zone_id
    INNER JOIN data_providers dp ON lf.provider_id = dp.provider_id
    INNER JOIN forecast_runs fr ON lf.run_id = fr.run_id
    WHERE bz.name = %s
    AND dp.name = %s
    AND date(lf.datetime_from) BETWEEN %s AND %s
    """

    # 通过params传递参数，psycopg2使用%s作为占位符
    df_forecasts = pd.read_sql_query(fquery, self.connection, params=(zone, provider, self.date_start, self.date_end))
    return df_forecasts

可选方案：使用F-string快速替换（仅适用于内部可控场景） #

如果参数来源完全可信，也可以用Python F-string直接格式化SQL语句：

def forecast(self, provider: str, zone: str='Mainland'):
    self.df_forecasts = pd.DataFrame()
        
    fquery = f"""
    SELECT dp.name AS provider_name, lf.datetime_from AS date, fr.name AS run_name, lf.value AS value
    FROM load_forecasts lf
    INNER JOIN bidding_zones bz ON lf.zone_id = bz.zone_id
    INNER JOIN data_providers dp ON lf.provider_id = dp.provider_id
    INNER JOIN forecast_runs fr ON lf.run_id = fr.run_id
    WHERE bz.name = '{zone}'
    AND dp.name = '{provider}'
    AND date(lf.datetime_from) BETWEEN '{self.date_start}' AND '{self.date_end}'
    """

    df_forecasts = pd.read_sql_query(fquery, self.connection)
    return df_forecasts

验证执行 #

重新运行脚本：

python myscript.py '2022-11-10' '2022-11-18'

内容的提问来源于stack exchange，提问作者needHelpToCode