sqlite-utilsを使ってみよう(コマンドライン)

sqlite-utils について

slite-utils はSQLite のデータベースをいろいろな方法で操作することができるコマンドラインツールとPythonユーティリティー関数を含むライブラリです。

qlite-utils は完全な ORM を意図したものではありません。データベースを作成し、そこにデータを入力することを可能な限り生産的にするための助けとなるツールです。

ここでは、sqlite-utils のコマンドラインツールの使用方法について説明してゆきます。

インストール

PyPIのsqlite-utilsパッケージには，sqlite_utils Pythonライブラリとsqlite-utilsコマンドラインツールが含まれています。pipを使って以下のようにインストールできます。

code: bash

$ pip install sqlite-utils

ヘルプ表示

code: bash

% sqlite-utils --help

Commands for interacting with a SQLite database

Options:

--version Show the version and exit.

-h, --help Show this message and exit.

Commands:

query* Execute SQL query and return the results as JSON

add-column Add a column to the specified table

add-foreign-key Add a new foreign key constraint to an existing table.

add-foreign-keys Add multiple new foreign key constraints to a...

analyze-tables Analyze the columns in one or more tables

convert Convert columns using Python code you supply.

create-index Add an index to the specified table covering the...

create-table Add a table with the specified columns.

create-view Create a view for the provided SELECT query

disable-fts Disable full-text search for specific table

disable-wal Disable WAL for database files

drop-table Drop the specified table

drop-view Drop the specified view

dump Output a SQL dump of the schema and full contents...

enable-counts Configure triggers to update a _counts table with...

enable-fts Enable full-text search for specific table and columns

enable-wal Enable WAL for database files

extract Extract one or more columns into a separate table

index-foreign-keys Ensure every foreign key column has an index on it.

indexes Show indexes for this database

insert Insert records from JSON file into a table,...

insert-files Insert one or more files using BLOB columns in the...

memory Execute SQL query against an in-memory database,...

optimize Optimize all full-text search tables and then run...

populate-fts Re-populate full-text search for specific table and...

rebuild-fts Rebuild all or specific full-text search tables

reset-counts Reset calculated counts in the _counts table

rows Output all rows in the specified table

schema Show full schema for this database or for specified...

search Execute a full-text search against this table

tables List the tables in the database

transform Transform a table beyond the capabilities of ALTER...

triggers Show triggers configured in this database

upsert Upsert records based on their primary key.

vacuum Run VACUUM against the database

views List the views in the database

これを見てわかるように、SQLite データベースへの操作のほとんどを、コマンドラインから実行できるようになります。

この資料では SQlite-utils の使い方を説明しますが、これはSQLite の持っている機能について説明していることと同じです。

SQLクエリの実行

sqlite-utils の query サブコマンドを使用すると、SQLiteデータベースファイルに対して直接クエリを実行することができます。これはデフォルトのサブコマンドなので、以下の2つの例では同じように動作します。

code: python

$ sqlite-utils query dogs.db "select * from dogs"

$ sqlite-utils dogs.db "select * from dogs"

code: bash

% sqlite-utils example.sqlite "SELECT name FROM sqlite_master WHERE type='table';"

queryサブコマンドのヘルプの表示は、次のように実行します。

code: bash

% sqlite-utils query --help

Execute SQL query and return the results as JSON

Options:

--attach <TEXT FILE>... Additional databases to attach - specify alias

and filepath

--nl Output newline-delimited JSON

--arrays Output rows as arrays instead of objects

--csv Output CSV

--tsv Output TSV

--no-headers Omit CSV headers

-t, --table Output as a table

--fmt TEXT Table format - one of fancy_grid, fancy_outline,

github, grid, html, jira, latex, latex_booktabs,

latex_longtable, latex_raw, mediawiki, moinmoin,

orgtbl, pipe, plain, presto, pretty, psql, rst,

simple, textile, tsv, unsafehtml, youtrack

--json-cols Detect JSON cols and output them as JSON, not

escaped strings

-r, --raw Raw output, first column of first row

-p, --param <TEXT TEXT>... Named :parameters for SQL query

--load-extension TEXT SQLite extensions to load

-h, --help Show this message and exit.

JSONを返す

クエリで返されるデフォルトのフォーマットはJSONです。

code: bash

% sqlite-utils example.sqlite "SELECT * FROM stocks;"

[{"date": "2020-03-06", "trans": "BUY", "symbol": "GOOG", "qty": 100.0, "price": 1298.41},

{"date": "2020-03-09", "trans": "BUY", "symbol": "AAPL", "qty": 100.0, "price": 288.06}]

% sqlite-utils example.sqlite "SELECT * FROM stocks;" | python -m json.tool

[

{

"date": "2020-03-06",

"trans": "BUY",

"symbol": "GOOG",

"qty": 100.0,

"price": 1298.41

},

{

"date": "2020-03-09",

"trans": "BUY",

"symbol": "AAPL",

"qty": 100.0,

"price": 288.06

}

]

改行されたJSON

出力がリストを表示するのではなく、リストの各要素を表示することになります。

code: bash

% sqlite-utils example.sqlite "SELECT * FROM stocks;" --nl

{"date": "2020-03-06", "trans": "BUY", "symbol": "GOOG", "qty": 100.0, "price": 1298.41}

{"date": "2020-03-09", "trans": "BUY", "symbol": "AAPL", "qty": 100.0, "price": 288.06}

JSONの配列

出力がオブジェクトではなくて、それぞれの属性がもっている値を、リストとして出漁するようになります。

code: bash

% sqlite-utils example.sqlite "SELECT * FROM stocks;" --arrays

code: bash

% sqlite-utils example.sqlite "SELECT * FROM stocks;" --arrays --nl

JSONでのバイナリデータ

バイナリ文字列は有効なJSONではありませんので、バイナリデータを含むBLOBカラムは、base64エンコードされたデータを含むJSONオブジェクトとして返され、以下のようになります。

code: bash

$ sqlite-utils dogs.db "select name, content from images" | python -mjson.tool

[

{

"name": "transparent.gif",

"content": {

"$base64": true,

"encoded": "R0lGODlhAQABAIAAAAAAAP///yH5BAEAAAAALAAAAAABAAEAAAIBRAA7"

}

}

]

ネストされたJSONの値

カラムの1つにJSONが含まれている場合、デフォルトではエスケープされた文字列として返されます。

code: bash

$ sqlite-utils dogs.db "select * from dogs" | python -mjson.tool

[

{

"id": 1,

"name": "Cleo",

}

]

code: bash

$ sqlite-utils dogs.db "select * from dogs" --json-cols | python -mjson.tool

[

{

"id": 1,

"name": "Cleo",

"friends": [

{

"name": "Pancakes"

},

{

"name": "Bailey"

}

]

}

]

CSVまたはTSVを返す

code: bash

% sqlite-utils example.sqlite "SELECT * FROM stocks;" --csv

date,trans,symbol,qty,price

2020-03-06,BUY,GOOG,100.0,1298.41

2020-03-09,BUY,AAPL,100.0,288.06

code: bash

% sqlite-utils example.sqlite "SELECT * FROM stocks;" --csv --no-headers

2020-03-06,BUY,GOOG,100.0,1298.41

2020-03-09,BUY,AAPL,100.0,288.06

code: bash

% sqlite-utils example.sqlite "SELECT * FROM stocks;" --tsv

date trans symbol qty price

2020-03-06 BUY GOOG 100.0 1298.41

2020-03-09 BUY AAPL 100.0 288.06

% sqlite-utils example.sqlite "SELECT * FROM stocks;" --tsv --no-headers

2020-03-06 BUY GOOG 100.0 1298.41

2020-03-09 BUY AAPL 100.0 288.06

表形式での出力

code: bash

% sqlite-utils example.sqlite "SELECT * FROM stocks;" --table

date trans symbol qty price

---------- ------- -------- ----- -------

2020-03-06 BUY GOOG 100 1298.41

2020-03-09 BUY AAPL 100 288.06

code: bash

% sqlite-utils example.sqlite "SELECT * FROM stocks;" --table --fmt rst

========== ======= ======== ===== =======

date trans symbol qty price

========== ======= ======== ===== =======

2020-03-06 BUY GOOG 100 1298.41

2020-03-09 BUY AAPL 100 288.06

========== ======= ======== ===== =======

% sqlite-utils example.sqlite "SELECT * FROM stocks;" --table --fmt github

| date | trans | symbol | qty | price |

|------------|---------|----------|-------|---------|

| 2020-03-06 | BUY | GOOG | 100 | 1298.41 |

| 2020-03-09 | BUY | AAPL | 100 | 288.06 |

バイナリコンテンツなどの生データを返す

例えば、BLOBカラムからバイナリ画像を取得してファイルに保存するには次のようにします。

code: bash

$ sqlite-utils photos.db "select contents from photos where id=1" --raw > myphoto.jpg

名前付きパラメータの使用

code: bash

% sqlite-utils query dogs.db "select :num * :num2" -p num 5 -p num2 6

これらはSQLクエリの中で正しく引用され、エスケープされるので、他の値を安全にSQLと組み合わせることができます。

UPDATE、INSERT、DELETE

UPDATE、INSERT、DELETEのクエリを実行すると、コマンドは影響を受けたレコードの数を返します。

code: bash

% sqlite-utils example.sqlite "UPDATE stocks SET qty = 200 WHERE symbol=='GOOG';"

% sqlite-utils example.sqlite "SELECT * FROM stocks WHERE symbol=='GOOG';"

SQLite 拡張モジュール

このオプションは複数回適用して、複数の拡張機能をロードすることができます。

SpatiaLiteはSQLiteと一緒によく使われるので、spatialiteという値は特別です。これは、最も一般的なインストール場所でSpatiaLiteを検索するので、そのモジュールがどこにあるかを正確に覚えておく必要がありません。

code: bash

$ sqlite-utils dogs.db "select spatialite_version()" --load-extension=spatialite

追加のデータベースをアタッチする

SQLite は、複数のデータベースファイル内のテーブルのデータを結合することができる、クロスデータベース SQL クエリをサポートしています。

この例では、books.dbデータベースをbooksというエイリアスでアタッチし、そのデータベースとデフォルトのdogs.dbデータベースのデータを結合するクエリを実行しています。

code: bash

$ sqlite-utils dogs.db --attach books books.db \

'select * from sqlite_master union all select * from books.sqlite_master'

インメモリデータベースを使用したデータへの直接クエリ実行

sqlite-utils memoryコマンドはsqlite-utils queryと似た動作をしますが、インメモリデータベースに対してクエリを実行することができます。

このコマンドにCSVファイルやJSONファイルを渡すと、一時的なインメモリテーブルに読み込まれ、データをSQLiteに変換する別のステップを経ることなく、そのデータに対してSQLを実行することができます。

追加の引数がない場合、このコマンドはインメモリ・データベースに対して直接SQLを実行します。

code: bash

% sqlite-utils memory 'select sqlite_version()'

code: bash

% sqlite-utils memory 'select sqlite_version()' --table --fmt grid

+--------------------+

| sqlite_version() |

+====================+

| 3.36.0 |

+--------------------+

CSV または JSON に対して直接クエリを実行

CSVやJSON形式のデータがあれば、インメモリのSQLiteデータベースにロードして、sqlite-utils memory を使って、次のように1つのコマンドで直接クエリを実行することができます。

code: bash

$ sqlite-utils memory data.csv "select * from data"

異なるファイルのデータ間で結合を実行したい場合は、コマンドに複数のファイルを渡すことができます。

code: bash

$ sqlite-utils memory one.csv two.json "select * from one join two on one.id = two.other_id"

データがJSONの場合は、sqlite-utilsのinsertコマンドでサポートされているのと同じ形式でなければなりません。つまり、単一のJSONオブジェクト（単一の行として扱われる）またはJSONオブジェクトのリストです。

CSVデータは、カンマまたはタブで区切られています。

code: bash

$ sqlite-utils memory example.csv "select * from t"

code: bash

% cat stock.csv | sqlite-utils memory - "SELECT * FROM stdin;"

[{"date": "2020-03-06", "trans": "BUY", "symbol": "GOOG", "qty": 200.0, "price": 1298.41},

{"date": "2020-03-09", "trans": "BUY", "symbol": "AAPL", "qty": 100.0, "price": 288.06}]

入力したCSVデータは、utf-8を使用しているとみなされます。別の文字エンコーディングを使用する場合は、

code: bash

$ cat example.csv | sqlite-utils memory - "select * from stdin" --encoding=latin-1

複数のCSVファイルを結合する場合は、すべてのCSVファイルで同じエンコーディングを使用する必要があります。

フォーマットの明示的な指定

デフォルトでは、sqlite-utils memoryは入力データ形式（JSON、TSV、CSV）を自動的に検出しようとします。

code: bash

$ sqlite-utils memory one.dat:csv two.dat:nl "select * from one union select * from two"

ここでは、one.datの内容はCSVとして、two.datの内容は改行付きJSONとして扱われます。

標準入力でツールにパイプされたデータのフォーマットを明示的に指定するには、stdin:formatを使用します。

code: bash

$ cat one.dat | sqlite-utils memory stdin:csv "select * from stdin"

インメモリデータと既存のデータベースとの結合

code: bash

$ echo "id\n1\n3\n5" | sqlite-utils memory - --attach trees trees.db \

"select * from trees.trees where rowid in (select id from stdin)"

--schema、--analyze、--dump、-save

code: bash

$ sqlite-utils memory dogs.csv --schema

);

code: bash

$ sqlite-utils memory dogs.csv --analyze

dogs.id: (1/3)

Total rows: 2

Null rows: 0

Blank rows: 0

Distinct values: 2

dogs.name: (2/3)

Total rows: 2

Null rows: 0

Blank rows: 0

Distinct values: 2

dogs.age: (3/3)

Total rows: 2

Null rows: 0

Blank rows: 0

Distinct values: 2

code: bash

$ sqlite-utils memory dogs.csv --dump

BEGIN TRANSACTION;

);

INSERT INTO "dogs" VALUES('1','4','Cleo');

INSERT INTO "dogs" VALUES('2','2','Pancakes');

COMMIT;

code: bash

$ sqlite-utils memory dogs.csv --save dogs.db

これらの機能は、主にデバッグツールとして使用することを目的としています。

テーブル内のすべてのレコードを返す

rowsコマンドを使うと、指定したテーブルのすべての行を返すことができます。

code: bash

$ sqlite-utils rows dogs.db dogs

[{"id": 1, "age": 4, "name": "Cleo"},

{"id": 2, "age": 2, "name": "Pancakes"}]

このコマンドは、queryと同じ出力オプションを受け付けます。

code: bash

$ sqlite-utils rows dogs.db dogs -c age -c name

[{"age": 4, "name": "Cleo"},

{"age": 2, "name": "Pancakes"}]

テーブルの一覧表示

データベース内のテーブル名を一覧表示するには、tablesコマンドを使用します。

code: bash

$ sqlite-utils tables mydb.db

[{"table": "dogs"},

{"table": "cats"},

{"table": "chickens"}]

code: bash

$ sqlite-utils tables mydb.db --csv --no-headers

dogs

cats

chickens

code: bash

$ sqlite-utils tables docs.db --fts4

SQLite3 での FTS

code: bash

$ sqlite-utils tables mydb.db --counts

[{"table": "dogs", "count": 12},

{"table": "cats", "count": 332},

{"table": "chickens", "count": 9}]

code: bash

$ sqlite-utils tables dogs.db --counts --columns

code: bash

$ sqlite-utils tables dogs.db --schema --table

table schema

------- -----------------------------------------------

Gosh CREATE TABLE Gosh (c1 text, c2 text, c3 text)

Gosh2 CREATE TABLE Gosh2 (c1 text, c2 text, c3 text)

ビューの一覧表示

views サブコマンドは、データベースに定義されているすべてのビューを表示します。

code: bash

$ sqlite-utils views sf-trees.db --table --counts --columns --schema

view count columns schema

--------- ------- -------------------- --------------------------------------------------------------

tablesコマンドと同じオプションを使用することができます。

--columns

--schema

--counts

--nl

--csv

--tsv

--table

インデックスの一覧表示

indexes サブコマンドは、データベースに設定されているインデックスを一覧表示します。

code: bash

$ sqlite-utils indexes covid.db --table

table index_name seqno cid name desc coll key

-------------------------------- ------------------------------------------------------ ------- ----- ----------------- ------ ------ -----

johns_hopkins_csse_daily_reports idx_johns_hopkins_csse_daily_reports_combined_key 0 12 combined_key 0 BINARY 1

johns_hopkins_csse_daily_reports idx_johns_hopkins_csse_daily_reports_country_or_region 0 1 country_or_region 0 BINARY 1

johns_hopkins_csse_daily_reports idx_johns_hopkins_csse_daily_reports_province_or_state 0 2 province_or_state 0 BINARY 1

johns_hopkins_csse_daily_reports idx_johns_hopkins_csse_daily_reports_day 0 0 day 0 BINARY 1

ny_times_us_counties idx_ny_times_us_counties_date 0 0 date 1 BINARY 1

ny_times_us_counties idx_ny_times_us_counties_fips 0 3 fips 0 BINARY 1

ny_times_us_counties idx_ny_times_us_counties_county 0 1 county 0 BINARY 1

ny_times_us_counties idx_ny_times_us_counties_state 0 2 state 0 BINARY 1

すべてのテーブルのインデックスを表示します。特定のテーブルのインデックスを表示するには、データベースの後にそのテーブルを列挙します。

code: bash

$ sqlite-utils indexes covid.db johns_hopkins_csse_daily_reports --table

このコマンドは、tablesやviewsコマンドと同じフォーマットオプションを使用します。

トリガーの表示

triggers サブコマンドは、データベースに設定されているすべてのトリガーを表示します。

code: bash

$ sqlite-utils triggers global-power-plants.db --table

name table sql

--------------- --------- -----------------------------------------------------------------

BEGIN

VALUES (

'plants',

COALESCE(

0

) + 1

);

END

デフォルトでは、すべてのテーブルのトリガーを表示します。1つまたは複数の特定のテーブルのトリガーを表示するには、引数としてその名前を渡します。

code: bash

$ sqlite-utils triggers global-power-plants.db plants

このコマンドは、tablesおよびviewsコマンドと同じフォーマットオプションを取ります。

スキーマの表示

schema サブコマンドは、データベースの完全なSQLスキーマを表示します。

code: bash

$ sqlite-utils schema dogs.db

CREATE TABLE "dogs" (

);

これにより、データベース内のすべてのテーブルとインデックスのスキーマが表示されます。指定したテーブルのサブセットだけのスキーマを表示するには、それらを追加の引数として渡します。

code: bash

$ sqlite-utils schema dogs.db dogs chickens

...

テーブルの分析

新しいデータベースを扱う際には、データの形状を把握することが有効です。sqlite-utilsのanalyze-tablesコマンドは、指定したテーブル（またはすべてのテーブル）を検査し、それらのテーブルの各列について有用な詳細を計算します。

github.dbデータベースのtagsテーブルを検査するには、次のように実行します。

code: bash

$ sqlite-utils analyze-tables github.db tags

tags.repo: (1/3)

Total rows: 261

Null rows: 0

Blank rows: 0

Distinct values: 14

Most common:

88: 107914493

75: 140912432

27: 206156866

Least common:

1: 209590345

2: 206649770

2: 303218369

tags.name: (2/3)

Total rows: 261

Null rows: 0

Blank rows: 0

Distinct values: 175

Most common:

10: 0.2

9: 0.1

7: 0.3

Least common:

1: 0.1.1

1: 0.11.1

1: 0.1a2

tags.sha: (3/3)

Total rows: 261

Null rows: 0

Blank rows: 0

Distinct values: 261

このツールは、各列について、NULL行の数、空白行（空の文字列を含む行）の数、異なる値の数、そして完全には異なる値ではない列については、最も一般的な値と最も一般的でない値を表示します。

テーブルを指定しない場合は、データベース内のすべてのテーブルが分析されます。

code: bash

$ sqlite-utils analyze-tables github.db

code: bash

$ sqlite-utils analyze-tables github.db tags -c sha

分析済みテーブルの詳細の保存

code: bash

$ sqlite-utils analyze-tables github.db --save

code: SQL

);

code: JSON

[

]

JSON データの挿入

JSON 形式のデータがあれば、sqlite-utils insert テーブル名 を使用してデータベースに挿入することができます。テーブルがまだ存在しない場合は、正しい（自動的に検出された）カラムでテーブルが作成されます。

単一のJSONオブジェクトまたはJSONオブジェクトのリストを、ファイル名として、または標準入力に直接パイプで（ファイル名に-を使用して）渡すことができます。

最もシンプルな例を示します。

code: bash

$ echo '{"name": "Cleo", "age": 4}' | sqlite-utils insert dogs.db dogs -

JSONのリストを与えると、複数のレコードを挿入します。例えば、dogs.jsonが以下のような場合。

code: JSON

[

{

"id": 1,

"name": "Cleo",

"age": 4

},

{

"id": 2,

"name": "Pancakes",

"age": 2

},

{

"id": 3,

"name": "Toby",

"age": 6

}

]

3つのレコードを自動的に作成された dogs テーブルにインポートし、idカラムをプライマリキーとして設定すると次のようになります。

code: bash

$ sqlite-utils insert dogs.db dogs dogs.json --pk=id

code: bash

$ sqlite-utils insert dogs.db dogs dogs.json --ignore

code: bash

$ sqlite-utils insert dogs.db dogs dogs.json --truncate

バイナリデータの挿入

バイナリデータをBLOBカラムに挿入するには、まずbase64でエンコードし、次のように構造化します。

code: JSON

[

{

"name": "transparent.gif",

"content": {

"$base64": true,

"encoded": "R0lGODlhAQABAIAAAAAAAP///yH5BAEAAAAALAAAAAABAAEAAAIBRAA7"

}

}

]

改行されたJSONの挿入

code: bash

| sqlite-utils insert nl-demo.db facetable - --pk=id --nl

これは、sqlite-utilsをパイプで繋ぎ、他のデータベースに対するSQLクエリの結果を含む新しいSQLiteデータベースファイルを簡単に作成することも意味します。

code: bash

$ sqlite-utils sf-trees.db \

"select TreeID, qAddress, Latitude, Longitude from Street_Tree_List" --nl \

| sqlite-utils insert saved.db trees - --nl

# saved.dbにtreesというテーブルが1つ作成される

$ sqlite-utils saved.db "select * from trees limit 5" --csv

TreeID,qAddress,Latitude,Longitude

141565,501X Baker St,37.7759676911831,-122.441396661871

232565,940 Elizabeth St,37.7517102172731,-122.441498017841

119263,495X Lakeshore Dr,,

207368,920 Kirkham St,37.760210314285,-122.47073935813

188702,1501 Evans Ave,37.7422086702947,-122.387293152263

ネストされたJSONオブジェクトの平坦化

sqlite-utilsのinsertは、入力データがJSONオブジェクトの配列で構成されていることを想定しており、各オブジェクトのトップレベルのキーが作成されたデータベーステーブルのカラムになります。

次の例では、log.jsonというファイルのドキュメントを考えてみましょう。

code: JSON

{

"httpRequest": {

"latency": "0.112114537s",

"requestMethod": "GET",

"requestSize": "534",

"status": 200

},

"insertId": "6111722f000b5b4c4d4071e2",

"labels": {

"service": "datasette-io"

}

}

code: SQL

);

code: SQL

);

CSVまたはTSVデータの挿入

code: bash

$ sqlite-utils insert dogs.db dogs docs.csv --csv

code: bash

$ sqlite-utils insert dogs.db dogs dogs.tsv --tsv

code: bash

$ sqlite-utils insert dogs.db dogs dogs.tsv --tsv --encoding=latin-1

ファイルからデータを挿入する際、プログレスバーが表示されます。このプログレスバーは、-silent オプションを使って隠すことができます。

例えば、次のような内容の creatures.csv ファイルがあるとします。

code: CSV

name,age,weight

Cleo,6,45.5

Dori,1,3.5

次のコマンドを実行します。

code: bash

$ sqlite-utils insert creatures.db creatures creatures.csv --csv --detect-types

次のようなスキームを出力します。

code: bash

$ sqlite-utils schema creatures.db

CREATE TABLE "creatures" (

);

code: bash

$ export SQLITE_UTILS_DETECT_TYPES=1

区切り文字と引用符の代替

code: bash

$ sqlite-utils insert dogs.db dogs dogs.csv --sniff

code: CSV

name;description

Cleo;|Very fine; a friendly dog|

Pancakes;A local corgi

このCSVファイルを読み込むときは次のようにコマンドを実行します。

code: bash

$ sqlite-utils insert dogs.db dogs dogs.csv --delimiter=";" --quotechar="|"

ヘッダ行のないCSVファイル

CSVファイルやTSVファイルの最初の行には、そのファイルのカラム名が含まれていることが期待されます。

このようにすると、テーブルは「untitled_1」「untitled_2」などのカラム名で作成されます。その後、sqlite-utils transform ... --renameコマンドを使用して、これらの名前を変更することができます。

データの挿入・置換

挿入置換は挿入と全く同じように動作しますが、データのプライマリキーが既存のレコードと一致する場合、そのレコードは新しいデータで置き換えられます。

上記のdogs.jsonの例を実行した後、次のように実行してみてください。

code: bash

$ echo '{"id": 2, "name": "Pancakes", "age": 3}' | \

sqlite-utils insert dogs.db dogs - --pk=id --replace

これにより、id=2(Pancakes)のレコードは、年齢が更新された新しいレコードに置き換えられます。

データのアップサーティング

アップサーティング(Upserting)は、更新または挿入を行います。指定されたプライマリキーを持つ行が存在する場合は、指定されたカラムが更新されます。行が存在しない場合は、行が作成されます。

例えば、以下のようになります。

code: bash

$ echo '{"id": 2, "age": 4}' | \

sqlite-utils upsert dogs.db dogs - --pk=id

これにより、id=2の犬の年齢が4に更新され、レコードが存在しない場合は新しいレコード（名前はnull）が作成されます。行が存在する場合は、名前はそのまま残されます。

テーブル上に存在しない列を参照すると、このコマンドは失敗します。存在しない列を自動的に作成するには、

ファイルからのデータの挿入

insert-filesコマンドは、ファイルの内容とそのメタデータをSQLiteのテーブルに挿入するために使用できます。

以下の例では、カレントディレクトリにあるすべてのGIFファイルをgifs.dbデータベースに挿入し、ファイルの内容をimagesテーブルに配置しています。

code: bash

$ sqlite-utils insert-files gifs.db images *.gif

また、1つまたは複数のディレクトリを指定することもでき、その場合は、それらのディレクトリ内のすべてのファイルが再帰的に追加されます。

code: bash

$ sqlite-utils insert-files gifs.db images path/to/my-gifs

デフォルトでは、このコマンドは次のようなスキーマを持つテーブルを作成します。

code: SQL

);

code: bash

$ sqlite-utils insert-files gifs.db images *.gif -c path -c md5 -c mtime --pk=path

これは次のようなスキーマを出力します。

code: SQL

);

code: bash

$ sqlite-utils insert-files gifs.db images *.gif \

-c path -c md5 -c last_modified:mtime --pk=path

使用できるカラム定義の全リストは次のとおりです。

name：ファイルの名前、例：cleo.jpg

path：ルートフォルダーからのファイルへの相対パス、例：pictures/cleo.jpg

fullpath：画像への完全に解決されたパス、例：/home/simonw/pictures/cleo.jpg

sha256：ファイルの内容のSHA256ハッシュ

md5：ファイルの内容のMD5ハッシュ

mode：ファイルのパーミッションビット（整数） - 8進数に変換するとよいでしょう。

content：BLOBとして保存されるバイナリファイルの内容。

content_text：テキストとして保存されるテキストファイルの内容。

mtime：ファイルの修正時間。Unixエポックからの浮動小数点秒数。

ctime：ファイルの作成時刻。Unixエポック以降の浮動小数点秒数で表されます。

mtime_int：修正時間を浮動小数点ではなく整数で表したもの

ctime_int：作成時刻を浮動小数点ではなく整数で表したもの。

mtime_iso：修正時刻をISOタイムスタンプで表したもの（例：2020-07-27T04:24:06.654246

ctime_iso：作成時刻をISOタイムスタンプで表したもの

size：ファイルのサイズをバイト数で表した整数

標準入力からパイプしたデータを挿入するには、次のようにします。

code: bash

$ cat dog.jpg | sqlite-utils insert-files dogs.db pics - --name=dog.jpg

引数-は、データを標準入力から読み込むことを示します。nameオプションで渡された文字列が、ファイル名とパスの値として使用されます。

標準入力からデータを挿入する場合、次のカラム定義のみがサポートされています：

データをカラムのデータに変換する

convertコマンドは、指定したカラムのデータを変換するために使用できます。たとえば、日付の文字列を解析してISOタイムスタンプに変換したり、タグの文字列をJSON配列に分割したりすることができます。

このコマンドは、データベース、テーブル、1つまたは複数のカラム、そしてそれらのカラムの値に対して実行されるPythonコードの文字列を受け取ります。次の例では、 articles テーブルの headline カラムの値を大文字に置き換えることができます。

code: bash

$ sqlite-utils convert content.db articles headline 'value.upper()'

Pythonコードは文字列として渡されます。そのPythonコードの中では、value変数は現在のカラムの値になります。

入力されたコードはvalueを1つの引数として受け取る関数にコンパイルされます。関数本体を複数の行に分割した場合、最後の行はreturn文になります。

code: bash

$ sqlite-utils convert content.db articles headline value = str(value)

return value.upper()'

code: bash

$ sqlite-utils convert content.db articles content \

'"\n".join(textwrap.wrap(value, 100))' \

--import=textwrap

変換は、指定されたテーブルのすべてのレコードに適用されます。WHERE句にマッチするレコードだけに制限するには、

code: bash

$ sqlite-utils convert content.db articles headline 'value.upper()' \

--where "headline like '%cat%'"

code: bash

$ sqlite-utils convert content.db articles headline 'value.upper()' \

--where "headline like :like" \

--param like '%cat%'

sqlite-utils 変換レシピ

一般的な操作のために、さまざまな組み込み関数が用意されています。

r.jsonsplit(value, delimiter=',', type=<class 'str'>)

r.jsonsplit(value, type=float)

以下のような配列になります。

[1.2, 3.0, 4.5]

r.parsedate(value, dayfirst=False, yearfirst=False)

r.Parsedatetime(value, dayfirst=False, yearfirst=False)

これらのレシピは、次のようにsqlite-utils convertに渡されるコードで使用できます。

code: bash

$ sqlite-utils convert my.db mytable mycolumn \

'r.jsonsplit(value)'

ドキュメントに記載されているパラメータを使用するには、次のようにします。

code: bash

$ sqlite-utils convert my.db mytable mycolumn \

'r.jsonsplit(value, delimiter=":")'

変換結果を別のカラムへの保存

code: bash

$ sqlite-utils convert content.db articles headline 'value.upper()' \

--output headline_upper

code: bash

$ sqlite-utils convert content.db articles id 'float(value) + 0.5' \

--output id_as_a_float \

--output-type float

1つの列を複数の列に変換する

1つのカラムを複数の派生カラムに変換したい場合があります。例えば、緯度と経度の値を含むlocation列があり、これを別々の緯度と経度の列に分割したい場合があります。

latitude,longitudeの例では以下のようになります。

code: bash

$ sqlite-utils convert demo.db places location \

'bits = value.split(",")

return {

}' --multi

返された値の型は、新しいカラムを作成する際に考慮されます。この例では、結果としてデータベーススキーマは次のようになります。

code: SQL

);

テーブルの作成

たいていの場合、サンプルデータを挿入してテーブルを作成するのが最も手っ取り早い方法です。データを挿入する前に空のテーブルを作成する必要がある場合は、create-tableコマンドを使用して作成できます。

code: bash

$ sqlite-utils create-table mydb.db mytable id integer name text --pk=id

これにより、整数のidカラムとテキストのnameカラムの2つのカラムを持つmytableというテーブルが作成されます。idカラムにはプライマリキーが設定されます。

code: bash

$ sqlite-utils create-table mydb.db mytable \

id integer \

name text \

age integer \

is_good integer \

--not-null name \

--not-null age \

--default is_good 1 \

--pk=id

$ sqlite-utils tables mydb.db --schema -t

table schema

------- --------------------------------

)

code: bash

$ sqlite-utils create-table books.db authors \

id integer \

name text \

--pk=id

$ sqlite-utils create-table books.db books \

id integer \

title text \

author_id integer \

--pk=id \

--fk author_id authors id

$ sqlite-utils tables books.db --schema -t

table schema

------- -------------------------------------------------

)

)

テーブルの削除

テーブルを削除するには、drop-tableコマンドを使用します。

code: bash

$ sqlite-utils drop-table mydb.db mytable

テーブルの変換

transformコマンドを使用すると、通常のSQLite ALTER TABLEコマンドでは実装できない複雑な変換をテーブルに適用することができます。この機能の詳細については、テーブルの変換を参照してください。

code: bash

$ sqlite-utils transform mydb.db mytable \

--drop column1 \

--rename column2 column_renamed

--type column-name new-type

指定したカラムのタイプを変更します。有効なタイプは integer, text, float, blob です。

--drop column-name

指定したカラムを削除します。

--rename column-name new-name

この列の名前を新しい名前に変更します。

--column-order column

--not-null column-name

このカラムをNOT NULLに設定します。

--not-null-false column-name

現在NOT NULLに設定されているカラムについて、NOT NULLを解除します。

--pk column-name

--pk-none

このテーブルからプライマリキーを削除し、rowidテーブルにします。

--default column-name value

このカラムのデフォルト値を設定します。

--default-none column

このカラムのデフォルト値を削除します。

--drop-foreign-key column

指定した外部キーをドロップします。

たとえば、以下のようになります。

code: bash

$ sqlite-utils transform fixtures.db roadside_attractions \

--rename pk id \

--default name Untitled \

--column-order id \

--column-order longitude \

--column-order latitude \

--drop address \

--sql

);

別のテーブルへのカラムの抽出

sqlite-utilsのextractコマンドを使用して、指定したカラムを別のテーブルに抽出することができます。

抽出操作の実行前と実行後のテーブルスキーマの例を含む、この動作の詳細な説明については、Python API documentation for Extracting columns into a separate table を参照してください。

このコマンドでは、データベース、テーブル、抽出したい1つ以上のカラムを受け取ります。例えば、trees テーブルから species カラムを抽出するには次のように実行します。

code: bash

$ sqlite-utils extract my.db trees species

次のようなスキーマを出力されます。

code: SQL

CREATE TABLE "trees" (

FOREIGN KEY(species_id) REFERENCES species(id)

)

)

このコマンドは以下のオプションを取ります。

--table TEXT

カラムを抽出するルックアップの名前です。デフォルトでは、抽出されるカラムの名前を使用します。

--fk-column TEXT

テーブルに追加する外部キーカラムの名前です。デフォルトでは columnname_id を使用します。

--rename <TEXT TEXT>

このオプションを使用して、新しいルックアップ テーブルに作成される列の名前を変更します。

-サイレント

プログレスバーを表示しません。

これらのオプションを利用した、より複雑な例を示します。この例では、世界の発電所を集めたCSVファイルをSQLiteに変換し、countryとcountry_longのカラムを別のcountryテーブルに抽出しています。

code: bash

$ sqlite-utils insert global.db power_plants \

'global_power_plant_database.csv?raw=true' --csv

# カラムの抽出:

$ sqlite-utils extract global.db power_plants country country_long \

--table countries \

--fk-column country_id \

--rename country_long name

この実行後、sqlite-utils schema global.dbというコマンドを実行すると、以下のようなスキーマが表示されます。

code: SQL

);

CREATE TABLE "power_plants" (

);

ビューの作成

create-viewコマンドでビューを作成することができます。

code: bash

$ sqlite-utils create-view mydb.db version "select sqlite_version()"

$ sqlite-utils mydb.db "select * from version"

同じ名前の既存のビューを置き換えるには--replaceを、すでにビューが存在する場合に何もしないには--ignoreを使います。

ビューの削除

drop-viewコマンドでビューを削除することができます。

code: bash

$ sqlite-utils drop-view myview

カラムの追加

カラムを追加するには、add-columnコマンドを使用します。

code: bash

$ sqlite-utils add-column mydb.db mytable nameofcolumn text

code: bash

$ sqlite-utils add-column mydb.db dogs species_id --fk species

これにより、speciesテーブルのプライマリキーの名前が自動的に検出され、その名前（およびそのタイプ）が新しいカラムに使用されます。

code: bash

$ sqlite-utils add-column mydb.db dogs species_id --fk species --fk-col ref

挿入/更新時に自動的に列を追加する

code: bash

$ sqlite-utils insert dogs.db dogs new-dogs.json --pk=id --alter

外部キー制約の追加

add-foreign-keyコマンドは、SQLiteのALTER TABLEコマンドがサポートしていない、既存のテーブルに新しい外部キーの参照を追加するために使用することができます。

books.author_idカラムを他のテーブルのauthors.idに指し示す外部キー制約を追加するには、次のようにします。

code: bash

$ sqlite-utils add-foreign-key books.db books author_id authors id

他のテーブルや列の参照を省略した場合、sqlite-utilsはそれらを推測しようとしますので、上記の例は次のようになります。

code: bash

$ sqlite-utils add-foreign-key books.db books author_id

code: bash

$ sqlite-utils add-foreign-key books.db books author_id --ignore

テーブルの自動推測メカニズムがどのように機能するかなどの詳細については、Python API documentation の Adding foreign key constraints を参照してください。

複数の外部キーを一度に追加

外部キーの追加にはVACUUMが必要です。大規模なデータベースでは、これは高価な操作になる可能性があります。 そのため、複数の外部キーを追加する場合は、add-foreign-keysを使用して1つの操作（したがって1つのVACUUM）にまとめることができます。

code: bash

$ sqlite-utils add-foreign-keys books.db \

books author_id authors id \

authors country_id countries id

このコマンドを使用する場合、各外部キーは、テーブル、カラム、他のテーブル、他のカラムの4つの引数として、完全に定義する必要があります。

すべての外部キーにインデックスを追加

データベースのすべての外部キーカラムに対応するインデックスを確保したい場合は、次のようにします。

code: bash

$ sqlite-utils index-foreign-keys books.db

デフォルトとnot null制約の設定

code: bash

$ sqlite-utils insert dogs.db dogs_with_scores dogs-with-scores.json \

--not-null=age \

--not-null=name \

--default age 2 \

--default score 5

インデックスの作成

create-indexコマンドを使って、既存のテーブルにインデックスを追加することができます。

code: bash

これは、単一のカラムまたは複数のカラムに対するインデックスを作成するために使用できます。

インデックスの名前は、テーブルとカラムから自動的に導き出されます。別の名前を指定するには、

if-not-existsを使用すると、その名前のインデックスがすでに存在している場合にインデックスの作成を試みないようにすることができます。

列のインデックスを降順で追加するには、列の前にハイフンを付けます。これはコマンドラインオプションと混同される可能性があるため、次のように構成する必要があります。

code: bash

$ sqlite-utils create-index mydb.db mytable -- col1 -col2 col3

これにより、そのテーブルの(col1, col2 desc, col3)にインデックスが作成されます。

カラム名の前にすでにハイフンが付いている場合は、このツールを使用するのではなく、CREATE INDEX SQL文を手動で実行してインデックスを追加する必要があります。

全文検索の設定

以下のように、テーブルとカラムのセットに対してSQLiteの全文検索を有効にすることができます。

code: bash

$ sqlite-utils enable-fts mydb.db documents title summary

デフォルトではSQLiteのFTS5モジュールが使用されます。

code: bash

$ sqlite-utils enable-fts mydb.db documents title summary --fts4

enable-ftsコマンドは、新しいインデックスに既存のドキュメントをすべて投入します。後にドキュメントを追加した場合は、populate-ftsを使ってそれらもインデックスに登録する必要があります。

code: bash

$ sqlite-utils populate-fts mydb.db documents title summary

ここでのより良い解決策は、データベーストリガーを使用することです。最初に enable-fts を実行する際に --create-triggers オプションを使用すると、フルテキストインデックスを自動的に更新するデータベーストリガーを設定できます。

code: bash

$ sqlite-utils enable-fts mydb.db documents title summary --create-triggers

code: bash

$ sqlite-utils populate-fts mydb.db documents title summary --tokenize=porter

作成したFTSテーブルとトリガーを削除するには、disable-ftsを使用します。

code: bash

$ sqlite-utils disable-fts mydb.db documents

1つまたは複数のFTSテーブルを再構築するには rebuild-ftsを使用します。

code: bash

$ sqlite-utils rebuild-fts mydb.db documents

テーブル名を渡さずにrebuild-ftsを実行すると、すべてのFTSテーブルを再構築することができます。

code: bash

$ sqlite-utils rebuild-fts mydb.db

検索の実行

テーブルのフルテキスト検索を設定したら、sqlite-utilsのsearchを使って検索することができます。

code: bash

$ sqlite-utils search mydb.db documents searchterm

code: bash

# rowid でソート

$ sqlite-utils search mydb.db documents searchterm -o rowid

# 作成された順に並べる

$ sqlite-utils search mydb.db documents searchterm -o 'created desc'

code: bash

$ sqlite-utils search mydb.db documents searchterm -c title -c created

デフォルトでは、すべての検索結果が返されます。--limit 20を使うと、最初の20件の検索結果だけを返すことができます。

sqlオプションを使うと、クエリを実行するのではなく、実行されるSQLを出力します。

code: bash

$ sqlite-utils search mydb.db documents searchterm --sql

with original as (

select

rowid,

*

)

select

from

where

order by

キャッシュされたカウントの有効化

sqlite-utilsは、_countsテーブルを管理するトリガを追加することで、これらを高速化することができます。詳細はCached table counts using triggersを参照してください。

sqlite-utilsのenable-countsコマンドを使用して、データベース内のすべてのテーブルまたは特定のテーブルに対するトリガを設定することができます。

code: bash

# データベースのすべてのテーブルにトリガーを設定する

$ sqlite-utils enable-counts mydb.db

# 特定のテーブルのためだけのトリガーの設定

$ sqlite-utils enable-counts mydb.db table1 table2

もし、_countsテーブルが実際のテーブルカウントと同期しなくなった場合は、reset-countsコマンドで修復することができます。

code: bash

$ sqlite-utils reset-counts mydb.db

バキューム(VACUUM)

VACUUMを実行すると、以下のようにデータベースを最適化することができます。

code: bash

$ sqlite-utils vacuum mydb.db

最適化

optimizeコマンドは、SQLiteフルテキスト検索を使用している場合、データベースのサイズを劇的に削減することができます。optimizeコマンドは、FTS4とFTS5のすべてのテーブルに対してOPTIMIZEを実行し、その後VACUUMを実行します。

VACUUMを行わずにOPTIMIZEだけを実行したい場合は、--no-vacuumフラグを使用してください。

code: bash

# すべてのFTSテーブルを最適化してからバキュームする

$ sqlite-utils optimize mydb.db

# バキュームを使わずに最適化

$ sqlite-utils optimize --no-vacuum mydb.db

すべてのFTSテーブルではなく、特定のテーブルを最適化するには、それらのテーブルを追加の引数として渡します。

code: bash

$ sqlite-utils optimize mydb.db table_1 table_2

WALモード

enable-walコマンドを使用して、データベースファイルのWAL（Write-Ahead Logging）を有効にすることができます。

code: bash

$ sqlite-utils enable-wal mydb.db

WALモードはdisable-walで無効にすることができます。

code: bash

$ sqlite-utils disable-wal mydb.db

これらのコマンドは、いずれも1つまたは複数のデータベースファイルを引数として受け取ります。

データベースをSQLにダンプ

dumpコマンドは、指定されたデータベースファイルのスキーマとフルコンテンツのSQLダンプを出力します。

code: bash

$ sqlite-utils dump mydb.db

BEGIN TRANSACTION;

CREATE TABLE ...

...

COMMIT;

参考