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

ExcelシートをPostgreSQLのテーブルへ変換。

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

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

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

expg1.png

スクリプトは次のようになりました。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)

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

expg2.png

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

expy3.png

SQLite版との違い

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

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

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

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

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

まとめ

一般的な手法としてはExcelをCSVにしてからpgAdminやpsqlで取り込みかと思いますが、一度db_mapを設定してしまえば、以降はCSVにする手間もなくスクリプトを走らせるだけなので簡単かな~と。

サーバー型DBであれば、いつ何時スクリプトを走らせようとDB側で処理の競合は吸収してくれるので安心です。

どうでもいい話ですけど、PostgreSQLはアイコンが可愛くない(若干キモい)ので損してますね。MySQLみたいに愛嬌があるのにすればいいのに。

おわり。

related pages
ExcelブックからPythonでメールを送る
PythonでExcelブックのリストからメール送信。

当然といえば当然なのですが、Pythonでメールの送受信をすることができます。それもBattery Includedの理念にあるように、標準モジュールのみの構成で実現できます。ここに神ツールOpenPyXLを組み合わせると、Excelシートにあるメールアドレス一覧へ片っ端からメールを送信するスクリプトができあがります。

Read More ...
PCのカメラでバーコードを読み取りExcelに取り込む
Excelシートでバーコードによる物品管理を企むの巻。

VBAではバーコードの扱いは何とかなるにしても、カメラは絶望的です。世界は広いのでVBAから使えるカメラ制御DLLがあるのかもしれませんが、あったとしても私の技量では扱える気がしないので早々にあきらめ、Pythonでやります。

Read More ...
結合セルで集計できない表を何とかする
人間にしか読めない表をPCで使えるように正規化します。

セルの結合は使ったその瞬間から「データとしての価値」がなくなります。Excelは結合されたセルが同じ値をグループ化したものだとは認識しません。集計などの計算をしたり、データベースとして使用したりするためには表が正規化されている必要があるのです。

Read More ...
この記事の
作成日

2020-09-25

更新日

2020-09-25

ページ内検索
目次
WEB MASTER
さいた
神エクセル撲滅協会理事(自称)
さいたま市民 埼玉こそ地上の楽園