python - 将列表中的项目传递给python中的sql查询

Question

import mysql.connector
import pandas as pd
testlist=['abc', 'cde', 'def']
testvar='phone'

query = "select column1,column2, column3 from testtable where column4=%(var1)s and column5 in %(listitem)s "

content=pd.read_sql(query, con=mydb, params= {"var1":testvar, "listitem":testlist})

错误

Failed processing pyformat-parameters; Python 'list' cannot be converted to a MySQL type

我们如何将项目列表传递给“IN”参数？

标签： pythonpandas

Answer 1

您应该将列表转换为字符串。我猜通过将 a 传递list()给listitem查询无法创建并引发此错误。

import mysql.connector
import pandas as pd
testlist=['abc', 'cde', 'def']
testvar='phone'

query = "select column1,column2, column3 from testtable where column4=%(var1)s and column5 in %(listitem)s "

content=pd.read_sql(query, con=mydb, params= {"var1":testvar, "listitem": str(testlist)})