前回、分解したテーブルやマスタの関係を図示するところまで学んだが、今回は、この関係をPythonプログラムに実装し、「9.1 スプレッドシートからリレーショナルデータベースへ」で表示した分解前の成績表スプレッドシートを再構築するところまでを学ぶ。引きつづきSQLは使わないが、テーブルの結合やデータの抽出など、データベース特有の手法を習得することを目指す。
再構築の手順は次の通り。
- 児童テーブルを表示する。
- 児童テーブルと性別マスタを結合し、表示する（結合の仕組みを学ぶ）。
- 指定日時点の最新イベントを絞り込み、表示する。
- 児童テーブルとイベント・テーブルを結合し、児童一覧表を表示する。
- 児童、イベント、得点テーブルを結合し、成績表を表示する。
目次
サンプル・プログラム
| dispStudent1.py | 児童テーブルの内容を一覧表示するプログラム |
| dispStudent2.py | 児童テーブルと性別マスタを結合して一覧表示するプログラム |
| dispEvent1.py | 指定年月日の時点のイベント一覧を表示するプログラム |
| dispEvent2.py | dispEvent1.pyを指定学年・クラスで絞り込むプログラム |
| dispStudent3.py | 児童テーブルとイベントマスタを結合して指定学年・クラスで絞り込むプログラム |
| dispTestScore1.py | 成績表を成功チクするプログラム |
| testScore2.sqlite3 | 前回作成したデータベース(SQLite) |
解説：児童テーブルを表示する
ダウンロードしたZIPファイルを解凍すると、前回CSVから作成したデータベース(SQLite)のファイル "testScore2.sqlite3" が得られる。今回は、このデーターベース・ファイルを使う。
カレントディレクトリにPythonプログラムとデータベース・ファイルを配置できたら、まずは、テーブル tableStudent の内容を一覧表示するプログラム "dispStudent1.py" を実行してみてほしい。上図のように、ウィンドウに児童一覧が表示される。このウィンドウは、「8.1 画像フォーマット変換」で学んだ標準ライブラリ tkinter を使っており、ウィンドウの右上×ボタンをクリックするとプログラムは終了する。
プログラムの流れは下図の通りで、ユーザー定義関数 getDataFrame1 を使ってデータベースから取りだしたデータを、pandas の辞書型 DataFrame に変換し、ユーザー定義関数 dispDataFrame に渡し、tkinter の Treeviewウィジェット を使って画面に一覧表示する。データベースから取りだしたデータを DataFrame に変換するのは、その方が Treeviewウィジェット に表示しやすいからだ。
また、データの再構築に関わるのはユーザー定義関数 getDataFrame1 だけで、一覧表示に関わる dispDataFrame や calcColumnsWidths は同じものを使う。
カレントディレクトリにPythonプログラムとデータベース・ファイルを配置できたら、まずは、テーブル tableStudent の内容を一覧表示するプログラム "dispStudent1.py" を実行してみてほしい。上図のように、ウィンドウに児童一覧が表示される。このウィンドウは、「8.1 画像フォーマット変換」で学んだ標準ライブラリ tkinter を使っており、ウィンドウの右上×ボタンをクリックするとプログラムは終了する。
プログラムの流れは下図の通りで、ユーザー定義関数 getDataFrame1 を使ってデータベースから取りだしたデータを、pandas の辞書型 DataFrame に変換し、ユーザー定義関数 dispDataFrame に渡し、tkinter の Treeviewウィジェット を使って画面に一覧表示する。データベースから取りだしたデータを DataFrame に変換するのは、その方が Treeviewウィジェット に表示しやすいからだ。
また、データの再構築に関わるのはユーザー定義関数 getDataFrame1 だけで、一覧表示に関わる dispDataFrame や calcColumnsWidths は同じものを使う。
dispStudent1.py
import sqlite3
import tkinter as tk
from tkinter import ttk
try:
import pandas as pd
from sqlalchemy import create_engine, Table
from sqlalchemy import Column, Integer, String, Text, Boolean
from sqlalchemy.orm import sessionmaker, declarative_base
except ImportError as errmsg:
print(f"外部ライブラリをインストールしてください ... {errmsg}")
exit()
標準モジュールとして、sqlite3 と tkinter を、外部モジュールとして pandas と sqlalchemy を import するのは、これまで学んできたとおりだ。SQLAlchemy については、create_engine や Table など、必要なモジュールを import しているが、これはプログラムによって変わるので留意してほしい。
dispStudent1.py
# 初期値(START) =============================================================
# DBパス：SQLiteが利用するファイル名
databaseURI = "sqlite:///testScore2.sqlite3"
# ベースモデル
Base = declarative_base()
# 初期値(END) ================================================================
# テーブル定義(START) ========================================================
# 児童テーブル
class tableStudent(Base):
__tablename__ = "tableStudent" # テーブル名
id = Column(String(20), primary_key = True) # 学籍番号
lastName = Column(String(50)) # 姓
firstName = Column(String(50)) # 名
lastNameYomi = Column(String(50)) # 姓よみ
firstNameYomi = Column(String(50)) # 名よみ
sex = Column(String(2)) # 性別コード
birth = Column(String(10)) # 生年月日
regist = Column(String(30)) # 登録日時
update = Column(String(30)) # 更新日時
delete = Column(Boolean) # 削除フラグ
# 表示するカラムのリスト（表示順）
columnList = ["id", "lastName", "firstName", "lastNameYomi", \
"firstNameYomi", "sex", "birth", "regist", "update", "delete"]
# テーブル定義(END) ==========================================================
初期値とテーブル定義も、これまで学んできたとおりだ。今回使うテーブル（オブジェクト）は児童テーブル tableStudent のみである。
それから、一覧にしたときのカラムの表示順序（左側からの順序）を、あらかじめリスト columnList に代入しておく。要素は、テーブルのカラム名である。
それから、一覧にしたときのカラムの表示順序（左側からの順序）を、あらかじめリスト columnList に代入しておく。要素は、テーブルのカラム名である。
dispStudent1.py
def getDataFrame1(columnList):
"""表示データ（児童テーブル）を取得する
Args:
columnList(list): 表示するカラムのリスト（表示順）
Returns:
dict: pandasのDataFrame
"""
# データベースセッションを作成
session = Session()
# クエリ
query = (
session.query(tableStudent)
)
results = query.all()
# クエリ結果をpandasのDataFrameに変換する
df = pd.DataFrame([row.__dict__ for row in results])
# SQLAlchemyの内部属性を削除
df = df.drop(columns=['_sa_instance_state'])
# データベースセッションを閉じる
session.close()
return df
ユーザー定義関数 getDataFrame1 は、データベースからデータを取りだし、pandas で扱うことができる辞書型 DataFrame に代入する。
データベースからデータを取り出して辞書型 DataFrame に代入する流れは、次の通り。
ここまでがデータベース操作だ。
次に、pandas.DataFrame を使って、辞書型 DataFrame 変数 df に全てのデータを変換、代入する。
ここで、_sa_instance_state というカラムは、SQLAlchemy が付加する内部属性データなので、これを削除する。
データベースからデータを取り出して辞書型 DataFrame に代入する流れは、次の通り。
- セッションを張る
- クエリを発行する
- クエリ結果を取得する（データ取得）
- 取得したデータを DataFrame に変換する
- セッションを閉じる
ここまでがデータベース操作だ。
次に、pandas.DataFrame を使って、辞書型 DataFrame 変数 df に全てのデータを変換、代入する。
ここで、_sa_instance_state というカラムは、SQLAlchemy が付加する内部属性データなので、これを削除する。
dispStudent1.py
def calcColumnWidths(df, padding=10):
"""一覧表示のカラム幅を設定する
Args:
df(dict): pandasのDataFrame
padding(int): カラム間のパディング（ピクセル）
Returns:
dict: 各カラムの幅（ピクセル）
Note:
DataFrameに入っている各カラムのデータ長から幅を計算する
"""
columnWidths = {}
for col in df.columns:
# 最大文字数を取得
maxWidth = max(df[col].astype(str).map(len).max(), len(col))
# パディングを追加
columnWidths[col] = maxWidth + padding
return columnWidths
ユーザー定義関数 calcColumnWidths は、辞書型 DataFrame の各カラムの幅（単位：ピクセル）を計算し、それをリストに入れて返す。
for文を使って、各カラムの最大幅を取得するのだが、df[col].astype(str).map(len).max( とすることで、当該カラムの全レコードを縦覧して最大幅を返してくれる。その最大値と、ラベルとなる col の長さを比べ、より大きい方を最大幅 maxWidth とする。
for文を使って、各カラムの最大幅を取得するのだが、df[col].astype(str).map(len).max( とすることで、当該カラムの全レコードを縦覧して最大幅を返してくれる。その最大値と、ラベルとなる col の長さを比べ、より大きい方を最大幅 maxWidth とする。
dispStudent1.py
def dispDataFrame(title, df, columnOrder, height=400):
"""データを一覧表示する
Args:
title(str): 表のタイトル
df(object): pandasのDataFrame
columnOrder(list): 表示するカラムのリスト（表示順）
height(int): 表の高さ（ピクセル）
Note:
一覧表表示はTkinterのTreeviewウィジェットを利用する
"""
# 一覧表の幅を計算
columnWidths = calcColumnWidths(df, 2)
width = 40
for col in columnOrder:
width += columnWidths[col] * 7
# tkinterウィンドウを作成
root = tk.Tk()
root.title(title)
# ウィンドウのサイズを指定
root.geometry(f"{width}x{height}")
# フレームを作成して配置
frame = ttk.Frame(root)
frame.pack(expand=True, fill="both")
# Treeviewウィジェットを作成
tree = ttk.Treeview(frame)
tree["columns"] = columnOrder
tree["show"] = "headings"
# 指定した順序でカラムを設定
for col in columnOrder:
tree.heading(col, text=col)
tree.column(col, anchor="center", width=columnWidths[col] * 7)
# 指定したカラム順でデータを挿入
for index, row in df[columnOrder].iterrows():
tree.insert("", "end", values=list(row))
# 縦スクロールバーを作成して配置
scrollbar = ttk.Scrollbar(frame, orient="vertical", command=tree.yview)
tree.configure(yscrollcommand=scrollbar.set)
scrollbar.pack(side="right", fill="y")
tree.pack(expand=True, fill="both")
# tkinterウィンドウを開始
root.mainloop()
ユーザー定義関数 dispDataFrame1 は、辞書型 DataFrame で受け取ったデータを tkinter の Treeviewウィジェット を使って画面に一覧表示する。
まず、前述のユーザー定義関数 calcColumnWidths を使って得られたカラム幅の合計値に余裕を持たせて 40 を加えてウィンドウの幅 width とする。
次に tkinter ウィンドウを作成し、geometry でウィンドウ幅と高さを設定する。
次に、一覧表を配置するフレームを Frame を使って作成する。
そして、Treeviewウィジェットを Treeview を使って作成する。Treeview はその名の通り、階層構造をGUI表示するためのクラスなのだが、これを表形式に応用することができ、データベース一覧を表示するときによく使われる。見出しやデータの設定方法はコメントに記した通りだが、「tkinter：テーブル（表）の作成方法」に詳しい説明がある。
まず、前述のユーザー定義関数 calcColumnWidths を使って得られたカラム幅の合計値に余裕を持たせて 40 を加えてウィンドウの幅 width とする。
次に tkinter ウィンドウを作成し、geometry でウィンドウ幅と高さを設定する。
次に、一覧表を配置するフレームを Frame を使って作成する。
そして、Treeviewウィジェットを Treeview を使って作成する。Treeview はその名の通り、階層構造をGUI表示するためのクラスなのだが、これを表形式に応用することができ、データベース一覧を表示するときによく使われる。見出しやデータの設定方法はコメントに記した通りだが、「tkinter：テーブル（表）の作成方法」に詳しい説明がある。
解説：児童テーブルと性別マスタを結合し、表示する
次に、カレントディレクトリで "dispStudent2.py" を実行してみてほしい。上図のように児童一覧が表示されるが、性別の数字が文字列に置き換わっている。これは、前回の再構成で見たように、児童テーブル tableStudent と性別マスタ masterSex を性別コードをキーにして結合することで、性別マスタの表示テキスト masterSex.label を児童一覧に表示するようにしている。
この結合の概念を示したものが下図になる。
この結合の概念を示したものが下図になる。
dispStudent2.py
# テーブル定義(START) ========================================================
# 児童テーブル
class tableStudent(Base):
__tablename__ = "tableStudent" # テーブル名
id = Column(String(20), primary_key = True) # 学籍番号
lastName = Column(String(50)) # 姓
firstName = Column(String(50)) # 名
lastNameYomi = Column(String(50)) # 姓よみ
firstNameYomi = Column(String(50)) # 名よみ
sex = Column(String(2)) # 性別コード
birth = Column(String(10)) # 生年月日
regist = Column(String(30)) # 登録日時
update = Column(String(30)) # 更新日時
delete = Column(Boolean) # 削除フラグ
# 性別マスタ
class masterSex(Base):
__tablename__ = "masterSex" # テーブル名
code = Column(String(2), primary_key = True) # ISO5218コード
label = Column(String(20)) # 表示テキスト
regist = Column(String(30)) # 登録日時
update = Column(String(30)) # 更新日時
delete = Column(Boolean) # 削除フラグ
# 結合テーブル：児童テーブル＋性別マスタ
joinStudentSex = [
tableStudent.id,
tableStudent.lastName,
tableStudent.firstName,
tableStudent.lastNameYomi,
tableStudent.firstNameYomi,
masterSex.label.label("sexLabel"),
tableStudent.birth,
tableStudent.regist,
tableStudent.update,
tableStudent.delete,
]
# 表示するカラムのリスト（表示順）
columnList = ["id", "lastName", "firstName", "lastNameYomi", \
"firstNameYomi", "sexLabel", "birth", "regist", "update", "delete"]
# テーブル定義(END) ==========================================================
テーブル定義に性別マスタ masterSex を加え、結合後のテーブルのイメージをリスト joinStudentSex に定義する。たとえば tableStudent.id, は、ドット . の左がテーブル名、右がカラム名を表す。データベースのカラムを表現するのに、この記法を用いる。
そして、tableStudent.sex の代わりに、masterSex.label を配置し、カラム名を "sexLabel" と定義する。表示するカラムのリスト columnList も sex の代わりに sexLabel を代入する。
ここでは結合のイメージを定義しただけで、実際の結合処理は、このあと説明するクエリで行う。
そして、tableStudent.sex の代わりに、masterSex.label を配置し、カラム名を "sexLabel" と定義する。表示するカラムのリスト columnList も sex の代わりに sexLabel を代入する。
ここでは結合のイメージを定義しただけで、実際の結合処理は、このあと説明するクエリで行う。
dispStudent2.py
def getDataFrame2(columnList):
"""児童テーブルと性別マスタを結合して表示データを取得する
Args:
columnList(list): 表示するカラムのリスト（表示順）
Returns:
dict: pandasのDataFrame
"""
# データベースセッションを作成
session = Session()
# クエリ
query = (
session.query(*joinStudentSex)
# 児童テーブルと性別マスタを結合する
.join(masterSex, tableStudent.sex == masterSex.code)
)
results = query.all()
# クエリ結果をpandasのDataFrameに変換する
df = pd.DataFrame(results, columns=columnList)
# データベースセッションを閉じる
session.close()
return df
表示データを取得するユーザー定義関数 getDataFrame2 と、先ほどのプログラムで使った getDataFrame1 の相違点は、発行するクエリにある。
まず、クエリ対象を session.query(*joinStudentSex) にしている。最初に定義した、結合後のテーブルのイメージ joinStudentSex が引数とする。ここで、リスト joinStudentSex の頭にアスタリスク * が付いているのはアンパックといって、リストの内容を展開して session.query に渡すという意味になる。つまり、結合語に出来るはずの全てのカラムのデータを取り出せという意味である。
次の joinメソッドは、joinStudentSex に結合するのは masterSex で、tableStudent.sex と masterSex.code が等しいデータを結合するという意味になる。ここで実際に2つのテーブルの結合が行われる。
データの一覧表示処理は、先ほどのプログラムと同じものを使う。
まず、クエリ対象を session.query(*joinStudentSex) にしている。最初に定義した、結合後のテーブルのイメージ joinStudentSex が引数とする。ここで、リスト joinStudentSex の頭にアスタリスク * が付いているのはアンパックといって、リストの内容を展開して session.query に渡すという意味になる。つまり、結合語に出来るはずの全てのカラムのデータを取り出せという意味である。
次の joinメソッドは、joinStudentSex に結合するのは masterSex で、tableStudent.sex と masterSex.code が等しいデータを結合するという意味になる。ここで実際に2つのテーブルの結合が行われる。
データの一覧表示処理は、先ほどのプログラムと同じものを使う。
解説：指定日時点の最新イベントを絞り込み、表示する
カレントディレクトリで "dispEvent1.py" を実行してみてほしい。上図のように、2024年11月20日時点の最新イベント一覧（2024年11月20日のイベント、もしくはそれ以前の年月日で最新のイベント）を表示する。この表から、id（学籍番号）の、2024年11月20日時点の学年とクラスを知ることができる。id=20210094のgrade（学年）、cclass（クラス）、attendNumber（出席番号）がNoneになっているのは、2024年8月26日にeventID=5（転出）したため、この学校内にいなくなったためである。
イベント・テーブルには id=20210094のイベントが複数あるのだが、そのうちの2024年11月20日時点の最新イベントを絞り込むクエリが必要になる。
イベント・テーブルには id=20210094のイベントが複数あるのだが、そのうちの2024年11月20日時点の最新イベントを絞り込むクエリが必要になる。
dispEvent1.py
# テーブル定義(START) ========================================================
# イベント・テーブル
class tableEvent(Base):
__tablename__ = "tableEvent" # テーブル名
id = Column(String(20)) # 学籍番号
date = Column(String(10)) # 発生日
eventID = Column(String(6)) # イベントID
grade = Column(String(4)) # 学年
cclass = Column(String(4)) # クラス
attendNumber = Column(String(6)) # 出席番号
regist = Column(String(30)) # 登録日時
update = Column(String(30)) # 更新日時
delete = Column(Boolean) # 削除フラグ
# 複合プライマリーキーを定義
__table_args__ = (
PrimaryKeyConstraint("id", "date", "eventID"),
)
# 絞り込みテーブル
# 最新日時のイベントに絞り込む
selectEvent = [
tableEvent.id,
tableEvent.eventID,
func.max(tableEvent.date).label("latestDate"),
tableEvent.grade,
tableEvent.cclass,
tableEvent.attendNumber,
]
# 表示するカラムのリスト（表示順）
columnList = ["id", "eventID", "latestDate", "grade", "cclass", "attendNumber"]
# テーブル定義(END) ==========================================================
テーブル定義は、イベントテーブル tableEvent と、絞り込み後のテーブルのイメージをリスト selectEvent に定義する。
そして、一覧表示するカラムに func.max(tableEvent.date).label("latestDate") を加える。ここで、func.max は引数にあるカラムの最大値（つまり、最新日の値）を絞り込む関数を意味し、クエリを実行するときにこの関数を実行する。
そして、一覧表示するカラムに func.max(tableEvent.date).label("latestDate") を加える。ここで、func.max は引数にあるカラムの最大値（つまり、最新日の値）を絞り込む関数を意味し、クエリを実行するときにこの関数を実行する。
dispEvent1.py
def getDataFrame3(columnList, targetDate):
"""テーブル内容一覧を取得する
Args:
columnList(list): 表示するカラムのリスト（表示順）
targetDate(str): 絞り込みを行いたい年月日 "yyyy-mm-dd"形式
Returns:
dict: pandasのDataFrame
"""
# データベースセッションを作成
session = Session()
# クエリ
query = (
session.query(*selectEvent)
# targetDate以下のdateをもつレコードに絞り込む
.filter(tableEvent.date <= targetDate)
# idでグルーピングする
.group_by(tableEvent.id)
)
results = query.all()
# クエリ結果をpandasのDataFrameに変換する
df = pd.DataFrame(results, columns=columnList)
# データベースセッションを閉じる
session.close()
return df
表示データを取得するユーザー定義関数 getDataFrame3 と、先ほどのプログラムで使った getDataFrame2 の相違点を見ていこう。
まず、クエリ対象は session.query(*selectEvent) になった。これは一覧するデータが変わったから自明である。
次に、filterメソッド が加わった。これは、tableEvent.date の値が targetDate 以下である、つまり、この関数に渡した引数 targetDate 以下の値となる tableEvent.date を絞り込むという意味になる。
tableEvent.date が "2024-04-01" で、targetDate が "2024-11-20" の場合、いずれも文字列だが、これら2つの文字列の大小比較は左から1文字ずつ順に比較していくので、左から6番目の "2024-0" と "2024-1" を比較した時点で "2024-11-20" の方が大きいという結果になる。
3つ目は、group_by というメソッドが加わった。これは、テーブル tableEvent の各id毎にグルーピングするという意味である。
ここで注意してほしいのは、ある id に複数のイベントが記録されていた場合、filterメソッドでは targetDate 以下になる複数のイベントが絞り込まれるということだ。目標は「2024年11月20日のイベント、もしくはそれ以前の年月日で最新のイベント」という絞り込み条件なので、ここでは「2024年11月20日のイベント、もしくはそれ以前の年月日」までしか満たしていない。
じつは、 session.query(*selectEvent) を指定したとき、前述のリスト selectEvent に定義した関数 func.max(tableEvent.date).label("latestDate") が実行され、最新のイベントだけに絞り込む。
これらを図示すると下図のようになる。
まず、クエリ対象は session.query(*selectEvent) になった。これは一覧するデータが変わったから自明である。
次に、filterメソッド が加わった。これは、tableEvent.date の値が targetDate 以下である、つまり、この関数に渡した引数 targetDate 以下の値となる tableEvent.date を絞り込むという意味になる。
tableEvent.date が "2024-04-01" で、targetDate が "2024-11-20" の場合、いずれも文字列だが、これら2つの文字列の大小比較は左から1文字ずつ順に比較していくので、左から6番目の "2024-0" と "2024-1" を比較した時点で "2024-11-20" の方が大きいという結果になる。
3つ目は、group_by というメソッドが加わった。これは、テーブル tableEvent の各id毎にグルーピングするという意味である。
ここで注意してほしいのは、ある id に複数のイベントが記録されていた場合、filterメソッドでは targetDate 以下になる複数のイベントが絞り込まれるということだ。目標は「2024年11月20日のイベント、もしくはそれ以前の年月日で最新のイベント」という絞り込み条件なので、ここでは「2024年11月20日のイベント、もしくはそれ以前の年月日」までしか満たしていない。
じつは、 session.query(*selectEvent) を指定したとき、前述のリスト selectEvent に定義した関数 func.max(tableEvent.date).label("latestDate") が実行され、最新のイベントだけに絞り込む。
これらを図示すると下図のようになる。
データの一覧表示処理は、先ほどのプログラムと同じものを使う。
解説：児童テーブルとイベント・テーブルを結合し、児童一覧表を表示する
カレントディレクトリで "dispStudent3.py" を実行してみてほしい。上図のように、2024年7月11日時点の4年1組の児童一覧を表示する。
dispStudent3.py
# テーブル定義(START) ========================================================
# 児童テーブル
class tableStudent(Base):
__tablename__ = "tableStudent" # テーブル名
id = Column(String(20), primary_key = True) # 学籍番号
lastName = Column(String(50)) # 姓
firstName = Column(String(50)) # 名
lastNameYomi = Column(String(50)) # 姓よみ
firstNameYomi = Column(String(50)) # 名よみ
sex = Column(String(2)) # 性別コード
birth = Column(String(10)) # 生年月日
regist = Column(String(30)) # 登録日時
update = Column(String(30)) # 更新日時
delete = Column(Boolean) # 削除フラグ
# イベント・テーブル
class tableEvent(Base):
__tablename__ = "tableEvent" # テーブル名
id = Column(String(20)) # 学籍番号
date = Column(String(10)) # 発生日
eventID = Column(String(6)) # イベントID
grade = Column(String(4)) # 学年
cclass = Column(String(4)) # クラス
attendNumber = Column(String(6)) # 出席番号
regist = Column(String(30)) # 登録日時
update = Column(String(30)) # 更新日時
delete = Column(Boolean) # 削除フラグ
# 複合プライマリーキーを定義
__table_args__ = (
PrimaryKeyConstraint("id", "date", "eventID"),
)
# 絞り込みテーブル
# 最新日時のイベントに絞り込む
selectEvent = [
tableEvent.id,
tableEvent.eventID,
func.max(tableEvent.date).label("latestDate"),
tableEvent.grade,
tableEvent.cclass,
tableEvent.attendNumber,
]
# 結合テーブル：児童テーブル＋イベント・テーブル
joinStudentEvent = [
tableStudent.id,
tableEvent.grade,
tableEvent.cclass,
tableEvent.attendNumber,
tableStudent.lastName,
tableStudent.firstName,
]
# 表示するカラムの辞書（表示順）
columnList = ["id", "cclass","grade", "attendNumber", "lastName", "firstName"]
# テーブル定義(END) ==========================================================
テーブル定義は、児童テーブル tableStudent と イベントテーブル tableEvent に加え、絞り込み後のテーブルのイメージをリスト selectEvent にしたもの、および児童テーブルとイベント・テーブルを結合したイメージをリスト joinStudentEvent にしたものを用意する。
前3者は、これまで説明してきたものと同じである。リスト joinStudentEvent は、「児童テーブルと性別マスタを結合し、表示する」で作ったのと同様の結合テーブルだ。
前3者は、これまで説明してきたものと同じである。リスト joinStudentEvent は、「児童テーブルと性別マスタを結合し、表示する」で作ったのと同様の結合テーブルだ。
dispStudent3.py
def getDataFrame4(obj, columnList, targetDate, \
targetGrade, targetCclass):
"""テーブル内容一覧を取得する
Args:
columnList(list): 表示するカラムのリスト（表示順）
targetDate(str): 絞り込みを行いたい年月日 "yyyy-mm-dd"形式
targetGrade(str): 絞り込みを行いたい学年
targetCclass(str): 絞り込みを行いたいクラス
Returns:
object: pandasのDataFrame
"""
# データベースセッションを作成
session = Session()
# サブ・クエリ
subquery = (
session.query(*selectEvent)
# targetDate以下のdateをもつレコードに絞り込む
.filter(tableEvent.date <= targetDate)
# idでグルーピングする
.group_by(tableEvent.id)
# サブ・クエリにする
.subquery()
)
# メイン・クエリ
query = (
session.query(*joinStudentEvent)
# 児童テーブルとサブ・クエリ結果を結合する
.join(subquery, (tableEvent.id == subquery.c.id) & \
(tableEvent.date == subquery.c.latestDate))
# 児童テーブルとイベント・テーブルを結合する
.join(tableStudent, tableStudent.id == tableEvent.id)
# targetGradeとtargetCclassでレコードを絞り込む
.filter(tableEvent.grade == targetGrade, \
tableEvent.cclass == targetCclass)
# attendNumberの小さい順に並べ替える
.order_by(cast(tableEvent.attendNumber, Integer).asc())
)
results = query.all()
# クエリ結果をpandasのDataFrameに変換する
df = pd.DataFrame(results, columns=columnList)
# データベースセッションを閉じる
session.close()
return df
表示データを取得するユーザー定義関数 getDataFrame4 と、先ほどのプログラムで使った getDataFrame3 の相違点を見ていこう。
まず、getDataFrame3 で作った、targetDateのイベント、もしくはそれ以前の年月日を絞り込むクエリをサブ・クエリとして利用する。
サブ・クエリとは、下図のように、このクエリを使って絞り込んだデータをテーブルのようにして扱うことができる仕組みである。サブ・クエリを発行すると、selectEvent という仮想テーブルができると考えてほしい。
最終的に児童一覧を表示するのはメイン・クエリの方だ。
joinメソッドを使って、id（学籍番号）をキーにして、児童テーブルとサブ・クエリ結果を結合する。次に、id（学籍番号）をキーにして、児童テーブルとイベント・テーブルを結合する。
そして、filterメソッドを使って、学年、クラスを絞り込む。
最後に order_by という新しいメソッドが加わったが、これは、引数で指定するカラムを並べ替える作用を持つ。ここでは、attendNumber(出席番号)で並べ替えたいのだが、attendNumberは文字列で定義しているため、文字列の大小比較の結果、"1", "10", "11", "12"..."19", "2", "20"... という順序に並び替えられてしまう。これでは都合が悪いので、cast 関数を使って文字列をInteger（整数）に変換してから並び替えを行う。.asc はデータの小さい順（ascend；上る）への並べ替えを指定する。大きい順に並べ替えたいときには、.desc（descend；下る）を指定する。
これらを図示すると下図のようになる。
まず、getDataFrame3 で作った、targetDateのイベント、もしくはそれ以前の年月日を絞り込むクエリをサブ・クエリとして利用する。
サブ・クエリとは、下図のように、このクエリを使って絞り込んだデータをテーブルのようにして扱うことができる仕組みである。サブ・クエリを発行すると、selectEvent という仮想テーブルができると考えてほしい。
最終的に児童一覧を表示するのはメイン・クエリの方だ。
joinメソッドを使って、id（学籍番号）をキーにして、児童テーブルとサブ・クエリ結果を結合する。次に、id（学籍番号）をキーにして、児童テーブルとイベント・テーブルを結合する。
そして、filterメソッドを使って、学年、クラスを絞り込む。
最後に order_by という新しいメソッドが加わったが、これは、引数で指定するカラムを並べ替える作用を持つ。ここでは、attendNumber(出席番号)で並べ替えたいのだが、attendNumberは文字列で定義しているため、文字列の大小比較の結果、"1", "10", "11", "12"..."19", "2", "20"... という順序に並び替えられてしまう。これでは都合が悪いので、cast 関数を使って文字列をInteger（整数）に変換してから並び替えを行う。.asc はデータの小さい順（ascend；上る）への並べ替えを指定する。大きい順に並べ替えたいときには、.desc（descend；下る）を指定する。
これらを図示すると下図のようになる。
データの一覧表示処理は、先ほどのプログラムと同じものを使う。
解説：児童、イベント、得点テーブルを結合し、成績表を表示する
今回の最後のプログラムになる。カレントディレクトリで "dispTestScore1.py" を実行してみてほしい。上図のように、2024年7月11日時点の4年1組の成績表を表示する。
dispTestScore1.py
# テーブル定義(START) ========================================================
# 児童テーブル
class tableStudent(Base):
__tablename__ = "tableStudent" # テーブル名
id = Column(String(20), primary_key = True) # 学籍番号
lastName = Column(String(50)) # 姓
firstName = Column(String(50)) # 名
lastNameYomi = Column(String(50)) # 姓よみ
firstNameYomi = Column(String(50)) # 名よみ
sex = Column(String(2)) # 性別コード
birth = Column(String(10)) # 生年月日
regist = Column(String(30)) # 登録日時
update = Column(String(30)) # 更新日時
delete = Column(Boolean) # 削除フラグ
# イベント・テーブル
class tableEvent(Base):
__tablename__ = "tableEvent" # テーブル名
id = Column(String(20)) # 学籍番号
date = Column(String(10)) # 発生日
eventID = Column(String(6)) # イベントID
grade = Column(String(4)) # 学年
cclass = Column(String(4)) # クラス
attendNumber = Column(String(6)) # 出席番号
regist = Column(String(30)) # 登録日時
update = Column(String(30)) # 更新日時
delete = Column(Boolean) # 削除フラグ
# 複合プライマリーキーを定義
__table_args__ = (
PrimaryKeyConstraint("id", "date", "eventID"),
)
# 得点テーブル
class tableTestScore(Base):
__tablename__ = "tableTestScore" # テーブル名
id = Column(String(20)) # 学籍番号
testID = Column(String(50)) # テストID
subjectID = Column(String(6)) # 科目ID
score = Column(Integer) # 得点
regist = Column(String(30)) # 登録日時
update = Column(String(30)) # 更新日時
delete = Column(Boolean) # 削除フラグ
# 複合プライマリーキーを定義
__table_args__ = (
PrimaryKeyConstraint("id", "testID", "subjectID"),
)
# 絞り込みテーブル
# 最新日時のイベントに絞り込む
selectEvent = [
tableEvent.id,
tableEvent.eventID,
func.max(tableEvent.date).label("latestDate"),
tableEvent.grade,
tableEvent.cclass,
tableEvent.attendNumber,
]
# 結合テーブル：児童テーブル＋イベント・テーブル＋得点テーブル
# 国語
japanese = func.max(case(
(tableTestScore.subjectID == "1", tableTestScore.score),
else_=None
)).label("japanese")
# 算数
arithmetic = func.max(case(
(tableTestScore.subjectID == "2", tableTestScore.score),
else_=None
)).label("arithmetic")
# 理科
science = func.max(case(
(tableTestScore.subjectID == "3", tableTestScore.score),
else_=None
)).label("science")
# 社会
society = func.max(case(
(tableTestScore.subjectID == "4", tableTestScore.score),
else_=None
)).label("society")
joinStudentScore = [
tableStudent.id,
tableEvent.grade,
tableEvent.cclass,
tableEvent.attendNumber,
tableStudent.lastName,
tableStudent.firstName,
japanese,
arithmetic,
science,
society,
]
# 表示するカラムのリスト（表示順）
columnList = ["id","grade", "cclass", "attendNumber", "lastName", \
"firstName", "japanese", "arithmetic", "science", "society"]
# テーブル定義(END) ==========================================================
テーブル定義は、児童テーブル tableStudent、イベントテーブル tableEvent、得点テーブル tableTestScore に加え、絞り込み後のテーブルのイメージをリスト selectEvent にしたもの、および児童・イベント・得点の3つのテーブルを結合したイメージをリスト joinStudentScore にしたものを用意する。
前3者は、これまで説明してきたものと同じである。リスト joinStudentScore は、func.mnax と case を使い、subjectID（科目コード）が1～4のものを純に、カラムとして並べることを意味している。
前3者は、これまで説明してきたものと同じである。リスト joinStudentScore は、func.mnax と case を使い、subjectID（科目コード）が1～4のものを純に、カラムとして並べることを意味している。
dispTestScore1.py
def getDataFrame5(columnList, targetDate, targetGrade, \
targetCclass, targetTestID):
"""表示データを取得する
Args:
columnList(list): 表示するカラムのリスト（表示順）
targetDate(str): 絞り込みを行いたい年月日 "yyyy-mm-dd"形式
targetGrade(str): 絞り込みを行いたい学年
targetCclass(str): 絞り込みを行いたいクラス
targetTestID(str): 絞り込みたいテストID
Returns:
dict: pandasのDataFrame
"""
# データベースセッションを作成
session = Session()
# サブ・クエリ
subquery = (
session.query(*selectEvent)
# targetDate以下のdateをもつレコードに絞り込む
.filter(tableEvent.date <= targetDate)
# idでグルーピングする
.group_by(tableEvent.id)
# サブ・クエリにする
.subquery()
)
# メイン・クエリ
query = (
session.query(*joinStudentScore)
# 児童テーブルとサブ・クエリ結果を結合する
.join(subquery, (tableEvent.id == subquery.c.id) & \
(tableEvent.date == subquery.c.latestDate))
# 児童テーブルとイベント・テーブルを結合する
.join(tableStudent, tableStudent.id == tableEvent.id)
# 児童テーブルと得点テーブルを結合する
.outerjoin(tableTestScore, tableStudent.id == tableTestScore.id)
.group_by(tableStudent.id)
# targetGrade, targetCclass, targetTestIDでレコードを絞り込む
.filter(tableEvent.grade == targetGrade, \
tableEvent.cclass == targetCclass, \
tableTestScore.testID == targetTestID)
# attendNumberの小さい順に並べ替える
.order_by(func.cast(tableEvent.attendNumber, Integer).asc())
)
results = query.all()
# クエリ結果をpandasのDataFrameに変換する
df = pd.DataFrame(results, columns=columnList)
# データベースセッションを閉じる
session.close()
return df
表示データを取得するユーザー定義関数 getDataFrame5 と、先ほどのプログラムで使った getDataFrame4 の相違点を見ていこう。
サブ・クエリは、getDataFrame4 で作ったものと同じだ。
メイン・クエリは、id（学籍番号）をキーにして、児童テーブルとサブ・クエリ結果を結合する。そして、id（学籍番号）をキーにして、児童テーブルとイベント・テーブルを結合する。ここまでは getDataFrame4 と同じだ。
次に、outerjoinメソッド を使って児童テーブルと得点テーブルを結合する。outerjoinメソッド は、すべての行を結果に含め、結合条件 tableStudent.id == tableTestScore.id に一致しない右側のテーブルの行にはNULLが入る。
ここまでの状態を一部抜粋すると、下表のようになっている。
サブ・クエリは、getDataFrame4 で作ったものと同じだ。
メイン・クエリは、id（学籍番号）をキーにして、児童テーブルとサブ・クエリ結果を結合する。そして、id（学籍番号）をキーにして、児童テーブルとイベント・テーブルを結合する。ここまでは getDataFrame4 と同じだ。
次に、outerjoinメソッド を使って児童テーブルと得点テーブルを結合する。outerjoinメソッド は、すべての行を結果に含め、結合条件 tableStudent.id == tableTestScore.id に一致しない右側のテーブルの行にはNULLが入る。
ここまでの状態を一部抜粋すると、下表のようになっている。
| id | grade | cclass | attendNumber | lastName | firstName | japanese | arithmetic | science | society |
|---|---|---|---|---|---|---|---|---|---|
| 20210070 | 4 | 1 | 1 | 相沢 | 優利 | 83 | NULL | NULL | NULL |
| 20210070 | 4 | 1 | 1 | 相沢 | 優利 | NULL | 63 | NULL | NULL |
| 20210070 | 4 | 1 | 1 | 相沢 | 優利 | NULL | NULL | 31 | NULL |
| 20210070 | 4 | 1 | 1 | 相沢 | 優利 | NULL | NULL | NULL | 81 |
| 20210090 | 4 | 1 | 2 | 相原 | 莉緒 | 77 | NULL | NULL | NULL |
| 20210090 | 4 | 1 | 2 | 相原 | 莉緒 | NULL | 68 | NULL | NULL |
| 20210090 | 4 | 1 | 2 | 相原 | 莉緒 | NULL | NULL | 77 | NULL |
| 20210090 | 4 | 1 | 2 | 相原 | 莉緒 | NULL | NULL | NULL | 79 |
これでは一覧表として読みにくいので、group_byメソッド を使ってid（学籍番号）ごとにグルーピングする。
filterメソッドを使って、学年、クラス、テストIDによって絞り込む。
最後に order_byメソッド で出席番号順に並び替える。
これらを図示すると下図のようになる。
filterメソッドを使って、学年、クラス、テストIDによって絞り込む。
最後に order_byメソッド で出席番号順に並び替える。
これらを図示すると下図のようになる。
データの一覧表示処理は、先ほどのプログラムと同じものを使う。
なお、社会科のみ小数表示になっているのは、社会科の得点がない（テストを休んだ）児童がいるためだ。NULLデータがあると、pandasの方で自動的に Float（浮動小数点数）に変換し、nan と表示する。
なお、社会科のみ小数表示になっているのは、社会科の得点がない（テストを休んだ）児童がいるためだ。NULLデータがあると、pandasの方で自動的に Float（浮動小数点数）に変換し、nan と表示する。
練習問題
次回予告
次回は、データベースから成績データの集計を行うプログラムを作り、データの2次利用について学ぶ。
コラム：SQLとODRB
SQLAlchemy は RDBMS に SQL を発行していると書いたが、実際に、どのような SQL を発行しているのだろうか。
じつは簡単に確認することができる。下記のように RDBMSエンジンを作成するときに、引数 "echo=True" を追加すると、クエリ発行の際に SQL がコンソールに表示される。
じつは簡単に確認することができる。下記のように RDBMSエンジンを作成するときに、引数 "echo=True" を追加すると、クエリ発行の際に SQL がコンソールに表示される。
engine = create_engine(databaseURI, echo=True)たとえば "dispStudent1.py" では下記のような SQL が発行されていた。読みやすいように、改行してインデントを加えている。
SELECTいささか冗長ではあるものの、正確な SQL である。
"tableStudent".id AS "tableStudent_id",
"tableStudent"."lastName" AS "tableStudent_lastName",
"tableStudent"."firstName" AS "tableStudent_firstName",
"tableStudent"."lastNameYomi" AS "tableStudent_lastNameYomi",
"tableStudent"."firstNameYomi" AS "tableStudent_firstNameYomi",
"tableStudent".sex AS "tableStudent_sex",
"tableStudent".birth AS "tableStudent_birth",
"tableStudent".regist AS "tableStudent_regist",
"tableStudent"."update" AS "tableStudent_update",
"tableStudent"."delete" AS "tableStudent_delete"
FROM "tableStudent"
それでは、レコードをカラムに押し込めるという無理をした "dispTestScore1.py" ではどうだろうか。
SELECTたいへん長い。SQL の経験者であれば最適化することができるだろうが、RDBMS固有の命令も使っているし、初学者は本編のクエリでさえ理解するのにも時間がかかるだろう。ここで、この長大な SQL を習得しろ、と言えるものではないのだが、かといって、理解・分解・再構築の習慣を疎かにするわけにはいかないので、ORDB を紹介することにした。
"tableStudent".id AS "tableStudent_id",
"tableEvent".grade AS "tableEvent_grade",
"tableEvent".cclass AS "tableEvent_cclass",
"tableEvent"."attendNumber" AS "tableEvent_attendNumber",
"tableStudent"."lastName" AS "tableStudent_lastName",
"tableStudent"."firstName" AS "tableStudent_firstName",
max(CASE WHEN ("tableTestScore"."subjectID" = ?)
THEN "tableTestScore".score END) AS japanese,
max(CASE WHEN ("tableTestScore"."subjectID" = ?)
THEN "tableTestScore".score END) AS arithmetic,
max(CASE WHEN ("tableTestScore"."subjectID" = ?)
THEN "tableTestScore".score END) AS science,
max(CASE WHEN ("tableTestScore"."subjectID" = ?)
THEN "tableTestScore".score END) AS society
FROM "tableEvent"
JOIN (
SELECT
"tableEvent".id AS id,
"tableEvent"."eventID" AS "eventID",
max("tableEvent".date) AS "latestDate",
"tableEvent".grade AS grade,
"tableEvent".cclass AS cclass,
"tableEvent"."attendNumber" AS "attendNumber"
FROM "tableEvent"
WHERE "tableEvent".date <= ?
GROUP BY "tableEvent".id
) AS anon_1
ON "tableEvent".id = anon_1.id
AND "tableEvent".date = anon_1."latestDate"
JOIN "tableStudent"ON "tableStudent".id = "tableEvent".id
LEFT OUTER JOIN "tableTestScore" ON "tableStudent".id = "tableTestScore".id
WHERE "tableEvent".grade = ?
AND "tableEvent".cclass = ?
AND "tableTestScore"."testID" = ?
GROUP BY "tableStudent".id
ORDER BY CAST(
"tableEvent"."attendNumber" AS INTEGER
) ASC
もし、この連載を通じて SQL に興味を持った方がいたら、ぜひトライしてほしい。言語はPHPになるが、当サイトの「PHPとデータベース」で SQLite で標準的な SQL を使ったプログラムを紹介している。
また、次の参考書が役に立つだろう。
|
ビジネスパーソンのためのSQL入門 | ||
| 著者 | 高橋 光 | ||
| 出版社 | 技術評論社 | ||
| サイズ | 単行本 |
|
|
| 発売日 | 2023年03月16日頃 | ||
| 価格 | 2,200円（税込） | ||
| ISBN | 9784297134433 | ||
| 変化の激しい今の時代、これまでの経験則だけでビジネスにおける重要な決定を下すことは難しくなってきました。そんなときに使われるのがデータ分析です。仮説を立て、データを活用して検証することで、カンや経験だけではない根拠にもとづいて意思決定をすることができます。 スマートフォン、IoTなどの普及により、企業はあらゆるデータを取得することができるようになりました。そんな中で、データの取得はエンジニアだけの仕事ではなくなってきています。ほしいデータをそのつどエンジニアに依頼していると時間がかかります。ビジネスパーソンがデータ分析力を身につけ、自らデータの収集、検証を行うことで、素早い意思決定が可能になります。こういった背景から、データ分析力で汎用的に使えるSQLを学ぶ必要性も高まっています。 本書では、データ分析のためのSQLという観点から、実務として活用できる必要最低限の知識をまとめます。到達目標は以下のとおりです。 ・ビジネスパーソンとしてデータ分析に必要なSQLの構文について理解できる ・他の人が書いたSQL、もしくは自分が過去に書いたSQLを参考にして、ほしいデータを抽出することができる あえて難しい内容まで踏み込まないことで、SQL初心者でも挫折することなく、最後まで読み進めることができる本を目指します。 以下は、本書で扱う内容です。 ・エンジニアとしてではなくビジネスパーソンとしてデータ分析に必要なSQLの知識 ・SQLの読み解きができる能力を身につける 本書はただ読み進めるだけでなく、自分のPCにSQLを実行できる環境を構築し、ハンズオンで学びます。DBは、比較的導入が簡単なSQLite（DB Browser for SQLite）を使用します。自身のPCで実際にSQLを実行し、どんな結果が返ってくるのか確認しながら進めることで、ただの知識としてではなく、実務として使えるようなスキルが身につきます。 | |||
|
標準SQL＋データベース入門 --RDBとDB設計、基本の力 | ||
| 著者 | 西村 めぐみ | ||
| 出版社 | 技術評論社 | ||
| サイズ | 単行本 |
|
|
| 発売日 | 2024年09月24日頃 | ||
| 価格 | 2,860円（税込） | ||
| ISBN | 9784297144692 | ||
| 「標準SQL」＆「データ設計」を土台に、 SQL＆データベースの基本を学べる入門書。 「SQLでどんなことができるのか」 「どんなときに便利なのか」 「なぜそんなしくみになっているのか」 一つ一つ、ステップアップしながら解説します。 本書の特徴は「標準SQL」準拠である点と 文法の背景にある「データ設計」を丁寧に扱っている点です。 SQL学習時の頻出ケースである、 思ったように操作できない…… この書き方のどこが便利なのか実感が湧（わ）かない…… そんなとき、標準SQLとデータ設計について 少しでも知ってることが大きな力になります。 動作確認環境は幅広い読者の方々を想定し、 学習に取り組みやすいようMySQL, PostgreSQL, MariaDB, SQL Serverと多様な製品/無償版に対応。 サポートサイトではオンラインのSQL実行環境もフォローし、 サンプルを活用して試しながら独学で学びやすいを用意しました。 広くSQLとデータベースを学んでいきたい方々に向けて、 長く役立つ技術知識を凝縮してお届けします。 ＜特別収録「標準SQL＋データ設計 Quickリファレンス」＞ ※本書は『基本がわかるSQL入門 データベース＆設計の基礎から楽しく学ぶ』（西村めぐみ著、技術評論社、2020）を元に、対応環境の拡充、大幅な加筆/ 修正、目次構成の変更、最新情報へのアップデートを行ったものです。 | |||
|
SQLではじめるデータ分析 | ||
| 著者 | Cathy Tanimura/大橋 真也/嶋田 健志/木下 哲也 | ||
| 出版社 | オライリー・ジャパン | ||
| サイズ | 単行本 |
|
|
| 発売日 | 2023年01月26日 | ||
| 価格 | 3,740円（税込） | ||
| ISBN | 9784814400201 | ||
| データ分析関連のSQLのテクニックを学び、SQLの可能性を広げる！ クラウドの利用と共に、SQLの利用はますます広がり、データサイエンティストでもDBを扱う機会も増えています。本書は、データ分析関連のSQLのテクニックを学び、SQLを最大限活用するためのものです。日付と時刻の比較や処理は、SQLは他の言語の追随を許さないものがあり、時系列解析などはその威力を発揮できる最たるもので、本書では、具体例を時系列解析から始め、コホート分析、経験的分析など、高度な分析まで学ぶことができる構成になっています。 | |||
参考サイト
- SQLite
- SQLAlchemy
- PHPとデータベース：ぱふぅ家のホームページ
（この項おわり）
