Select specific columns to read from PostgreSQL based on python list

Question

I have two lists : one contains the column names of categorical variables and the other numeric as shown below.

cat_cols = ['stat','zip','turned_off','turned_on']
num_cols = ['acu_m1','acu_cnt_m1','acu_cnt_m2','acu_wifi_m2']

These are the columns names in a table in Redshift.

I want to pass these as a parameter to pull only numeric columns from a table in Redshift(PostgreSql),write that into a csv and close the csv.

Next I want to pull only cat_cols and open the csv and then append to it and close it.

my query so far:

#1.Pull num data:
seg = ['seg1','seg2']
sql_data = str(""" SELECT {num_cols} """ + """FROM public.""" + str(seg) + """ order by random() limit 50000 ;""")
df_data = pd.read_sql(sql_data, cnxn)

# Write to csv.
df_data.to_csv("df_sample.csv",index = False)

#2.Pull cat data:
sql_data = str(""" SELECT {cat_cols} """ + """FROM public.""" + str(seg) + """ order by random() limit 50000 ;""")
df_data = pd.read_sql(sql_data, cnxn)
# Append to df_seg.csv and close the connection to csv.
with open("df_sample.csv",'rw'):
    ## Append to the csv ##

This is the first time I am trying to do selective querying based on python lists and hence stuck on how to pass the list as column names to select from table.

Can someone please help me with this?

myusko · Accepted Answer · 2018-05-25 14:52:24Z

1

If you want, to make a query in a string representation, in your case will be better to use format method, or f-strings (required python 3.6+).

Example for the your case, only with built-in format function.

seg = ['seg1', 'seg2']
num_cols = ['acu_m1','acu_cnt_m1','acu_cnt_m2','acu_wifi_m2']

query = """
SELECT {} FROM public.{} order by random() limit 50000;
""".format(', '.join(num_cols), seg)
print(query)

If you want use only one item from the seg array, use seg[0] or seg[1] in format function.

I hope this will help you!

edited May 25, 2018 at 14:52

answered May 25, 2018 at 14:42

myusko

9678 silver badges13 bronze badges

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

Collectives™ on Stack Overflow

Select specific columns to read from PostgreSQL based on python list

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related