Обработка JSON в SQLite

Введение

В этой лабораторной работе вы узнаете, как обрабатывать данные JSON в SQLite. Вы изучите, как хранить, извлекать, фильтровать и обновлять данные JSON в базе данных SQLite. Эта лабораторная работа представляет собой практическое введение в работу с данными JSON в SQLite, навык, который становится все более ценным в современном управлении данными.

Skills Graph

Создание базы данных и таблицы

На этом шаге вы создадите базу данных SQLite и таблицу для хранения данных JSON. SQLite — это легковесная база данных, которая хранит данные в одном файле, что упрощает управление.

Сначала откройте свой терминал. Путь по умолчанию: /home/labex/project.

Теперь давайте создадим каталог для хранения нашей базы данных.

mkdir sqlite_json
cd sqlite_json

Эти команды создают каталог с именем sqlite_json, а затем изменяют текущий каталог на него. Это поможет организовать файлы вашего проекта.

Далее создайте базу данных SQLite с именем mydatabase.db.

sqlite3 mydatabase.db

Эта команда открывает оболочку SQLite, подключаясь к базе данных mydatabase.db. Если файл базы данных не существует, SQLite создаст его.

Теперь создайте таблицу с именем products со столбцами для id, name и details. Столбец details будет хранить данные JSON в виде текста.

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

Эта команда SQL создает таблицу products:

• id: Уникальное целое число, которое автоматически увеличивается для каждого нового продукта.
• name: Название продукта (например, Laptop, Smartphone).
• details: Текстовое поле для хранения данных JSON для продукта.

Вставка данных JSON

На этом шаге вы вставите данные JSON в таблицу products.

Давайте вставим две демонстрационные записи в таблицу products.

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. Столбец details содержит данные JSON в виде текстовой строки.

Чтобы убедиться, что данные были вставлены правильно, выполните следующий запрос:

SELECT * FROM products;

Ожидаемый результат:

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 с помощью пользовательской функции

Поскольку в SQLite нет встроенных функций JSON, вы создадите пользовательскую функцию Python для извлечения данных из строк JSON.

Сначала выйдите из оболочки SQLite.

.exit

Теперь создайте файл Python с именем json_extractor.py.

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_extract, которая принимает строку JSON и путь (path) в качестве входных данных и возвращает значение по этому пути. Он также включает функцию connect_db для подключения к базе данных SQLite и регистрации функции json_extract.

• json.loads(json_str): Эта строка преобразует строку JSON в словарь Python.
• path.split('.'): Это разделяет путь на список компонентов. Например, 'specs.cpu' становится ['specs', 'cpu'].
• Цикл перебирает компоненты пути, получая доступ к вложенным значениям в данных JSON.

Сохраните файл и выйдите из nano.

Теперь запустите скрипт Python.

python3 json_extractor.py

Ожидаемый результат:

Dell
Intel i7

Этот скрипт подключается к базе данных, регистрирует функцию json_extract, а затем использует ее для извлечения бренда (brand) и CPU (центрального процессора) ноутбука (Laptop).

Фильтрация данных с использованием JSON-запросов

На этом шаге вы будете использовать пользовательскую функцию 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)

Этот код добавляет функцию filter_products, которая принимает путь к базе данных (database path), путь JSON (JSON path) и значение (value) в качестве входных данных. Затем он подключается к базе данных, регистрирует функцию json_extract и выполняет запрос для поиска всех продуктов, где значение по указанному пути JSON соответствует заданному значению.

Сохраните файл и выйдите из nano.

Теперь запустите скрипт Python.

python3 json_extractor.py

Ожидаемый результат:

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

На этом шаге вы узнаете, как обновлять значения внутри полей 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()

Этот код добавляет две функции:

• update_json_value: Эта функция принимает строку JSON, путь (path) и новое значение (new value) в качестве входных данных. Она анализирует строку JSON, обновляет значение по указанному пути и возвращает обновленную строку JSON.
• update_product_details: Эта функция принимает путь к базе данных (database path), название продукта (product name), путь JSON (JSON path) и новое значение (new value) в качестве входных данных. Она подключается к базе данных, извлекает текущие данные JSON для продукта, обновляет значение по указанному пути с помощью update_json_value, а затем обновляет базу данных измененными данными JSON.

Сохраните файл и выйдите из nano.

Теперь запустите скрипт Python.

python3 json_extractor.py

Ожидаемый результат:

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

Этот вывод подтверждает, что объем памяти (memory) ноутбука (Laptop) был обновлен до 32 ГБ в базе данных.

Итог

В этой лабораторной работе вы научились обрабатывать данные JSON в SQLite. Вы начали с создания базы данных и таблицы для хранения данных JSON. Затем вы узнали, как вставлять данные JSON в таблицу. Вы создали пользовательскую функцию Python для извлечения определенных полей из данных JSON и использовали эту функцию для фильтрации данных на основе значений внутри полей JSON. Наконец, вы узнали, как обновлять значения внутри полей JSON с помощью пользовательской функции Python. Эти навыки обеспечивают основу для эффективного управления данными JSON в базах данных SQLite.