Python+Qtアプリ開発日記#13
前回からの続き
前回は、アプリケーションで扱うデータを検討してデータベース化するためにデータベーステーブルの検討をしました。
今回は、検討した結果から具体的にPythonでデータベースを扱うコードを作っていきます。
データベース（ファイル名）
Python標準で利用できるデータベースはSQLiteです。このデータベースは1つのデータベースファイルの中にデータベーステーブルやデータそのものが格納されていきますので、まずはデータベースのファイル名を決める必要があります。
データベースファイル名はアプリケーションの名前でもいいですが、今回は違う名前にしようと思います。違う名前にするのは場合によって、アプリケーション側でデータベース自体を分けて扱いたくなったことを考慮すると違う名前にしておいた方がデータベースのファイル名に縛られなくなるので、より柔軟に運用ができるからです。
今回は、"metadata.db"とします。
SQLiteのデータベースファイル名は、一般的に、".sqlite"、".db"、".sqlite3"などです。個人的にあまり長いファイル拡張子は好きではないので".db"にします。ファイル名を"metadata"としたのは、言葉の定義としては、データについてのデータを指す言葉です。簡単に言うと、メタデータは他のデータに関する情報を提供するものです。※わかったような？わからんような？説明ですね。要するに、ある管理したいデータについて、それは何なのか？どういったものか？誰が作ったものか？などの付帯情報ということになります。
今作っているアプリケーションは、所蔵しているデータ（コンテンツ）を管理するものですので、データ（コンテンツ）に付帯する情報ということで採用しました。
データベースを扱うパッケージの置き場を作る
guiではないので、データベースを扱うパッケージとして独立させますので
pkg以下に"metadata"というディレクトリを作り、"__init__.py"（中身はまだない）を作ります。
以降は、この中にデータベースを操作するコードを作っていきます。
クラスで実装
データベースを扱うのにモジュールだけでも可能ですが、クラス化して利用するほうが後で処理を変える必要が出てきたときに対応がしやすいのでクラス化します。
クラス設計
クラス化するにあたってどんな機能やデータを待たせればいいかを検討します。
・データ
データベースにアクセスする情報
アクセスするデータベースのどこから取得するのかまたはどこに格納するのか
・データベースの操作
データベースにデータを追加する処理（INSERT）
データベースからデータを読み出す処理（SELECT）
データベースのデータを更新する処理（UPDATE）
データベースからデータを削除する処理（DELETE）
などです。
データベースにアクセスする情報
SQLiteでは、データベースの実体は1つのファイルですのでアクセスするに必要な情報としては、データベースファイルがどこに格納されているかが必要になります。どこに格納されているかは、当該のファイルが格納されているファイルパス情報になります。また、ファイル名が必要ですので合わせてアクセスするデータベースファイルへのパス情報が必要になりますのでデータベースファイルまでのパス情報をもたせます。
この情報を"db_file_path"とします。
どこから取得するのかまたはどこに格納するのか
これはデータベースでのデータの在処です。データベースは少なくともテーブルを持ちますので、この場合はテーブルを示すテーブル名を知っておく必要があります。よって、テーブル名の保持が必要です。
この情報を”table_name”とします。
どの段階でこれらの情報を決定するか
データベースファイル名とテーブル名を保持するのにクラスのどこで決定するかということです。クラスを生成（インスタンス化）したあとに設定するのもいいですが、クラス生成した時に一緒に決定するほうが間違いがないので生成時に決定するようにします。
よって、"__init__"は、データベースファイル名を指定できるようにします。
テーブル名は、今回の場合1つなのでわざわざ指定されなくても確定できるので"__init__"内で設定します。
class MetaDataDB:
def __init__(self, db_file_path: str | None = None):
# DBファイル
self.db_file_path = db_file_path
# テーブル名
self.table_name = "MetaDataTbl"テーブルの定義
データベースからデータを出し入れする際に、データベース上のテーブルに相当するものを内部で持っておくのが理にかなうので定義します。
複数のテーブルを扱うのであれば、テーブルの定義をクラス化するのが扱いやすいですが、テーブルは1つなのでクラスではなくPythonの辞書型で今回は定義します。
リストやタプルでも定義自体はできますが、辞書型で扱うようにすると後で楽になるので辞書型とします。
辞書型でのテーブル定義
前回、作成したものをそのままPythonの辞書型で定義した結果です。
キー：データベース上のテーブルにあるカラム名に相当します。
値：値はデータベース上のテーブルにあるカラムの型情報をそのまま値としています。※このやり方がPythonデータベースエンジニアではないので、王道なのか？と問われると回答できません。こうしておくと後々作るモジュールで使えるようになるのでこういった定義を作りました。
# テーブルカラム定義
self.table_columns = {
"id": "INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT",
"protection": "INTEGER",
"deletion_mark": "INTEGER",
#
"title": "TEXT",
#
"author": "TEXT",
"series": "TEXT",
"series_index": "TEXT",
"category": "TEXT",
#
"brand": "TEXT",
"publisher": "TEXT",
"company": "TEXT",
"club": "TEXT",
#
"description": "TEXT",
"release_date": "TEXT",
"price": "TEXT",
"product_number": "TEXT",
"jancode": "TEXT",
#
"media_type": "TEXT",
#
"rating": "TEXT",
#
"still_width": "TEXT",
"still_height": "TEXT",
#
"video_codec_name": "TEXT",
"video_width": "TEXT",
"video_height": "TEXT",
#
"audio_codec_name": "TEXT",
"audio_sample_rate": "TEXT",
#
"duration": "TEXT",
#
"save_dir_path": "TEXT",
"file_name": "TEXT",
"file_hash_algorithm": "TEXT",
"file_hash_data": "TEXT",
#
"updated_at": (
"TEXT NOT NULL " "DEFAULT (DATETIME('now', 'localtime'))"
),
"created_at": (
"TEXT NOT NULL " "DEFAULT (DATETIME('now', 'localtime'))"
),
}前回、説明をしていませんがここで少しこの定義の意味を説明しておきます。
以下は、idというキーを定義し、値はSQL文でそのまま使えるように
この"id"は、"INTEGER"（整数型）で値（データベースとしての値）として"NOT NULL"つまり、NULL値は許容しない。また、" PRIMARY KEY"（このテーブルでの主キー）かつ"AUTOINCREMENT"するです。
id": "INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT",
”AUTOINCREMENT”は、データをデータベースにテーブルに格納するときに設定しなければ、データベース側で自動で値を加算（INCREMENT）した値にしてくれるというものです。そうしておくことでデータベースを使う側でわざわざ新しい"id"の値を考慮する必要がなくなります。
以下の2つは、"updated_at"（更新日）と"created_at"（作成日）で"TEXT"型（SQLiteは年月日時分秒はテキストで扱います）で"NOT NULL"（NULL値は許容しない）としています。その次に書いてある内容は、DEFAULT（デフォルト値）を値としてDATETIME('now', 'localtime')とするという定義です。
"updated_at": ( "TEXT NOT NULL " "DEFAULT (DATETIME('now', 'localtime'))" ), "created_at": ( "TEXT NOT NULL " "DEFAULT (DATETIME('now', 'localtime'))" ),
DATETIME('now', 'localtime')は、SQLiteの関数を使って現在のローカルタイムゾーンの日時を取得する方法です。つまり、"updated_at"（更新日）と"created_at"（作成日）のデフォルト値は、データベース処理を行っているPC側のローカルタイム（日本なら日本時間）で今"now"の日時とするということです。こうしておけば、このカラムはデータベースを利用する側（アプリ）が値を準備する必要がなくなります。
テーブルが存在しない場合は作成する
この定義したテーブル定義を使って、データベース上にテーブルが存在しない場合に作成する処理を作ります。
具体的なコードは次の通りで、データベースファイルが存在していて、そのデータベースにテーブルが無い場合に作成するといった処理です。
# テーブルが存在しない場合は作成
if not os.path.exists(self.db_file_path) or not self._table_exists():
self._create_table()データベースにテーブルが存在するか？
全文のコードを示すと次の通りです。
やっていることは、SQL文として
"SELECT name FROM sqlite_master WHERE type='table' AND name=?;"
※?は実際にSQL文（クエリー）実行時に置き換わるところを表現しています。
でデータベースに問い合わせをして、結果を取得し結果をNoneまたはそれ以外で返却しています。
def _table_exists(self) -> bool:
"""
テーブルがデータベースに存在するかを確認するメソッド。
Returns:
bool: テーブルが存在する場合はTrue、存在しない場合はFalse。
"""
sql = "SELECT name FROM sqlite_master WHERE type='table' AND name=?;"
with sqlite3.connect(self.db_file_path) as conn:
cursor = conn.cursor()
cursor.execute(sql, (self.table_name,))
result = cursor.fetchone()
return result is not Noneもう少し、説明します。
クエリーを文字列変数に代入。
sql = "SELECT name FROM sqlite_master WHERE type='table' AND name=?;"
with文を使って、データベース（ファイルパスで指定したデータベースファイル）をsqlite3.connectで接続し、接続結果をconnとして受けています。
with sqlite3.connect(self.db_file_path) as conn:
このconnから実際にデータベースに対してクエリーを実行するカーサー（cursor ）を取得。
cursor = conn.cursor()
そのカーサーを使って、カーサーのexecuteメソッドにクエリーとテーブル名（self.table_name）を渡してクエリーを実行しています。
cursor.execute(sql, (self.table_name,))
この時、テーブル名はタプルで渡す必要があり、今回は1つなので","後は何もありません。
cursor.executeの実行時にクエリーの" name=?;"が、self.table_nameで示されるテーブル名に置き換えてクエリーを実行してくれます。
クエリーの実行結果はカーサーからフェッチして取り出します。
今回は複数の結果ではなく1つの結果でよいので、”fetchone”を使います。
result = cursor.fetchone()
あとは得られた結果を呼び出し側にreturn文で返却しています。
return result is not None
実際にテーブルを作る
self._create_table()の全文は以下の通りです。
やっていることは、テーブル定義をPython内包表記を組み合わせて
テーブル作成時のクエリーの一部を作っています。
※カラムが多いので、コードでカラム名や型を示すクエーリーの一部を作っています。こうしないとテーブルのカラムの名前とかを変えたくなったときやクエリーが正しく動かないといったことに直面するので、カラムが多い場合はコードで作るのが無難です。これを可能とするのに、テーブル定義に辞書型を使っています。
def _create_table(self) -> None:
"""
テーブルを生成するメソッド。
"""
columns = ", ".join(
[
f"{col} {attributes}"
for col, attributes in self.table_columns.items()
]
)
sql = (
f"CREATE TABLE IF NOT EXISTS {self.table_name} ({columns});\n"
+ "CREATE TRIGGER IF NOT EXISTS trigger_updated_at "
+ f"AFTER UPDATE ON {self.table_name} "
+ "BEGIN "
+ f"UPDATE {self.table_name} SET updated_at = "
+ "DATETIME('now', 'localtime') WHERE rowid = NEW.rowid; "
+ "END;"
)
with sqlite3.connect(self.db_file_path, isolation_level=None) as conn:
cursor = conn.cursor()
cursor.executescript(sql)クエリーは、何をしているかというとテーブルが存在するかを確認して、
テーブルを生成し、さらにトリガーと呼ばれるものを生成しています。
そのトリガーは、テーブルが更新されたあと更新日を更新してとしています。こうしてトリガーと呼ばれるものを定義することで、特定のカラムの値をデータベース側で自動更新させることができます。
ここではトリガーは、説明しませんが、Pythonで言うところのモジュールと同じでデータベース側で何かを実行したい場合のものと考えてください。
※興味があればwebなどでトリガーを調べてください。使いこなせれば利用側でガリガリとコードを書く必要がなくなるものもあります。
次回は、他のデータベース操作を行うモジュールです。
いいなと思ったら応援しよう！
