将值列表从 Python 传递到 SQL 查询的 IN 子句 [英] Passing a list of values from Python to the IN clause of an SQL query

问题描述

我正在尝试将如下列表传递给 sql 查询

I am trying to pass a list like below to a sql query

x = ['1000000000164774783','1000000000253252111']

我正在使用 sqlalchemy 和 pyodbc 连接到 sql:

I am using sqlalchemy and pyodbc to connect to sql:

import pandas as pd
from pandas import Series,DataFrame
import pyodbc
import sqlalchemy

cnx=sqlalchemy.create_engine("mssql+pyodbc://Omnius:MainBrain1@172.31.163.135:1433/Basis?driver=/opt/microsoft/sqlncli/lib64/libsqlncli-11.0.so.1790.0")

我尝试在 sql 查询中使用各种字符串格式函数.下面是其中之一

I tried using various string format functions to be used in the sql query. below is one of them

  xx = ', '.join(x)
  sql = "select * from Pretty_Txns where Send_Customer in (%s)" % xx
  df = pd.read_sql(sql,cnx)

他们似乎都将其转换为数字格式

All of them seem to convert it into a numeric format

(1000000000164774783, 1000000000253252111) instead of ('1000000000164774783','1000000000253252111')

因此查询失败，因为 Send_Customer 的数据类型是 SQL 中的 varchar(50)

And hence the query fails as datatype of Send_Customer is varchar(50) in SQL

 ProgrammingError: (pyodbc.ProgrammingError) ('42000', '[42000] [Microsoft][SQL Server Native Client 11.0]
  [SQL Server]Error converting data type varchar to numeric. (8114) (SQLExecDirectW)') 
 [SQL: 'select * from Pretty_Txns where Send_Customer in (1000000000164774783, 1000000000253252111)']

推荐答案

如对另一个答案的评论所述，该方法可能会因多种原因而失败.您真正想做的是创建一个具有所需数量的参数占位符的 SQL 语句，然后使用 read_sql 的 params= 参数来提供值:

As stated in the comment to the other answer, that approach can fail for a variety of reasons. What you really want to do it create an SQL statement with the required number of parameter placeholders and then use the params= parameter of read_sql to supply the values:

x = ['1000000000164774783','1000000000253252111']
placeholders = ','.join('?' for i in range(len(x)))  # '?,?'
sql = "select * from Pretty_Txns where Send_Customer in (%s)" % placeholders
df = pd.read_sql(sql, cnx, params=x)

这篇关于将值列表从 Python 传递到 SQL 查询的 IN 子句的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！