1

My code is reading SQL queries from text file and executing them one by one in python.I am trying save result of queries in the same excel but in different tabs/worksheets

import pyodbc as hive
import pandas as pd

filename =r'C:\Users\krkg039\Desktop\query.txt'
fd=open(filename,'r')

sqlFile=fd.read()

fd.close()

# all SQL commands (split on ';')
sqlCommands = sqlFile.split(';')

# Execute every command from the input file
for command in sqlCommands:
    try:
        con = hive.connect("DSN=SFO-LG", autocommit=True)
        df = pd.read_sql(command,con)
        print(df)
        print(command)
        writer = pd.ExcelWriter('Result.xlsx')
        df.to_excel(writer, sheet_name='Test',index=False)
        writer.save()
    except:
        print("Command skipped: ")

In the code I want to python to add sheets to existing excel for each SQL queries executed.

Basically python should NOT replace my excel every time

1 Answers1

3

I don't know if this is exactly the problem but I think if you take the writer = pd.ExcelWriter('Result.xlsx') out of the for loop then you're not re-defining it every time. Also make the name of the sheets dynamic with the loop so you're not over-writing it.

writer = pd.ExcelWriter('Result.xlsx')
for command in sqlCommands:
    try:
        con = hive.connect("DSN=SFO-LG", autocommit=True)
        df = pd.read_sql(command,con)
        print(df)
        print(command)
        df.to_excel(writer, sheet_name='Test'+command,index=False)
        writer.save()
    except:
        print("Command skipped: ")
FiercestJim
  • 198
  • 1
  • 7