如何在Python的read_sql_query中为SQL查询传参？遇参数识别错误

错误信息 #

DatabaseError: Execution failed on sql ' Select top 2 * from search_source_202206 where CAST(search_start_user_action_server_ts AS DATE) between '?' and '?' ': ('The SQL contains 0 parameter markers, but 2 parameters were supplied', 'HY000')

问题场景 #

使用pyodbc结合pandas执行带日期参数的SQL查询时，出现参数标记不被识别的错误：传入2个参数，但数据库判定SQL语句中没有参数占位符。

原代码 #

import pandas as pd
import numpy as np
import pyodbc

CONN_STR = r'Driver={SQL Server};Server=SQLIPMDS\SQLI;Database=Analytics;Trusted_Connection=yes;'

sqlString = """
Select top 2 *
from search_source_202206
where CAST(search_start_user_action_server_ts AS DATE) between  ? and ?
"""

with pyodbc.connect(CONN_STR) as con:
    df = pd.read_sql_query(sqlString, con=con, params=['2022-06-06','2022-06-07'])

df

解决方案 #

错误核心原因是参数标记?被错误添加了单引号（从错误信息里的'?'可确认），导致数据库将其解析为普通字符串字面量，而非参数占位符。只需确保?直接作为占位符使用，不要包裹单引号。

修正后的代码（确保SQL语句中的?无引号包裹）：

import pandas as pd
import numpy as np
import pyodbc

CONN_STR = r'Driver={SQL Server};Server=SQLIPMDS\SQLI;Database=Analytics;Trusted_Connection=yes;'

sqlString = """
Select top 2 *
from search_source_202206
where CAST(search_start_user_action_server_ts AS DATE) between ? and ?
"""

with pyodbc.connect(CONN_STR) as con:
    df = pd.read_sql_query(sqlString, con=con, params=['2022-06-06','2022-06-07'])

print(df)

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