ExcelをPostgreSQLへ変換するPythonスクリプト

ExcelシートをSQLiteへ変換するスクリプトを作りましたが、やっぱりSQLiteだと、定期的に走らせているメインのスクリプトと同じタイミングで、ビューワーでテーブルを覗いていたりすると簡単にコケるのでイマイチでした。完全にアプリの中にデータベースを隠蔽しないのであれば、素直にサーバー型のデータベースにした方がいいですね。

ExcelシートをPostgreSQLテーブルに変換

ということで私はPostgreSQL派なので、先のスクリプトをPostgreSQL用に書き換えました。サンプルは架空の個人情報作成サイトで生成した次のようなExcelファイルです。シート名はpeopleです。

スクリプトは次のようになりました。PostgreSQL側にデータベースはすでに存在しテーブルは存在していないものとします。

import sys
import openpyxl as excel
import psycopg2 as pg

# シートのDB対応
excel_db_map = {
    'people': {
        'min_row': 2,
        'table_name': 'personal_information',
        'column': {
            'name': {'type': 'text', 'index': 1},
            'kana': {'type': 'text', 'index': 2},
            'sex': {'type': 'text', 'index': 3},
            'mail_num': {'type': 'text', 'index': 4},
            'address1': {'type': 'text', 'index': 5},
            'address2': {'type': 'text', 'index': 6},
            'address3': {'type': 'text', 'index': 7},
            'age': {'type': 'int', 'index': 8},
            'birthday': {'type': 'date', 'index': 9},
        }
    }
}

# DB接続情報
HOST = 'localhost'
PORT = '5432'
DB_NAME = 'saitama'
USER = 'ore'
PASSWORD = 'oreore'


def db_init(db_map, db, sheet_name):
    param = []
    table_name = db_map[sheet_name]['table_name']

    for k, v in db_map[sheet_name]['column'].items():
        param.append(f"{k} {v['type']}")

    params = ','.join(param)

    db.execute(f'DROP TABLE IF EXISTS {table_name}')
    db.execute(f'CREATE TABLE {table_name}({params})')


def db_insert(book, db_map):

    conn = pg.connect(f'host={HOST} port={PORT} dbname={DB_NAME} user={USER} password={PASSWORD}')
    cur = conn.cursor()

    for sheet_name in db_map:
        db_init(db_map, cur, sheet_name)
        sheet = book[sheet_name]
        col_name = []
        val = []
        min_row = db_map[sheet_name]['min_row']
        table_name = db_map[sheet_name]['table_name']

        for k, v in db_map[sheet_name]['column'].items():
            col_name.append(k)
            val.append(v['index'])

        col_names = ','.join(col_name)

        for r in sheet.iter_rows(min_row=min_row):
            values = []
            place_holder = []

            if r[0].value is None:
                break

            for v in val:
                cell_val = r[v-1].value
                place_holder.append('%s')

                if type(cell_val) is not str and type(cell_val) is not int:
                    values.append(str(cell_val))
                else:
                    values.append(cell_val)

            ph = ','.join(place_holder)
            sql = f'INSERT INTO {table_name} ({col_names}) VALUES({ph})'
            cur.execute(sql, tuple(values))

    conn.commit()
    cur.close()
    conn.close()


fp = sys.argv[1]
wb = excel.load_workbook(fp, data_only=True)
db_insert(wb, excel_db_map)

実行するとデータベースにテーブルができます。

テーブルの内容も問題なさそうです。

SQLite版との違い

基本的にはSQLite版と同じことをやっています。詳細はSQLite版を見ていただくとして

主な相違点は、SQLiteは実質intとstringしか型がありませんが、PostgreSQLはたくさんあり、テストでdateとtimestampはExcelでそれぞれの形式のセル書式が設定されていれば、PostgreSQL側でも反映されることは確認しました。よってtypeにはdateとtimestampも指定できます。その他の型がどうなるかは、ほとんど使うことがないのでテストしていません。

テーブルはすでに同名で存在していたらDROPしてからCREATE、なければCREATEだけするようにしました。つまり、毎回完全にExcelデータへ置き換わります。

ドライバの仕様によりSQLのプレースホルダーが%sなので、そのままjoinでつなごうとすると%,s,%,sとなってしまい使えないので、若干回り道をしています。

PythonでPostgreSQLを扱っているなら説明不要かと思いますが、DB操作にはpsycopg2を使用しています。