how to use a list as a parameter for SQL connection in python using pyodbc

Question

I am learning python, and I am trying to pass a parameter as part of the WHERE clause. I am using pandas and pyodbc. Here is what I've tried so far. I first get the data for column c from a pandas dataframe, and convert it to a list called df_col, which has about 100 numeric values

df_col=df['data_for_colc'].tolist()

then, I execute the SQL statement:

execu = mycursor.execute(
"""
Select 
 columnA
 ,columnb
 ,columnc
where
 columnc in (?)
""",df_col)

rows = mycursor.fetchall()
print(rows)

I am able to connecto and download data from SQL server, but I am not able to pass parameters. I just need to be able to download those 100 rows based on the list I created with 100 values, but I get an error: ('42000', "[42000] [Microsoft][ODBC SQL Server Driver][SQL Server]Incorrect syntax near ','. (102) (SQLExecDirectW)")

any help would be appreciated. Thanks

Your SELECT statement has no FROM clause.

Gord Thompson
– Gord Thompson

2018-01-25 00:14:31 +00:00
Commented Jan 25, 2018 at 0:14 — Gord Thompson
– Gord Thompson, Commented Jan 25, 2018 at 0:14

Barmar · Accepted Answer · 2018-01-25 00:23:43Z

8

The syntax error is because you left out the FROM clause in your query.

Once you fix that, you need to have as many ? in the IN() list as there are elements in df_col.

placeholders = ", ".join(["?"] * len(df_col))
sql = """
    SELECT columnA, columnB, columnC
    FROM yourTable
    WHERE columnc IN (""" + placeholders + ")"
execu = mycursor.execute(sql, df_col)
rows = mycursor.fetchall()
print(rows)

edited Jan 25, 2018 at 0:23

answered Jan 25, 2018 at 0:13

Barmar

789k57 gold badges554 silver badges669 bronze badges

Sign up to request clarification or add additional context in comments.

5 Comments

Gord Thompson Over a year ago

True, but the wrong number of parameter placeholders does not explain the error in syntax. The problem with the query stated in the question is more fundamental than that.

Barmar Over a year ago

yeah, that's because he forgot FROM tablename

Barmar Over a year ago

@roganjosh The other answer uses .format(), so I'll leave mine with string concatenation. But I've moved the joining out to another variable.

user7146708 Over a year ago

thank you. It was not working at the beginning, but I figured out it was missing some "" at the end. Thank you very much.

user7146708 Over a year ago

@barmar yes, I forgot the FROM in the SQL statement. my bad.

nosklo · Accepted Answer · 2018-01-25 00:13:27Z

3

you have to generate all those question marks...

execu = mycursor.execute(
"""
Select 
 columnA
 ,columnb
 ,columnc
where
 columnc in ({})
""".format(','.join("?"*len(df_col))), df_col)

rows = mycursor.fetchall()
print(rows)

answered Jan 25, 2018 at 0:13

nosklo

224k58 gold badges300 silver badges299 bronze badges

2 Comments

nosklo Over a year ago

@Barmar why would I want a list... I can use','.join() with a string. It works fine as it is

Barmar Over a year ago

Ah, right, I forgot that join will iterate over the characters of the string. Too much PHP/JS...

Collectives™ on Stack Overflow

how to use a list as a parameter for SQL connection in python using pyodbc

2 Answers 2

5 Comments

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

5 Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related