Convert pandas columns to comma separated lists to be used in sql statements

user_3788557 0
Viewed 15983

I have a dataframe and I am trying to turn the column into a comma separated list. The end goal is to pass this comma seperated list as a list of filtered items in a SQL query.

How do I go about doing this?

> import pandas as pd
> 
> mydata = [{'id' : 'jack', 'b': 87, 'c': 1000},
>           {'id' : 'jill', 'b': 55, 'c':2000}, {'id' : 'july', 'b': 5555, 'c':22000}] 
  df = pd.DataFrame(mydata) 
  df

Expected solution - note the quotes around the ids since they are strings and the items in column titled 'b' since that is a numerical field and the way in which SQL works. I would then eventually send a query like

select * from mytable where ids in (my_ids)  or values in (my_values):

my_ids = 'jack', 'jill','july'

my_values = 87,55,5555

pandasql pandas dataframe csv
3 Answers

@Atiska made a good start. How do one move the my_values to SQL environment? This might mean that the list needs to be written to a file.

The solutions is below:

with open ('filename.txt', "w") as f:
  f.write(",".join(item for item in r_list)

The above code will write a flat comma separated list file.

user_38104040
Related