Excelの表からSQLを作成してsqliteにぶち込む

タイトルの通りの内容です。

Excelのデータ(.xlsx)をDBにまとめて突っ込みたい時があると思います。そんな時のためのスクリプトを書いたのでメモです。30分くらいで書いたので雑ですが。 (なおテーブルは初めからあるものとする)

サンプルデータは仙台のオープンデータを使用しました。(Excelで開いてすらいない)

データの例:

コード:

import sqlite3
import openpyxl
from pathlib import Path


db_path = Path('./data.db') # データを投げ込むdb
file_path = Path('./opendata_sigaihisai.xlsx') # 読み込み対象のファイル名
sheet_name = '市外被災オープンデータ' # 読み込み対象シート名


# エクセルファイル、シートの読み込み
wb = openpyxl.load_workbook(file_path)
ws = wb[sheet_name]

# 範囲データ取得
tbl = ws['A2':'Q10356']

# db接続
con = sqlite3.connect(db_path)
c   = con.cursor()

# 範囲データを順次処理
for i, row in enumerate(tbl):
    sql = 'INSERT OR REPLACE INTO static_data VALUES ('
    for j, cell in enumerate(row):
        # data_type: 文字列は’s’、booleanは'b'、日時型は'd', 数値とNoneは'n'
        if cell.data_type=='s':
            sql+=f'"{cell.value}", '
        elif cell.data_type=='n':
            sql+=f'{cell.value}, '
        elif cell.data_type=='d':
            pass
        else:
            print('OH NO') # エラー
            exit()

    # レコード登録
    sql = sql[:-2]+')' # 末尾のスペースとカンマを削除して閉じる
    sql = sql.replace('None', '""')
    print(sql)
    c.execute(sql)

con.commit()

# for s in c.execute('SELECT * from static_data'):
#     print(s)

con.close()

pop-web

スマートかつクールでアトラクティブなブログです

Excelの表からSQLを作成してsqliteにぶち込む