9.1 スプレッドシートからリレーショナルデータベースへ

(1/1)
データサイエンティストの女性
Python はデータ処理が得意で、ビッグデータの解析処理や機械学習に利用されることが多い。処理するデータとして、「7.2 ファイル入力」ではCSVファイルを紹介したが、より複雑で大規模なデータを扱えるよう、Python で SQL を使わずにデータベース処理を行うための基礎を学んでいく。データベースとは何かという概念は追い追い説明していくとして、まずは、「7.2 ファイル入力」で使ったCSVファイルをデータベースに登録することからはじめる。

目次

  1. サンプル・プログラム
  2. 準備
  3. CSVファイルをDBに登録するプログラムの使い方
  4. 解説：CSVファイルをDBに登録するプログラム
  5. 成績集計プログラムの使い方
  6. 解説：成績集計プログラム
  7. 4年生の成績集計
  8. 4年生のCSVファイルをDBに登録するプログラムの使い方
  9. 解説：4年生のCSVファイルをDBに登録するプログラム
  10. 解説：4年2組の成績を集計する
  11. データベース化のメリット
  12. 解説：スプレッドシートからリレーショナルデータベースへ
  13. 練習問題
  14. 次回予告
  15. コラム：IDは整数か？
  16. コラム：さまざまなRDBMS
  17. 参考サイト

サンプル・プログラム

download ダウンロード

圧縮ファイルの内容
csv2db.pyCSVファイルをDBに登録するプログラム
testScore.csv成績表CSVファイル
testScore8.py成績集計プログラム
csv2db2.py4年生のCSVファイルをDBに登録するプログラム
testScore2.csv4年生の成績表CSVファイル
testScore9.py4年生の成績集計プログラム

準備

Python 2.5以降では、sqlite3モジュールというデータベースエンジンが標準モジュールとして組み込まれた。これは SQLite という、軽量で汎用のリレーショナルデータベースシステム（RDBMS）を動かすことができるモジュールだ。RDBMS の役割については後述する。

データベースというと SQL を連想する方が多いと思うが、このシリーズでは SQL は取り上げない。SQL は RDBMS を運用するための非常に強力な命令体系ではあるが、それがゆえ、ミスでデータベースを壊したり、データベース構造の矛盾をきたしてしまうことがあり、利用には相応の技術力が求められるからだ。
代わりに、プログラミング言語のデータ構造（オブジェクト）をそのままデータベースに反映できる ORM（Object Relational Mapper）を用いることにする。ここでは、Python でよく使われる外部モジュール「SQLAlchemy」を用いる。
また、CSVファイルを簡単に SQLAlchemy に渡せるように、データ解析ライブラリとして外部モジュール「pandas」を用いる。
これら外部モジュールは下記のようにpipコマンドを使って導入してほしい。
pip install sqlalchemy
pip install pandas
次に、SQLite で作ったデータベースの内容を確認するビューワを用意しよう。いくつかのビューワがフリーソフトとして流通しているが、ここでは、日本語に対応しており、Windows、macOS、Linuxで利用できる「DB Browser for SQLite」を利用することにする。サイトからダウンロードしてインストールしておいてほしい。

CSVファイルをDBに登録するプログラムの使い方

準備ができたら、ダウンロードしたZIP書庫に入っている "csv2db.py" と成績CSVファイル "testScore.csv" をカレントディレクトリに配置し、Python で実行する。コンソールに「tableTextScore テーブルにデータが追加されました」と表示すれば成功だ。
カレントディレクトリに "testScore.sqlite3" というファイルができる。これが SQLite のデータベースファイルだ。SQLite は、Oracle や SQLServer といった大規模RDBMSと異なり、1つのデータベースが1つのファイルに収まっているので、取り扱いが簡単だ。
CSVファイルをDBに登録する - Python
DB Browser for SQLite を使って "testScore.sqlite3" を開くと、左図のように成績表CSVファイルの内容がデータベースに登録されていることが分かる。

解説：CSVファイルをDBに登録するプログラム

csv2db.py
import sqlite3


try:
	import pandas as pd
	from sqlalchemy import create_engine, Column, Integer, String, Table
	from sqlalchemy.orm import sessionmaker, declarative_base
except ImportError as errmsg:
	print(f"外部ライブラリをインストールしてください ... {errmsg}")
	exit()


# 初期値  =================================================================
# CSVファイル名：「7.2 ファイル入力」で使った成績表
csvFileName = "./testScore.csv"


# CSVファイルのエンコード = シフトJIS
csvEncoding = "shift_jis"


# CSVファイルで読み飛ばす行数
csvSkipRows = 1


# DBパス：SQLiteが利用するファイル名
databaseURI = "sqlite:///testScore.sqlite3"
では、"csv2db.py" をご覧いただきたい。
まず、標準モジュール sqlite3 と外部モジュールを import する。

次に、読み込むCSVファイル名を変数 csvFileName に代入する。
このCSVファイルは、「7.2 ファイル入力」で使ったものと同じで、Excelエクスポート機能で作ったのでシフトJISコードのままだ。変数 csvEncoding にエンコード方式を代入する。

後述する外部モジュール pandas を使ってCSVファイルを読み込むとき、1行目をデータベースのカラム名（列名）とすることができるのだが、日本語なので、この1行は読み飛ばして、プログラム側でカラム名を付けることにする。読み飛ばす行数を csvSkipRows に代入する。
なお、SQLite では日本語のカラム名も設定できるのだが、作成したデータベースを Python 以外のプログラミング言語で操作しようとすると、日本語カラム名に対応していないことがあるかもしれないので、ここでは一般的な英数字（ASCII文字）をカラム名として用いる。

DBパスは、"sqlite:///{データベースファイルのフルパス}" という書式だ。ここでは、カレントディレクトリに "testScore.sqlite3" として保存するので、パス名は記載しなかった。データベースファイル名の拡張子は何でもいいのだが、SQLiteバージョン3であることはわかりやすいよう .sqlite3 にした。
CSVファイルをDBに登録する - Python
1つの DBパスには複数のテーブルを格納することができる。テーブルというのは、Excelファイルのワークシートのようなものと考えてほしい。
1つのテーブルは1つのテーブル名（Excelのワークシート名に相当）をもち、複数のカラム（Excelの列に相当）があり、複数のレコード（Excelの行に相当）から成る。
前述した DB Browser for SQLite での表示（左図）では、画面全体が1つのテープルをあらわし、横方向に複数並んでいるのがカラム、縦方向に複数並んでいるのがレコードである。
csv2db.py
# ベースモデル
Base = declarative_base()


# テーブル定義
class tableTestScore(Base):
	__tablename__ = "tableTestScore"	# テーブル名
	id			= Column(String(20), primary_key = True)
	lastName	= Column(String(50))
	firstName	= Column(String(50))
	japanese	= Column(Integer)
	arithmetic	= Column(Integer)
	science		= Column(Integer)
	social		= Column(Integer)


# メイン・プログラム =======================================================
SQLAlchemyは、このテーブルを定義するのに Python のクラスを使う。
ここで、1つのテーブルに対して1つのテーブル定義クラスを用意することを覚えておいてほしい。SQLAlchemy のような ORM は、RDBMSのテーブルにクラスが対応しており、クラス変数にアクセスするようにしてテーブルのカラム（列）に入っているデータ本体にアクセスすることができる。したがって、SQL を覚える必要はない。

まず、SQLAlchemy の declarative_base を使って、テーブルのモデルベースクラスを生成する。そして、ユーザークラス "tableTestScore" の中で目的とするテーブルの形に拡張する。
クラスの中には、テーブル名と、カラム名をクラス変数として定義する。カラムの定義は、Columnクラスを使う。第1引数には、そのカラムのデータ型を記載する。
「3.9 基本データ型と型変換」で学んだように、Python は動的データ型付け言語で、これまでデータ型をあまり意識してこなかった。しかし、RDBMS は静的データ型であるため、明確にデータ型を定義する必要がある。
といっても、このシリーズでは String（長さが比較的短い文字列）、Text（長い文字列）、Integer（整数）、Float（浮動小数）くらいを覚えておけば十分だ。DateTime（日時）も覚えておいてもいいのだが、SQLite 以外の RDBMS を使うとき――RDBMS によって格納される値が異なるので、このシリーズでは日時は文字列として扱うことにする。
csv2db.py
# メイン・プログラム =======================================================
# SQLiteエンジンを作成
engine = create_engine(databaseURI)


# テーブルを作成する
Base.metadata.create_all(engine)
続いてメイン・プログラムであるが、まず、SQLite のデータベースエンジンを Python のオブジェクトとして生成する。これが create_engine である。引数として、先ほど定義したDBパスを渡す。
テーブルの作成は、拡張したモデルベースクラスに metadata.create_all を実行する。これで、空の成績表テーブル "tableTestScore" が作成された。
csv2db.py
# CSVファイルを読み込む（エンコードはシフトJIS）
# 1行目の見出し行を読み飛ばす
# カラム名を表すヘッダはない
df = pd.read_csv(csvFileName, encoding=csvEncoding, \
		skiprows=csvSkipRows, header=None)
# カラム名を指定する
df.columns = [
	"id", "lastName", "firstName",
	"japanese", "arithmetic", "science", "social"
]


# データベースにデータを追加する
次に、外部モジュール pandas.read_csv を使ってCSVファイルを読み込む。
pandas.read_csv には多数の引数があるのだが、ここでは、読み込むCSVファイル名、エンコード（encoding）、冒頭スキップ行数（skiprows）、冒頭がカラム名ではないこと（header=None）を指定する。
冒頭がカラム名ではないので、代わりに、プログラム側でカラム名をリスト df.columns に代入しておく。これは、すでに定義したテーブル tableTestScore のカラム名と一致させる必要がある（純画を一致させる必要はない）。
csv2db.py
# データベースにデータを追加する
with engine.connect() as conn:
	# データを一括でインサート
	df.to_sql(tableTestScore.__tablename__, conn, if_exists="append", \
		index=False)


print(f"{tableTestScore.__tablename__} テーブルにデータが追加された")
いよいよデータベースへの登録であるが、SQLを使うよりも簡単である。
まず with engine.connect() as conn により、先ほど作成したデータベースエンジンに接続する。
つづいて、pandas.DataFrame.to_sql を使って、読み込んだデータを一気にデータベースに挿入（append）する。pandas.DataFrame.to_sql] には多数の引数があるのだが、ここでは、挿入したいテーブル名、DBコネクション（conn）、テーブルが存在すれば挿入（append）を実行する（if_exists="append"）、登録にはカラム名を含めない（index=False）を指定する。
これだけでCSVファイルのデータを丸ごとデータベース・テーブルに登録することができる。

成績集計プログラムの使い方

ここまでで作成したデータベースを使って成績集計を行うプログラムが "testScore8.py" である。
データベースファイル "testScore.sqlite3" と同じディレクトリに置いて実行してみてほしい。このプログラムは、各教科の平均値と4教科合計平均値のみ表示するが、「5.5 タプル、集合型、辞書型」のときと同じ結果が得られる。

解説：成績集計プログラム

testScore8.py
import sqlite3

try:
	from sqlalchemy import create_engine, Column, Integer, String, Table, func
	from sqlalchemy.orm import declarative_base, sessionmaker
except ImportError as errmsg:
	print(f"外部ライブラリをインストールしてください ... {errmsg}")
	exit()

# 初期値  =================================================================
# DBパス：SQLiteが利用するファイル名
databaseURI = 'sqlite:///testScore.sqlite3'

# ベースモデル
Base = declarative_base()

# テーブル定義
class tableTestScore(Base):
	__tablename__ = "tableTestScore"	# テーブル名
	id			= Column(String(20), primary_key = True)
	lastName	= Column(String(50))
	firstName	= Column(String(50))
	japanese	= Column(Integer)
	arithmetic	= Column(Integer)
	science		= Column(Integer)
	social		= Column(Integer)

# 科目辞書
subjects = {
	"国語": "japanese", "算数": "arithmetic",
	"理科": "science", "社会": "social"
}

# メイン・プログラム =====================================================
では、"testScore8.py" をご覧いただきたい。
必要なモジュールの import からテーブル定義までは、"csv2db.py" と同じだ。
科目毎の集計のため、科目名とカラム名の対応辞書 subjects を用意しておく。
testScore8.py
# メイン・プログラム =====================================================
# SQLiteエンジンを作成
engine  = create_engine(databaseURI)
Session = sessionmaker(bind=engine)
session = Session()

# 各科目の平均点を計算する
total = 0;
for label, column in subjects.items():
	average = session.query(func.avg(getattr(tableTestScore, column))).scalar()
	total += average
	print(f"{label} : {average:3.1f}")

print(f"合計 : {total:3.1f}")
メイン・プログラムを見ていこう。
「7.3 正規表現」で作った "testScore7.py" と比較してもらうと分かるのだが、わずか数行で4教科の平均計算ができてしまう。

まず、データベースエンジンのオブジェクトを精製するところまでは "csv2db.py" だが、データベースに接続したままで集計処理を行っていくことになるので、sessionmaker を使ってデータベースへのセッションを張る。

各科目の平均点計算だが、科目名とカラム名の対応辞書 subjects を参照しながら、1科目ずつ平均値を計算する。SQLAlchemy には平均計算の関数 func.avg が用意されており、引数に計算条件を指定する。ここでは、テーブル tableTestScore の column の平均を求めるよう指定する。
あとは、セッションにクエリーを発行する session.query ことで、自動的に平均値を求めることができる。

4年生の成績集計

これまで扱ってきた成績表は、ある小学校の4年1組のものだとする。4年生は3クラスあり、あなたは4年生の学年主任で、3クラス分の成績を集計することになった状況を想像してほしい。
4年生の名簿
上図のような4年生の学年名簿がExcelファイルとして保管されていたので、これをコピペして、各クラスの成績表データをマージすることにした。クラス毎の集計ができるように、学年、クラスというカラムを追加した。これらは面倒な手作業だ。そして完成したExcelファイルから抽出したCSVファイルが "testScore2.csv" である。

4年生のCSVファイルをDBに登録するプログラムの使い方

では、ダウンロードしたZIP書庫に入っている "csv2db2.py" と成績CSVファイル "testScore2.csv" をカレントディレクトリに配置し、Python で実行する。コンソールに「tableTextScore2 テーブルにデータが追加されました」と表示すれば成功だ。
DB Browser for SQLite を使って "testScore.sqlite3" を見ると、tableTestScore2 というテーブルが追加になったことが分かる。こちらが4年生全員の成績表だ。

解説：4年生のCSVファイルをDBに登録するプログラム

csv2db2.py
import sqlite3


try:
	import pandas as pd
	from sqlalchemy import create_engine, Column, Integer, String, Table
	from sqlalchemy.orm import sessionmaker, declarative_base
except ImportError as errmsg:
	print(f"外部ライブラリをインストールしてください ... {errmsg}")
	exit()


# 初期値  ===================================================================
# CSVファイル名：「7.2 ファイル入力」で使った成績表
csvFileName = "./testScore2.csv"


# CSVファイルのエンコード = シフトJIS
csvEncoding = "shift_jis"


# CSVファイルで読み飛ばす行数
csvSkipRows = 1


# DBパス：SQLiteが利用するファイル名
databaseURI = "sqlite:///testScore.sqlite3"


# ベースモデル
Base = declarative_base()


# テーブル定義
class tableTestScore2(Base):
	__tablename__ = "tableTestScore2"	# テーブル名
	id				= Column(String(20), primary_key = True)
	lastName		= Column(String(50))
	firstName		= Column(String(50))
	grade			= Column(String(6))
	cclass			= Column(String(6))
	attendNumber	= Column(String(6))
	japanese		= Column(Integer)
	arithmetic		= Column(Integer)
	science			= Column(Integer)
	social			= Column(Integer)


# メイン・プログラム =======================================================
では、"csv2db2.py" をご覧いただきたい。
まず、モジュールの import からモデルベースクラスの精製までは "csv2db.py" と同じだ。CSVファイルの構造が違うので、テーブル定義 tableTestScore2 が異なる。学籍番号（id）、学年（grade）、クラス（cclass）の各カラムを追加した。ここで、クラスを class としてしまうと、クラス定義の予約語 class と被ってエラーとなるため、cclass という名前にした。
csv2db2.py
# CSVファイルを読み込む（エンコードはシフトJIS）
# 1行目の見出し行を読み飛ばす
# カラム名を表すヘッダはない
# DBに取り込むカラム番号を指定する
df = pd.read_csv(csvFileName, encoding=csvEncoding,
	skiprows=csvSkipRows, header=None,
	usecols=[0, 1, 2, 7, 8, 9, 10, 11, 12, 13],
	dtype={0:str, 1:str, 2:str, 7:str, 8:str, 9:str}
)
メイン・プログラムも "csv2db2.py" とほぼ同じだが、pandas.read_csv に追加した項目がある。
CSVファイルの方には、姓名の読みや性別、生年月日が含まれているが、成績表を作るのには不要なのでデータベースには登録しないようにする。そこで、CSVファイルのウチ使うカラム番号をリストで指定する（usecols=[0, 1, 2, 7, 8, 9, 10, 11, 12, 13]）。一番左のカラムが0番で、右へ向かって1, 2, 3‥‥と番号が振られている。
また、CSVファイルにはデータ型に関する情報がないので、使うカラムのうち文字列のものを指定する（dtype={0:str, 1:str, 2:str, 7:str, 8:str, 9:str}）。
その他は "csv2db2.py" とほぼ同じである。

解説：4年2組の成績を集計する

testScore9.py
# 集計対象の学年
targetGrade = "4"
# 集計対象のクラス
targetClass = "2"

# メイン・プログラム =======================================================
# SQLiteエンジンを作成
engine = create_engine(databaseURI)
Session = sessionmaker(bind=engine)
session = Session()

# 各科目の平均点を計算する
total = 0;
for label, column in subjects.items():
	average = session.query(func.avg(getattr(tableTestScore2, column))) \
		.filter(tableTestScore2.grade == targetGrade, \
			tableTestScore2.cclass == targetClass).scalar()
	total += average
	print(f"{label} : {average:3.1f}")

print(f"合計 : {total:3.1f}")
"testScore9.py" は、先ほど作成したテーブル tableTestScore2 から4年2組のデータを抽出して、各科目平均と4教科平均を計算するプログラムだ。
冒頭の部分は "testScore8.py" と同じなので解説を割愛する。
抽出したい学年を変数 targetGrade に、クラスを targetClass に代入する。
平均値計算に 関数 func.avg を使うのは "testScore8.py" と同じだが、 session.query のメソッドとして filter が加わった。これが、テーブルからデータを抽出するための条件を書く部分である。
ここでは、テーブル tableTestScore2 の学年、すなわち tableTestScore2.grade と先に代入した targetGrade が等しく、かつ、クラス tableTestScore2.cclass が targetClass と等しいデータの平均を計算する式となる。

この抽出条件をなくせば、1学年全体の平均値を求めることができる。これは課題として取り組んでみてほしい。

データベース化のメリット

ここで、Python、S0LAlchemy、SQLite、データベースの関係について整理しおこう。
Python・SQLite・SQLAlchemy
左図のように、この4つは互いに独立している。Python で書いたプログラムは S0LAlchemy によってSQL分に翻訳され、データベースの管理を行う SQLite に渡され、SQLiteがデータベースの作成・参照・更新・削除などを行う。
SQLiteはデータベースを管理（マネジメント）するので、データベースマネージメントシステム（正確には RSBMS；リレーショナルデータベースマネジメントシステム）と呼ぶ。SQLAlchemy は、冒頭でも紹介したように、RDBMSとプログラミング言語の間を仲介してデータベースをオブジェクトのように見せかける ORM（Object Relational Mapper）と呼ぶ。
PythonとDB Browser for SQLite
Python と DB Browser for SQLite は、これら4つとは独立したアプリケーションで、SQLiteを内蔵し、データベース管理をサポートする。

このようにデータベースは、プログラミング言語や他のアプリケーションから独立しているので、同時に複数の人がアクセスすることができる。つまり、成績表をデータベースにしておけば、同時に複数の教員が別の場所から成績表を管理することができるわけだ。
デスクトップ版のExcelを利用している場合、Excelファイルを共有フォルダに置いたとしても、同時に編集できるのは1人だけで、他の人は参照しかできない。クラウド版Excelにすれば共同編集できるが、アカウントやセキュリティ管理の手間がかかる。

解説：スプレッドシートからリレーショナルデータベースへ

だが、今回学んだ範囲では、データベースのメリットを十分活かし切れていない。
というのは、今回は1クラスの成績データ、1学年3クラスの成績データの2つをデータベースに登録し、平均計算するプログラムを作ったわけだが、これらのプログラムは、いずれもExcelスプレッドシートと同じことをやっており、わざわざデータベースで処理する必要がないからだ。

ここで、この作業を拡大し、学校全体（1～6年生全クラス）の成績表データを集計したり、今回の期末テストだけでなく1年を通じてのテスト全てのデータを集計することを想像してみてほしい。
今回取り上げたように、学年毎にワークシート（＝テーブル）を増やすだろうか――テストの都度、各クラスのデータをマージする手作業は大変なことだ。
では、学校全体を1つの巨大なワークシート（＝テーブル）にするか――しかし、1年生、2年生では理科、社会の期末テストはないから、カラムの構造がチグハグナものになってしまう。
そして、次の期末テストの点数はどこに入れるか――列を増やすか、ワークシートで増やすか。
さらには、年度内に他校から転入してきたり、他校へ転出する児童がいる。ワークシート上でどう扱えばいいか‥‥。

課題山積である。これらはリレーショナルデータベース（RDBMS)を正しく設計することで解決することを、次回学んでいくことにする。

練習問題

次回予告

スプレッドシートをただデータベースに登録するのではなく、データベースを正しく設計することで、今回見えてきた課題を解決することができる。
そこで次回は、理解・分解・構築という流れでデータベースを設計し、Excelに集めた成績表などのデータをCSVファイルにしてデータベースを構築するプログラムを作る。SQL は使用しない。

コラム：IDは整数か？

社員証のイラスト（女性）
本編のテーブル定義クラス tableTestScore, tableTestScore2 をよく見ていただくと、id（出席番号や学籍番号）のデータ型が String（文字列）になっている。いずれも見た目は数字なので、Integer（整数）で良いと思われるかもしれないが‥‥ゼロで始まる id が無いとは限らない。0ではじまる数字をIntegerにすると、データベース登録時に頭の0が無くなってしまう。Integerなので当たり前のことなのだが‥‥。
電話番号も要注意だ。国内の電話番号は必ず0で始まるからだ。電話番号のカラムのデータ型は必ず String または Text にしなければいけない。

このように、見た目が数字だからといって、データ型としてIntegerを指定してはならないものがある。区別の区分基準としては、計算や集計などを行う場合には Integer、そうでない場合は String または Textと覚えておけば間違いない。
本編では、学年やクラスも string にした。学年は数字のことがほとんどだろうが、クラスはアルファベットのことがあるからだ。
そして、平均値の計算対象となっているものだけをIntegerにした。

コラム：さまざまなRDBMS

さまざまなRDBMS
このシリーズでは紹介しないが、世の中にはさまざまな RDBMS がある。
MySQL はオープンソースで、比較的高速で、全文検索を行うこともできる。所有者が、サン・マイクロシステムからにオラクルと移り、現在もオープンソースではあるのだが、オリジナルメンバは MariaDB という互換RDBMSを供給している。
そのオラクルは、商用 RDBMS「Oracle」を主力製品として販売している。
PostgreSQL もオープンソースで、使用にも耐えられるセキュリティや拡張性をもった RDBMS だ。
これらは、いずれも Python から接続するための外部ライブラリが用意されており、さらに SQLAlchemy を使って利用することもできる。
Python 以外のプログラミング言語、たとえば PHP や Java も、これらのメジャーな RDBMS に接続する機能を備えている。

ただし、SQLite のデータベースは SQLite 専用構造であり、MySQL のデータベースは MySQL 専用構造であるというように、異なる RDBMS を使って管理することはできない。
したがって、アプリケーションを開発する言語は目的に合わせて何でも選んで構わないのだが、組織のデータを一元管理するという観点からすると、RDBMS は1種類に絞った方がいい。

参考サイト

（この項おわり）
前へ 目次 次へ
関連ページ bluesky
Valid HTML5 正当なCSSです!
2024年11月16日 作成／2024年11月16日 更新
Copyright by studio pahoo, (C)2024
（※）本ページはリンクフリーですが、複製・転載時にはご一報ください。
🤖 当サイト内でのAI利用については、「AIの利用について」をご覧下さい。
★本ページへのご意見・ご質問・お便りは、こちらへお願いします。
header
ホーム  > Web技術  > Python入門