pythonを使ってEXCELのテーブル定義書からDLLを生成してみた

どうもこんにちは。

最近某案件にて、EXCEL製テーブル定義書からDDLを生成する、という作業がありました。
最初は「エディタ使えば人力で十分！」と考えていたのですが、
あまりにも数が多く...
人がやる量じゃない...

なのでpythonに任せることにしました。

というわけで、今回のお題は"pythonを使ってEXCEL製テーブル定義書からDDLを作ろう"です。

このエントリの内容

当エントリでは以下の内容について説明します。

1. pythonのxlrdパッケージについて
2. xlrdパッケージの基本的な使い方
3. サンプル「Excel製テーブル定義書からDDLを生成」

xlrdパッケージについて

xlrd · PyPI
pythonからEXCELファイルの内容を読むためのパッケージです。
簡単かつ直感的にExcelファイル上のデータを取得することができます。
(COM時代に比べるとなんと手間の少ないなことでしょうか...)

試験環境構築からパッケージのインストールまで

$ python --verion
Python 3.6.6
$ python -m venv xlrdtest
$ . xlrdtest/bin/activate
(xlrdtest) $ pip install --upgrade pip
(xlrdtest) $ pip install xlrd
(xlrdtest) $ pip list
Package    Version
---------- -------
pip        18.1
setuptools 39.0.1
xlrd       1.1.0

xlrdパッケージの基本的な使い方

パッケージのimport

import xlrd

EXCELファイルを開いてxlrd.bookクラスのインスタンスを取得

wb = xlrd.open_workbook(filepath)

xlrd.bookクラスのインスタンスからシートを取得

# シート順で取得(1枚目=0)
sheet = wb.sheet_by_index(sheetindex)
sheet = wb.sheets()[sheetindex]
# シート名で取得
sheet = wb.sheet_by_name(sheetname)

シートからcellのデータを取得

# セルを直接指定して取得。第1引数が行、第2引数が列。以下はB1から取得
value = sheet.cell_value(0, 1)
# 特定列のデータをリストで取得。以下はC列から取得
cols = sheet.col_values(2)
# スライス指定も可能
cols = sheet.col_values(2, 2, -3)

サンプル「Excel製テーブル定義書からDDLを生成」

• テーブル名はB1
• 以下は3行目以降に記載
  • A列:カラム名
  • B列:主キー
  • C列:データ型
  • D列:桁数と精度
  • E列:コメント

こんな感じのExcelのテーブル定義書から、DDLを生成するpythonスクリプトを作ってみました。

ソースファイル

samplescript.py

#!/usr/bin/env python
# -*- coding: utf-8 -*-

import xlrd
import sys
import types
import os.path
from operator import itemgetter

type2line = {
	'char':    "\t{colnames} char({sizes:.0f})",
	'datetime':"\t{colnames} datetime",
	'decimal': "\t{colnames} decimal({sizes})",
	'integer': "\t{colnames} integer",
	'varchar': "\t{colnames} varchar({sizes:.0f})",
}

def readxlsx(args):
	# コマンドラインの引数から (1)スキーマ名 (2)出力先ディレクトリ (3)excelファイル名を受け取る  
	schema = args[1]        # (1)スキーマ名
	outputfolder = args[2]  # (2)出力先ディレクトリ
	filepath = args[3]      # (3)excelファイル名

	try:
		wb = xlrd.open_workbook(filepath)
	except Exception as e:
		print(e)
		return

	# 各シートに対して実行
	for tablesheet in wb.sheets():

		# テーブル名とカラム定義を読み込む
		try:
			# (0,1)なのでB1からテーブル名を取得
			tablename = tablesheet.cell_value(0, 1)
			
			colnames = [x for x in tablesheet.col_values(0)[2:]]
			keys = [x for x in tablesheet.col_values(1)[2:]]
			types = [x for x in tablesheet.col_values(2)[2:]] 
			sizes = [x for x in tablesheet.col_values(3)[2:]]
			comments = [x for x in tablesheet.col_values(4)[2:]]
		except Exception as e:
			print(e)
			continue

		# 出力ファイル名の作成
		outputfile = os.path.join(outputfolder, f"{schema}_{tablename}.sql")

		with open(outputfile, 'wt') as file:
			# 読み込んだテーブル名とカラム定義からCREATE TABLE文作成
			file.write(f'CREATE TABLE {schema}.{tablename}\n(\n')

			# 各カラムの記述を一旦リストへ
			fieldlist = list()
			for i in range(0, len(colnames)): 
				coltype = types[i].strip()
				s = type2line[coltype]
				#print(s)
				fieldlist.append(s.format(colnames=colnames[i], sizes=sizes[i] if len(sizes)>i else None))

			# カラム間をカンマと改行で埋める
			file.write(',\n'.join(fieldlist))

			if not all('' == k for k in keys):
				# PK定義があればPKの設定も行う
				pk = ','.join(list(map(lambda x:x[0], sorted(filter(lambda x: x[1] != '', zip(colnames, keys)), key=itemgetter(1)))))
				file.write(f",\n\tCONSTRAINT pk_{tablename} PRIMARY KEY({pk})")

			file.write('\n);\n')
		
			# コメント登録のSQLも作る
			for i in range(0, min(len(colnames),len(comments))): 
				file.write(f"COMMENT ON COLUMN {schema}.{tablename}.{colnames[i]} IS '{comments[i]}';\n")

argvs = sys.argv
readxlsx(argvs)

スクリプトの実行方法について

引数は次のように設定します。
./samplescript.py <スキーマ名> <出力先ディレクトリ> <テーブル定義書>

ここでは例として以下のパラメータで実行してみます。

• スキーマ名：testschema
• 出力先ディレクトリ：output/
• テーブル定義書：tablesample.xlsx

(xlrdtest) $ ./samplescript.py testschema output/ tablesample.xlsx

出力を確認

(xlrdtest) $ ls output/
testschema_test_table1.sql testschema_test_table2.sql
(xlrdtest) $ cat output/testschema_test_table1.sql
CREATE TABLE testschema.test_table1
(
	pk_datetime datetime,
	pk_id varchar(10),
	col_decimal decimal(10,2),
	col_datetime datetime,
	col_varchar varchar(16),
	CONSTRAINT pk_test_table1 PRIMARY KEY(pk_id,pk_datetime)
);
COMMENT ON COLUMN testschema.test_table1.pk_datetime IS 'PK用の日時';
COMMENT ON COLUMN testschema.test_table1.pk_id IS 'PK用のID';
COMMENT ON COLUMN testschema.test_table1.col_decimal IS 'decimal型';
COMMENT ON COLUMN testschema.test_table1.col_datetime IS 'datetime型';
COMMENT ON COLUMN testschema.test_table1.col_varchar IS 'varchar型';

最後に

COMを利用してEXCELの内容を取得するのは大変でしたが、これなら気軽にサクサク作れますので、今後も利用しよかなと思いました。

参考文献

• PythonでExcelファイルを読み込み・書き込みするxlrd, xlwtの使い方 | note.nkmk.me
• Python Excel