【応用編】SQLiteのメタデータをPythonで利用しよう!初めてのデータベース入門

SQLiteを使いこなすためには、単なるデータ操作だけでなく、スキーマ情報やデータベース管理の方法を理解することが大切です。

本記事では、Pythonを使ってSQLiteのメタデータ(テーブル一覧、カラム情報)を取得したり、CREATE文を自動生成する方法を解説します。

また、VACUUMによるデータベースの最適化など、運用で役立つテクニックも網羅していますので、SQLiteをさらに深く理解し、効率的に活用したい方は、是非ご覧ください。

目次

テストテーブルとデータの作成

import sqlite3

statements = '''
-- 従業員マスターのテーブル作成
CREATE TABLE employee_master (
    employee_id INTEGER PRIMARY KEY,  -- 従業員No
    name TEXT NOT NULL                -- 従業員名
);

-- サンプルデータの挿入
INSERT INTO employee_master (employee_id, name) 
VALUES (1, '田中太郎'),(2, '鈴木一郎'), (3, '佐藤花子');

-- 売上テーブルの作成
CREATE TABLE sales (
    sale_id INTEGER PRIMARY KEY,       -- 売上ID
    employee_id INTEGER,               -- 従業員No(従業員マスターの外部キー)
    sale_amount INTEGER NOT NULL,  -- 売上金額
    sale_date DATE NOT NULL,           -- 売上日
    FOREIGN KEY (employee_id) REFERENCES employee_master(employee_id)
);
-- サンプルデータの挿入
INSERT INTO sales (sale_id, employee_id, sale_amount, sale_date)
VALUES (1, 1, 100000, '2024-11-01'),(2, 2, 150000, '2024-11-02'),(3, 3, 50000, '2024-11-03');

-- インデックスの作成
CREATE INDEX idx_employee_id ON sales (employee_id);  -- salesテーブルのemployee_idにインデックスを作成
CREATE INDEX idx_employee_master_id ON employee_master (employee_id);  -- employee_masterテーブルのemployee_idにインデックスを作成

-- ビューの作成
CREATE VIEW sales_view AS
SELECT s.*,e.name FROM sales s JOIN employee_master e ON e.employee_id = s.employee_id;
'''

# SQL文の実行
with sqlite3.connect('./example.db') as conn:
    conn.executescript(statements)

テーブル名/ビュー名/CREATE文の取得

SQLiteの管理テーブルである sqlite_master に、テーブル名やビュー名、CREATE文が格納されています。

SELECDT * FROM sqlite_master

カラム名説明
typeオブジェクトの種類(table,index,view,triggerなど)
nameオブジェクトの名前(テーブル名、インデックス名、ビュー名など)
table_nameオブジェクトが関連付けられたテーブルの名前。インデックスやトリガーに関連するテーブル名。
rootpageオブジェクトのデータが格納されている最初のページ番号。テーブルやインデックスの物理的な位置。
sqlオブジェクトを作成するために使用されたSQL文。CreateTableやCreateIndexなどが格納される。

テーブル/ビューの一覧取得

import sqlite3

with sqlite3.connect('./example.db') as conn:
    for row in conn.execute("SELECT name,type FROM sqlite_master"):
        print(row) 

('employee_master', 'table')
('sales', 'table')
('idx_employee_id', 'index')
('idx_employee_master_id', 'index')
('sales_view', 'view')

CREATE文の取得

import sqlite3

with sqlite3.connect('./example.db') as conn:
    for row in conn.execute("SELECT sql FROM sqlite_master WHERE  name='employee_master'"):
        print(row) 

('CREATE TABLE employee_master (\n employee_id INTEGER PRIMARY KEY, -- 従業員No\n name TEXT NOT NULL -- 従業員名\n)',)

カラム一覧の取得

カラム名は PRAGMA table_info() を使います。PRAGMAは、標準SQLではサポートしきれないSQLite独自の操作や設定を行うための命令です。SQLの代わりにPRAGMA table_info()を実行することで、カラムの情報が取得できます。

PRAGMA table_info(テーブル名)

実行すると下記の情報が返されます。

項目名説明
CIDSQLite が内部的に管理するカラムの順序番号(0 から始まる整数)
Nameカラム名
Typeカラムのデータ型を示す。ここでは INTEGER、REAL など
NotNullカラムが NOT NULL 制約を持つかを示す
1:NOT NULL 制約が存在する(NULL 値は許容されない)
0: 制約がない(NULL 値を許容する)
DefaultValueカラムのデフォルト値を示す。設定されていない場合は NULL
PK (Primary Key)プライマリキー(主キー)として指定されている場合は 1、そうでない場合は 0
import sqlite3

with sqlite3.connect('./example.db') as conn:
    for row in conn.execute("PRAGMA table_info('sales')"):
        print(row) 

(0, 'sale_id', 'INTEGER', 0, None, 1)
(1, 'employee_id', 'INTEGER', 0, None, 0)
(2, 'sale_amount', 'DECIMAL(10, 2)', 1, None, 0)
(3, 'sale_date', 'DATE', 1, None, 0)

INDEX情報の取得

INDEXに関する詳細な情報は、PRAGMA index_list()で取得可能です。引数としてテーブル名を指定することで、そのテーブルに関するINDEX情報が取得できます。

PRAGMA index_list(テーブル名)

実行すると下記の情報が返されます。

項目名説明
seqインデックスの順序番号を示します。0 から始まる番号で、テーブル内でのインデックスの定義順を表します。
nameインデックスの名前を示します。CREATE INDEX で指定した名前が表示されます。
uniqueインデックスがユニークインデックスかどうかを示します。
1 : ユニークインデックス。
0 : 通常のインデックス。
originインデックスの作成方法を示します。以下の値を取り得ます:
c : 明示的に作成(CREATE INDEX)。
u : UNIQUE 制約で自動作成。
pk : PRIMARY KEY 制約で自動作成。
partialパーシャルインデックス(条件付きインデックス)かどうかを示します。
1 : パーシャルインデックス。
0 : 条件なしのインデックス。
import sqlite3

with sqlite3.connect('./example.db') as conn:
    for row in conn.execute("PRAGMA index_list('employee_master')"):
        print(row) 

(0, 'idx_employee_master_id', 0, 'c', 0)

一時テーブルを作成する

一時テーブルは、接続が切断された時点で自動的に削除されるテーブルです。一時的にデータを保存しておき、最後に削除したいケースでは、一時テーブルが重宝します。

通常のテーブル作成と同じ要領で作成可能ですが、 TEMP 又は TEMPORARY を CREATE と TABLE の間に記述する必要があります。

CREATE TEMP TABLE テーブル名(
カラム名1 [データ型] [制約]
カラム名2 [データ型] [制約]
  ・・・
)

import sqlite3

statements = '''
-- 一時テーブルの作成
CREATE TEMPORARY TABLE temp_sales (
    sale_id INTEGER,
    employee_id INTEGER,
    sale_amount REAL
);

-- 一時テーブルにデータを挿入
INSERT INTO temp_sales (sale_id, employee_id, sale_amount)
VALUES (1, 101, 5000.5), (2, 102, 3000.2);
'''

# SQL文の実行
with sqlite3.connect('./example.db') as conn:
    conn.executescript(statements)
    for row in conn.execute("select * from temp_sales"):
        print(row)

ユーザー関数を作成する

SQLiteでは、Pythonで書いたユーザー関数を登録して使うことが可能です。ユーザー関数はPythonにおける一般的な関数と同じ書き方で構いませんが、戻り値は1つにする必要があります。
また、

def 関数名(引数1,引数2,・・・):
 ~ここに処理を記述~
return 戻り値


conn.create_function(呼び出す時の名前, 引数の数, ユーザー関数)

import sqlite3

# ユーザー関数の定義
def calculate_tax(amount, tax_rate=1.1):
    return round(amount * tax_rate)

with sqlite3.connect("./example.db") as conn:
    
    # ユーザー関数を登録する
    conn.create_function("calc_tax", 1, calculate_tax)

    # SELECTの中でユーザー関数を使う
    for row in conn.execute("SELECT sale_amount,calc_tax(sale_amount) FROM sales"):
        print(row)

(100000, 110000)
(150000, 165000)
(50000, 55000)

3件だけデータが登録されている sales テーブルに対して、ユーザー関数を使う方法と、直接SELECT文の中に計算式を記述する方法で、それぞれ10万回 実行した際の処理速度を計測してみました。
ユーザー関数にした場合、1割程度余分に時間が掛かる計算になります。
思っていたよりオーバーヘッドは少ないようです。

処理方法10万回の処理速度(秒)倍率
SELECT文に直接計算式を記述1.076 1.0
SELECT文からユーザー関数を呼び出し1.168 1.09
SELECT文直接計算とユーザー関数利用時の速度比較
SELECT文に直接計算式を記述
1.076秒/10万回
SELECT文からユーザー関数を呼び出し
1.168秒/10万回

トリガーを作成する

SQLiteでもトリガーを登録することが可能です。
トリガーに渡ってくる値は、 NEW.カラム名 で取得できます。

DROP TRIGGER IF EXISTS トリガー名;
CREATE TRIGGER トリガー名
[ AFTER | BEFORE ] [ INSERT | UPDATE | DELETE ] ON テーブル名
FOR EACH ROW
BEGIN
~ここに処理を記述~

~NEW.カラム名 でトリガ実行時に渡される値の取得が可能~
END;

下記のプログラムでは、プライマリキー(salie_id)が全く同じデータが複数件INSERTされるため、一意エラーになるはずですが、トリガー内で重複削除しているため、エラーになりません。

import sqlite3

trigger = '''
DROP TRIGGER IF EXISTS before_sales_insert;
CREATE TRIGGER before_sales_insert
BEFORE INSERT ON sales
FOR EACH ROW
BEGIN
    -- 新しい sale_id がすでに存在する場合、その ID を持つレコードを削除
    DELETE FROM sales
    WHERE sale_id = NEW.sale_id;
END;

'''

with sqlite3.connect("./example.db") as conn:
    
    # トリガーを実行(トリガーを登録)
    conn.executescript(trigger)

    # SQLを実行
    conn.execute("INSERT INTO sales VALUES (1, 102, 75000, '2024-11-25')")
    conn.execute("INSERT INTO sales VALUES (1, 102, 75000, '2024-11-25')")
    conn.execute("INSERT INTO sales VALUES (1, 102, 75000, '2024-11-25')")

データベースファイルの無駄な領域を削除する

データの削除や更新が行われても、SQLite ではその領域がすぐにはファイルサイズから削除されません。また、多くの削除や更新操作があると、データの断片化(フラグメント化)が発生し、データベースのパフォーマンスに影響します。

このため、無駄な領域や断片化を解消するための PRAGMA vaccumeが用意されています。
実行しても戻り値はありません。

PRAGMA vacuum

import sqlite3

with sqlite3.connect('./example.db') as conn:
    conn.execute("PRAGMA vacuum")

まとめ

本記事では、SQLiteのスキーマ情報取得の他、ユーザー関数やトリガーの使い方、データベース上の無駄な領域の削除などの応用的な技術について紹介しました。

これらのテクニックを使うことで、SQLiteの活用の幅がもっと広がると思います。

データベースシステムとしては極めて軽量ですが、できることは一般的なデータベースとほとんど同じです。是非みなさんのプログラムで活用してください。

次回は、pandasと組み合わせてCSVデータを扱う方法を紹介致します。

よかったらシェアしてね!
  • URLをコピーしました!
  • URLをコピーしました!

この記事を書いた人

コメント

コメントする

目次