Output the specified table of Oracle database in Python to Excel for each file

Reason for making

I am making an Oracle web application, but I made it to easily output data for confirmation and backup.

Prerequisites

OS：Windows 8.1 Python：2.7.10 x64 Required modules: cx_Oracle, xlwt

code

output_oracle_to_excel.py

# -*- coding: utf-8 -*-

from __future__ import unicode_literals

import os,sys
import csv
import codecs
import numbers
import unittest
import cStringIO
import itertools

import cx_Oracle

import xlwt

from itertools import chain

os.environ["NLS_LANG"] = "JAPANESE_JAPAN.AL32UTF8"

ORACLE_CONF = {
	'ID'		: 'SCHEMENAME'
	, 'PASS'	: 'SCHEMEPASSWORD'
	, 'HOST'	: '192.168.1.5'
	, 'SID'		: 'XE'
}

def fetchTables( table_name ):
	output_file_name = 'DB_to_Excel_' + HOST + '_' + ID + '_' + table_name + '.xls'

	wb = xlwt.Workbook()				#Create a new workbook
	ws = wb.add_sheet( table_name )		#Create a new worksheet

	column_name_sql = 'select column_name from user_tab_columns where table_name = :tbl'
	cur_columns = conn.cursor()
	cur_columns.execute(column_name_sql, tbl=table_name)
	columns = cur_columns.fetchall()
	cur_columns.close()
	columns = tuple(chain.from_iterable(columns))

	data_sql = 'select * from "%s"' % table_name
	cur_data = conn.cursor()
	cur_data.execute(data_sql)

	nRow = 0
	nLine = 0
	for value in columns:
		ws.write(nRow, nLine, value) 
		nLine = nLine + 1

	row = cur_data.fetchone()

	nRow	= 1
	while row != None:										#If you have the data
		nLine = 0
		for value in row:									#Get element loop
			if isinstance( value , cx_Oracle.LOB ):
				value = value.read()
			ws.write(nRow, nLine, value)					#Write to excel
			nLine = nLine + 1
		nRow = nRow + 1
		row = cur_data.fetchone()							#To the next record

	cur_data.close()
	wb.save( output_file_name )							#Save Excel file


def fetchAllTables( conn ):
	str_sql = "select TABLE_NAME from user_tables where TABLE_NAME LIKE '%USER%' or TABLE_NAME LIKE '%SHOP%' ORDER BY TABLE_NAME"
	cur_tables = conn.cursor()
	cur_tables.execute( str_sql )
	rows = cur_tables.fetchall()
	i = 0
	lst_table = list()
	for row in rows:
		print row[0]
		lst_table.append( row[0] )
		fetchTables(lst_table[i])
		i = i + 1

conn = cx_Oracle.connect( ORACLE_CONF['ID'] , ORACLE_CONF['PASS'] , ORACLE_CONF['HOST'] + '/' + ORACLE_CONF['SID'] )

fetchAllTables( conn )

Commentary

Oracle character code

os.environ["NLS_LANG"] = "JAPANESE_JAPAN.AL32UTF8" It is specified by. Recently, UTF-8 has increased, so I think that UTF-8 can be used as it is.

Connection information

ORACLE_CONF = { 'ID' : 'SCHEMENAME' , 'PASS' : 'SCHEMEPASSWORD' , 'HOST' : '192.168.1.5' , 'SID' : 'XE' } Please change according to your own environment as appropriate

Output file name

def fetchTables( table_name ): output_file_name = 'DB_to_Excel_' + HOST + '' + ID + '' + table_name + '.xls'

The file name is generated in the part of. For example, if the table acquired with HOST: 192.168.1.5 and ID: SCHEMENAME is "TEST_USER", DB_to_Excel_192.168.1.5_SCHEMENAME_TEST_USER.xls The output result is saved in the file.

When narrowing down the output table or not narrowing down

def fetchAllTables( conn ): str_sql = "select TABLE_NAME from user_tables where TABLE_NAME LIKE '%USER%' or TABLE_NAME LIKE '%SHOP%' ORDER BY TABLE_NAME" It is possible if you change the WHERE clause of.