PythonでOracleのデータベースの指定したテーブルをファイル毎にExcelに出力する

作った理由

OracleのWebアプリを作っていますが、手軽にデータを出力して確認したりバックアップするために作成しました。

前提条件

OS：Windows 8.1
Python：2.7.10 x64
必須モジュール：cx_Oracle、xlwt

コード

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()                # 新規ワークブック作成
    ws = wb.add_sheet( table_name )     # 新規ワークシート作成

    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:                                      # データがあれば
        nLine = 0
        for value in row:                                   # 取得要素分ループ
            if isinstance( value , cx_Oracle.LOB ):
                value = value.read()
            ws.write(nRow, nLine, value)                    # Excelに書き込み
            nLine = nLine + 1
        nRow = nRow + 1
        row = cur_data.fetchone()                           # 次レコードへ

    cur_data.close()
    wb.save( output_file_name )                         # Excelファイル保存


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 )

解説

Oracleの文字コード

os.environ["NLS_LANG"] = "JAPANESE_JAPAN.AL32UTF8"
で指定しています。
最近は、UTF-8が多くなりましたので、UTF-8の場合は、そのまま利用できると思います。

接続情報

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'

の部分でファイル名を生成しています。
例えば、HOST：192.168.1.5、ID:SCHEMENAMEで取得したテーブルが「TEST_USER」であれば、
DB_to_Excel_192.168.1.5_SCHEMENAME_TEST_USER.xls
というファイルに出力結果が保存されます。

出力するテーブルを絞り込んだり、絞り込みをしない場合

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"
のWHERE句を適宜変更して頂きますと可能です。