multiple for in python query loop and save them

Question

need to save them into different data frames

query = '''select name 
from my_table
where class = {}
and student_number > {}
and student_number <= {} +10
group by name'''
inputs = list(range(0, 100,10))
classes = [1,2,3,4]

the expected result is running these batches for each class individually. e.g df_class1, df_class2 df_class3, df_class4

query = '''
select name  from my_table where class = {} and student_number > 
{} and student_number <= {} +50 group by name'''  
inputs = list(range(0, 100,10)) 
classes = [1,2,3,4] 
not sure on this part ##for i in inputs:     for c in classes: query.format(c, i, i)##

results = pd.DataFrame() for input, query in queries.items(): 
res = my_db.execute(query)     
results = results.append(pd.DataFrame(res))

each results as sth like ;df_class1, df_class2 df_class3, df_class4

yeah thats what i will be trying. data is too big I am creating student_number batches. i need 4 different data frames to save as csv as well. 1- run loops for different classes 2- create dataframes for each class 3- save the data frames as files — lincolnab
– lincolnab, Commented Aug 18, 2022 at 15:17
So save the dataframe as files with different file names using filename with a sone suffix 1,2,3 — Himanshu Poddar
– Himanshu Poddar, Commented Aug 18, 2022 at 15:22
Hi @lincolnab could you try my answer and let me know if it gives you desired output — Himanshu Poddar
– Himanshu Poddar, Commented Aug 18, 2022 at 16:57

Himanshu Poddar · Accepted Answer · 2022-08-18 15:41:34Z

1

You can use formatted string to save the resultant dataframe for each iteration.

inputs = list(range(0, 100,10)) 
classes = [1,2,3,4]
for i in inputs:     
    for c in classes: 
        query.format(c, i, i)
        res = my_db.execute(query)
        df = pd.DataFrame(res)
        df.to_csv(f'result_{i}_{c}.csv')

answered Aug 18, 2022 at 15:41

Himanshu Poddar

7,90116 gold badges60 silver badges120 bronze badges

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

4 Comments

Barmar Over a year ago

Don't use string formatting to substitute parameters into a query, use query parameters.

Himanshu Poddar Over a year ago

elaborate my code? What I am doing here is going through every inputs and classes, querying the dataframe and then saving the result using formatted string, so we will get file with names as result_inputid_classid

lincolnab Over a year ago

@Himanshuman thank you! just a final question can I do a part as my previous code? results = results.append(pd.DataFrame(res)) for each {c} I would like to have the dataframe as well. so I can have 1 data frame and print that on a since csv. i would have 4 different results (appended) instead of (result_{i}_{c}.csv I would have result_{c}.csv)

Himanshu Poddar Over a year ago

So you want to have all in one single dataframe right, you sure can append the dataframe to one dataframe which you need to insitialise outside the loop and then inside the loop keep on appending data to it and at the end you can do df.to_cav

Collectives™ on Stack Overflow

multiple for in python query loop and save them

1 Answer 1

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related