Excel データを SQL に変換：Python でワークシートから INSERT スクリプトを生成

2024-06-17

Excel ワークシートから SQL INSERT スクリプトを生成する

前提条件

このチュートリアルを進める前に、以下の準備が必要です。

Python がインストールされていること
データを含む Excel ワークシート
対象となるデータベースへのアクセス権

手順

ライブラリのインストール

まず、必要なライブラリをインストールする必要があります。以下のコマンドを実行します。

pip install openpyxl pyodbc

Excel ワークシートの読み込み

以下のコードを使用して、Excel ワークシートを読み込みます。

import openpyxl

# ワークブックを開く
wb = openpyxl.load_workbook('data.xlsx')

# ワークシートを取得
sheet = wb.active

SQL INSERT スクリプトの生成

以下のコードを使用して、Excel ワークシートのデータから SQL INSERT スクリプトを生成します。

import pyodbc

# データベースへの接続
conn = pyodbc.connect('DRIVER={ODBC Driver 17 for SQL Server};SERVER=your_server;DATABASE=your_database;UID=your_username;PWD=your_password')
cursor = conn.cursor()

# テーブル名を取得
table_name = sheet.title

# カラム名を取得
column_names = []
for cell in sheet[1]:
    column_names.append(cell.value)

# INSERT スクリプトの生成
insert_query = f"INSERT INTO {table_name} ({','.join(column_names)}) VALUES ("
for row in sheet[2:]:
    values = []
    for cell in row:
        if isinstance(cell.value, str):
            values.append(f"'{cell.value}'")
        else:
            values.append(str(cell.value))
    insert_query += f"({','.join(values)}), "
insert_query = insert_query[:-2] + ";"

# INSERT スクリプトの実行
cursor.execute(insert_query)
conn.commit()

# データベース接続のクローズ
conn.close()

上記のコードは、data.xlsx という名前の Excel ファイルにある Sheet1 という名前のワークシートからデータを your_database データベースの your_table テーブルに挿入します。必要に応じて、ワークシート名、テーブル名、およびデータベース接続情報を変更してください。

補足

上記のコードは基本的な例であり、ニーズに合わせてカスタマイズする必要があります。
エラー処理やデータ型変換などの機能を追加することもできます。
より複雑なデータ処理については、pandas ライブラリの使用を検討してください。

import openpyxl
import pyodbc

# データベースへの接続
conn = pyodbc.connect('DRIVER={ODBC Driver 17 for SQL Server};SERVER=your_server;DATABASE=your_database;UID=your_username;PWD=your_password')
cursor = conn.cursor()

# ファイル名とワークシート名
filename = 'data.xlsx'
sheet_name = 'Sheet1'

# ワークブックを開く
wb = openpyxl.load_workbook(filename)

# ワークシートを取得
sheet = wb[sheet_name]

# テーブル名を取得
table_name = sheet.title

# カラム名を取得
column_names = []
for cell in sheet[1]:
    column_names.append(cell.value)

# INSERT スクリプトの生成
insert_query = f"INSERT INTO {table_name} ({','.join(column_names)}) VALUES ("
for row in sheet[2:]:
    values = []
    for cell in row:
        if isinstance(cell.value, str):
            values.append(f"'{cell.value}'")
        else:
            values.append(str(cell.value))
    insert_query += f"({','.join(values)}), "
insert_query = insert_query[:-2] + ";"

# INSERT スクリプトの実行
cursor.execute(insert_query)
conn.commit()

# データベース接続のクローズ
conn.close()

説明

ライブラリのインポート: openpyxl と pyodbc ライブラリをインポートします。
データベース接続: pyodbc ライブラリを使用して、データベースに接続します。
ファイル名とワークシート名: filename と sheet_name 変数に、Excel ファイル名とワークシート名を格納します。
ワークブックを開く: openpyxl ライブラリを使用して、Excel ワークブックを開きます。
ワークシートを取得: sheet 変数に、対象となるワークシートを取得します。
テーブル名を取得: table_name 変数に、ワークシート名 (テーブル名) を格納します。
カラム名を取得: ループを使用して、最初の行 (ヘッダー行) の各セルからカラム名を取得し、column_names リストに格納します。
INSERT スクリプトの生成: 以下のステップで、INSERT スクリプトを生成します。
- insert_query 変数に、INSERT ステートメントの最初の部分 (テーブル名、カラム名) を格納します。
- ループを使用して、各行のデータを取得します。
  - 各セル値を処理し、文字列の場合は ' で囲み、そうでない場合はそのまま文字列に変換します。
  - 処理された値を values リストに格納します。
  - values リストの要素をカンマ (,) で区切って結合し、INSERT ステートメントに追加します。
- insert_query 変数の末尾に ; を追加して、INSERT ステートメントを完成させます。
INSERT スクリプトの実行: cursor.execute() メソッドを使用して、生成されたINSERT スクリプトを実行します。
データベースコミット: conn.commit() メソッドを使用して、データベースへの変更をコミットします。