前回の記事に引き続き、SQLite3の話です。
今回はPythonのコードの中でSQLite3を使っていきます。Pythonでは標準ライブラリにSQLite3用のインターフェースがあり、特に何か追加でインストールしなくてもSQLite3を使えます。MySQL等と比較したときの大きなアドバンテージですね。
参考: sqlite3 — DB-API 2.0 interface for SQLite databases — Python 3.11.3 documentation
使い方はドキュメントにあるので早速使っていきましょう。
DB(SQLiter3のDBはただのファイル)は前回の記事で作ったのがあるのでそれに接続します。存在しなかったら勝手に生成されるのでここはあまり難しいところではありません。
前回の記事を少しおさらいしておくと、DBファイル名は、mydatabase.dbで、userってテーブルができています。これの全レコードをSELECTするコードは次の様になります。
import sqlite3
# データベースに接続
con = sqlite3.connect('mydatabase.db')
cursor = con.cursor()
# SQLの実行と結果の表示
cursor.execute("SELECT * FROM user")
row = cursor.fetchall()
print(row)
# 以下結果
# [(1, 'Alice', 20), (2, 'Bob', 30), (3, 'Charlie', 40)]
簡単ですね。
結果はタプルのリストで得られました。列名(SELECT文ではアスタリスク指定にしている)の一覧が欲しいと思われますが、それは、coursor.descriptionという属性に入ってます。ただ、入り方がちょっとクセがあって、DB API の仕様との整合性のために、不要なNone が入ってます。
# 列名 + 6個の None という値が入ってる
print(cursor.description)
"""
(
('id', None, None, None, None, None, None),
('name', None, None, None, None, None, None),
('age', None, None, None, None, None, None)
)
"""
# 列名の一覧を得るコードの例
print([row[0] for row in cursor.description])
# ['id', 'name', 'age']
これだとちょっと使いにくいということもあるともうのですが、row_factory というのを使うと、列名: 値 の辞書で結果を得ることができます。ドキュメントに「How to create and use row factories」ってしょうがあるのでそれを参考にやってみます。
con = sqlite3.connect('mydatabase.db')
# row_factoryを指定
con.row_factory = sqlite3.Row
cursor = con.cursor()
cursor.execute("SELECT * FROM user")
row = cursor.fetchall()
print(row)
# [<sqlite3.Row object at 0x109b4de70>, <sqlite3.Row object at 0x109b4dae0>, <sqlite3.Row object at 0x109b4fc70>]
# これらはそれぞれ辞書形でデータが入っているので次の様にして値を取り出せる。
print(row[0]["name"])
# Alice
print(row[0]["age"])
# 20
# 上記の状態だと、DataFrameへの変換が簡単
import pandas as pd
print(pd.DataFrame(row))
"""
0 1 2
0 1 Alice 20
1 2 Bob 30
2 3 Charlie 40
"""
これでSELECTはできましたね。
次はINSERT等のデータの編集の話です。INSERTだけ取り上げますが、UPDATEもDELETEも同様です。
基本的にexecuteでクエリを実行できるのですが、端末でSQLite3を使う時と違って、明示的にコミットしないと、セッションを切って繋ぎ直したらデータが消えてしまいます。commit()を忘れない様にしましょう。
con = sqlite3.connect('mydatabase.db')
cursor = con.cursor()
# 1行挿入
cursor.execute("INSERT INTO user (name, age) VALUES (?, ?)", ["Daniel", 35])
# コミット
con.commit()
# レコードが1レコード増えてる
cursor.execute("SELECT * FROM user")
row = cursor.fetchall()
print(row)
# [(1, 'Alice', 20), (2, 'Bob', 30), (3, 'Charlie', 40), (4, 'Daniel', 35)]
この他 CREATE TABLE等々も実行できますので以上の知識で通常の使用はできると思います。
ただ、Pythonで使う場合と端末で直接使う場合で異なる点もあります。それが、.(ドット)で始まるSQLite3の固有コマンドたちが使えないってことです。
.table が使えないのでテーブルの一覧を得るには代わりに次の様にします。
# テーブル名の一覧を取得するSQL文を実行
cursor.execute("SELECT name FROM sqlite_master")
print(cursor.fetchall())
# [('user',)]
そして、特定のテーブルのスキーマを確認するクエリはこちらです。userテーブルしか作ってないので、userテーブルで実験します。
# スキーマを取得するSQL
cursor.execute("SELECT sql FROM sqlite_master WHERE name = 'user'")
print(cursor.fetchone()[0])
"""
CREATE TABLE user (
id INTEGER PRIMARY KEY,
name TEXT,
age INTEGER
)
"""
どちらも sqlite_master ってテーブルからデータを引っ張ってきてることからわかる通り、メタデータ的なテーブルが存在してその中に情報があるということです。
ちなみに、この sqlite_master に含まれる列名の一覧は次の様になってます。 sqlite_master の中に sqlite_master の情報はなさそうなので、*で全列SELECTして列名の一覧を取得しています。
cursor.execute("SELECT * FROM sqlite_master WHERE name = 'user'")
print([row[0] for row in cursor.description])
# ['type', 'name', 'tbl_name', 'rootpage', 'sql']
以上で、PythonでSQLite3を使える様になると思います。