我正试图将一个列表传递给SQL。据我所知,没有针对列表的bind
类型。因此,我试图将列表转换为字符串:
这是我尝试执行的SQL:
select a,b,sum(c) as total from t where d in ('x','y') group by 1,2
因为我需要进行参数化,所以我使用了以下代码:
import pandas as pd
import pyodbc
#conn_vertica -- connection object to vertica
f_list = ['x','y']
sql_test= select a,b,sum(c) as total from t where d in (%s) group by 1,2
l = ', '.join(map(lambda x: '%s',f_list))
sql_test = sql_test % l
df_test = pd.read_sql(sql_test,conn_vertica) # to read it into a dataframe
在运行时,我遇到了一个错误:
pandas.io.sql.DatabaseError: Execution failed on sql 'select a, b, sum(c) as total from t where d in (%s, %s) group by 1,2': ('42601', '[42601] ERROR 4856: Syntax error at or near "%" at character 123\n (4856) (SQLExecDirectW)')
有没有关于如何将列表传递给SQL的建议?