Excelにデータがまとまっているので、そのデータを直接データベースへ登録したいようなケースは、結構ありがち。こんなときはやっぱりPythonの出番。SQLServerへさくっと登録してみる。

事前準備

SQL Serverのインストール

データベース環境に直接アクセスできなかったり、その前に別環境で試してみたい場合は自前のPCにSQLServerを立てる。ずっとサーバーを立てたままでよければ、SQL ServerのダウンロードからDeveloperもしくはExpressをダウンロードしてインストールすればよい。どちらも無料だ。

しかし、一過性の開発環境なので終わったらもとに戻したい（、しかし、いつかまた忘れたころに作業を再開しなきゃかも）、とか。そもそも、MacOSなので入れられない、とかの場合は、やっぱりDockerでインストールするしかないだろう。

クイックスタート:Docker を使用して SQL Server コンテナーイメージを実行する

ODBCドライバのインストール

SQL Serverがインストールされている環境でプログラムから操作する場合は、 SQL Serverのネイティブドライバーがインストールされているため、 ODBCドライバをインストールする必要はない。

SQL Serverがインストールされていない環境でプログラムから操作する場合は、 ODBCドライバをインストールしておく。

ODBC Driver for SQL Server のダウンロード

Microsoft ODBC Driver for SQL Server をインストールする (macOS)

サンプルデータベース

単純なデータベースを使っている。

CREATE DATABASE TestDB;
USE TestDB;
CREATE TABLE Worklist (id INT, name NVARCHAR(50), filename NVARCHAR(128));
INSERT INTO Worklist VALUES(1, 'Taro Arai', '00000001.jpg');
INSERT INTO Worklist VALUES(2, 'Hanako Arai', '00000002.jpg');
SELECT * FROM Worklist;

SQL Server チュートリアル

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

pythonとpipがインストールされているのが、大前提。必要なライブラリをインストールしておく。 Jupyter notebookに手順をまとめてあるので、必要であればJupyter関連もインストールしておく。（直接実行できるけど、内容はここに書いているものとほぼ同じ。）

pyodbc  # ODBCドライバ
xlrd  #EXCEL
jupyter # Jupyter（使うなら）
jupyterlab # Jupyter（使うなら）

$ pip install -r requirements.txt

Python SQL ドライバー - pyodbc

操作手順

データベース

環境に合わせて変数を設定しておく。オープン（接続）時に渡す文字列中に埋め込んでも構わない。

import pyodbc

driver = 'ODBC Driver 17 for SQL Server'  # ODBCドライバを使う場合
# driver = 'SQL Server'  # SQL Serverのネイティブドライバーを使う場合
server = 'localhost'
database = 'TestDB'
username = 'SA'
password = '<YourNewStrong@Passw0rd>'

データベース操作の前に接続しておく。

conn = pyodbc.connect(f'DRIVER={{{driver}}};SERVER={server};DATABASE={database};UID={username};PWD={password}')

レコードの追加はこんな感じ。

cursor = conn.cursor()
params = (id, name, filename) # id, name, filenameにはそれぞれ値を設定しておく。
sql_str = f"INSERT INTO Worklist(id, name, filename) VALUES(?, ?, ?)"
cursor.execute(sql_str, params)

サンプルデータベースではidをUNIQUEに設定していない。同じidがあるかチェックしたかったらこんな感じ。

cursor = conn.cursor()
sql_str = f"SELECT COUNT(*) FROM Worklist WHERE id={id}"
cursor.execute(sql_str)
row = cursor.fetchone()
if row[0] > 0:
    ...snip...

あとはSQL文が変わるだけ。レコードを取得したかったらこんな感じ。

cursor = conn.cursor()
sql_str = "SELECT * FROM Worklist"
if id is not None: # idが設定されていたら、そのidで絞り込む。
    sql_str += f" WHERE id={id}"
cursor.execute(sql_str)
rows = cursor.fetchall()
for row in rows:
    ....snip....

サンプルではクラス化している。

Excel

サンプルではカラムは固定されている前提。

class Column(Enum):
    ID = 1
    NAME = 2
    FILENAME = 3

pandasを使ってもExcel操作は可能だが、単純にExcelにアクセスするだけだったらxlrdで構わない。

import xlrd

Excelファイルをオープンする。

excel = xlrd.open_workbook('sample/sample_data.xlsx')

シートを読み込む。

sheet = excel.sheet_by_name('Sheet1')

先頭からデータが存在している最後の行までを順次読み込んで処理をおこなう。

for y in range(0, sheet.nrows):
    row = sheet.row(y)
    tup = (int(row[Column.ID.value].value),
           row[Column.NAME.value].value,
           row[Column.FILENAME.value].value,)
    # データベースにレコードを追加

サンプルでは、レコード追加の結果をcsvファイルへ出力しているが割愛。

サンプルのソースコードは以下から取得できる。

https://github.com/tatsu/sqlserver_sample

Pythonを使ってExcelデータをデータベースに登録する