Python 3の「openpyxl」でExcelからInsert文を作る
Python 3の「openpyxl」でExcelからInsert文を作る

Python 3の「openpyxl」でExcelからInsert文を作る

Excelベースの資料からデータベースの設計をする場合、最終的にInsert文を作成するかcsvからインポートする2種類が挙がるでしょう。

 

CI環境である「Jenkins」などを視野に入れると、Insert文による反映が望ましいかと思います。

 

そこで、Excelの資料からInsert文を作成するPython 3コードをご紹介します。

 

Python 3の「openpyxl」を使ってExcelを操作

Python 3のライブラリの1つに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 5個入りで200円とする
03 3000 刺身用で300円とする

商品テーブル

商品番号 商品名
01 バナナ
02 にんじん
03 サケ

分類テーブル

分類コード 分類名
1000 果物
2000 野菜
3000 鮮魚

 

商品情報テーブルをそのままExcelへ落とし込んでも商品情報テーブルの内容だけでは、商品番号と分類コードの名称を把握することができません。

 

そこでExcelの関数「Vlookup」を使用して、名称を表示させる「補足セル」も用意しましょう。

 

「商品名」と「分類名」の2つの行を追加するだけで商品情報テーブルの全体を把握することができます。

 

「商品番号」の隣に「商品名」、「分類コード」の隣に「分類名」の列を追加するとExcelの中身はこのようになります。

 

商品情報テーブル

商品番号 商品名 分類コード 分類名 補足情報
01 バナナ 1000 果物 毎週金曜日に99円とする
02 にんじん 2000 野菜 5個入りで200円とする
03 サケ 3000 鮮魚 刺身用で300円とする

 

この時点で、補足情報付きのシート「商品情報テーブル」と補足情報なしの「商品テーブル」と「分類テーブル」をそれぞれ3シート準備されたこととなります。

 

ひとまず以下の条件が整いました。

 

  1. テーブル名と一致したシート
  2. データベースに入力する値
  3. データベースとは関係ない名称や補足情報

 

最後に、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):
    # 1行目と2行目からカラム名のため
    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

 

必要な項目は、「商品番号」と「分類コード」、「補足情報」の3つだけであるため、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」や「C4」などセルの位置を指定する
  • 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):
 
    # 1行目と2行目からカラム名のため
    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ファイルを実行することで、「商品情報テーブル」と「商品テーブル」、「分類テーブル」の3つのテーブルのInsert文を作成することができました。

 

しかも、管理者が扱いやすいExcelファイルベースでInsert文に含めたくな行も指定できるので、メンテナンスにも最適にです。

 

CI環境まで視野を広げると、Jenkinsにより定期的に自動生成と反映が可能となります。

  • .

Comments

入力エリアすべてが必須項目です。
メールアドレスが公開されることはありません。