8

I have a pandas dataframe that has a column of IDs. I need to run another sql query whose 'WHERE' clause is dictated by all of the IDs in the aforementioned column.

Ex:

df1 = DataFrame({'IDs' : [1,2,3,4,5,6]})

query = """ Select id, SUM(revenue) AS revenue WHERE id IN (***want df1['IDs'] here***) Group by 1"""

df2 = my_database.select_dataframe(query)
thecoshman
  • 8,394
  • 8
  • 55
  • 77
Christopher Jenkins
  • 825
  • 3
  • 12
  • 16

2 Answers2

6

Convert the series to string

str = ','.join([str(x) for x in df1['IDs'].tolist()])

str
'1,2,3,4,5,6'

And, then insert it into the query string -

qry = "Select id, SUM(revenue) AS revenue WHERE id IN (%s) Group by 1" % str

qry
'Select id, SUM(revenue) AS revenue WHERE id IN (1,2,3,4,5,6) Group by 1'
Zero
  • 74,117
  • 18
  • 147
  • 154
3

for this to work for me I had to surround the list items with single quotes.

str = ','.join(["'" + str(x) + "'" for x in df1['IDs'].tolist()])
Obsidian
  • 3,719
  • 8
  • 17
  • 30
Valdeza
  • 31
  • 1