I have written the below script to fetch result from oracle to csv but not sure how can i add column headers in csv file. Also, is there a way to run multiple select queries using the same script and append all the data in one dataframe ?
from sqlalchemy.engine import create_engine
import cx_Oracle
from xlsxwriter.workbook import Workbook
DIALECT = 'oracle'
SQL_DRIVER = 'cx_oracle'
USERNAME = '***' # enter your username
PASSWORD = '****' # enter your password
HOST = 'pv-prod-orc-01.ihsmvals.com' # enter the oracle db host url
PORT = 1521 # enter the oracle port number
SERVICE = 'PVL01PD_APP.ec2.internal' # enter the oracle db service name
ENGINE_PATH_WIN_AUTH = DIALECT + '+' + SQL_DRIVER + '://' + USERNAME + ':' + PASSWORD + '@' + HOST + ':' + str(
PORT) + '/?service_name=' + SERVICE
engine = create_engine(ENGINE_PATH_WIN_AUTH)
# test query
import pandas as pd
query = """select id.idvalue ,ie.* from instruments i, instruments_equities ie, instruments_ids id
where ie.instrument = i.pkey
and id.idset = i.ids
and id.idvalue = 'ABEV3.SA'"""
con = engine.connect()
outpt = con.execute(query)
workbook = Workbook('C:/Users/rahul/PycharmProjects/pythonProject3/outfile.xlsx')
sheet = workbook.add_worksheet()
for r,row in enumerate(outpt.fetchall()):
for c, col in enumerate(row):
sheet.write(r,c,col)
workbook.close()
con.close()
