Oracle connection and database export with python 3.6+

sample.py

1# https://oracle.github.io/python-cx_Oracle/samples/tutorial/Python-and-Oracle-Database-Scripting-for-the-Future.html#overview
 2import cx_Oracle
 3import db_config
 4
 5
 6###################################################################################
 7#                               Connect to an oracle database
 8###################################################################################
 9con = cx_Oracle.connect(db_config.user, db_config.pw, db_config.dsn)
10print("Database version: ", con.version)
11
12cur = con.cursor()
13
14###################################################################################
15# describe a table
16###################################################################################
17cur.execute("SELECT TABLE_NAME, DATA_TYPE, COLUMN_NAME, DATA_LENGTH from user_tab_columns where table_name = 'address' order by column_id")
18
19# [print(row) for row in cur.fetchall()]
20headers = list()
21[headers.append(column[0]) for column in cur.description]
22
23print(headers)
24
25[print("%s.%s-%s(%s)" % (table_name, column_name, data_type, data_length)) for table_name, data_type, column_name, data_length in cur.fetchall()]
26
27cur.close()
28
29##################################################################################
30# load and transform data from sql to csv
31##################################################################################
32import csv
33import uuid
34
35cur = con.cursor()
36cur.execute('SELECT * FROM "address" where ROWNUM <= 10')
37headers = list()
38[headers.append(column[0]) for column in cur.description]
39
40print(headers)
41
42rows = cur.fetchall()
43if rows:
44    results = list()
45    results.append(headers)
46    [results.append(row) for row in rows]
47    filename = '/tmp/mlcsv/{}.csv'.format(str(uuid.uuid4()))
48    with open(filename, 'w', newline='') as f:
49        w = csv.writer(f)
50        w.writerows(results)
51        f.close()
52else:
53    print("no data to export")