Save Looping SQL Query Results as a single table file-CodePudding

I am trying to use a SQL query for the first time and the problem I have with my python code is that I can't save the SQL results coming from the loop as a single output file. After each loop, it just creates a new output with column names and values etc and writes over it so when the code stops, there is only one results inside the file. How can I simply have one output with under the column names for all the results?

import numpy as np
import pandas as pd

from dl import authClient as ac, queryClient as qc 
from dl.helpers.utils import convert

import openpyxl as xl

wb = xl.load_workbook('/Users/somethingfile.xlsx')
sheet = wb['Sheet 1']

here the file has lots of rows simply having two columns I get the values I need to send to server.

df = pd.DataFrame([],columns = ['a','b','c','d','e'])
for row in range(3, sheet.max_row   1): 
    a0, b0, r = sheet.cell(row,1).value, sheet.cell(row,2).value, 0.001
    query = """
    SELECT a,b,c,d,e FROM smthng
      WHERE q3c_radial_query(a,b,{:f},{:f},{:f}) LIMIT 1
    """.format(a0,b0,r)
    response = qc.query(sql=query,format='csv')
    temp_df = convert(response,'pandas')
    pd.concat([df,temp_df])

df.to_csv('trial.csv')

It does not matter if I put the output_csv inside or outside the loop, the final result in the output file is always the last result coming from sql query.

CodePudding user response：

That is because of the way you are handling your loop you see friendo. You are creating a new csv file for each looped object. That is why you have multiple CSVs. Why dont you try something like this?

First you create and empty df, object. But with all the definitions like wanted column index, and whatsoevers so you dont get an error

df = pd.DataFrame([],columns = ['col1','col2',...,'coln'])

Then you utilize your loop so concatenate your temporary dfs into your empty df

for row in range(3, sheet.max_row   1): 
    a0, b0, r = sheet.cell(row,1).value, sheet.cell(row,2).value, 0.001
    query = """
    SELECT a,b,c,d,e FROM smthng
      WHERE q3c_radial_query(a,b,{:f},{:f},{:f}) LIMIT 1
    """.format(a0,b0,r)
    response = qc.query(sql=query,format='csv')
    temp_df = convert(response,'pandas')
    df = pd.concat([df,temp_df])

After that just use your to_csv method outside of your looparoo.

df.to_csv('your_path')