SQLiteの使い方｜コマンド早見表とPython連携を実行例で解説

SQLiteとは、サーバーを立てずにファイル1つで動く、軽量なリレーショナルデータベースです。 インストールしてすぐ sqlite3 ファイル名 で使い始められ、Python には標準モジュール sqlite3 が同梱されているので、追加のインストールなしにプログラムから扱えます。

この記事では、sqlite3 コマンド（ドットコマンド）の早見表から、CRUD・データ型・STRICT テーブル・UPSERT・RETURNING・JSON 関数・WAL モード・CSV 入出力・Python 連携・バックアップまでを、実際に実行した出力つきで解説します。検証環境は Ubuntu 24.04 の apt-get install -y sqlite3（SQLite 3.45.1）と Python 3.11（同梱ライブラリも 3.45.1）です。公式サイトの最新版は 3.53.4（2026-07-24） で、本文で扱う機能はすべて 3.45 以前に入っているため、どちらでも同じように動きます。

SQLite ドットコマンドの早見表

CLI（sqlite3）の「ドットコマンド」は SQL ではなく、; なしで 1 行に 1 つ書きます。この表の分を覚えれば日常の操作は足ります。

SQL 側でよく使う機能も、対応バージョンとあわせて一覧にします。

SQLiteの特徴とインストール（バージョンの確認）

SQLite は「ライブラリ」であり、アプリのプロセス内でファイルを直接読み書きします。 PostgreSQL や MySQL のようなサーバープロセス、ユーザー管理、ポート番号がなく、DB は .db ファイル 1 つです。スマートフォンやブラウザ、各種アプリの内部ストレージとして広く使われています。

CLI は Ubuntu / Debian なら sudo apt-get install -y sqlite3、macOS は標準で同梱、Windows は公式サイトの「Precompiled Binaries」の zip を展開して使います。Python は標準モジュール sqlite3 をそのまま使えます。

$ sqlite3 --version
3.45.1 2024-01-30 16:01:20 e876e51a0ed5c5b3126f52e532044363a014bc594cfefa87ffb5b82257ccalt1 (64-bit)
$ python3 -c "import sqlite3; print(sqlite3.sqlite_version)"
3.45.1

パッケージ管理で入るバージョンは公式最新版より前のことが多いので、使いたい機能の対応バージョンを上の表で確認しておくと安心です。

sqlite3 コマンド（CLI）を実行結果で確認する

DB ファイルを作り、テーブルを作成する

sqlite3 ファイル名 で起動すると、ファイルがなければ新しく作られます。SQL は ; で終わらせ、ドットコマンドは ; なしで書きます。

$ sqlite3 shop.db
sqlite> CREATE TABLE items (
   ...>   id    INTEGER PRIMARY KEY,
   ...>   name  TEXT NOT NULL,
   ...>   price INTEGER NOT NULL DEFAULT 0,
   ...>   stock INTEGER NOT NULL DEFAULT 0
   ...> );
sqlite> INSERT INTO items (name, price, stock) VALUES ('notebook', 120, 50), ('pen', 100, 200), ('sticky', 250, 30);
sqlite> .tables
items
sqlite> .schema items
CREATE TABLE items (
  id    INTEGER PRIMARY KEY,
  name  TEXT NOT NULL,
  price INTEGER NOT NULL DEFAULT 0,
  stock INTEGER NOT NULL DEFAULT 0
);

出力形式を切り替える（.headers / .mode）

既定の出力は | 区切りで列名なしです。人が読むなら .headers on と .mode box（または column）、プログラムに渡すなら csv や json に切り替えます。.mode markdown なら Markdown の表がそのまま出ます。

sqlite> SELECT * FROM items;
1|notebook|120|50
2|pen|100|200
3|sticky|250|30
sqlite> .headers on
sqlite> .mode box
sqlite> SELECT id, name, price FROM items WHERE price >= 120;
┌────┬──────────┬───────┐
│ id │   name   │ price │
├────┼──────────┼───────┤
│ 1  │ notebook │ 120   │
│ 3  │ sticky   │ 250   │
└────┴──────────┴───────┘

ファイル出力・SQL ファイル実行・ダンプ・バックアップ

sqlite> .output out.csv        -- 以降の出力を out.csv へ
sqlite> .mode csv
sqlite> SELECT * FROM items;
sqlite> .output stdout          -- 画面に戻す
sqlite> .read init.sql          -- SQL ファイルを実行
sqlite> .dump                   -- CREATE TABLE と INSERT 文が BEGIN〜COMMIT に包まれて出力される
sqlite> .backup shop_backup.db

.dump は「SQL 文としての書き出し」、.backup は「ファイルとしての複製」です。別のマシンやバージョンへ移すなら .dump、同じ環境の複製なら .backup が手軽です。

対話モードに入らず 1 行で実行する

シェルスクリプトや cron からは、引数に SQL を渡します。-header -column -json -csv で出力形式も指定でき、ドットコマンドは引数を分けて渡します。

$ sqlite3 -json shop.db "SELECT id, name FROM items LIMIT 1"
[{"id":1,"name":"notebook"}]
$ sqlite3 shop.db ".tables" ".schema items"

CRUD・データ型・STRICT・UPSERT・RETURNING

CRUD の基本

CREATE TABLE members (
  id    INTEGER PRIMARY KEY,          -- 自動採番される rowid の別名
  email TEXT NOT NULL UNIQUE,
  name  TEXT NOT NULL,
  point INTEGER NOT NULL DEFAULT 0
);
INSERT INTO members (email, name) VALUES ('a@example.com', 'Aoki'), ('b@example.com', 'Baba');
UPDATE members SET point = point + 100 WHERE email = 'a@example.com';
DELETE FROM members WHERE email = 'b@example.com';
SELECT * FROM members;   -- 1|a@example.com|Aoki|100

データ型と「型親和性」を typeof() で確認する

SQLite の列の型は「親和性（affinity）」で、値そのものは INTEGER / REAL / TEXT / BLOB / NULL の 5 種類のどれかとして保存されます。 列に宣言した型は「できればこの型に変換する」というヒントで、変換できない値はそのまま入ります。

CREATE TABLE t_affinity (a INTEGER, b TEXT, c REAL, d BLOB, e NUMERIC, f);
INSERT INTO t_affinity VALUES ('123', 456, '7.5', 'abc', '12.0', 'x');
SELECT typeof(a) AS a, typeof(b) AS b, typeof(c) AS c, typeof(d) AS d, typeof(e) AS e, typeof(f) AS f FROM t_affinity;

┌─────────┬──────┬──────┬──────┬─────────┬──────┐
│    a    │  b   │  c   │  d   │    e    │  f   │
├─────────┼──────┼──────┼──────┼─────────┼──────┤
│ integer │ text │ real │ text │ integer │ text │
└─────────┴──────┴──────┴──────┴─────────┴──────┘

'123' は INTEGER 列で数値に、456 は TEXT 列で文字列に変換されました。一方、INTEGER 列に 'hello' を入れると 変換できないので text のまま格納されます（typeof(a) が text になることを実行で確認）。VARCHAR(255) や DATETIME と書いても親和性に丸められます（TEXT、NUMERIC）。日付・時刻型はなく、TEXT（ISO 8601）か INTEGER（UNIX 時刻）で保存し、datetime() などの関数で扱います。 CURRENT_TIMESTAMP と datetime('now') は UTC を返すので、日本時間が欲しいときは datetime('now', 'localtime') を使います。

STRICT テーブルで型を厳密にする（3.37.0 以降）

「INTEGER 列に文字列が入る」のを防ぎたいときは STRICT を付けます。使える型名は INT / INTEGER / REAL / TEXT / BLOB / ANY の 6 つだけです。

CREATE TABLE t_strict (id INTEGER PRIMARY KEY, qty INTEGER NOT NULL) STRICT;
INSERT INTO t_strict (qty) VALUES ('10');    -- 数値に変換できる文字列は OK
INSERT INTO t_strict (qty) VALUES ('ten');   -- エラー
-- Runtime error: cannot store TEXT value in INTEGER column t_strict.qty (19)

VARCHAR(10) のような型名を STRICT テーブルに書くと Parse error: unknown datatype になります。

アプリから型のそろったデータを入れるテーブルは STRICT にしておくと、後工程の型変換で悩まなくなります。

AUTOINCREMENT は原則不要

INTEGER PRIMARY KEY の列は rowid の別名になり、何もしなくても自動採番されます。AUTOINCREMENT を付けた表と付けない表に a, b を入れて id=2 を削除し、c を追加すると次の違いが出ます（実行で確認）。

UPSERT と RETURNING

UPSERT（3.24.0 以降）は「あれば更新、なければ追加」を 1 文で書く構文で、excluded.列名 で「入れようとした値」を参照します。RETURNING（3.35.0 以降）は変更した行を返すので、採番された id をすぐ受け取れます。

INSERT INTO members (email, name) VALUES ('c@example.com', 'Chiba') RETURNING id, email;
-- 2|c@example.com
INSERT INTO members (email, name, point) VALUES ('a@example.com', 'Aoki Taro', 10)
ON CONFLICT(email) DO UPDATE SET name = excluded.name, point = point + excluded.point
RETURNING *;
-- 1|a@example.com|Aoki Taro|110
INSERT INTO members (email, name) VALUES ('a@example.com', 'dup');
-- Runtime error: UNIQUE constraint failed: members.email (19)

ON CONFLICT(email) の列には UNIQUE 制約か主キーが必要です。重複を黙って飛ばしたいだけなら INSERT OR IGNORE が使えます。RETURNING の行の順序は保証されないため、順序が必要なら別途 SELECT します。

JSON 関数（json_extract / -> / ->> / json_each）

3.38.0 以降は JSON 関数が既定で組み込まれ、-> と ->> 演算子も使えます。 JSON は TEXT 列にそのまま保存します（3.45.0 からはバイナリ形式 jsonb も選べます）。

CREATE TABLE orders (id INTEGER PRIMARY KEY, detail TEXT);
INSERT INTO orders (detail) VALUES
  ('{"customer":"Aoki","items":[{"sku":"A1","qty":2},{"sku":"B2","qty":1}],"paid":true}'),
  ('{"customer":"Baba","items":[{"sku":"C3","qty":5}],"paid":false}');

SELECT id, json_extract(detail, '$.items[0].sku') AS first_sku,
       json_array_length(detail, '$.items') AS n_items,
       detail -> '$.customer' AS arrow, detail ->> '$.customer' AS double_arrow FROM orders;

┌────┬───────────┬─────────┬────────┬──────────────┐
│ id │ first_sku │ n_items │ arrow  │ double_arrow │
├────┼───────────┼─────────┼────────┼──────────────┤
│ 1  │ A1        │ 2       │ "Aoki" │ Aoki         │   ← -> は JSON のまま、->> は SQL の値
│ 2  │ C3        │ 1       │ "Baba" │ Baba         │
└────┴───────────┴─────────┴────────┴──────────────┘

配列を行に展開して集計する例です。true / false は ->> で取り出すと 1 / 0（integer）になります。

SELECT o.id, j.value ->> '$.sku' AS sku, j.value ->> '$.qty' AS qty
FROM orders o, json_each(o.detail, '$.items') j;
-- 1|A1|2  /  1|B2|1  /  2|C3|5
SELECT o.id, sum(j.value ->> '$.qty') AS total_qty
FROM orders o, json_each(o.detail, '$.items') j GROUP BY o.id;
-- 1|3  /  2|5
CREATE INDEX idx_orders_customer ON orders(detail ->> '$.customer');   -- 式インデックス
EXPLAIN QUERY PLAN SELECT * FROM orders WHERE detail ->> '$.customer' = 'Aoki';
-- `--SEARCH orders USING INDEX idx_orders_customer (<expr>=?)

JSON のキーで検索するなら ->> の式インデックスを作ると、実行計画が SCAN から SEARCH に変わります。 一部だけ書き換えるには json_set(detail, '$.paid', json('true')) のように使い、真偽値は json('true') と書きます（文字列 'true' と区別するため）。

WAL モードと同時アクセス（database is locked の再現と対処）

SQLite は「同時に書き込めるのは 1 接続だけ」です。 既定のロールバックジャーナル（journal_mode=DELETE）では、読み取りトランザクションが開いている間は書き込みの COMMIT も待たされます。WAL（Write-Ahead Logging）モードにすると、読み取りが書き込みをブロックしなくなります。 次の Python スクリプトは timeout=0（待たずに即エラー）で両モードの違いを再現したものです。

import sqlite3

def demo(journal_mode):                    # 事前に tasks 表だけの lock_XXX.db を作って journal_mode を設定しておく
    path = f"lock_{journal_mode}.db"
    print(f"=== journal_mode={journal_mode} ===")
    # isolation_level=None: 自動コミット。timeout=0: ロック待ちをせず即エラー
    writer, reader, other = (sqlite3.connect(path, timeout=0, isolation_level=None) for _ in range(3))

    reader.execute("BEGIN"); reader.execute("SELECT count(*) FROM tasks").fetchone()  # 読み取り中のまま
    writer.execute("BEGIN"); writer.execute("INSERT INTO tasks (title) VALUES ('a')")
    try:
        writer.execute("COMMIT"); print("writer: COMMIT OK（読み取り中でも書き込める）")
    except sqlite3.OperationalError as e:
        print("writer: COMMIT ->", e)
        reader.execute("COMMIT"); writer.execute("COMMIT"); print("writer: 読み取り側を閉じたら COMMIT OK")

    writer.execute("BEGIN IMMEDIATE")          # 書き込みロックを先に取る
    try:
        other.execute("BEGIN IMMEDIATE")
    except sqlite3.OperationalError as e:
        print("other : BEGIN IMMEDIATE ->", e)
    writer.execute("COMMIT")
    for c in (writer, reader, other): c.close()

demo("DELETE"); demo("WAL")

=== journal_mode=DELETE ===
writer: COMMIT -> database is locked
writer: 読み取り側を閉じたら COMMIT OK
other : BEGIN IMMEDIATE -> database is locked
=== journal_mode=WAL ===
writer: COMMIT OK（読み取り中でも書き込める）
other : BEGIN IMMEDIATE -> database is locked

database is locked への対処は次の 3 つの組み合わせです。

1. WAL モードにする：PRAGMA journal_mode = WAL; を一度実行すれば DB ファイルに記録され、以後の接続でも有効です。接続中は *.db-wal と *.db-shm が横に作られ、最後の接続が閉じるときに本体へ反映（チェックポイント）されます。ネットワーク共有ドライブ上のファイルでは使えません。
2. 待ち時間を設定する：PRAGMA busy_timeout = 5000;（ミリ秒）。CLI の既定は 0、Python の sqlite3.connect() は既定で 5 秒待ちます。
3. 書き込みトランザクションは短く、BEGIN IMMEDIATE で始める：先にロックを取り、途中で database is locked になる事態を減らします。

CSV のインポート／エクスポート

.import --csv は取り込み先テーブルがなければ 1 行目を列名として自動作成しますが、その場合は全列が TEXT になります（.schema で確認すると "id" TEXT, "price" TEXT, ... になります）。型を効かせたいときは先に CREATE TABLE し、--skip 1 でヘッダー行を飛ばします。

$ cat items.csv
id,name,price,stock
1,notebook,120,50
2,pen,100,200
3,sticky,250,30
$ sqlite3 imp.db "CREATE TABLE items2(id INTEGER PRIMARY KEY, name TEXT, price INTEGER, stock INTEGER);" \
    ".import --csv --skip 1 items.csv items2" \
    "SELECT id, typeof(id), price, typeof(price) FROM items2 LIMIT 1"
1|integer|120|integer
$ sqlite3 -csv -header shop.db "SELECT id, name, price FROM items" > out.csv   # エクスポート

Python の sqlite3 モジュール（コピペ例と実行結果）

Python 3.11 の標準モジュール sqlite3 だけで、接続・パラメータ付き実行・一括挿入・トランザクションまで扱えます。 以下は 1 本のスクリプトで、そのまま実行できます。

import sqlite3

con = sqlite3.connect("app.db")          # ファイルがなければ作られる
con.row_factory = sqlite3.Row            # 行を列名でアクセスできるようにする

con.executescript("""
CREATE TABLE IF NOT EXISTS members (
  id    INTEGER PRIMARY KEY,
  email TEXT NOT NULL UNIQUE,
  name  TEXT NOT NULL
);
CREATE TABLE IF NOT EXISTS tasks (
  id        INTEGER PRIMARY KEY,
  member_id INTEGER NOT NULL REFERENCES members(id),
  title     TEXT NOT NULL,
  done      INTEGER NOT NULL DEFAULT 0
);
""")

# 1) パラメータは ? または :name（文字列連結はしない）
with con:                                # with: 正常終了で commit、例外で rollback
    con.execute("INSERT INTO members (email, name) VALUES (?, ?)", ("a@example.com", "Aoki"))
    con.execute("INSERT INTO members (email, name) VALUES (:email, :name)",
                {"email": "b@example.com", "name": "Baba"})

# 2) executemany でまとめて INSERT
rows = [(1, "write report"), (1, "review PR"), (2, "update docs")]
with con:
    con.executemany("INSERT INTO tasks (member_id, title) VALUES (?, ?)", rows)

# 3) 例外が起きると with ブロック内の変更はロールバックされる
try:
    with con:
        con.execute("INSERT INTO tasks (member_id, title) VALUES (?, ?)", (2, "rolled back"))
        con.execute("INSERT INTO members (email, name) VALUES (?, ?)", ("a@example.com", "dup"))
except sqlite3.IntegrityError as e:
    print("IntegrityError:", e)
print("tasks count:", con.execute("SELECT count(*) FROM tasks").fetchone()[0])  # 3 のまま

# 4) Row で列名アクセス
for row in con.execute("""
    SELECT m.name, t.title, t.done
    FROM tasks t JOIN members m ON m.id = t.member_id ORDER BY t.id"""):
    print(dict(row))

# 5) RETURNING で採番された id を受け取る
with con:
    new_id = con.execute("INSERT INTO tasks (member_id, title) VALUES (?, ?) RETURNING id",
                         (1, "deploy")).fetchone()["id"]
    con.execute("UPDATE tasks SET done = 1 WHERE id = ?", (new_id,))
print("new task id:", new_id)

con.close()

# 6) インメモリ DB（テストに便利）
mem = sqlite3.connect(":memory:")
mem.execute("CREATE TABLE t (v)")
mem.executemany("INSERT INTO t VALUES (?)", [(i,) for i in range(5)])
print("memory sum:", mem.execute("SELECT sum(v) FROM t").fetchone()[0])

$ python3 py_demo.py
IntegrityError: UNIQUE constraint failed: members.email
tasks count: 3
{'name': 'Aoki', 'title': 'write report', 'done': 0}
{'name': 'Aoki', 'title': 'review PR', 'done': 0}
{'name': 'Baba', 'title': 'update docs', 'done': 0}
new task id: 4
memory sum: 10

with con: は commit / rollback だけを行い接続は閉じないので、最後に con.close() します。外部キー制約は既定では効かないため、使うなら接続ごとに con.execute("PRAGMA foreign_keys = ON") を実行します（違反時は IntegrityError: FOREIGN KEY constraint failed）。Python 3.12 以降は sqlite3.connect(..., autocommit=False) でトランザクション制御を明示でき、sqlite3.version は 3.14 で削除されたので、ライブラリのバージョンは sqlite3.sqlite_version で確認します。

1 行ずつコミットせず、トランザクションでまとめる（実測）

SQLite は コミットのたびにディスクへ同期するため、1 行ずつ自動コミットすると非常に遅くなります。20,000 行の INSERT を、自動コミット接続（isolation_level=None）で「1 行ずつ execute」と「BEGIN → executemany → COMMIT」で比べた結果です。

1行ずつ自動コミット      : 19.54 秒 (20000 行)
BEGIN〜COMMIT でまとめて: 0.02 秒 (20000 行)

大量の INSERT / UPDATE は必ず 1 つのトランザクションにまとめます（with con: の中で executemany を使えば自然にそうなります）。実測値は環境によって変わりますが、桁が違う差になるのは共通です。

バックアップ（.backup / VACUUM INTO / Python の backup）

稼働中の DB ファイルを cp でコピーすると、書き込みの途中を写してしまうことがあります。 一貫したバックアップは次のいずれかで取ります（いずれも実行して PRAGMA integrity_check; が ok になることを確認済み）。

WAL モードの DB をファイルコピーで退避するときは *.db-wal も一緒に、あるいは PRAGMA wal_checkpoint(TRUNCATE); を実行してからにします。

PostgreSQL / MySQL との使い分け

SQLite は「同時書き込みが少なく、1 台の中で完結する」用途に向いています。 複数のサーバーから同時に書く、ユーザー権限を分ける、といった要件が出てきたらサーバー型 DB を選びます。

列の型変更や制約の追加は、新しい定義でテーブルを作り INSERT INTO new SELECT ... FROM old でコピーし、古い表を DROP → RENAME する手順（公式ドキュメントの 12 ステップ）で行います（ALTER TABLE t ALTER COLUMN ... は syntax error になります）。

よくある質問

Q. sqlite3 コマンドが見つかりません。Python では使えているのに、なぜですか？ A. Python の sqlite3 モジュールはライブラリを同梱しているため、CLI（sqlite3 コマンド）とは別物です。CLI は apt-get install sqlite3 などで別途入れます。sqlite3.sqlite_version を見ると、Python 同梱ライブラリのバージョンがわかります。

Q. database is locked が出たとき、まず何を確認すればよいですか？ A. (1) 同時に書き込む処理が複数ないか、(2) 読み取りトランザクションを開いたままにしていないか、(3) journal_mode が WAL か、(4) busy_timeout が 0 になっていないか、の順で確認します。本文の再現スクリプトの通り、WAL にすると読み取りと書き込みの競合はなくなります。

Q. 日付はどう保存すればよいですか？ A. 専用の型はないので、TEXT に ISO 8601（2026-09-30 12:34:56）で保存するのが扱いやすいです。datetime('now') は UTC を返すため、日本時間なら datetime('now', 'localtime') を使います。文字列のままでも比較や並べ替えは正しく動きます。

まとめ

• SQLite はサーバー不要・ファイル 1 つの DBで、sqlite3 ファイル名 で即使える。公式最新は 3.53.4、apt 版は 3.45.1
• ドットコマンドは .tables .schema .mode box .headers on .import .dump .backup を押さえれば日常の操作は足りる
• 型は「親和性」で緩い。厳密にしたいテーブルは STRICT、id は INTEGER PRIMARY KEY だけで自動採番される
• UPSERT と RETURNING で「あれば更新、なければ追加して id を受け取る」が 1 文で書ける
• JSON は ->> と json_each で取り出し・展開でき、式インデックスで検索も速くなる
• 書き込みは常に 1 接続。WAL モード＋busy_timeout＋短いトランザクションで database is locked を避ける
• Python は標準の sqlite3 で完結し、with con: とプレースホルダ、executemany のまとめ書きが基本

株式会社RJCでは、SQLite のような小さく確実に動く仕組みから業務システムのデータベース設計までを、プロジェクトの中で仲間と学び合っています。こうした知識を共有しながら、日々技術を磨いています。

株式会社RJC ― SI事業・SES事業・AI駆動開発。RJCは一緒に成長を楽しめる会社です。