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

your query is actually selecting values from table and you delete the very same table after the first select? — pero_hero
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 — jjccgoh

pero_hero pero_hero · Accepted Answer · 2020-03-14T10:09:50

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

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

1 Answers