Passing list into query in Python

I am trying to execute an SQL Query in python. I have a pandas dataframe which is necessary for passing CompanyId column to my query.

data["CompanyId"].head()

0    16559
1    16753
2    16757
3    17491
4    17532

I want to use the CompanyId inside the IN operator of SQL. I tried,

x = ",".join(str(data["CompanyId"]))

myquery =  "SELECT * FROM mydb WHERE CompanyId IN (" +  x  + ")"

But no chance. I can create the very same query thanks to the paste function in R like,

paste0('SELECT * FROM mydb WHERE CompanyId IN (',paste(data[,"CompanyId"],collapse=","),')')

But I am unable to the this in python.

At the end, I’d like to execute the query like below,

pd.read_sql_query(myquery,conn)

Btw, I am not sure if this is the best way to execute a query through python. I am open to any advise that makes my life easier.

Thank you in advance!

Answer

The problem seems to be that you’re converting your dataframe into a string, rather than mapping its elements to strings. This would change

",".join(str(data["CompanyId"]))

to

",".join(map(str,df["CompanyId"]))