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!
Advertisement
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"]))