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):
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: 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")
|