Python 3の「openpyxl」でExcelからInsert文を作る
Excelベースの資料からデータベースの設計をする場合、最終的にInsert文を作成するかcsvからインポートする２種類が挙がるでしょう。
CI環境である「Jenkins」などを視野に入れると、Insert文による反映が望ましいかと思います。
そこで、Excelの資料からInsert文を作成するPython 3コードをご紹介します。
Python 3の「openpyxl」を使ってExcelを操作
Python 3のライブラリの１つにExcelを操作できる「openpyxl」が挙げられます。
「openpyxl」を使用すれば、Excelからデータを取り出したり、データを書き込むことが簡単にできます。
エンジニアの中には、「Excelのマクロでも同じことができるよ！」っと思われるかもいるかも知れませんが、ここではコーディングの時間や今後のメンテナンス性を考慮してPython 3を選択しております。
Python 3の導入は、「Python Japan」の環境構築ガイドをご参照ください。
Python 3の環境が整いましたら、「pip」経由で「openpyxl」をインストールしましょう。
pip3 install openpyxl
Excelのフォーマットを決める
Excelのフォーマットは、テーブルベースで考えるようにしましょう。
これは、エンジニアが最もわかりやすい形として理想的だからです。
データベース上の設計は、以下のような場合を想定します。
商品情報テーブル
| 商品番号 | 分類コード | 補足情報 |
|---|---|---|
| 01 | 1000 | 毎週金曜日に99円とする |
| 02 | 2000 | ５個入りで200円とする |
| 03 | 3000 | 刺身用で300円とする |
商品テーブル
| 商品番号 | 商品名 |
|---|---|
| 01 | バナナ |
| 02 | にんじん |
| 03 | サケ |
分類テーブル
| 分類コード | 分類名 |
|---|---|
| 1000 | 果物 |
| 2000 | 野菜 |
| 3000 | 鮮魚 |
商品情報テーブルをそのままExcelへ落とし込んでも商品情報テーブルの内容だけでは、商品番号と分類コードの名称を把握することができません。
そこでExcelの関数「Vlookup」を使用して、名称を表示させる「補足セル」も用意しましょう。
「商品名」と「分類名」の２つの行を追加するだけで商品情報テーブルの全体を把握することができます。
「商品番号」の隣に「商品名」、「分類コード」の隣に「分類名」の列を追加するとExcelの中身はこのようになります。
商品情報テーブル
| 商品番号 | 商品名 | 分類コード | 分類名 | 補足情報 |
|---|---|---|---|---|
| 01 | バナナ | 1000 | 果物 | 毎週金曜日に99円とする |
| 02 | にんじん | 2000 | 野菜 | ５個入りで200円とする |
| 03 | サケ | 3000 | 鮮魚 | 刺身用で300円とする |
この時点で、補足情報付きのシート「商品情報テーブル」と補足情報なしの「商品テーブル」と「分類テーブル」をそれぞれ３シート準備されたこととなります。
ひとまず以下の条件が整いました。
- テーブル名と一致したシート
- データベースに入力する値
- データベースとは関係ない名称や補足情報
最後に、Excelファイルを「*.xslx」拡張して保存したら準備完了です。
Python 3とopenpyxlでInsert文を作成しよう
保存したExcelファイルに対して、いよいよPythonで操作を行います。
最初に全体のソースコードを紹介します。
# -*- coding: utf-8 -*-
import openpyxl as xl
FILE_NAME = 'Table_Editor.xlsx'
def EditableSQL(FILE_NAME, sheetName, needList, skipList, intList):
book = xl.load_workbook(FILE_NAME, data_only=True)
# シートを開く
sheet = book.get_sheet_by_name(sheetName)
getList = []
# A1からA列が空セルになるまで縦にサーチ
cell = getRefCellValue('A1', sheet)
row = 1
while cell != None:
getList = searchHolizontal(getList, needList, sheet, skipList, row)
# 次の行のセル取得
row += 1
cell = getCellValue(sheet, row, 1)
Get_SQL_Value = createInsert_SQL(sheetName, getList, intList)
createSQLFile(Get_SQL_Value, sheetName)
# セル位置の値を取得
def getRefCellValue(ref, sheet):
return sheet[ref].value
def getCellValue(sheet, _row=0, _col=0):
if _row <= 0 or _col <= 0:
return ""
return sheet.cell(row=_row,column=_col).value
#横方向の検索
def searchHolizontal(getList, needList, sheet, skipList, _row=0):
if _row <= 0:
return
# 引数行をA列からサーチ
col = 1
cell = getCellValue(sheet, _row, col)
line = ""
list = []
while cell != None or getCellValue(sheet, 1, col) != None:
if needCcolumn(col, needList) : list.append(str(cell))
# 次の列のセル取得
col += 1
cell = getCellValue(sheet, _row, col)
if (len(list) != 0 and checkList(list, skipList)):
getList.append(list)
return getList
# 数値からアルファベットに変換
def toAlpha31(num):
h=int((num-1-26)/(26*26))
i=int((num-1-(h*26*26))/26)
j=num-(i*26)-(h*26*26)
Alpha=''
for k in h,i,j:
if k!=0:
Alpha+=chr(k+64)
return Alpha
# INSERT分に載せたい列なのか判定
def needCcolumn(col_int, needList):
col_str = toAlpha31(col_int)
return col_str in needList
# スキップリストに含まれているか判定
def checkList(lists, skipList):
i = 0
ch_flg = False
if len(lists) == 0: return ch_flg
while i < len(lists):
if i in skipList:
i +=1
continue
if lists[i].find('None') == -1 : ch_flg = True
i +=1
return ch_flg
# INSERT分作成
def createInsert_SQL(sheetName, getList, intList):
# １行目と２行目からカラム名のため
i = 1
j = 0
CREATE_SQL_VALUE = "TRUNCATE TABLE [" + sheetName + "];\n"
while i < len(getList):
CREATE_SQL_VALUE += "INSERT INTO [" + sheetName + "] VALUES ("
while j < len(getList[i]):
if getList[i][j].find('NULL') == -1:
if j in intList:
CREATE_SQL_VALUE += getList[i][j]
else:
CREATE_SQL_VALUE += "'" + getList[i][j] + "'"
else:
CREATE_SQL_VALUE += "NULL"
if j != len(getList[i])-1: CREATE_SQL_VALUE += ", "
j +=1
CREATE_SQL_VALUE += ");\n"
j = 0
i +=1
print(CREATE_SQL_VALUE)
return CREATE_SQL_VALUE
# SQL拡張子としてエクスポート（SJISで出力する）
def createSQLFile(Get_SQL_Value, sheetName):
f = open(sheetName + '.sql','w', encoding='Shift_JISx0213')
f.write(Get_SQL_Value.encode('CP932').decode('Shift_JISx0213'))
f.close()
# 商品情報テーブル
def run_Syohin_Jouhou_Table():
sheetName = '商品情報テーブル'
# INSERT分に反映したい列
needList =['A','C','E']
# 空白セルが含まれるセル（列検索に引掛かかるため）
skipList = [4]
# SQLにてダブルコート（"）させたくない列
intList = [0,1]
EditableSQL(FILE_NAME, sheetName, needList, skipList, intList)
# 商品テーブル
def run_Syohin_Table():
sheetName = '商品テーブル'
# INSERT分に反映したい列
needList =['A','B']
# 空白セルが含まれるセル（列検索に引掛かかるため）
skipList = []
# SQLにてダブルコート（"）させたくない列
intList = [0]
EditableSQL(FILE_NAME, sheetName, needList, skipList, intList)
# 分類テーブル
def run_Bunrui_Table():
sheetName = '分類テーブル'
# INSERT分に反映したい列
needList =['A','B']
# 空白セルが含まれるセル（列検索に引掛かかるため）
skipList = [] # SQLにてダブルコート（"）させたくない列
intList = [0]
EditableSQL(FILE_NAME, sheetName, needList, skipList, intList)
# メイン
if __name__ == '__main__':
##### 商品情報テーブル #####
run_Syohin_Jouhou_Table()
##### 商品テーブル #####
run_Syohin_Table()
##### 分類テーブル #####
run_Bunrui_Table()
それでは、一つ一つ解説していきましょう。
まず、「import」によりpipでインストールした「openpyxl」ライブラリーを「xl」として読み込みます。
「FILE_NAME」へ読み込み対象となるExcelファイル「Table_Editor.xlsx」を代入します。
# -*- coding: utf-8 -*-
import openpyxl as xl
FILE_NAME = 'Table_Editor.xlsx'
pythonファイル内で読んでいるファンクションは後ほど説明するとして、メイン処理はこちらとなります。
# メイン
if __name__ == '__main__':
##### 商品情報テーブル #####
run_Syohin_Jouhou_Table()
##### 商品テーブル #####
run_Syohin_Table()
##### 分類テーブル #####
run_Bunrui_Table()
メイン関数では、「run_Syouhin_Jouhou_Table」と「run_Syouhin_Table」、「run_Bunrui_Table」を呼び込みます。
これが、後々Insert文が生成されるテーブルの宣言となります。
それでは、「Run_Jouhou_Table」ではどのような処理になっているのでしょうか？
# 商品情報テーブル
def run_Syohin_Jouhou_Table():
sheetName = '商品情報テーブル'
# INSERT分に反映したい列
needList =['A','C','E']
# 空白セルが含まれるセル（列検索に引掛かかるため）
skipList = [4]
# SQLにてダブルコート（"）させたくない列
intList = [0,1]
EditableSQL(FILE_NAME, sheetName, needList, skipList, intList)
SheetNameは、Excelファイルのシート名を代入します。
この運用では、「シート名」＝「テーブル名」ですので、Insert対象となるテーブル名をSheetNameへ代入します。
neetListでは、InsertしたいExcel列を指定します。
Excelの列と「商品情報テーブル」で必要な項目はこの様になっております。
| 項目名 | 商品情報テーブルに必要な項目 | 列番号 |
|---|---|---|
| 商品番号 | 必要 | A |
| 商品名 | 必要ではない | B |
| 分類コード | 必要 | C |
| 分類名 | 必要ではない | D |
| 補足情報 | 必要 | E |
必要な項目は、「商品番号」と「分類コード」、「補足情報」の３つだけであるため、neetListには「AとCとE」だけ宣言します。
今回のpythonコードでは横に検索し、セルに入ってない場合に折り返す処理となっております。
そのため補足情報が仮に値が設定されていない場合は、E列をInsert文に含むことができないため、このような空白が想定される行をskipListに追加します。
最後に、Insert文にて文字コードではない数値、つまり「”」で囲みたくない列をintListに指定して呼び出し処理は完了です。
縦向きの検索
いよいよ、Excelのファイルの中身を操作していきます。
「EditableSQL」では引数のシートを開き、横向きに空白行が検出されるまでセルのサーチを行います。
def EditableSQL(FILE_NAME, sheetName, needList, skipList, intList):
book = xl.load_workbook(FILE_NAME, data_only=True)
# シートを開く
sheet = book.get_sheet_by_name(sheetName)
getList = []
# A1からA列が空セルになるまで縦にサーチ
cell = getRefCellValue('A1', sheet)
row = 1
while cell != None:
getList = searchHolizontal(getList, needList, sheet, skipList, row)
# 次の行のセル取得
row += 1
cell = getCellValue(sheet, row, 1)
Get_SQL_Value = createInsert_SQL(sheetName, getList, intList)
cleateSQLFile(Get_SQL_Value, sheetName)
「load_workbook」にて、読み込むExcelファイルのファイル名を宣言します。
そして、「get_sheet_by_name」により引数で渡したシート名を指定します。
指定シートの「A1」列からA列の空白セルまで、while文で縦向きに検索を行います。
横向きの検索
縦向きのループの中に、横向きの検索を「searchHolizontal」により行っております。
#横方向の検索
def searchHolizontal(getList, needList, sheet, skipList, _row=0):
if _row <= 0:
return
# 引数行をA列からサーチ
col = 1
cell = getCellValue(sheet, _row, col)
line = ""
list = []
while cell != None or getCellValue(sheet, 1, col) != None:
if needCcolumn(col, needList) : list.append(str(cell))
# 次の列のセル取得
col += 1
cell = getCellValue(sheet, _row, col)
if (len(list) != 0 and checkList(list, skipList)):
getList.append(list)
return getList
基本的には、横向きの検索は縦向きと変わりませんが、以下のファンクションにより、変換と判定を行っております。
- toAlpha31：数値からアルファベットに変換「B3」や「C４」などセルの位置を指定する
- needCcolumn：needLisで指定した行を判定する
- checkList：skipListで指定した行を判定する
# 数値からアルファベットに変換
def toAlpha31(num):
h=int((num-1-26)/(26*26))
i=int((num-1-(h*26*26))/26)
j=num-(i*26)-(h*26*26)
Alpha=''
for k in h,i,j:
if k!=0:
Alpha+=chr(k+64)
return Alpha
# INSERT分に載せたい列なのか判定
def needCcolumn(col_int, needList):
col_str = toAlpha31(col_int)
return col_str in needList
# スキップリストに含まれているか判定
def checkList(lists, skipList):
i = 0
ch_flg = False
if len(lists) == 0: return ch_flg
while i < len(lists):
if i in skipList:
i +=1
continue
if lists[i].find('None') == -1 : ch_flg = True
i +=1
return ch_flg
各ファンクションによりループ処理でセルを指定しながら、Insert文に載せたい列だけ判別、空白セルが期待されるセルの判定を同時に行っております。
Insert文の組み立て
「createInsert_SQL」にて、Insert文の組み立てを行います。
# INSERT分作成
def createInsert_SQL(sheetName, getList, intList):
# １行目と２行目からカラム名のため
i = 1
j = 0
CREATE_SQL_VALUE = "TRUNCATE TABLE [" + sheetName + "];\n"
while i < len(getList):
CREATE_SQL_VALUE += "INSERT INTO [" + sheetName + "] VALUES ("
while j < len(getList[i]):
if getList[i][j].find('(NULL)') == -1:
if j in intList:
CREATE_SQL_VALUE += getList[i][j]
else:
CREATE_SQL_VALUE += "'" + getList[i][j] + "'"
else:
CREATE_SQL_VALUE += "NULL"
if j != len(getList[i])-1: CREATE_SQL_VALUE += ", "
j +=1
CREATE_SQL_VALUE += ");\n"
j = 0
i +=1
print(CREATE_SQL_VALUE)
return CREATE_SQL_VALUE
sqlファイルは、連続して使用できるように「TRUNCATE TABLE」にてテーブルの初期化を先頭に出力します。
商品情報テーブルであれば、シート名を元にテーブル名を判定し、このように出力されるはずです。
TRUNCATE TABLE [商品情報テーブル];Whileのループでは、「INSERT INTO [テーブル名]」からなるデータを出力します。
商品情報テーブルであれば、このように出力されるはずです。
INSERT INTO [商品情報テーブル] VALUES (1, 1000, '毎週金曜日に99円とする');
ループの中では、Null判定を行っており「NULL」ではない場合は、数値型なのか文字列型なのかを「intList」により判別します。
「NULL」の場合は、文字列としての「”NULL”」と出力されないように「NULL」のみを出力するように工夫しております。
ループ処理の最後には、「,」で項目を区切るのを忘れないように宣言しております。
INSERTファイルを作成
作成したINSERT文をいよいよsqlファイルとしてエクスポートします。
# SQL拡張子としてエクスポート（SJISで出力する）
def createSQLFile(Get_SQL_Value, sheetName):
f = open(sheetName + '.sql','w', encoding='Shift_JISx0213')
f.write(Get_SQL_Value.encode('CP932').decode('Shift_JISx0213'))
f.close()
「createSQLFile」では、シート名 + .sqlとしてファイルを出力します。
Excelということもあり、今回は文字コードをSJISとしてファイルの出力を組んでみました。
（もちろん、UTF-8として出力したほうが楽ですし、SJISよりも理想的な文字コードです。）
今回のPythonファイルを実行することで、「商品情報テーブル」と「商品テーブル」、「分類テーブル」の３つのテーブルのInsert文を作成することができました。
しかも、管理者が扱いやすいExcelファイルベースでInsert文に含めたくな行も指定できるので、メンテナンスにも最適にです。
CI環境まで視野を広げると、Jenkinsにより定期的に自動生成と反映が可能となります。