Download an Oracle Table to a CSV File

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
__author__ = 'jheaton'

import codecs
import csv
import cx_Oracle

ORACLE_CONNECT = "schema/password@(DESCRIPTION=(SOURCE_ROUTE=OFF)(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=host)(PORT=1532)))(CONNECT_DATA=(SID=STLPESP)(SRVR=sid)))"
OUTPUT_ENCODING = "utf-8"
BATCH_SIZE = 100000

ORACLE_SQL = """
select * from mytable
"""

def append_row(filename,rows):
with codecs.open(filename, "a", OUTPUT_ENCODING) as outfile:
output = csv.writer(outfile, dialect='excel')
for row in rows:
output.writerow(row)

def export_table_data(orcl,filename):
# output each table content to a separate CSV file

with codecs.open(filename, "w", OUTPUT_ENCODING) as infile:
pass

sql = ORACLE_SQL
curs2 = orcl.cursor()
curs2.execute(sql)

cols = []
for col in curs2.description:
cols.append(col[0])
append_row(filename, [cols] )

row = 0
done = False
while not done: # add table rows
row_data = curs2.fetchmany(BATCH_SIZE)

if len(row_data)<1:
done = True
else:
append_row(filename, row_data)
row+=len(row_data)
print("{:,d}".format(row))

orcl = cx_Oracle.connect(ORACLE_CONNECT)
print("Connected to Oracle: " + orcl.version)

export_table_data(orcl,"output.csv")