Passing list into query in Python

Question

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!

What do you mean by 'no chance'? Are you getting errors? Unexpected results? — norie
– norie, Commented Sep 26, 2021 at 16:45

eeegnu · Accepted Answer · 2021-09-26 16:46:08Z

1

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

answered Sep 26, 2021 at 16:46

eeegnu

4664 silver badges14 bronze badges

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

Comments

balderman · Accepted Answer · 2021-09-26 16:50:19Z

1

See below

import pandas as pd
data = [{'companyId':12},{'companyId':19},{'companyId':121}]
df = pd.DataFrame(data)
ids = [str(x) for x in df['companyId'].tolist()]
myquery =  f"SELECT * FROM mydb WHERE CompanyId IN ({','.join(ids)})"
print(myquery)

output

SELECT * FROM mydb WHERE CompanyId IN (12,19,121)

answered Sep 26, 2021 at 16:50

balderman

24k8 gold badges39 silver badges60 bronze badges

1 Comment

datatech Over a year ago

This also gives the right output. I cannot upvote because of my reputation. Sorry.

norie · Accepted Answer · 2021-09-26 16:58:25Z

1

You can convert the ids to strings using astype.

ids = ','.join(data['CompanyId'].astype(str).to_list())

myquery =  f"SELECT * FROM mydb WHERE CompanyId IN ({ids})"

answered Sep 26, 2021 at 16:58

norie

9,9372 gold badges14 silver badges19 bronze badges

Collectives™ on Stack Overflow

Passing list into query in Python

3 Answers 3

Comments

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related