Python+Qtアプリ開発日記#14
前回のおさらい
前回はPython標準のSQLiteを使ってデータベーステーブルを作成するところまででした。今回は、作成したテーブルに新規にデータを追加したり、更新したり、削除したり、検索したりといった基本となるモジュールを追加します。
新規追加（INSERT）
データベースに新しいデータを追加する場合は、SQLでは"INSERT"文を使います。先にモジュール全体を示します。
今回、作成したモジュールはデータベーステーブルのカラム名を持ったリストとそのカラムと対となる実際に追加したい値を格納したリストを取ります。モジュールが期待する渡される引数（columsとvalues）を図にすると次のとおりになります。
モジュールの前半は、渡された引数が期待する型であるか？双方のリスト内の数が一致しているか？（対になっているはずなので格納されている個数は同じになる）リストに入っているカラム名はデータベーステーブルのカラム名になっているか？格納する値（values）はデータベーステーブルのカラムの型に一致しているか？を確認し、期待するものが来ていない場合は、TyoeまたはValueの例外を発行（raise）しています。
SQL（クエリー）文の組み立て
SQLiteでのデータ追加は、INSERT文で追加します。
基本の書式は次の通りです。
INSERT INTO テーブル名(カラム1, カラム2, ...) VALUES(値1, 値2, ...);例えばカラム"title"にデータを格納する場合の簡易な書き方は次のような書き方になります。
sql = f"INSERT INTO {self.table_name}(title) VALUES( "タイトル");"
データ部分はSQL（クエリー）実行時に指定する場合は、
"?"にします。
sql = f"INSERT INTO {self.table_name}(title) VALUES( ?);"
今回のモジュールでは、カラムとそれと対になるデータ（Value）ですので
それを利用して、SQL（クエリー）文を組み立てます。
Pythonにはjoinという便利なモジュールが標準であるのでこれを使っています。
はじめのjoinは、"VALUES"の（）内にValuesリスト内の個数分"?"とjoinで","を付けて文字列を組みててています。
Valuesリストに3つある場合は"?, ?, ?"になります。
次の文でSQL（クエリー）文を完全に組み立ててます。
ここでもカラム名を","区切りで並べています。
これで"INSERT"用のSQL（クエリー）文が出来上がったので、あとは実行するだけです。
SQL（クエリー）文の実行はテーブルを作成したのと同じ手順です。
isolation_level=Noneというのは、SQLiteデータベース接続に設定されるオプションで、トランザクションの管理方法を変更します。通常、SQLite
はデータベース操作ごとに自動的にトランザクションを開始し、コミットまたはロールバックします。しかし、isolation_level=Noneを設定すると、トランザクションは明示的に実施することを示しています。
トランザクションを自動で実施しないのは、複数の操作を1つのトランザクションとしてまとめて処理できるようになり、パフォーマンスの向上などが見込めるためです。
トランザクションとは何か？
トランザクションは、データベースに対して行う一連の操作を一つのまとまりとして扱うことです。
スーパーでの買い物で例えます
・カートに商品を入れる
スーパーに入って商品を選び、カートに入れます。これはデータベースの操作の一部を行っている状態に似ています。例えば、商品を追加する、更新するなどの操作が該当します。
・レジで支払いをする
レジに行って、全ての商品の合計金額を支払います。これがトランザクションの「コミット」に当たります。つまり、全ての操作を確定してデータベースに反映させることです。
・問題が発生した場合
例えば、お金を忘れた場合や商品に問題があった場合、レジの操作をキャンセルします。これが「ロールバック」に当たります。つまり、全ての操作を取り消して、元の状態に戻します。
トランザクションのポイント
トランザクションは全ての操作が成功するか、全てが取り消されるかのどちらかです。途中で問題が発生した場合、全ての操作が取り消されます。
・一貫性
トランザクションによってデータベースの状態が一貫して保たれます。途中でエラーが発生しても、データベースが不整合な状態になることはありません。
・隔離性
同時に実行されるトランザクションは互いに干渉しないようにします。例えば、他の人の操作が自分の操作に影響を与えないようにします。
・耐久性
トランザクションが一度コミットされると、その変更は永久に保存されます。システム障害が発生しても、コミットされた変更は失われません。
更新（UPDATE）
データベーステーブルのデータを更新するには、UPDATE文を使います。
おおよそは、INSERTと大差はありません。
違うのは、更新するデータを指定することです。
モジュールは、そのため"id"指定するようになっています。
今回のデータベーステーブルでは、"id"というカラムが主キーでデータベース上では重複することはありませんので、"id"を指定することで更新するデータを特定できます。
UPDATEのSQL（クエリー）文は、
UPDATE テーブル名
SET 列名 = 新しい値, 列名2 = 新しい値2, ...
WHERE 条件;
ここでの条件は"id"です。
削除（DELETE）
データベーステーブルのデータを削除するには、DELETE文を使います。
このモジュールでは、他と違うことをいくつかしています。
データの"保護"を確認
削除前に対象のデータが"保護"になっているかを確認しています。
データベーステーブルの定義で"保護"を示すカラムを定義していますので、そのカラムの値を確認しています。
確認した結果、削除を指定されっても削除しないでモジュールの処理を終わらせます。
削除のSQL（クエリー）文は、
DELETE FROM テーブル名
WHERE 条件;
ここでの条件は"id"です。
id値のリセット
次のSQL文2つは、何をしているかを説明するとデータの削除の結果、データが無くなった場合に"id"の値をリセットする処理です。
今回のデータベースで主キーとしてる"id"は、データの追加のたびに新しい値になります。データが削除された場合に"id"値も削除されますが、削除された"id"値が次にデータを追加した際にリサイクルされるわけではありません。欠番となります。そのためデータがすべてなくなった状態でそのままデータを追加すると、1まじまりではなく、削除前での新しい値が採用されます。できればデータがなくなったときは、リセットしたいので今回は、データを削除してなくなった場合はリセットする処理を入れています。
SQLiteでは、自動採番する場合の管理データは、"sqlite_sequence"というテーブルで管理されていますので、そこにある"id"というキーを削除しています。"sqlite_sequence"というテーブルは、SQLiteでデータベースを作成すると自動で作成される管理テーブルです。
データの取得（SELECT）
指定したデータを取得するモジュールです。
SELECTのSQL（クエリー）文は、
SELECT 列名1, 列名2, ...
FROM テーブル名
WHERE 条件
ORDER BY 列名 [ASC|DESC]
LIMIT 数量;
このモジュールでは、LIMITは省略しています。
このモジュールでは、SELECTでデータを取得する前に、PRAGMA TABLE_INFOでテーブルの情報を取得しています。
PRAGMA TABLE_INFO
PRAGMA TABLE_INFOは、SQLiteの特定のテーブルについての情報を取得するためのコマンドです。このコマンドを使用すると、テーブルの列（カラム）に関する詳細な情報を得ることができます。特に、テーブルの構造を確認したい場合に便利です。
取得したデータPython辞書型で返す
SQL（クエリー）文を実行し、"fetchone"で取得。
その結果がNoneならデータなしで終了。
データがある場合は、PRAGMA TABLE_INFOで取得したカラム名をキーにしたPython辞書型に取得した値をセットして呼び出し元に返す処理になっています。
複数データの取得（SELECT）
全データを取得するモジュールです。
基本は先に示した1つのデータを取得するのと同じです。
違うのは、複数（このモジュールでは全て）のデータを取得するのに
"fetchall"を使っています。
取得したデータPython辞書型のリストで返す
SQL（クエリー）文を実行し、"fetchall"で全取得。
その結果がNoneならデータなしで終了。
データがある場合は、PRAGMA TABLE_INFOで取得したカラム名をキーにしたPython辞書型に取得した値をセット。
複数のデータがある場合に備えて、Python辞書型を格納するリストに積んで行く処理になっています。
次回は、作成したデータベースにデータを追加する処理を追加していきます。
いいなと思ったら応援しよう！
