How do I use f-strings to populate a dataframe using sqlalchemy

April 8, 2022

I am trying to populate a dataframe with the average called_count using f-strings. Where I am struggling is that my script will only populate the dataframe with last item in the list the being 8112

var = ["8113","8114","8112"]

id_list_string = "','".join(var)
for var in id_list_string:
    sql_query = f"SELECT list_id, avg(called_count) FROM list WHERE list_id IN ('{id_list_string}');"


    df = pd.read_sql(sql=sql_query,con=cnx)
df

the output produces the last id in the list as such:

    list_id avg(called_count)
0   8112    1.408

What I am trying to produce is:

list_id avg(called_count)
0   8113    1.7268
1   8114    0.3802
2   8112    1.408

What am I missing?

>Solution :

You don’t need the for loop since you’re joining list_id in a tuple.

var = ["8113","8114","8112"]

id_list_string = "','".join(var)
sql_query = f"SELECT list_id, avg(called_count) FROM list WHERE list_id IN ('{id_list_string}') groupby list_id;"
df = pd.read_sql(sql=sql_query,con=cnx)
df