无法使用python中的pyobc将多个值的列表插入到带参数的SQL表中

Question

我使用python 3.7将多个值的列表从panda数据帧插入到SQL表中，并使用pyobc参数，但总是得到编程错误：''，S(编程错误：('42000'，"[42000][Microsoft][ODBC SQL Server Driver][SQL Server:'kkinner'附近的语法不正确。(102((SQLExecDirectW(；[4200][Microsoft][ODBCSQL Server Driver][SQL Server:语句无法准备。(8180("(

python代码：

import pandas as pd
import pyodbc
REQUESTOR=AllSTATUS[['Requestor']].dropna().drop_duplicates()
S=REQUESTOR.values.tolist()
con = pyodbc.connect(Driver='SQL Server',host='XXX',user='XXX',password='XXX')
cur=con.cursor()
MANAGEMENT = cur.executemany(''' SELECT  
[WORKER NUMBER],
[WORKER USERNAME],
[WORKER FIRST NAME],
[WORKER LAST NAME],
[WORKER POSITION], 
[SUPERVISOR WORKER NUMBER],
[ WORKER USERNAME 1] AS [SUPERVISOR NAME]  
FROM #MANAGEMENT WHERE [WORKER USERNAME]=? DROP TABLE #MANAGEMENT''',S)

S的输出：[[安松]]、[[JOY]]、[[NEO]]、[[ALEX]]、[[SIAO]]、[[KOKK]]、[[UIYYYUN]]、[[WELIN]]

当尝试运行MANAGEMENT变量时，它会出现以下错误：''，S(

编程错误：('42000'，"[42000][Microsoft][ODBC SQL Server Driver][SQL Server:'kkinner'附近的语法不正确。(102((SQLExecDirectW(；[4200][Microsoft][ODBCSQL Server Driver][SQL Server:语句无法准备。(8180("(

我尝试使用下面的代码来替换S，但仍然收到类似的错误消息。

import pandas as pd
import pyodbc
REQUESTOR=AllSTATUS[['Requestor']].dropna().drop_duplicates() 
params = list(tuple(row) for row in REQUESTOR.values)
con = pyodbc.connect(Driver='SQL Server',host='XXX',user='XXX',password='XXX') 
cur=con.cursor() 
MANAGEMENT = cur.executemany(''' SELECT  
[WORKER NUMBER],
[WORKER USERNAME],
[WORKER FIRST NAME],
[WORKER LAST NAME],
[WORKER POSITION],
[SUPERVISOR WORKER NUMBER],
[ WORKER USERNAME 1] AS [SUPERVISOR NAME] 
FROM #MANAGEMENT
WHERE [WORKER USERNAME]=?
DROP TABLE #MANAGEMENT''',params)

参数输出：[('ANSON'，(，('JOY'，(、('ANDY'

当尝试运行MANAGEMENT变量时，它会出现以下错误："，params(

编程错误：('42000'，"[42000][Microsoft][ODBC SQL Server Driver][SQL Server:'kkinner'附近的语法不正确。(102((SQLExecDirectW(；[4200][Microsoft][ODBCSQL Server Driver][SQL Server:语句无法准备。(8180("(

Answer 1

如前所述，您正在运行一个选择查询，因此您应该使用execute方法：

usernames = [i[0] for i in params] #convert params(list of tuples) to a list of strings

然后用IN为您的所有用户名创建一个查询

query = '''SELECT  
[WORKER NUMBER],
[WORKER USERNAME],
[WORKER FIRST NAME],
[WORKER LAST NAME],
[WORKER POSITION],
[SUPERVISOR WORKER NUMBER],
[ WORKER USERNAME 1] AS [SUPERVISOR NAME] 
FROM #MANAGEMENT
WHERE [WORKER USERNAME] IN ({0})'''.format(', '.join('?' for _ in usernames))

最终，您执行查询：

cur.execute(query, usernames)
rows = cur.fetchall()
for row in rows:
print(row)

或者，如果您真的想将值插入数据库，请执行以下操作：

cur.executemany('INSERT INTO #MANAGEMENT ([WORKER USERNAME]) VALUES(?)', params)
con.commit()