Not able to insert list of multiple values into SQL table with parameter using pyobc in python

Question

I'm using python 3.7 to insert a list of multiple value from panda dataframe to SQL table with parameter using pyobc but always getting programming error of : ''',S) ProgrammingError: ('42000', "[42000] [Microsoft][ODBC SQL Server Driver][SQL Server]Incorrect syntax near 'kkinner'. (102) (SQLExecDirectW); [42000] [Microsoft][ODBC SQL Server Driver][SQL Server]Statement(s) could not be prepared. (8180)")

python code:

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)

Output of S: [['ANSONGOH'], ['JOY'], ['ANEO'], ['ALEX'], ['SIAO'], ['KOKK'], ['WUIYYYYUN'], ['WELIN']]

when try to run into MANAGEMENT variable, it will come out with the error of: ''',S)

ProgrammingError: ('42000', "[42000] [Microsoft][ODBC SQL Server Driver][SQL Server]Incorrect syntax near 'kkinner'. (102) (SQLExecDirectW); [42000] [Microsoft][ODBC SQL Server Driver][SQL Server]Statement(s) could not be prepared. (8180)")

I have tried to used code below to replace S, but still getting the similar error message.

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)

Output of params: [('ANSON',), ('JOY',), ('ANDY',), ('ALEX',), ('SIAO',), ('KOKKK',), ('WUIYUN',)]

when try to run into MANAGEMENT variable, it will come out with the error of: ''',params)

ProgrammingError: ('42000', "[42000] [Microsoft][ODBC SQL Server Driver][SQL Server]Incorrect syntax near 'kkinner'. (102) (SQLExecDirectW); [42000] [Microsoft][ODBC SQL Server Driver][SQL Server]Statement(s) could not be prepared. (8180)")

• pero_hero: your query is actually selecting values from table and you delete the very same table after the first select?
• jjccgoh: hi pero, can ignore the DROP TABLE #MANAGEMENT. The SQL contained more coding which never get published here..just need some help on how to solve the parameter insertion using pyodbc python.thanks

Answer 1

like already mentioned you are running a select query, therefore you should use the execute method:

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

then you create a query with IN for all your usernames

 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))

and eventually, you execute you query:

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

or if you really want to insert values into db do something like:

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

• jjccgoh: Hi pero. Thanks for your answer. It's working fine now..Thanks.