SQLite JSON 処理 - データベースでの JSON データ活用

はじめに (Introduction)

この実験 (lab) では、SQLite 内で JSON データを処理する方法を学びます。SQLite データベース内での JSON データの保存、抽出、フィルタリング、および更新の方法を検証します。この実験 (lab) は、SQLite での JSON データの操作に関する実践的な入門となり、これは現代のデータ管理においてますます価値のあるスキルです。

Skills Graph

%%%%{init: {'theme':'neutral'}}%%%% flowchart RL sqlite(("SQLite")) -.-> sqlite/SQLiteGroup(["SQLite"]) sqlite/SQLiteGroup -.-> sqlite/make_table("Create New Table") sqlite/SQLiteGroup -.-> sqlite/add_rows("Insert Multiple Rows") sqlite/SQLiteGroup -.-> sqlite/get_all("Select All Rows") sqlite/SQLiteGroup -.-> sqlite/query_where("Filter With WHERE") sqlite/SQLiteGroup -.-> sqlite/edit_row("Update Single Row") sqlite/SQLiteGroup -.-> sqlite/check_version("Get SQLite Version") subgraph Lab Skills sqlite/make_table -.-> lab-552553{{"SQLite JSON 処理"}} sqlite/add_rows -.-> lab-552553{{"SQLite JSON 処理"}} sqlite/get_all -.-> lab-552553{{"SQLite JSON 処理"}} sqlite/query_where -.-> lab-552553{{"SQLite JSON 処理"}} sqlite/edit_row -.-> lab-552553{{"SQLite JSON 処理"}} sqlite/check_version -.-> lab-552553{{"SQLite JSON 処理"}} end

データベースとテーブルの作成 (Create a Database and Table)

このステップでは、SQLite データベースと JSON データを格納するためのテーブルを作成します。SQLite は、データを単一のファイルに格納する軽量なデータベースであり、管理が容易です。

まず、ターミナルを開きます。デフォルトのパスは /home/labex/project です。

次に、データベースを格納するためのディレクトリを作成しましょう。

mkdir sqlite_json
cd sqlite_json

これらのコマンドは、sqlite_json という名前のディレクトリを作成し、現在のディレクトリをそのディレクトリに変更します。これにより、プロジェクトファイルが整理されます。

次に、mydatabase.db という名前の SQLite データベースを作成します。

sqlite3 mydatabase.db

このコマンドは SQLite シェルを開き、mydatabase.db データベースに接続します。データベースファイルが存在しない場合、SQLite はそれを作成します。

次に、id、name、および details の列を持つ products という名前のテーブルを作成します。details 列は、JSON データをテキストとして格納します。

CREATE TABLE products (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    name TEXT,
    details TEXT
);

この SQL コマンドは、products テーブルを作成します。

id: 各新しい製品に対して自動的にインクリメントされる一意の整数 (unique integer)。
name: 製品の名前 (例：Laptop, Smartphone)。
details: 製品の JSON データを格納するためのテキストフィールド (text field)。

JSON データの挿入 (Insert JSON Data)

このステップでは、JSON データを products テーブルに挿入します。

products テーブルに 2 つのサンプルレコードを挿入しましょう。

INSERT INTO products (name, details) VALUES (
    'Laptop',
    '{"brand": "Dell", "model": "XPS 13", "specs": {"cpu": "Intel i7", "memory": "16GB", "storage": "512GB SSD"}}'
);

INSERT INTO products (name, details) VALUES (
    'Smartphone',
    '{"brand": "Samsung", "model": "Galaxy S21", "specs": {"display": "6.2 inch", "camera": "12MP", "storage": "128GB"}}'
);

これらの INSERT ステートメントは、products テーブルに 2 つの行を追加します。details 列には、テキスト文字列 (text string) として JSON データが含まれています。

データが正しく挿入されたことを確認するには、次のクエリを実行します。

SELECT * FROM products;

期待される出力 (Expected Output):

1|Laptop|{"brand": "Dell", "model": "XPS 13", "specs": {"cpu": "Intel i7", "memory": "16GB", "storage": "512GB SSD"}}
2|Smartphone|{"brand": "Samsung", "model": "Galaxy S21", "specs": {"display": "6.2 inch", "camera": "12MP", "storage": "128GB"}}

この出力は、JSON データが products テーブルに正常に格納されたことを確認します。

カスタム関数を使用した JSON フィールドの抽出 (Extract JSON Fields with a Custom Function)

SQLite には組み込みの JSON 関数がないため、JSON 文字列からデータを抽出するためのカスタム Python 関数を作成します。

まず、SQLite シェルを終了します。

.exit

次に、json_extractor.py という名前の Python ファイルを作成します。

nano json_extractor.py

次の Python コードを json_extractor.py ファイルに貼り付けます。

import sqlite3
import json

def json_extract(json_str, path):
    try:
        json_data = json.loads(json_str)
        path_components = path.split('.')
        value = json_data
        for component in path_components:
            value = value.get(component)
        return value
    except (json.JSONDecodeError, AttributeError, TypeError):
        return None

def connect_db(db_path):
    conn = sqlite3.connect(db_path)
    conn.create_function("json_extract", 2, json_extract)
    return conn

if __name__ == '__main__':
    conn = connect_db('mydatabase.db')
    cursor = conn.cursor()

    cursor.execute("SELECT json_extract(details, 'brand') FROM products WHERE name = 'Laptop'")
    print(cursor.fetchone()[0])

    cursor.execute("SELECT json_extract(details, 'specs.cpu') FROM products WHERE name = 'Laptop'")
    print(cursor.fetchone()[0])

    conn.close()

この Python コードは、JSON 文字列とパスを入力として受け取り、そのパスにある値を返す json_extract 関数を定義します。また、SQLite データベースに接続し、json_extract 関数を登録するための connect_db 関数も含まれています。

json.loads(json_str): この行は、JSON 文字列を Python の辞書 (dictionary) に解析します。
path.split('.'): これは、パスをコンポーネントのリストに分割します。たとえば、'specs.cpu' は ['specs', 'cpu'] になります。
ループはパスコンポーネントを反復処理し、JSON データ内のネストされた値にアクセスします。

ファイルを保存して nano を終了します。

次に、Python スクリプトを実行します。

python3 json_extractor.py

期待される出力 (Expected Output):

Dell
Intel i7

このスクリプトは、データベースに接続し、json_extract 関数を登録し、それを使用して Laptop のブランドと CPU を抽出します。

JSON クエリを使用したデータのフィルタリング (Filter Data Using JSON Queries)

このステップでは、カスタムの json_extract 関数を使用して、JSON フィールド内の値に基づいてデータをフィルタリングします。

json_extractor.py ファイルを再度開きます。

nano json_extractor.py

json_extractor.py ファイルを修正して、データベースをクエリするための関数を含めます。

import sqlite3
import json

def json_extract(json_str, path):
    try:
        json_data = json.loads(json_str)
        path_components = path.split('.')
        value = json_data
        for component in path_components:
            value = value.get(component)
        return value
    except (json.JSONDecodeError, AttributeError, TypeError):
        return None

def connect_db(db_path):
    conn = sqlite3.connect(db_path)
    conn.create_function("json_extract", 2, json_extract)
    return conn

def filter_products(db_path, json_path, value):
    conn = connect_db(db_path)
    cursor = conn.cursor()
    query = f"SELECT * FROM products WHERE json_extract(details, '{json_path}') = '{value}'"
    cursor.execute(query)
    results = cursor.fetchall()
    conn.close()
    return results

if __name__ == '__main__':
    ## Example usage:
    dell_products = filter_products('mydatabase.db', 'brand', 'Dell')
    print("Products with brand 'Dell':", dell_products)

    intel_products = filter_products('mydatabase.db', 'specs.cpu', 'Intel i7')
    print("Products with CPU 'Intel i7':", intel_products)

このコードは、データベースパス (database path)、JSON パス (JSON path)、および値を入力として受け取る filter_products 関数を追加します。次に、データベースに接続し、json_extract 関数を登録し、指定された JSON パスの値が指定された値と一致するすべての製品を検索するクエリを実行します。

ファイルを保存して nano を終了します。

次に、Python スクリプトを実行します。

python3 json_extractor.py

期待される出力 (Expected Output):

Products with brand 'Dell': [(1, 'Laptop', '{"brand": "Dell", "model": "XPS 13", "specs": {"cpu": "Intel i7", "memory": "16GB", "storage": "512GB SSD"}}')]
Products with CPU 'Intel i7': [(1, 'Laptop', '{"brand": "Dell", "model": "XPS 13", "specs": {"cpu": "Intel i7", "memory": "16GB", "storage": "512GB SSD"}}')]

この出力は、指定された基準に一致する製品を示しています。

JSON 値の更新 (Update JSON Values)

このステップでは、JSON フィールド内の値を更新する方法を学びます。

json_extractor.py ファイルを開きます。

nano json_extractor.py

json_extractor.py ファイルを修正して、JSON とデータベースを更新するための関数を含めます。

import sqlite3
import json

def json_extract(json_str, path):
    try:
        json_data = json.loads(json_str)
        path_components = path.split('.')
        value = json_data
        for component in path_components:
            value = value.get(component)
        return value
    except (json.JSONDecodeError, AttributeError, TypeError):
        return None

def connect_db(db_path):
    conn = sqlite3.connect(db_path)
    conn.create_function("json_extract", 2, json_extract)
    return conn

def filter_products(db_path, json_path, value):
    conn = connect_db(db_path)
    cursor = conn.cursor()
    query = f"SELECT * FROM products WHERE json_extract(details, '{json_path}') = '{value}'"
    cursor.execute(query)
    results = cursor.fetchall()
    conn.close()
    return results

def update_json_value(json_str, path, new_value):
    try:
        json_data = json.loads(json_str)
        path_components = path.split('.')
        target = json_data
        for i in range(len(path_components) - 1):
            target = target.get(path_components[i])

        target[path_components[-1]] = new_value
        return json.dumps(json_data)
    except (json.JSONDecodeError, AttributeError, TypeError):
        return json_str  ## Return original if update fails

def update_product_details(db_path, product_name, json_path, new_value):
    conn = sqlite3.connect(db_path)
    cursor = conn.cursor()

    ## Get the current details
    cursor.execute("SELECT details FROM products WHERE name = ?", (product_name,))
    result = cursor.fetchone()
    if not result:
        conn.close()
        return False

    current_details = result[0]

    ## Update the JSON
    updated_details = update_json_value(current_details, json_path, new_value)

    ## Update the database
    cursor.execute("UPDATE products SET details = ? WHERE name = ?", (updated_details, product_name))
    conn.commit()
    conn.close()
    return True

if __name__ == '__main__':
    ## Example usage:
    update_product_details('mydatabase.db', 'Laptop', 'specs.memory', '32GB')
    print("Laptop memory updated to 32GB")

    ## Verify the update
    conn = sqlite3.connect('mydatabase.db')
    cursor = conn.cursor()
    cursor.execute("SELECT details FROM products WHERE name = 'Laptop'")
    updated_details = cursor.fetchone()[0]
    print("Updated Laptop details:", updated_details)
    conn.close()

このコードは、次の 2 つの関数を追加します。

update_json_value: この関数は、JSON 文字列、パス (path)、および新しい値を入力として受け取ります。JSON 文字列を解析し、指定されたパスの値を更新し、更新された JSON 文字列を返します。
update_product_details: この関数は、データベースパス (database path)、製品名、JSON パス (JSON path)、および新しい値を入力として受け取ります。データベースに接続し、製品の現在の JSON データを取得し、update_json_value を使用して指定されたパスの値を更新し、変更された JSON データでデータベースを更新します。

ファイルを保存して nano を終了します。

次に、Python スクリプトを実行します。

python3 json_extractor.py

期待される出力 (Expected Output):

Laptop memory updated to 32GB
Updated Laptop details: {"brand": "Dell", "model": "XPS 13", "specs": {"cpu": "Intel i7", "memory": "32GB", "storage": "512GB SSD"}}

この出力は、Laptop のメモリ (memory) がデータベースで 32GB に更新されたことを確認します。

まとめ (Summary)

この実験 (lab) では、SQLite 内で JSON データを処理する方法を学びました。まず、データベースと JSON データを格納するテーブルを作成しました。次に、JSON データをテーブルに挿入する方法を学びました。JSON データから特定のフィールドを抽出するカスタム Python 関数を作成し、この関数を使用して JSON フィールド内の値に基づいてデータをフィルタリングしました。最後に、カスタム Python 関数を使用して JSON フィールド内の値を更新する方法を学びました。これらのスキルは、SQLite データベース内で JSON データを効果的に管理するための基礎となります。