sqlite-utilsを使ってみよう(Pythonライブラリ)
sqlite-utils について
sqlite-utils はSQLite のデータベースをいろいろな方法で操作することができるコマンドラインツールとPythonユーティリティー関数を含むライブラリです。
ここでは、sqlite-utils の Pythonライブラリの使用方法について説明してゆきます。
インストール
PyPIのsqlite-utilsパッケージには，sqlite_utils Pythonライブラリとsqlite-utilsコマンドラインツールが含まれています。pipを使って以下のようにインストールできます。
code: bash
$ pip install sqlite-utils
データベースへの接続または作成
データベースオブジェクトは、ディスク上のファイルへのパス、または既存のSQLite3データベース接続のいずれかを渡すことで構築されます。
code: python
from sqlite_utils import Database
db = Database("my_database.db")
これにより、my_database.db がまだ存在していなければ作成されます。
データベースをゼロから再作成したいときは、recreate=True 引数を使用できます。
この引数を与えると、既存のファイルがすでに存在するときは、まずディスクから削除されます。
code:python
db = Database("my_database.db", recreate=True)
ファイルパスの代わりに、既存のSQLite接続を渡すことができます。
code: python
import sqlite3
db = Database(sqlite3.connect("my_database.db"))
インメモリーのデータベースを作りたい場合は、次のようにします。
code: python
db = Database(memory=True)
接続では、デフォルトで recursive_triggers=on 引数があたえられたものとして再帰的トリガーが有効になります。これを無効にしたい場合は、、以下のようにしてオフにすることができます。
code: python
db = Database(memory=True, recursive_triggers=False)
追加のデータベースをアタッチする
SQLite はデータベース間の SQL クエリをサポートしており、複数のデータベースファイルのテーブルからデータを結合することができます。
.attach() メソッドを使って追加のデータベースをアタッチすることができます。その際、データベースに使用するエイリアスとディスク上の SQLite ファイルへのパスを指定します。
code: python
db = Database("first.db")
db.attach("second", "second.db")
# Now you can run queries like this one:
print(db.query("""
select * from table_in_first
union all
select * from second.table_in_second
"""))
db.attach(alias, filepath) に渡したエイリアス値をプレフィックスとして、添付されたデータベースのテーブルを参照することができます。例えば、上記のSQLクエリの second.table_in_second の参照のように。
クエリのトレース
トレーサーの仕組みを使って、SQLite で実行されている SQL クエリを確認することができます。トレーサーとは、SQL が実行されるたびに sql と params を引数にして呼び出される、あなたが提供する関数のことです。
code: python
def tracer(sql, params):
print("SQL: {} - params: {}".format(sql, params))
この関数をDatabase()のコンストラクタに次のように渡すことができます。
code: python
db = Database(memory=True, tracer=tracer)
また、with db.tracer(...) コンテキストマネージャーを使って、コードブロックに対して一時的にトレーサー機能をオンにすることもできます。
code: python
db = Database(memory=True)
with db.tracer(print):
db"dogs".insert({"name": "Cleo"}) この例では、withブロックの期間中のみ、クエリを出力します。
クエリの実行
Databaseクラスには、SQLクエリを直接実行するメソッドがいくつかあります。
db.query(sql, params)
db.query(sql) 関数は、SQL クエリを実行し、結果の行を表す Python 辞書のイテレータを返します。
code: python
db = Database(memory=True)
for row in db.query("select * from dogs"):
print(row)
# Outputs:
# {'name': 'Cleo'}
# {'name': 'Pancakes'}
db.execute(sql, params)
db.execute() および db.executescript() メソッドは、基礎となるSQLite接続上の .execute() および. executescript() のラッパーを提供します。これらのラッパーは、トレーサー関数が登録されている場合は、トレーサー関数にログを記録します。
db.execute(sql) は、SQL の実行に使用された sqlite3.Cursor を返します。
code: python
db = Database(memory=True)
db"dogs".insert({"name": "Cleo"}) cursor = db.execute("update dogs set name = 'Cleopaws'")
print(cursor.rowcount)
# 更新の影響を受ける行の数を出力 この場合は2
.fetchone() や .fetchall() のような他のカーソルメソッドもありますので、標準ライブラリのドキュメントをご覧ください。
パラメータの受け渡し
db.query() と db.execute() は、SQLクエリに渡すパラメータのためのオプションの第二引数を受け入れます。
これは、クエリで使用するパラメータの種類に応じて、 タプル/リストあるいは辞書の形式をとります。このようにして渡された値は、正しく引用符で囲まれ、エスケープされるので、XSS脆弱性の回避に役立ちます。
XSS脆弱性
クロスサイトスクリプティング（Cross Site Scripting）のことで、ユーザーの入力内容によって表示が動的に変わる Web ページにおいて、悪意のあるスクリプトが Web サイトに入り込んでしまう脆弱性のことです
SQL クエリのパラメータは、リストを使って入力することができます。
code: python
db.execute("update dogs set name = ?", "Cleopaws") # これですべての犬の名前が "Cleopaws" に変更されます。
nameを使った名前付きパラメータは、辞書を使って入力することができます。
code: python
dog = next(db.query(
"select rowid, name from dogs where name = :name",
{"name": "Cleopaws"}
))
# dog is now {'rowid': 1, 'name': 'Cleopaws'}
この例では、next() を使って、db.query()メソッドが返すイテレータの最初の結果を取得しています。
テーブルへのアクセス
テーブルにアクセスするには、次のようにインデックス演算子を使用します。
code: python
テーブルがまだ存在していない場合は、初めてデータを挿入またはアップサートしようとしたときに作成されます。
また、テーブルにアクセスするには、.table() メソッドを使用します。
code: python
table = db.table("my_table")
このファクトリー機能を使用すると、テーブルの構成オプションを設定することができます。
テーブルの一覧表示
.table_names() メソッドを使用すると、データベース内のテーブルの名前を一覧表示することができます。
code: python
db.table_names()
FTS4のテーブルだけを見るには、.table_names(fts4=True) を使います。FTS5では、.table_names(fts5=True) を使用します。
また、.tables プロパティを使用して、テーブル オブジェクト自体を繰り返し表示することもできます。
code: python
db.tables
ビューの一覧表示
.view_names() は、データベース内のビューの一覧を表示します。
code: python
db.view_names()
.viewsプロパティを使って、ビューオブジェクトを反復処理することができます。
code: python
db.views
ビューオブジェクトはテーブルオブジェクトと似ていますが、データの挿入や更新をしようとするとエラーになります。ビューオブジェクトで使用できるメソッドとプロパティの一覧は以下のとおりです。
columns
columns_dict
count
schema
rows
rows_where(where, where_args, order_by, select)
drop()
レコードのリストアップ
テーブルの各行のディクショナリーを繰り返し検索するには、.rows を使用します。
code: python
db = sqlite_utils.Database("dogs.db")
print(row)
# {'id': 1, 'age': 4, 'name': 'Cleo'}
# {'id': 2, 'age': 2, 'name': 'Pancakes'}
WHERE句で行をフィルタリングするには、.rows_where(where, where_args) を使用します。
code: python
db = sqlite_utils.Database("dogs.db")
for row in db"dogs".rows_where("age > ?", 3): print(row)
# {'id': 1, 'age': 4, 'name': 'Cleo'}
最初の引数は、SQLのフラグメントです。2番目の引数（オプション）は、そのフラグメントに渡す値です。?プレースホルダーを使って配列を渡すこともできますし、:named parameters を使って次のように辞書を渡すこともできます。
code: python
for row in db"dogs".rows_where("age > :age", {"age": 3}): print(row)
# {'id': 1, 'age': 4, 'name': 'Cleo'}
デフォルトの select * ではなく、カスタムカラムを返す場合は、select="column1, column2 " としてください。
code: python
db = sqlite_utils.Database("dogs.db")
for row in db"dogs".rows_where(select='name, age'): print(row)
# {'name': 'Cleo', 'age': 4}
順番を指定するには、order_by= 引数を使います。
code: python
for row in db"dogs".rows_where("age > 1", order_by="age"): print(row)
# {'id': 2, 'age': 2, 'name': 'Pancakes'}
# {'id': 1, 'age': 4, 'name': 'Cleo'}
order_by="age desc " で降順にすることができます。
テーブルのすべてのレコードを、where 引数を除いて並べることができます。
code: python
for row in db"dogs".rows_where(order_by="age desc"): print(row)
# {'id': 1, 'age': 4, 'name': 'Cleo'}
# {'id': 2, 'age': 2, 'name': 'Pancakes'}
このメソッドは、offset= と limit= の引数も受け付け、SQLクエリのoffsetとlimitを指定します。
code: python
for row in db"dogs".rows_where(order_by="age desc", limit=1): print(row)
# {'id': 1, 'age': 4, 'name': 'Cleo'}
行数のカウント
WHEREフィルタで返される行の数を数えるには、.count_where(where, where_args) を使います。
code: python
db"dogs".count_where("age > ?", 1): # 2
プライマリキーによる行のリストアップ
各行と一緒にプライマリキーを取得し、そのキー (またはプライマリキーのタプル) を .get() や .update() メソッドに渡すことが有用な場合があります。
.pks_and_rows_where() メソッドは、.rows_where() と同じシグネチャ (select=引数を除く) を持ちますが、 (プライマリキー、レコードの辞書) のペアを生成するジェネレータを返します。
プライマリキーの値は通常は単一の値となりますが、複合プライマリキーを持つテーブルの場合はタプルになることもあります。
テーブルが（明示的なプライマリキーカラムを持たない）rowidテーブルである場合は、そのIDが返されます。
code: python
db = sqlite_utils.Database(memory=True)
db"dogs".insert({"name": "Cleo"}) for pk, row in db"dogs".pks_and_rows_where(): print(pk, row)
# 1 {'rowid': 1, 'name': 'Cleo'}
print(pk, row)
# 5 {'id': 5, 'name': 'Cleo'}
{"species": "dog", "id": 3, "name": "Cleo"},
pk=("species", "id")
)
print(pk, row)
# ('dog', 3) {'species': 'dog', 'id': 3, 'name': 'Cleo'}
特定のレコードの取得
table.get() を使うと、プライマリキーでレコードを取得することができます。
code: python
db = sqlite_utils.Database("dogs.db")
# {'id': 1, 'age': 4, 'name': 'Cleo'}
テーブルに複合プライマリキーがある場合は、プライマリキーの値をタプルとして渡すことができます。
code: python
レコードが存在しない場合は NotFoundError 例外が発生します。
code: python
from sqlite_utils.db import NotFoundError
try:
except NotFoundError:
print("Dog not found")
スキーマの表示
db.schema プロパティは、データベースの完全な SQL スキーマを文字列として返します。
code: python
db = sqlite_utils.Database("dogs.db")
print(db.schema)
print(db.schema)
CREATE TABLE "dogs" (
);
テーブルの作成
新しいテーブルを作成する最も簡単な方法は、そのテーブルにレコードを挿入することです。
code: python
from sqlite_utils import Database
import sqlite3
db = Database(sqlite3.connect("/tmp/dogs.db"))
dogs.insert({
"name": "Cleo",
"twitter": "cleopaws",
"age": 3,
"is_good_dog": True,
})
これにより、以下のようなスキーマを持つ「dogs」という新しいテーブルが自動的に作成されます。
code: SQL
CREATE TABLE dogs (
name TEXT,
twitter TEXT,
age INTEGER,
is_good_dog INTEGER
)
.insert() の呼び出しに pk= 引数を渡すことで、プライマリキーを指定することもできます。これは、挿入されるレコードによってテーブルが作成される場合にのみ従うことになります。
code: python
dogs.insert({
"id": 1,
"name": "Cleo",
"twitter": "cleopaws",
"age": 3,
"is_good_dog": True,
}, pk="id")
このように行を挿入した後、dogs.last_rowidプロパティは、最も最近挿入されたレコードに割り当てられたSQLiteのrowidを返します。
dogs.last_pkプロパティは、最後に挿入されたプライマリキーの値を返します（指定した場合）。これは、外部キーや多対多のリレーションを作成するコードを書くときに非常に便利です。
カスタムのカラム順序とカラムタイプ
Python 3.6以降を使用している場合、テーブル内のカラムの順序は、辞書内のキーの順序に由来します。
カラムの順序を明示的に設定したい場合は、column_order=パラメータを使用して設定することができます。
code: python
"id": 1,
"name": "Cleo",
"twitter": "cleopaws",
"age": 3,
"is_good_dog": True,
}, pk="id", column_order=("id", "twitter", "name"))
column_orderパラメータには、すべてのカラムを渡す必要はありません。カラムのサブセットのみを渡した場合、残りのカラムはディクショナリーのキーオーダーに基づいて並べられます。
カラムの種類は、提供されたサンプルデータに基づいて検出されます。例えば、文字列として提供されたデータに整数カラムを作成したり、最初の例がNoneであったテーブルをTEXTカラムではなくINTEGERとして作成するなど、検出されたタイプを上書きする必要がある場合があります。これには columns= 引数を使用します。
code: pyton
"id": 1,
"name": "Cleo",
"age": "5",
}, pk="id", columns={"age": int, "weight": float})
これにより、以下のようなスキーマを持つテーブルが作成されます。
code: SQL
)
テーブルの明示的な作成
.create() メソッドを使えば、データを挿入せずに直接新しいテーブルを作成することができます。
code: python
"id": int,
"name": str,
"weight": float,
}, pk="id")
ここでの最初の引数は、作成したいカラムを指定するディクショナリーです。各カラムは、カラムの種類を示すPythonタイプと対になっています。これらの型がどのように機能するかについての詳細はカラムの追加を参照してください。
このメソッドは、オプションの引数 pk=, column_order=, foreign_keys=, not_null=set(), defaults=dict() を取ります - 以下に説明します。
複合プライマリキー
複数のカラムにまたがる複合プライマリキーを持つテーブルを作成したい場合は、 pk= 引数を受け付けるメソッドのいずれかにカラム名のタプルを渡すことで実現できます。例えば、以下のようになります。
code: python
"id": int,
"breed": str,
"name": str,
"weight": float,
}, pk=("breed", "id"))
これは、.insert()、.insert_all()、.upsert()、.upsert_all() の各メソッドでも同様です。
外部キーの指定
テーブルを作成するすべての操作 (.create()、.insert()、.insert_all()、.upsert()、.upsert_all()) は、オプションの foreign_keys= 引数を受け入れ、作成されるテーブルの外部キー制約を設定するために使用できます。
Datasette でデータベースを使用している場合、Datasette はこれらの制約を検出し、関連するレコードへのハイパーリンクを生成するために使用します。
foreign_keys 引数は、どの外部キーを作成すべきかを示すリストを取ります。このリストにはいくつかの形式があります。最も単純なのは、列のリストです。
code: python
ライブラリは、「外部キー制約の追加」で説明したルールを使用して、カラム名に基づいて参照したいテーブルを推測します。
タプルのリストを渡すことで、より明確にすることもできます。
code: python
foreign_keys=[
("author_id", "authors", "id")
]
つまり、author_id カラムは authors テーブルの id カラムを参照する外部キーでなければなりません。
タプルの3番目の項目を省略すると、参照されるカラムがそのテーブルのプライマリキーに自動的に設定されます。完全な例です。
code: python
{"id": 1, "name": "Sally"},
{"id": 2, "name": "Asheesh"}
], pk="id")
{"title": "Hedgehogs of the world", "author_id": 1},
{"title": "How to train your wolf", "author_id": 2},
], foreign_keys=[
("author_id", "authors")
])
テーブル構成オプション
.insert()、.upsert()、.insert_all()、.upsert_all() の各メソッドは、それぞれいくつかのキーワード引数を取ります。その中には、テーブルが作成された場合の動作や、これらのメソッドの動作に影響を与えるものがあります。
これらのメソッドにデフォルト値を設定するには、db["テーブル名"] を使う代わりに db.table(...) メソッドを使ってテーブルにアクセスします。
code: python
table = db.table(
"authors",
pk="id",
not_null={"name", "score"},
column_order=("id", "name", "score", "url")
)
# Now you can call .insert() like so:
table.insert({"id": 1, "name": "Tracy", "score": 5})
この方法で指定できる設定オプションは、pk、foreign_keys、column_order、not_null、defaults、batch_size、hash_id、alter、ignore、replace、extracts、conversions、columnsです。これらについては、以下で説明します。
既定値とnot null制約の設定
テーブルを作成することができる各メソッドは、オプションの引数 not_null=set() および defaults=dict() を取ります。これらのオプション引数を取るメソッドは
db.create_table(...)
table.create(...)
table.insert(...)
table.insert_all(...)
table.upsert(...)
table.upsert_all(...)
not_null=を使用すると、作成時にNOT NULL制約が設定されるべき列名のセットを渡すことができます。
defaults=を使用すると、CREATE TABLE文で指定されるべきデフォルト値に列をマッピングする辞書を渡すことができます。
これらの機能を使用した例を示します。
code: python
pk="id",
not_null={"name", "score"},
defaults={"score": 1},
)
# Outputs:
# [{'id': 1, 'name': 'Sally', 'score': 2},
# {'id': 3, 'name': 'Dharma', 'score': 1}]
# Outputs:
# id INTEGER PRIMARY KEY, # score INTEGER NOT NULL DEFAULT 1 # )
一括挿入
複数のレコードを挿入する場合は、insert_all() メソッドを使用すると、より効率的に挿入できます。insert() と同様に、作成すべきカラムを自動的に検出しますが、最初の 100 件のバッチを検査して、それらのカラムタイプを決定するのに役立てます。
次のように使用します。
code: python
"id": 1,
"name": "Cleo",
"twitter": "cleopaws",
"age": 3,
"is_good_dog": True,
}, {
"id": 2,
"name": "Marnie",
"twitter": "MarnieTheDog",
"age": 16,
"is_good_dog": True,
}], pk="id", column_order=("id", "twitter", "name"))
CREATE TABLE文で使用される列の種類は、最初の行のバッチのデータの種類から自動的に導き出されます。後続のバッチで列が追加されると、alter=True引数が与えられない限り、sqlite3.OperationalError例外が発生し、その場合は新しい列が作成されます。
この関数は、行のイテレータやジェネレータを受け取ることができ、バッチサイズに応じてコミットします。デフォルトのバッチサイズは100ですが，batch_sizeパラメータで異なるサイズを指定することができます．
code: pytohn
"id": 1,
"name": "Name {}".format(i),
} for i in range(10000)), batch_size=1000)
ignore=True を使用すると、既に存在するプライマリキーを持つレコードの挿入をスキップすることができます。これは、.insert({...}, ignore=True)と .insert_all([...], ignore=True) の両方で機能します。
truncate=True を使えば、新しいレコードを挿入する前に、テーブルの既存の行をすべて削除することができます。これは、テーブルのデータを入れ替えたい場合に便利です。
データの挿入-置換
レコードを挿入したり、同じプライマリキーを持つ既存のレコードを置き換えたい場合は、.insert() または .insert_all() の replace=True 引数を使用します。
code: python
"id": 1,
"name": "Cleo",
"twitter": "cleopaws",
"age": 3,
"is_good_dog": True,
}, {
"id": 2,
"name": "Marnie",
"twitter": "MarnieTheDog",
"age": 16,
"is_good_dog": True,
}], pk="id", replace=True)
特定のレコードの更新
table.update() メソッドを使えば、プライマリキーでレコードを更新することができます。
code: python
db = sqlite_utils.Database("dogs.db")
{'id': 1, 'age': 4, 'name': 'Cleo'}
db"dogs".update(1, {"age": 5}) {'id': 1, 'age': 5, 'name': 'Cleo'}
update()メソッドの最初の引数は、プライマリキーです。これは単一の値で、そのテーブルが複合プライマリキーを持つ場合はタプルになります。
code: python
2番目の引数は、更新されるべき列とその新しい値の辞書です。
alter=True を使用すると、不足しているカラムを自動的に追加することができます。
code: python
db"dogs".update(1, {"breed": "Mutt"}, alter=True) 特定のレコードを削除する
レコードの削除には、table.delete() メソッドを使用します。
code: python
db = sqlite_utils.Database("dogs.db")
delete() メソッドは、レコードのプライマリキーを受け取ります。これは、行に複合プライマリキーがある場合は値のタプルになります。
code: python
複数のレコードの削除
table.delete_where() を使えば、特定のWHERE文にマッチするテーブルのすべてのレコードを削除することができます。
code: python
db = sqlite_utils.Database("dogs.db")
# 年齢が3歳以下の犬をすべて削除
db"dogs".delete_where("age < ?", 3) table.delete_where()を他の引数なしで呼び出すと、テーブルのすべての行が削除されます。
データのアップサーティング
アップサートを使用すると、プライマリキーとのマッチングに基づいて、レコードが存在しない場合は挿入し、存在する場合は更新することができます。
たとえば、dogs データベースでは、Cleo のレコードを次のようにアップサートすることができます。
code: python
"id": 1,
"name": "Cleo",
"twitter": "cleopaws",
"age": 4,
"is_good_dog": True,
}, pk="id", column_order=("id", "twitter", "name"))
id=1 のレコードが存在する場合は、そのフィールドに合わせて更新されます。存在していない場合は、作成されます。
.upsert() に渡されたディクショナリで参照されていない既存の列は、変更されません。レコードを完全に置き換えたい場合は、代わりに .insert(doc, replace=True) を使用してください。
ここでの pk および column_order 引数は、テーブルがすでに作成されていることが確実な場合には省略可能であることに注意してください。最初のアップサートの実行時にテーブルが存在しない可能性がある場合は、これらの引数を渡す必要があります。
upsert_all() メソッドも用意されており、insert_all()と同様の動作をしますが、代わりにアップサートを実行します。
カラムのデータを変換する
table.convert(...) メソッドは、列の値に変換関数を適用して、その列を更新したり、新しい列を追加したりするのに使用できます。これは、sqlite-utilsのconvertコマンドに相当するPythonライブラリです。
この機能は、Pythonの変換関数を適用するカスタムSQLite関数を登録し、次の同等のSQLクエリを実行することで動作します。
code: python
UPDATE table SET column = convert_value(column)
特定のカラムを大文字に変換するには、次のようにします。
code: python
db"dogs".convert("name", lambda value: value.upper()) カラムのリストを渡すこともでき、その場合はそれぞれのカラムに変換が適用されます。
code: python
変換の出力先を別の列に保存するには、output= パラメータを使用します。
code: python
db"dogs".convert("name", lambda value: value.upper(), output="name_upper") これにより、新しいカラムがまだ存在していなければ、それが追加されます。output_type=int またはその他の型を渡して、新しい列の型を制御することができます。そうしないと、デフォルトでtextになります。
結果を別の出力列に保存した後に元の列を削除したい場合は、drop=True を渡します。
multi=True とPython辞書を返す変換関数を渡すことで、1つの入力カラムから複数の新しいカラムを作成することができます。この例では、1つのタイトルカラムから生成された新しい上段と下段のカラムを作成しています。
code: python
table.convert(
"title", lambda v: {"upper": v.upper(), "lower": v.lower()}, multi=True
)
.convert() メソッドには、オプションで where= と where_args= というパラメータがあり、これを使用することで、where 節で指定された行のサブセットに変換を適用することができます。ここでは、20 以上の ID を持つ行にのみ変換を適用する方法を示します。
code: python
table.convert("title", lambda v: v.upper(), where="id > :id", where_args={"id": 20})
これらは、.rows_where() メソッドの対応するパラメータと同じように動作するので、 :named プレースホルダーと辞書の代わりに ???
ルックアップテーブルの使用
大きなテーブルに値を入力する際に便利なのが、共通の値をルックアップテーブルに分割する方法です。木のテーブルを考えてみましょう。各木には種があります。理想的には、これらの種は別の Species テーブルに分割され、それぞれの種には Trees テーブルの species_id カラムから参照可能な整数のプライマリキーが割り当てられます。
ルックアップテーブルの明示的な作成
db["Species"].lookup({"name": "Palm"}) を呼び出すと、id と name の 2 つのカラムを持つ、Species というテーブルが作成されます（まだ存在していない場合）。name カラムにはユニーク制約を設定し、重複する行が含まれないようにしています。そして、name を Palm に設定した行を挿入し、新しい整数のプライマリキー値を返します。
Species テーブルが既に存在している場合は、新しい行を挿入してプライマリキーを返します。その名前の行が既に存在する場合は、対応するプライマリキーの値を直接返します。
ユニーク制約のない既存のテーブルに対して .lookup() を呼び出すと、制約の追加が試みられ、制約が作成できない場合はIntegrityError 例外が発生します。
複数の値を持つ辞書を渡した場合、両方の値が対応するIDの挿入または取得に使用され、作成された一意の制約は、例えば、それらのすべての列をカバーします。
code: python
"latitude": 49.1265976,
"longitude": 2.5496218,
"common_name": "Common Juniper",
"latin_name": "Juniperus communis"
})
})
挿入/アップサート時にルックアップ・テーブルを自動的に生成する
このパラメータは、.insert()、.upsert()、.insert_all()、.upsert_all()、.table(...) 関数で使用できます。
extracts= データ挿入時に別のルックアップテーブルに「抽出」されるべき列を指定します。
この場合、抽出されるテーブル名はカラム名と完全に一致します。また、カラム名を抽出されるテーブルの希望の名前にマッピングするディクショナリを指定することもできます。
Species カラムを別の Species テーブルに抽出するには、次のようにします。
code: python
# Using the table factory
trees = db.table("Trees", extracts={"species": "Species"})
trees.insert({
"latitude": 49.1265976,
"longitude": 2.5496218,
"species": "Common Juniper"
})
# If you want the table to be called 'species', you can do this:
trees = db.table("Trees", extracts="species") # Using .insert() directly
"latitude": 49.1265976,
"longitude": 2.5496218,
"species": "Common Juniper"
}, extracts={"species": "Species"})
多対多のリレーションの処理
sqlite-utilsには、table.m2m(...) メソッドという形で、多対多のリレーションを使ってレコードを作成するためのショートカットがあります。
ここでは、1行のコードで2つの新しいレコードを作成し、多対多のテーブルを介して接続する方法を紹介します。
code: python
db"dogs".insert({"id": 1, "name": "Cleo"}, pk="id").m2m( "humans", {"id": 1, "name": "Natalie"}, pk="id"
)
この例を実行すると、dogs、human、および多対多の dogs_humans テーブルの 3 つのテーブルが実際に作成されます。これらのテーブルにそれぞれレコードを挿入します。
.m2m() メソッドは .insert() や .update() で影響を受けた最後のレコード、つまり table.last_pk プロパティで特定されるレコードに対して実行されます。特定のレコードに対して .m2m() を実行するには、まずそのレコードのプライマリキーを .update() に渡して選択します。
code: python
"humans", {"id": 2, "name": "Simon"}, pk="id"
)
.m2m() の最初の引数には、テーブルの名前を文字列で指定することも、テーブルオブジェクトそのものを指定することもできます。
2 番目の引数には、単一の辞書レコードまたは辞書のリストを指定します。これらの辞書は、指定されたテーブルに対して.upsert()に渡されます。
以下は、犬のレコードを作成し、そこに2人を追加する代替コードです。
code: python
db = Database(memory=True)
dogs = db.table("dogs", pk="id")
humans = db.table("humans", pk="id")
dogs.insert({"id": 1, "name": "Cleo"}).m2m(
humans, [
{"id": 1, "name": "Natalie"},
{"id": 2, "name": "Simon"}
]
)
このメソッドは、既存の多対多のテーブルを見つけるために、リレーションシップを持つ両方のテーブルに対して外部キーのリレーションシップを持つテーブルを探します。
もしそのようなテーブルが見つからなければ、2つのテーブルの名前（この例では dogs_humans）を使って新しいテーブルを作成します。このテーブルの名前は .m2m() の m2m_table= 引数でカスタマイズすることができます。
指定されたテーブルの両方に外部キーを持つ複数の候補テーブルを見つけた場合、sqlite_utils.db.NoObviousTable 例外が発生します。m2m_table= を使って正しいテーブルを指定することで、このエラーを回避することができます。
.m2m() メソッドは、テーブルが作成されたときに使用されるプライマリキーを指定するオプションの pk= 引数と、既存のテーブルに欠けているカラムがあればそれを追加するように指定するオプションの alter=True 引数も取ります。
m2m とルックアップテーブルの併用
lookup= パラメータを使って .m2m() を呼び出す際に、ルックアップテーブルを使用 (または作成) することができます。これは table.lookup() と同じ引数、つまりルックアップテーブルの行を検索したり作成したりするための値の辞書を受け取ります。
この例では、犬のテーブルを作成し、それに値を入力し、特性のテーブルを作成し、それに値を入力し、2つの間に多対多の関係を設定しています。.m2m() を2回チェーンして、2つの関連する特性を作成しています。
code: python
db = Database(memory=True)
dogs = db.table("dogs", pk="id")
dogs.insert({"id": 1, "name": "Cleo"}).m2m(
"characteristics", lookup={
"name": "Playful"
}
).m2m(
"characteristics", lookup={
"name": "Opinionated"
}
)
このように、データベースを検査して結果を確認することができます。
code: python
db.table_names()
)
カラムの分析
table.analyze_column(column, common_limit=10, value_truncate=None) メソッドは、CLIコマンドのanalyze-tablesで使用されます。このメソッドは、以下のフィールドを持つColumnDetailsという名前のタプルを返します。
table
テーブルの名前。
column
カラムの名前。
total_rows
テーブルの総行数
num_null
このカラムがNULLである行の数
num_blank
このカラムが空白（空文字列）である行の数
num_distinct
このカラムに含まれる個別の値の数
most_common
(value, count)タプルのリストとしてのN個の最も一般的な値、またはテーブルが個別の値のみで構成されている場合はNone。
least_common
N個の最小公倍数を(value, count)タプル`のリストで表したもの、あるいは、テーブルが完全に個別の値で構成されている場合や、個別の値の数がNよりも少ない場合(most_commonで既に返されているため)は、Noneとなります。
Nのデフォルトは10ですが、common_limitパラメータを使って、カスタムのNを渡すこともできます。
value_truncate パラメータを使用すると、most_common および least_common リストの値を、指定した文字数に切り詰めることができます。
カラムの追加
テーブルに新しいカラムを追加するには、.add_column(col_name, col_type) メソッドを使用します。
code: python
db"dogs".add_column("instagram", str) db"dogs".add_column("weight", float) db"dogs".add_column("dob", datetime.date) db"dogs".add_column("image", "BLOB") db"dogs".add_column("website") # str by default col_type引数には、SQLiteの型を文字列で指定するか、Pythonの型（strやfloatなど）を直接指定することができます。
col_typeはオプションで、省略した場合はTEXT型が使用されます。
指定できる SQLite の型は、"TEXT"、"INTEGER"、"FLOAT"、"BLOB" です。
Pythonの型を指定した場合は、以下のようにSQLiteの型にマッピングされます。
code: python
float: "FLOAT"
int: "INTEGER"
bool: "INTEGER"
str: "TEXT"
bytes: "BLOB"
datetime.datetime: "TEXT"
datetime.date: "TEXT"
datetime.time: "TEXT"
# If numpy is installed
np.int8: "INTEGER"
np.int16: "INTEGER"
np.int32: "INTEGER"
np.int64: "INTEGER"
np.uint8: "INTEGER"
np.uint16: "INTEGER"
np.uint32: "INTEGER"
np.uint64: "INTEGER"
np.float16: "FLOAT"
np.float32: "FLOAT"
np.float64: "FLOAT"
また、fk引数を使って、他のテーブルの外部キー参照となるカラムを追加することもできます。
code: python
db"dogs".add_column("species_id", fk="species") これにより、speciesテーブルのプライマリキーの名前が自動的に検出され、その名前（およびそのタイプ）が新しいカラムに使用されます。
fk_colを使用して、参照したいカラムを明示的に指定することができます。
code: python
db"dogs".add_column("species_id", fk="species", fk_col="ref") not_null_defaultを使って、新しいカラム x にNOT NULL DEFAULT制約を設定することができます。
code: python
db"dogs".add_column("friends_count", int, not_null_default=0) 挿入/更新時のカラムの自動追加
新しいカラムを含むデータを挿入したり更新したりする際に、 alter=True 引数を使用して新しいスキーマに合わせてテーブルを自動的に変更することができます。この引数は、.insert()、.upsert()、.insert_all()、.upsert_all()の4つすべてに渡すことができます。また、db.table(table_name, alter=True) に渡すことで、そのテーブルインスタンスに対するすべてのメソッドコールでデフォルトで有効にすることもできます。
code: python
# This will throw an exception:
# This will succeed and add a new "age" integer column:
db"new_table".insert({"name": "Gareth", "age": 32}, alter=True) # You can see confirm the new column like so:
# Outputs this:
# {'name': <class 'str'>, 'age': <class 'int'>}
# This works too:
new_table = db.table("new_table", alter=True)
new_table.insert({"name": "Gareth", "age": 32, "shoe_size": 11})
外部キー制約の追加
SQLite の ALTER TABLE ステートメントには、既存のカラムに外部キーの参照を追加する機能はありません。
SQLiteのsqlite_masterテーブルをwritable_schemaを使用して非常に注意深く操作することで、これらの参照を追加することができます。
sqlite-utilsではこれを行うことができますが、何か問題が発生した場合にはデータが破損する大きなリスクがありますので、これを試みる前にデータベースファイルの新しいコピーを作成することをお勧めします。
以下にこのメカニズムの動作例を示します。
code: python
{"id": 1, "name": "Sally"},
{"id": 2, "name": "Asheesh"}
], pk="id")
{"title": "Hedgehogs of the world", "author_id": 1},
{"title": "How to train your wolf", "author_id": 2},
])
db"books".add_foreign_key("author_id", "authors", "id") table.add_foreign_key(column, other_table, other_column) メソッドは、カラムの名前、参照されているテーブル、その他テーブル内のキーカラムを受け取ります。other_columnを省略した場合は、そのテーブルのプライマリキーが自動的に使用されます。other_tableを省略した場合は、いくつかの簡単なルールに基づいてテーブルが推測されます。
カラムのフォーマットがauthor_idであれば、authorまたはauthorsというテーブルを探します。
カラムが_idで終わらない場合、そのカラムの正確な名前、またはその名前にsを付けたテーブルを探します。
このメソッドはまず、指定された外部キーが存在するテーブルや列を参照しているか、既存の外部キーと衝突していないかを確認します。これらのチェックに失敗した場合、sqlite_utils.db.AlterError 例外が発生します。
キーがすでに存在する場合を無視するには、ignore=True を使用します。
code: python
db"books".add_foreign_key("author_id", "authors", "id", ignore=True) code: ython
db.add_foreign_keys([
("dogs", "breed_id", "breeds", "id"),
("dogs", "home_town_id", "towns", "id")
])
このメソッドは .add_foreign_keys()と同じチェックを行い、チェックに失敗した場合は sqlite_utils.db.AlterError が発生します。
すべての外部キーに対するインデックスの追加
データベースのすべての外部キーカラムに対応するインデックスがあることを確認したい場合は、次のようにします。
code: python
db.index_foreign_keys()
テーブルやビューの削除
テーブルやビューを削除するには、.drop() メソッドを使用します。
code: python
テーブルやビューが存在しないことによるエラーを無視したい場合は、ignore=Trueを渡します。
code: python
テーブルの変換
SQLite の ALTER TABLE ステートメントには制限があり、カラムの追加やテーブル名の変更はできますが、カラムの削除、カラムの種類の変更、NOT NULLステータスの変更、テーブルのプライマリキーの変更はできません。
table.transform() メソッドは、SQLiteのドキュメントで説明されている複数のステップのパターンを実装することで、これらすべてのことを行うことができます。
1. トランザクションの開始
2. 必要な変更を加えたテーブルを作成 CREATE TABLE tablename_new_x123
3. データを新しいテーブルにコピー INSERT INTO tablename_new_x123 SELECT * FROM tablename
4. 古いテーブルを削除 DROP TABLE tablename
5. テーブル名を変更 ALTER TABLE tablename_new_x123 RENAME TO tablename
6. トランザクションのコミット
.transform() メソッドはいくつかのパラメータを受け取りますが、これらはすべて省略可能なオプションです。
カラムの型の変更
カラムのタイプを変更するには、types= 引数を使用します。
code: python
# age カラムを整数に、weight カラムを浮動小数点に変換する
table.transform(types={"age": int, "weight": float})
カラム名の変更
rename=パラメータは、カラムの名前を変更することができます。
code: python
# 'age' カラムを 'initial_age':
table.transform(rename={"age": "initial_age"})
カラムの削除
カラムを削除するには、drop= でカラムをセットで渡します。
code: python
table.transform(drop={"age"})
プライマリキーの変更
テーブルのプライマリキーを変更するには、pk= を使用します。 これには、通常のプライマリキーの場合は単一のカラムを、複合プライマリキーの場合は複数のカラムのタプルを渡すことができます。pk=Noneを渡すと、プライマリキーは削除され、テーブルはrowidテーブルに変換されます。
code: python
table.transform(pk="user_id")
NOT NULLステータスの変更
not_null= を使用すると、カラムのNOT NULLステータスを変更することができます。 これにカラムのセットを渡すと、それらのカラムをNOT NULLにすることができます。
code: python
table.transform(not_null={"age", "weight"})
既存のNOT NULLカラムをNULL値を許容するように変更したい場合は、代わりに True/False 値の辞書を渡すことで実現できます。
code: python
# 'age' は NOT NULL これを NULL を受け入れるように変更
table.transform(not_null={"age": False})
# 'age' はNULLを受け入れれ、'weight' は NOT NULL に変更
table.transform(not_null={"age": False, "weight": True})
カラムのデフォルト値の変更
defaults= 引数を使用して、異なるカラムのデフォルトを設定または変更することができます。
code: python
# 'age' カラムのデフォルト値を１にする
table.transform(defaults={"age": 1})
# 'age' カラムのデフォルト値を削除
table.transform(defaults={"age": None})
カラムの順序を変更する
column_order= 引数を使用すると、カラムの順序を変更することができます。カラムのサブセットの名前を渡すと、それらのカラムが最初に表示され、省略したカラムはその次の既存の順序で表示されます。
code: python
table.transform(column_order=("name", "age", "id")
外部キー制約の削除
.transform() を使って、テーブルから外部キー制約を取り除くことができます。
この例では、places.country から country.id への外部キーと、places.continent から continent.id への外部キーという 2 つの外部キーを削除します。
code: python
drop_foreign_keys=("country", "continent")
)
.transform_sql()によるカスタムトランスフォーム
.transform() メソッドはほとんどの場合に対応できますが、変換対象のテーブルに関連するインデックスやビュー、トリガーを自動的にアップグレードすることはありません。
もっと高度なことをしたい場合は、table.transform(...)に渡したのと同じ引数で、table.transform_sql(...) メソッドを呼び出すことができます。
このメソッドは、変更を実装するために実行すべきSQL文のリストを返します。そして、自分で実行する前に、そのSQLに修正を加えたり、SQL文を追加したりすることができます。
カラムを別のテーブルに抽出する
table.extract() メソッドを使用すると、指定したカラムを別のテーブルに抽出することができます。
以下のようなTreesテーブルを想像してみてください。
table: Trees テーブル
id TreeAddress Species
1 52 Vine St Palm
2 12 Draft St Oak
3 51 Dark Ave Palm
4 1252 Left St Palm
カラム Species に重複した値が含まれています。このデータベースは、このカラムを別の Species テーブルに抽出し、外部キーカラムを使用してそれを指すようにすることで改善できます。
上記テーブルのスキーマは
code: SQL
)
ここでは、.extract() を使用して Species カラムを抽出する方法を説明します。
code: python
このコードを実行すると、テーブルスキーマは次のようになります。
code: SQL
CREATE TABLE "Trees" (
FOREIGN KEY(Species_id) REFERENCES Species(id)
)
新しい Species テーブルが以下のようなスキーマで作成されています。
code: SQL
)
.extract() メソッドのデフォルトでは、抽出されたカラムと同じ名前のテーブルが作成され、外部キー・カラムtablename_id というが追加されます。
カスタムテーブル名は table= で、カスタム外部キー名は fk_column= で指定できます。 次の例では、tree_species というテーブルと、tree_species_id という外部キーカラムを作成しています。
code: python
db"Trees".extract("Species", table="tree_species", fk_column="tree_species_id") 結果として、スキーマは次のようになります。
code: SQL
CREATE TABLE "Trees" (
FOREIGN KEY(tree_species_id) REFERENCES tree_species(id)
)
)
また、複数のカラムを同じ外部テーブルに抽出することもできます。例えば、次のようなテーブルがあるとします。
table: TreeTable
id TreeAddress CommonName LatinName
1 52 Vine St Palm Arecaceae
2 12 Draft St Oak Quercus
3 51 Dark Ave Palm Arecaceae
4 1252 Left St Palm Arecaceae
.extract() に ["CommonName", "LatinName"] を渡すと、これら2つのカラムを抽出することができます。
code: python
これにより、次のようなスキーマを生成します。
code: SQL
CREATE TABLE "Trees" (
FOREIGN KEY(CommonName_LatinName_id) REFERENCES CommonName_LatinName(id)
)
)
テーブル名の CommonName_LatinName は、抽出されたカラムに由来します。table= と fk_column= を使って、このようなカスタム名を指定することができます。
code: python
これは、次のようなスキーマを生成します。
code: SQL
CREATE TABLE "Trees" (
FOREIGN KEY(species_id) REFERENCES Species(id)
)
)
ルックアップテーブルのカラム名を変更するには、 rename= 引数を使用します。name と latin というカラムを持つ Species テーブルを作成するには次のようにします。
code: python
table="Species",
fk_column="species_id",
rename={"CommonName": "name", "LatinName": "latin"}
)
これにより、次のようなルックアップテーブルが生成されます。
code: SQL
)
カラムの内容のハッシュ値に基づいてIDを設定する
データセットの中には、明らかなIDを持たない行が含まれていることがありますが、後でそのテーブルにアップサートする際に、重複したレコードを作成せずに済むようにIDを割り当てたい場合があります。
このような場合、カラムの内容のsha1ハッシュからIDを作成するのが有効な手法です。
sqlite-utils は、hash_id= 引数を使用してこれを行うことができます。例えば、以下のようになります。
code: python
db = sqlite_utils.Database("dogs.db")
db"dogs".upsert({"name": "Cleo", "twitter": "cleopaws"}, hash_id="id") この出力は次のようになります。
code: python
そのIDをすぐに使用する場合は、last_pkでアクセスできます。
code: python
"name": "Cleo",
"twitter": "cleopaws"
}, hash_id="id").last_pk
# dog_id is now "f501265970505d9825d8d9f590bfab3519fb20b1"
ビューの作成
ビューを作成するには、データベースクラスの .create_view() メソッドを使用します。
code: python
db.create_view("good_dogs", """
select * from dogs where is_good_dog = 1
""")
これは、その名前のビューが既に存在する場合、sqlite_utils.utils.OperationalError 例外を発生させます。
ignore=True を渡せば、既存のビューを無視して何もしません。また、replace=True を渡せば、select文が現在のビューと異なる場合、既存のビューを新しい定義で置き換えます。
code: python
db.create_view("good_dogs", """
select * from dogs where is_good_dog = 1
""", replace=True)
JSONデータを格納
SQLite はJSONサポートしてて、うまく処理することができます。 sqlite-utils も、この機能を利用することができます。JSONリストや辞書として表現できる値を挿入しようとした場合、sqlite-utils はTEXTカラムを作成し、データをシリアライズされたJSONとして保存します。つまり、複雑なデータ構造であってもSQLiteに素早く保存し、JSONの機能を使って照会することができます。
例えば、以下のようになります。
code: python
"name": "The Bigfoot Discovery Museum",
"hours": {
},
"address": {
"streetAddress": "5497 Highway 9",
"addressLocality": "Felton, CA",
"postalCode": "95018"
}
})
db.execute("""
select json_extract(address, '$.addressLocality')
from niche_museums
""").fetchall()
SQL関数を使ってカラムの値を変換する
値を挿入する前に、SQL関数に通しておくと便利な場合があります。例えば、挿入中に値を大文字に変換するといった簡単な例です。
conversions={...} 引数を使用して、SQLコマンドの INSERT または UPDATE の一部として使用するカスタムSQLを指定することができます。
以下のように、特定の列に対して大文字への変換を指定することができます。
code: python
"name": "The Bigfoot Discovery Museum"
}, conversions={"name": "upper(?)"})
辞書のキーは、変換されるカラム名です。値は、使用する SQL フラグメントで、元の値を保持するたえに使用されます。
より便利な例として、SpatiaLiteを使用している場合、WKTの値からジオメトリの値を作成したいことがあるかもしれません。そのためのコードは次のようになります。
code: python
import sqlite3
import sqlite_utils
from shapely.geometry import shape
import requests
# Open a database and load the SpatiaLite extension:
import sqlite3
conn = sqlite3.connect("places.db")
conn.enable_load_extension(True)
conn.load_extension("/usr/local/lib/mod_spatialite.dylib")
# Use sqlite-utils to create a places table:
db = sqlite_utils.Database(conn)
places = db"places".create({"id": int, "name": str,}) # Add a SpatiaLite 'geometry' column:
db.execute("select InitSpatialMetadata(1)")
db.execute(
"SELECT AddGeometryColumn('places', 'geometry', 4326, 'MULTIPOLYGON', 2);"
)
# Fetch some GeoJSON from Who's On First:
geojson = requests.get(
).json()
# Convert to "Well Known Text" format using shapely
# Insert the record, converting the WKT to a SpatiaLite geometry:
{"name": "Wales", "geometry": wkt},
conversions={"geometry": "GeomFromText(?, 4326)"},
)
イントロスペクション
既存のテーブルやビューを読み込んだ場合、イントロスペクションを使用してそれについての詳細を調べることができます。
イントロスペクション(introspection)
言葉通りでは、内省，内観，自己反省.のことですが、プログラミングでのイントロスペクションは、実行時にオブジェクトの情報を参照する方法のこといいます。
code: python
<Table PlantType (id, value)>
.exists()
.exists() メソッドを使用すると、テーブルが存在するかどうかを調べることができます。
code: pyton
True
False
.count
.countプロパティは、現在の行数を表示します（select count(*) from table と等価）。
code: python
3
189144
このプロパティは、データベースに use_counts_table プロパティが設定されている場合に、 トリガーを使用してキャッシュされたテーブルカウントを利用します。このプロパティにアクセスする代わりに table.count_where() を呼び出すことで、この最適化を完全に回避することができます。
.columns
.columns プロパティは、テーブルまたはビューの列を表示します。これは次のようなカラム名のタプルのリストを返します。
code: python
[Column(cid=0, name='id', type='INTEGER', notnull=0, default_value=None, is_pk=1),
Column(cid=1, name='value', type='TEXT', notnull=0, default_value=None, is_pk=0)]
.columns_dict
.columns_dict プロパティは、名前とPythonの型だけのカラムの辞書バージョンを返します。
code: python
{'id': <class 'int'>, 'value': <class 'str'>}
.pks
.pks プロパティは、テーブルのプライマリキーカラムの名前を示す文字列のリストを返します。
code: python
テーブルがプライマリキーを持たず、rowidテーブルである場合、このプロパティは['rowid']を返します。
.use_rowid
ほとんどすべての SQLite テーブルは rowid カラムを持っていますが、プライマリキーが明示的に定義されていないテーブルでは、個々の行を識別するためのプライマリキーとして rowid を使用しなければなりません。.use_rowidプロパティは、テーブルがこのようにrowidを使用する必要があるかどうかをチェックします。テーブルが明示的に定義されたプライマリキーを持たない場合は True を、そうでない場合は False を返します。
code: python
False
.foreign_keys
.foreign_keysプロパティは、テーブルの外部キー関係を、ForeignKey(table, column, other_table, other_column)のようにカラム名のタプルのリストとして返します。これは、ビューでは使用することはできません。
code: python
[ForeignKey(table='Street_Tree_List', column='qLegalStatus', other_table='qLegalStatus', other_column='id'),
ForeignKey(table='Street_Tree_List', column='qCareAssistant', other_table='qCareAssistant', other_column='id'),
ForeignKey(table='Street_Tree_List', column='qSiteInfo', other_table='qSiteInfo', other_column='id'),
ForeignKey(table='Street_Tree_List', column='qSpecies', other_table='qSpecies', other_column='id'),
ForeignKey(table='Street_Tree_List', column='qCaretaker', other_table='qCaretaker', other_column='id'),
ForeignKey(table='Street_Tree_List', column='PlantType', other_table='PlantType', other_column='id')]
.schema
.schema プロパティは、テーブルのスキーマを SQL 文字列として出力します。
code: python
CREATE TABLE "Street_Tree_List" (
"TreeID" INTEGER,
"qLegalStatus" INTEGER,
"qSpecies" INTEGER,
"qAddress" TEXT,
"SiteOrder" INTEGER,
"qSiteInfo" INTEGER,
"PlantType" INTEGER,
"qCaretaker" INTEGER,
"qCareAssistant" INTEGER,
"PlantDate" TEXT,
"DBH" INTEGER,
"PlotSize" TEXT,
"PermitNotes" TEXT,
"XCoord" REAL,
"YCoord" REAL,
"Latitude" REAL,
"Longitude" REAL,
"Location" TEXT
,
FOREIGN KEY ("PlantType") REFERENCES PlantType(id), FOREIGN KEY ("qCaretaker") REFERENCES qCaretaker(id), FOREIGN KEY ("qSpecies") REFERENCES qSpecies(id), FOREIGN KEY ("qSiteInfo") REFERENCES qSiteInfo(id), .indexes
.indexesプロパティは、テーブルに作成されたすべてのインデックスを、Index(seq, name, unique, origin, partial, columns)というようなカラム名のタプルのリストとして返します。これは、ビューでは利用することができません。
code: python
[Index(seq=0, name='"Street_Tree_List_qLegalStatus"', unique=0, origin='c', partial=0, columns='qLegalStatus'), Index(seq=1, name='"Street_Tree_List_qCareAssistant"', unique=0, origin='c', partial=0, columns='qCareAssistant'), Index(seq=2, name='"Street_Tree_List_qSiteInfo"', unique=0, origin='c', partial=0, columns='qSiteInfo'), Index(seq=3, name='"Street_Tree_List_qSpecies"', unique=0, origin='c', partial=0, columns='qSpecies'), Index(seq=4, name='"Street_Tree_List_qCaretaker"', unique=0, origin='c', partial=0, columns='qCaretaker'), Index(seq=5, name='"Street_Tree_List_PlantType"', unique=0, origin='c', partial=0, columns='PlantType')] .xindexes
.xindexes プロパティは、SQLiteのindex_xinfo()メカニズムを使用して、テーブル上のインデックスに関するより詳細な情報を返します。このプロパティは、XIndex(name, columns) というようなカラム名のタプルのリストを返します。columns は XIndexColumn(seqno, cid, name, desc, coll, key) というカラム名のタプルのリストです。
code: python
[
XIndex(
name='idx_ny_times_us_counties_date',
columns=[
XIndexColumn(seqno=0, cid=0, name='date', desc=1, coll='BINARY', key=1),
XIndexColumn(seqno=1, cid=-1, name=None, desc=0, coll='BINARY', key=0)
]
),
XIndex(
name='idx_ny_times_us_counties_fips',
columns=[
XIndexColumn(seqno=0, cid=3, name='fips', desc=0, coll='BINARY', key=1),
XIndexColumn(seqno=1, cid=-1, name=None, desc=0, coll='BINARY', key=0)
]
)
]
.triggers
.triggers プロパティは、データベースのトリガーを一覧表示します。これは、データベース オブジェクトとテーブル オブジェクトの両方で使用できます。これは、Trigger(name, table, sql) という名前のタプルのリストを返します。
code: python
[Trigger(name='authors_ai', table='authors', sql='CREATE TRIGGER authors_ai AFTER INSERT...'), Trigger(name='authors_ad', table='authors', sql="CREATE TRIGGER authors_ad AFTER DELETE..."), Trigger(name='authors_au', table='authors', sql="CREATE TRIGGER authors_au AFTER UPDATE")] db.triggers
.triggers_dict
.triggers_dictプロパティは、そのテーブルのトリガーを、その名前とSQL定義をマッピングした辞書として返します。
code: python
{'authors_ai': 'CREATE TRIGGER authors_ai AFTER INSERT...', 'authors_ad': 'CREATE TRIGGER authors_ad AFTER DELETE...', 'authors_au': 'CREATE TRIGGER authors_au AFTER UPDATE'} 同じプロパティがデータベースにも存在し、すべてのテーブルのすべてのトリガーを返します。
code: python
db.triggers_dict
{'authors_ai': 'CREATE TRIGGER authors_ai AFTER INSERT...', 'authors_ad': 'CREATE TRIGGER authors_ad AFTER DELETE...', 'authors_au': 'CREATE TRIGGER authors_au AFTER UPDATE'} .detect_fts()
detect_fts() メソッドは、このテーブルに関連する SQLite FTS テーブル名が存在する場合にはそれを返します。そのテーブルがフルテキスト検索用に設定されていない場合は None を返します。
code: python
"authors_fts"
.virtual_table_using
.virtual_table_usingプロパティは、テーブルが仮想テーブルであるかどうかを示します。通常のテーブルの場合はNoneを返し、それ以外の場合は仮想テーブルのタイプの大文字を返します。例えば、以下のようになります。
code: python
"FTS5"
.has_counts_triggers
.has_counts_triggers プロパティは、_counts テーブルを更新するトリガーがテーブルに設定されているかどうかを示します。
code: python
False
True
フルテキスト検索
SQLiteには強力なフルテキスト検索を実装したエクステンションがバンドルされています。
テーブルのフルテキスト検索の有効化
テーブルのフルテキスト検索を有効にするには、.enable_fts(columns)を使用します。
code: python
そして、.search() メソッドを使って検索を行うことができます。
code: python
rows = list(db"dogs".search("cleo")) このメソッドは、Listing rows と同様に各行の辞書を取得するためにループすることができるジェネレータを返します。
テーブルに追加のレコードを挿入した場合は、 populate_fts() を使用して検索インデックスを更新する必要があります。
code: python
"id": 2,
"name": "Marnie",
"twitter": "MarnieTheDog",
"age": 16,
"is_good_dog": True,
}, pk="id")
より良い解決策は、データベーストリガーを使用することです。create_triggers=True を使用して、フルテキストインデックスを自動的に更新するデータベーストリガーを設定することができます。
code: python
.enable_fts() のデフォルトは、FTS5を使用することになっています。代わりにFTS4を使用したい場合は、以下のようにしてください。
code: python
tokenize= 引数を使用して、テーブルに設定されるトークナイザをカスタマイズできます。たとえば、"running" のような英単語が "run" のようなステム処理された代替単語と一致するように、Porter のステム処理を有効にするには、tokenize="porter" を使用します。
code: python
SQLiteのドキュメントでは、FTS5のトークナイザーとFTS4のトークナイザーについて詳しく説明されていますが、ポーターはどちらでも有効なオプションです。
すでに存在するFTSテーブルを設定しようとすると、 sqlite3.OperationalError 例外が発生します。
既存のテーブルを新しい構成で置き換えるには、replace=Trueを与えます。
code: python
これは、FTSテーブルが既に存在している場合には影響がありませんが、そうでない場合には、新しい設定でテーブルを削除して再作成します。これは、カラム、トークナイザー、使用しているFTSのバージョン、テーブルにトリガーがあるかどうかを考慮します。
作成したFTSテーブルやトリガーを削除するには、table.disable_fts()メソッドを使用します。
code: python
検索で使用する引用文字
SQLite は高度な検索クエリ構文をサポートしています。.のような文字は特殊な意味を持つため、ユーザが入力した文字列を検索する際にエラーが発生する可能性があるからです。
db.quote_fts(query) メソッドは、SQLite フルテキスト検索のクウォートを適用したクエリーを返します。これにより、そのクエリーを検索に使用しても安全になります。
code: python
db.quote_fts("Search term.")
# Returns: '"Search" "term."'
table.search()による検索
table.search(q) メソッドは、検索フレーズqにマッチする行を表すPython辞書のジェネレーターを、関連性の高い結果から順に返します。
code: python
print(article)
また、.search() メソッドには、以下のオプションの引数を指定できます。
order_by string
並べ替えの対象となるカラム。デフォルトは relevance score です。オプションで desc を含めることができます。たとえば rowid desc です。
columns 文字列の配列
返すべきカラム。デフォルトではすべてのカラムを返します。
imit interger
返すべき結果の数。デフォルトではすべての結果を返します。
offset integer
limit パラメータに加えて使用するオフセット。
quote bool
FTS クォート規則を検索クエリに適用し、驚くようなエラーを回避する方法で高度なクエリ構文を無効にします。
dog "にマッチした3件の検索結果のうち、タイトルとパブリッシュされたカラムのみを、パブリッシュされた順に、最新のものから順に返すには、次のようにします。
code: python
"dog",
order_by="published desc",
limit=3,
):
print(article)
table.search_sql() による SQL クエリの作成
table.search_sql() メソッドを使用すると、検索に使用されるSQLクエリを生成することができます。このメソッドは table.search() と同じ引数を取りますが、検索クエリ自体は例外で、返される SQL には検索に使用できるパラメータが含まれているからです。
code: python
出力は次のようになります。
code: python
with original as (
select
rowid,
)
select
from
where
order by
このメソッドは、SQLiteテーブルがFTS4またはFTS5を使用しているかどうかを検出し、検索タイプに応じて関連性の高い順に並べるための正しいSQLを出力します。
FTS4の出力は以下のようになります。
これは、sqlite-fts4 のカスタム SQL 関数である rank_bm25() を使用しています。このメソッドを使用して、データベース接続に対してそのカスタム関数を登録することができます。
code: python
db.register_fts4_bm25()
フルテキスト検索テーブルの再構築
テーブルを再構築するには table.rebuild_fts() メソッドを使用します。これは、テーブルの構成が変更された場合や、 インデックス化されたデータが何らかの理由で破損した場合などに便利です。
code: python
このメソッドは、フルテキスト検索用に設定されたテーブル（この例ではdogs）に対して呼び出すことも、_ftsテーブルに対して直接呼び出すこともできます。
code: python
これにより、以下のようなSQLが実行されます。
code: SQL
INSERT INTO dogs_fts (dogs_fts) VALUES ("rebuild");
フルテキスト検索テーブルの最適化
FTSテーブルにデータを入力したら、次のように最適化してサイズを劇的に小さくすることができます。
code: python
これにより、以下のようなSQLが実行されます。
code: SQL
INSERT INTO dogs_fts (dogs_fts) VALUES ("optimize");
トリガーを使ったテーブルカウントのキャッシュ
SQLiteの select count(*) クエリは、プライマリキーインデックスのフルスキャンを必要とし、テーブルが大きくなると次第に時間がかかるようになることがあります。
table.enable_counts() メソッドを使用すると、 _counts `テーブルのレコードを継続的に更新するようにトリガを設定することができます。この値を使用して、関連するテーブルの行数をすばやく取得することができます。
code: python
これにより、_counts テーブルがまだ存在していなければ、次のようなスキーマで作成されます。
code: SQL
)
データベースの enable_counts() メソッドを使用すると、データベース内のすべてのテーブル (仮想テーブルと _counts テーブル自体を除く) に対してキャッシュカウントを有効にすることができます。
code: python
db.enable_counts()
この機能を有効にすると、テーブルのカウントは _counts テーブルに保存されます。カウントレコードは、テーブルにレコードが追加・削除された際に トリガーによって自動的に最新の状態に保たれます。
これらのカウントにアクセスするには、_counts テーブルに直接問い合わせるか、db.cached_counts() メソッドを使用することができます。このメソッドは、テーブルとそのカウント数をマッピングした辞書を返します。
code: python
db.cached_counts()
{'global-power-plants': 33643,
'global-power-plants_fts_data': 136,
'global-power-plants_fts_idx': 199,
'global-power-plants_fts_docsize': 33643,
'global-power-plants_fts_config': 1}
このメソッドにテーブル名のリストを渡すと、それらのカウントだけを取得することができます。
code: python
{'global-power-plants': 33643}
table.count プロパティは、db.use_counts_table プロパティが True に設定されていない限り、デフォルトでは、select count(*) クエリを実行します。
use_counts_tableをTrueに設定するには、データベース・オブジェクトをインスタンス化するときに行います。
code: python
db = Database("global-power-plants.db", use_counts_table=True)
このプロパティが True の場合、table.count プロパティへのすべての呼び出しは、まず _counts テーブルにキャッシュされたカウントを見つけようとし、値が利用できない場合やテーブルが存在しない場合は count(*) クエリにフォールバックします。
データベースやテーブルオブジェクトで .enable_counts() メソッドを呼び出すと、そのデータベースオブジェクトが存続する限り use_counts_table が True に設定されます。
もし _counts テーブルが実際のテーブルカウントと同期しなくなった場合は、 .reset_counts() メソッドを使用して修復することができます。
code: python
db.reset_counts()
インデックスの作成
テーブルにインデックスを作成するには、.create_index(columns) メソッドを使用します。このメソッドは、カラムのリストを受け取ります。
code: python
デフ ォル ト では、 イ ンデ ッ ク スは idx_{table-name}_{columns} とい う 名前にな り ますが、 作成 さ れ る イ ンデ ッ ク スの名前をカ ス タ マ イ ズ し たい場合は、 index_name 引数を与え る こ と がで き ます。
code: python
index_name="good_dogs_by_age"
)
ある列の降順でインデックスを作成するには、次のようにdb.DescIndex()で列名をラップします。
code: python
from sqlite_utils.db import DescIndex
index_name="good_dogs_by_age"
)
unique=True を指定すると、ユニークなインデックスを作成することができます。
code: python
if_not_exists=Trueを使うと、その名前のインデックスがすでに存在していても何もしません。
バキューム(VACUUM)
以下のようにVACUUMを実行することで、データベースを最適化することができます。
code: python
Database("my_database.db").vacuum()
WALモード
.enable_wal()を使って、データベースのWAL（Write-Ahead Logging）を有効にすることができます。
code: python
Database("my_database.db").enable_wal()
WALモードを無効にするには、.disable_wal() を使用します。
code: python
Database("my_database.db").disable_wal()
データベースの現在のジャーナル・モードは、journal_mode プロパティで確認できます。
code: python
journal_mode = Database("my_database.db").journal_mode
通常はwalまたはdelete（WALが無効の場合）ですが、他の値を指定することもできます。
カラムタイプの提案
挿入またはアップサートされた Python 辞書のリストのために新しいテーブルを作成した場合、これらのメソッドは、渡されたデータに基づいてデータベースのカラムのための正しい型を検出します。
いくつかの状況では、作成されるカラムを何らかの方法でカスタマイズするために、このプロセスに介入する必要があるかもしれません。
テーブルの .create() メソッドは、カラム名と格納すべき Python タイプをマッピングした辞書を受け取ります。
code: python
"id": int,
"name": str,
"weight": float,
})
suggest_column_types() ヘルパー関数を使用すると、 レコードのリストからカラム名と型の辞書を作成し、 table.create() に渡すことができます。
たとえば、以下のようになります。
code: python
from sqlite_utils import Database, suggest_column_types
cats = [{
"id": 1,
"name": "Snowflake"
}, {
"id": 2,
"name": "Crabtree",
"age": 4
}]
types = suggest_column_types(cats)
# types now looks like this:
# {"id": <class 'int'>,
# "name": <class 'str'>,
# "age": <class 'int'>}
# Manually add an extra field:
# types now looks like this:
# {"id": <class 'int'>,
# "name": <class 'str'>,
# "age": <class 'int'>,
# "thumbnail": <class 'bytes'>}
# Create the table
db = Database("cats.db")
db"cats".create(types, pk="id") # Insert the records
# list(db"cats".rows) now returns: # [{"id": 1, "name": "Snowflake", "age": None, "thumbnail": None}
# {"id": 2, "name": "Crabtree", "age": 4, "thumbnail": None}]
# The table schema looks like this:
# id INTEGER PRIMARY KEY, # )
SpatiaLiteの検索
find_spatialite() 関数は、いくつかの一般的な場所でSpatiaLite SQLite拡張を検索します。その場所へのパスを文字列で返し、SpatiaLite が見つからない場合は None を返します。
この関数は、次のようなコードで使用できます。
code: python
from sqlite_utils import Database
from sqlite_utils.utils import find_spatialite
db = Database("mydb.db")
spatialite = find_spatialite()
if spatialite:
db.conn.enable_load_extension(True)
db.conn.load_extension(spatialite)
カスタム SQL 関数の登録
SQLiteはPythonで書かれたカスタムSQL関数の登録をサポートしています。db.register_function() メソッドは、これらの関数を登録することができ、また、既に登録された関数を追跡します。
また、既に登録されている関数を追跡しています。メソッドとして使用する場合は、関数が必要とする引数の名前と数を自動的に検出します。
code: python
from sqlite_utils import Database
db = Database(memory=True)
def reverse_string(s):
return "".join(reversed(list(s)))
db.register_function(reverse_string)
print(db.execute('select reverse_string("hello")').fetchone()0) # This prints "olleh"
このメソッドは、次のように関数のデコレーターとしても使用できます。
code: python
@db.register_function
def reverse_string(s):
return "".join(reversed(list(s)))
print(db.execute('select reverse_string("hello")').fetchone()0) Python 3.8 では、決定論的な SQLite 関数を登録する機能が追加されました。これにより、関数がどのような入力に対しても全く同じ結果を返すことを示すことができ、その結果 SQLite はパフォーマンスの最適化を行うことができます。次のように deterministic=True を使って関数を決定論的にマークすることができます。
code: python
@db.register_function(deterministic=True)
def reverse_string(s):
return "".join(reversed(list(s)))
Python 3.8 以前のバージョンで実行した場合、コードは動作しますが、 deterministic=True パラメータは無視されます。
デフォルトでは、同じ名前と引数の数を持つ関数を登録しても何の効果もありません。データベースインスタンスは既に登録された関数を追跡しており、@db.register_functionデコレータが2回目に呼ばれた場合は登録をスキップします。
登録されている関数を意図的に新しい実装に置き換えたい場合は、replace=True 引数を使用します。
code: python
@db.register_function(deterministic=True, replace=True)
def reverse_string(s):
ユーザー定義関数の内部で発生した例外は、デフォルトで次のようなエラーを返します。
code: python
Unexpected error: user-defined function raised exception
カスタム関数が実行される前に以下を実行することで、sqlite3がカスタム関数からのトレースバックを含む、より有用なエラーを返すようにすることができます。
code: python
from sqlite_utils.utils import sqlite3
sqlite3.enable_callback_tracebacks(True)
カスタム関数が実行される前に以下を実行することで、sqlite3がカスタム関数からのトレースバックを含む、より有用なエラーを返すようにすることができます。
code: python
db = Database(memory=True)
db.quote("hello")
"'hello'"
db.quote("hello'this'has'quotes")
"'hello''this''has''quotes'"