read oracle sql file using python and fetch results in to CSV file

user_9433688 0
Viewed 1510

I am beginner to python and want to read a sql file from python and fetch those results in to CSV file. I have tried usin CX_oracle to connect to oracle database. It works when i directly give the sql query in the code. But, I am not sure how to read a sql file from python.

Here is the code which worked when i gave sql query directly in the code:

import csv 
import cx_Oracle
con = cx_Oracle.connect('XCM/XCM@home.com:1500/HOME')
cursor = con.cursor()
csv_file = open("exp.csv", "w")
writer = csv.writer(csv_file, delimiter='|', lineterminator="\n", quoting=csv.QUOTE_NONE)
r = cursor.execute("select * from home_parties where CREATION_DATE >= trunc(sysdate)")
for row in cursor:
    writer.writerow(row)
cursor.close()
con.close()
csv_file.close()

I tried the below code to fetch from sql file which has the same sql query as mentioned in the above code - sample.sql but it dint work

import csv
import cx_Oracle
con = cx_Oracle.connect('XCM/XCM@home.com:1500/HOME')
cursor = con.cursor()
csv_file = open("exp.csv", "w")
writer = csv.writer(csv_file, delimiter='|', lineterminator="\n", quoting=csv.QUOTE_NONE)
f = open('C:/Users/home1/sample.sql')
full_sql = f.read()
r = cursor.execute(full_sql)
for row in cursor:
    writer.writerow(row)
cursor.close()
con.close()
csv_file.close()

Kindly help me out!!

cx-oracle csv oracle sql python
2 Answers

The content of the sql file might be read within a for loop line by line such as

import csv 
import cx_Oracle
con = cx_Oracle.connect('XCM/XCM@home.com:1500/HOME')
cursor = con.cursor()

sql=""
with open("C:/Users/home1/sample.sql") as f_in:
    for line in f_in:
        sql += line

f_out = open("exp.csv", "w")
writer = csv.writer(f_out, delimiter='|', lineterminator="\n", quoting=csv.QUOTE_NONE)
cursor.execute(sql)
for row in cursor:
    writer.writerow(row)
cursor.close()
con.close()
f_out.close()
f_in.close()

considering the SQL statement to be spreaded across multiple lines

user_58413060

Assuming you have a sample.sql file in the same directory with the query as the first line. This would read the SQL query from that file, then execute the query and save the result into a CSV file named "exp.csv".

import cx_Oracle
con = cx_Oracle.connect('XCM/XCM@home.com:1500/HOME')
cursor = con.cursor()

import pandas as pd
with open('sample.sql') as f:
    sql_query_string = f.readline();
sql_query = pd.read_sql_query(sql_query_string, con)

sql_query.to_csv("exp.csv", sep='|')
user_147591370
Related