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

Question

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

Answer 1

I encountered a similar issue and solved it in one line using values and tolist() as

df['col_name'].values.tolist()

So in your case, it will be

my_ids = my_data['id'].values.tolist() # ['jack', 'jill', 'july']
my_values = my_data['b'].values.tolist()

Answer 2

Let's use apply with argument 'reduce=False' then check the dtype of the series and apply the proper argument to join:

df.apply(lambda x: ', '.join(x.astype(str)) if x.dtype=='int64' else ', '.join("\'"+x.astype(str)+"\'"), reduce=False)

Output:

b               87, 55, 5555
c          1000, 2000, 22000
id    'jack', 'jill', 'july'
dtype: object