【Python】sqlite3の使い方を超簡潔にまとめた
2023 年 9 月 28 日 by tomokiy概要
SQLiteそのままDBファイルをコピーして使えたりと、簡易なアプリの設定管理などに使えるので覚えたかった。
使い方
とりあえず以下のコードをコピペして修正したらOK
基本となるコード
import sqlite3
# DB名（ファイル名）
dbname = "notify_smb_usage.sqlite3"
# コネクション
connection = sqlite3.connect(dbname, isolation_level='IMMEDIATE')
# カーソル
cursor = connection.cursor()
# SELECT文の結果を辞書型で取得
cursor.row_factory = sqlite3.Row
# SQL
query = """
SELECT
*
FROM
smb_usage
WHERE
user = :user
"""
cursor.execute(query,{
"user": "tomokiy"
})
# 実行結果
results = [{key: value for key, value in dict(result).items()} for result in cursor.fetchall()]
# コミット
connection.commit()
# DB切断
connection.close()
DB名
拡張子は.dbでもいいけど、sqlite3にした方がいい。sqlite3にすることで、VScodeの拡張機能でDBの中身を参照できる（後述）。
コネクション
isolation_levelというのは、トランザクションの分離レベルなるものを指定する。デフォルトではNoneになっているので、自動でコミットされてしまう。各分離レベルの概要は以下の通り。
| 分離レベル | Pythonでの値 | Pythonでの動き |
|---|---|---|
| deferred | “DEFERRED” | 最初のread処理でSHAREDロックを取得する。最初のwrite処理でRESERVEDロックを取得する。 |
| immediate | “IMMEDIATE” | トランザクション開始時(*1)にRESERVEDロックを取得する。当該コネクション以外からはwriteができなくなる。 |
| exclusive | “EXCLUSIVE” | トランザクション開始時(*1)にEXCLUSIVEロックを取得する。当該コネクション以外からはreadもwriteもできなくなる。 |
| auto commit | None | 自動でコミットされる |
(*1)：Pythonの場合、最初のread、write処理でトランザクションが自動的に開始される。
上記のように書いてあるが、試してみたところ、Pythonの場合はSELECTではトランザクションが開始されないみたいだった。なので実質、DEFERREDとIMMEDIATEの違いは無い。。。
SQLとパラメータ
PostgreSQLライブラリのpsycopg2のように、Pythonの文字列フォーマットや書式フォーマットでパラメータをバインドできない。パラメータの先頭に:か@を付けてバインドする。
辞書型で結果を取得
まず、カーソルの設定を行う。
cursor.row_factory = sqlite3.Row
このままフェッチするだけでもいいが、完全にPython標準のdictのlistに仕上げるために以下の処理を行う。内包表記にすることで処理速度を上げている。
fetchone()の場合
result = {key: value for key, value in dict(cursor.fetchone()).items()}
fetchall()の場合
results = [{key: value for key, value in dict(result).items()} for result in cursor.fetchall()]
その他
トランザクションの状態を取得
connection.in_transaction
INSERTやUPDATEの実行件数を取得
cursor.rowcount
SELECTなど関係ないSQL実行後は-1が返る。
VScode拡張機能を使ってデータを参照する
SQLiteという拡張機能を入れる。
コマンドパレットでSQLiteと入力し、SQLite: Open Databaseを選択する。
DBを指定する。 フォルダを開いている場合は、当該フォルダにある.dbとsqlite3の拡張子が表示されるようだが、.sqlite3の拡張子のものでないと使えない模様。
このように表示される。
