CODEWITHSUNDEEP
pythonparameterspyodbc
user7146708
user7146708

Reputation: 197

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

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

Upvotes: 5

Views: 10047

Answers (2)

Barmar
Barmar

Reputation: 781059

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)

Upvotes: 7

nosklo
nosklo

Reputation: 222862

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)

Upvotes: 3

Related Questions