如何修复SQL查询中的ArgumentError：列表参数仅允许元组或字典

你的实现代码：

def print_relative_strength_by_ticker(self, tickers): # print relative strength
    tickers_list = tickers.split(" ")
    if len(tickers_list) == 1:
        query = "SELECT ticker, rs FROM companyinfo WHERE ticker = %s"
        params = [tickers_list[0]]  # Wrap the single ticker in a list or a tuple
    else:
        query = "SELECT ticker, rs FROM companyinfo WHERE ticker IN %(tickers_tuple)s" # The key 'tickers' is a string that represents the named parameter in the SQL query (%(tickers)s)
        params = {'tickers_tuple': tuple(tickers_list)} # creates a dictionary with a single key-value pair
    df = self.db.read_sql(query, params=params)
    for index, row in df.iterrows():
        print("Ticker:", row['ticker'], "\tRS:", row['rs'])

更新Spyder后调用s.print_relative_strength_by_ticker('estc')触发报错：

s.print_relative_strength_by_ticker('estc')
Traceback (most recent call last):

  Cell In[27], line 1
    s.print_relative_strength_by_ticker('estc')

  File D:\Stocks/Code\stock_analysis_class.py:111 in print_relative_strength_by_ticker
    df = self.db.read_sql(query, params=params)

  File D:\Stocks/Code\mysql_class.py:33 in read_sql
    df = pd.read_sql(query, conn, params=params, index_col=index_col)

  File C:\ProgramData\Anaconda3\lib\site-packages\pandas\io\sql.py:682 in read_sql
    return pandas_sql.read_query(

  File C:\ProgramData\Anaconda3\lib\site-packages\pandas\io\sql.py:1776 in read_query
    result = self.execute(sql, params)

  File C:\ProgramData\Anaconda3\lib\site-packages\pandas\io\sql.py:1599 in execute
    return self.con.exec_driver_sql(sql, *args)

  File C:\ProgramData\Anaconda3\lib\site-packages\sqlalchemy\engine\base.py:1767 in exec_driver_sql
    distilled_parameters = _distill_raw_params(parameters)

  File lib\\sqlalchemy\\cyextension\\util.pyx:38 in sqlalchemy.cyextension.util._distill_raw_params

  File lib\\sqlalchemy\\cyextension\\util.pyx:17 in sqlalchemy.cyextension.util._check_item

ArgumentError: List argument must consist only of tuples or dictionaries

问题原因 #

报错源于单ticker分支的params = [tickers_list[0]]。新版本SQLAlchemy对位置参数格式要求更严格：使用%s占位符时，若传递列表形式的参数，列表内必须是元组而非单个值，["estc"]不符合该规范。

解决方案 #

方案1：修正单ticker分支的参数格式

将单元素列表改为包含元组的列表：

if len(tickers_list) == 1:
    query = "SELECT ticker, rs FROM companyinfo WHERE ticker = %s"
    params = [(tickers_list[0],)]  # 改为元组的列表

方案2：统一使用命名参数（推荐，代码更简洁一致）

直接用IN子句兼容单/多ticker场景，无需分支判断：

def print_relative_strength_by_ticker(self, tickers): # print relative strength
    tickers_list = tickers.split(" ")
    tickers_tuple = tuple(tickers_list)
    query = "SELECT ticker, rs FROM companyinfo WHERE ticker IN %(tickers_tuple)s"
    params = {'tickers_tuple': tickers_tuple}
    df = self.db.read_sql(query, params=params)
    for index, row in df.iterrows():
        print("Ticker:", row['ticker'], "\tRS:", row['rs'])

IN子句对单个元素同样有效，这样可以消除分支逻辑，彻底避免参数格式不一致的问题。

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