How to export the results from multiple oracle sql scripts to csv only when the script returns data using python

Question

I am new to writing a python code for automation, so my motif is to execute multiple etl testing scripts written in oracle sql and write the result of each query to a csv file, the script has multiple sql queries each query result should be written to a different tab in the csv.. I tried to write a basic structure as below but need help on how I can modify the code below: import os import cx_Oracle import csv

f = open('tabledefinition.sql') #PATH WHERE THE SQL SCRIPTS ARE STORED

full_sql = f.read()

SQL= full_sql.split(';')

filename="S:\Output.csv" # PATH WHERE THE OUTPUT FILE IS STORED
FILE=open(filename,"w");
output=csv.writer(FILE, dialect='excel')

connection = cx_Oracle.connect('userid/password@99.999.9.99:PORT/SID') 
#CONNECTION DETAILS FOR DB

cursor = connection.cursor()

for sql_command in SQL:
    cursor.execute(SQL)
    for row in cursor:
        output.writerow(row)
        cursor.close()
        connection.close()
        FILE.close() 

Answer 1

Although I haven't tried this myself, these questions make it clear that csv files cannot have multiple sheets/tabs: q1, q2, q3.

I can, however, recommend the use of The Pandas package. It has a very handy read_sql function, as well as a, perhaps equally handy, to_csv function. The read_sql function will collect the information from your db into a DataFrame object. That Dataframe can then be manipulated or it can be stored in some number of different formats, one of which is csv. If you require multiple sheets in one file, one available option is an hdf file or even an excel (.xlsx) file.