Python: Passing list in sql query as parameters

Question

I have a below query:

import jaydebeapi,pandas as pd

cursor = conn.cursor()
cursor.execute('''INSERT INTO MD SELECT DISTINCT MD FROM viewshareddim.TMD_VIP
WHERE STN_ID=? AND P_T in ? AND ID in ? AND C_ID = ?''', (MD[0],MD[1],MD[2],MD[3]))

Values are:

MD = [01,(02,05,06),(02,04,05),8]

Tried to refer the solution provided here python list but not getting how I can implement the solution for my query. Would appreciate if any guidance can be provided.

Tried this:

INSERT INTO MD SELECT DISTINCT MD FROM viewshareddim.TMD_VIP
WHERE STN_ID=(?) AND P_T in (?) AND ID in (?) AND C_ID = (?), (MD[0],MD[1],MD[2],MD[3])


File "/Library/Frameworks/Python.framework/Versions/3.6/lib/python3.6/site- 
 packages/jaydebeapi/__init__.py", line 499, in execute
self._set_stmt_parms(self._prep, parameters)
File 
"/Library/Frameworks/Python.framework/Versions/3.6/lib/python3.6/site-packages/jaydebeapi/__init__.py", line 490, in _set_stmt_parms
prep_stmt.setObject(i + 1, parameters[i])
RuntimeError: No matching overloads found for setObject in find. at 
native/common/jp_method.cpp:127

Try putting parenthesis around your `IN` parameters? `AND P_T in (?) AND ID in (?)` — Simon, Jul 25 '18 at 20:11
I tried your suggestion but getting an error. Have posted the result in the main question above. — user9975773, Jul 25 '18 at 20:20
Which of your parameters isn't working? Also, `C_ID = ?, (MD[0],MD[1],MD[2],MD[3])` isn't any kind of valid SQL. — Andrew, Jul 25 '18 at 21:35
can you provide more of the Python code that produces this error to help frame the question? — Rob Paller, Jul 26 '18 at 16:32

score -1 · Answer 1 · edited Jul 25 '18 at 21:26

-1

I try to having the list get evaluated so when it's passed to the database it's just a string to be executed by the DB

edited Jul 25 '18 at 21:26

Igor Cova

3,126
4
31
57

answered Jul 25 '18 at 20:27

Adam Reger

1
3

Definitely don't do this. Look up sql injection attacks for details on why. – pault Jul 25 '18 at 20:28
agreed, I should have stated this, however for debugging it could help to see how / what is being sent to the DB. – Adam Reger Jul 25 '18 at 20:33

Python: Passing list in sql query as parameters

1 Answers1