LabEx
ВходРегистрация

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

SQLiteBeginner
Практиковаться сейчас

Введение

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

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

На этом шаге вы создадите базу данных 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

## Определение функции для извлечения значения из строки JSON по пути
def json_extract(json_str, path):
    try:
        ## Парсинг строки JSON в словарь Python
        json_data = json.loads(json_str)
        ## Разделение пути на компоненты (например, 'specs.cpu' становится ['specs', 'cpu'])
        path_components = path.split('.')
        ## Начинаем с полного объекта JSON
        value = json_data
        ## Обход объекта JSON с использованием компонентов пути
        for component in path_components:
            ## Получение значения для текущего компонента
            value = value.get(component)
        ## Возврат конечного значения
        return value
    ## Обработка ошибок, если JSON недействителен или путь не существует
    except (json.JSONDecodeError, AttributeError, TypeError):
        return None

## Определение функции для подключения к базе данных и регистрации пользовательской функции
def connect_db(db_path):
    ## Подключение к базе данных SQLite по указанному пути
    conn = sqlite3.connect(db_path)
    ## Регистрация функции Python 'json_extract' как пользовательской функции SQL
    ## "json_extract" - это имя в SQL, 2 - количество аргументов, json_extract - это функция Python
    conn.create_function("json_extract", 2, json_extract)
    ## Возврат соединения с базой данных
    return conn

## Этот блок выполняется при прямом запуске скрипта
if __name__ == '__main__':
    ## Подключение к базе данных и регистрация функции
    conn = connect_db('mydatabase.db')
    ## Создание объекта курсора для выполнения SQL-запросов
    cursor = conn.cursor()

    ## Использование пользовательской SQL-функции для извлечения 'brand' из столбца 'details'
    cursor.execute("SELECT json_extract(details, 'brand') FROM products WHERE name = 'Laptop'")
    ## Получение результата и его вывод
    print(cursor.fetchone()[0])

    ## Использование пользовательской SQL-функции для извлечения 'cpu' из вложенного объекта 'specs'
    cursor.execute("SELECT json_extract(details, 'specs.cpu') FROM products WHERE name = 'Laptop'")
    ## Получение результата и его вывод
    print(cursor.fetchone()[0])

    ## Закрытие соединения с базой данных
    conn.close()

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

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

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

Снова откройте файл json_extractor.py.

nano json_extractor.py

Измените файл json_extractor.py, добавив функцию для запроса к базе данных:

## Импорт необходимых библиотек
import sqlite3
import json

## Определение функции для извлечения значения из строки JSON по пути
def json_extract(json_str, path):
    try:
        ## Парсинг строки JSON в словарь Python
        json_data = json.loads(json_str)
        ## Разделение пути на компоненты (например, 'specs.cpu' становится ['specs', 'cpu'])
        path_components = path.split('.')
        ## Начинаем с полного объекта JSON
        value = json_data
        ## Обход объекта JSON с использованием компонентов пути
        for component in path_components:
            ## Получение значения для текущего компонента
            value = value.get(component)
        ## Возврат конечного значения
        return value
    ## Обработка ошибок, если JSON недействителен или путь не существует
    except (json.JSONDecodeError, AttributeError, TypeError):
        return None

## Определение функции для подключения к базе данных и регистрации пользовательской функции
def connect_db(db_path):
    ## Подключение к базе данных SQLite по указанному пути
    conn = sqlite3.connect(db_path)
    ## Регистрация функции Python 'json_extract' как пользовательской SQL-функции
    conn.create_function("json_extract", 2, json_extract)
    ## Возврат соединения с базой данных
    return conn

## Определение функции для фильтрации продуктов на основе поля JSON
def filter_products(db_path, json_path, value):
    ## Подключение к базе данных
    conn = connect_db(db_path)
    ## Создание объекта курсора
    cursor = conn.cursor()
    ## Создание SQL-запроса с использованием f-строки для фильтрации по значению JSON
    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__':
    ## Пример использования:
    ## Фильтрация продуктов, где бренд - 'Dell'
    dell_products = filter_products('mydatabase.db', 'brand', 'Dell')
    print("Products with brand 'Dell':", dell_products)

    ## Фильтрация продуктов, где процессор - 'Intel i7'
    intel_products = filter_products('mydatabase.db', 'specs.cpu', 'Intel i7')
    print("Products with CPU 'Intel i7':", intel_products)

Этот код добавляет функцию filter_products, которая принимает путь к базе данных, путь к JSON и значение в качестве входных данных. Затем он подключается к базе данных, регистрирует функцию 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

## Определение функции для извлечения значения из строки JSON по пути
def json_extract(json_str, path):
    try:
        ## Парсинг строки JSON в словарь Python
        json_data = json.loads(json_str)
        ## Разделение пути на компоненты
        path_components = path.split('.')
        ## Начинаем с полного объекта JSON
        value = json_data
        ## Обход объекта JSON
        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)
    ## Регистрация пользовательской SQL-функции
    conn.create_function("json_extract", 2, json_extract)
    return conn

## Определение функции для фильтрации продуктов на основе поля JSON
def filter_products(db_path, json_path, value):
    ## Подключение к базе данных
    conn = connect_db(db_path)
    ## Создание объекта курсора
    cursor = conn.cursor()
    ## Создание SQL-запроса для фильтрации по значению JSON
    query = f"SELECT * FROM products WHERE json_extract(details, '{json_path}') = '{value}'"
    ## Выполнение запроса
    cursor.execute(query)
    ## Получение всех совпадающих результатов
    results = cursor.fetchall()
    ## Закрытие соединения
    conn.close()
    return results

## Определение функции для обновления значения в строке JSON
def update_json_value(json_str, path, new_value):
    try:
        ## Парсинг строки JSON в словарь Python
        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
        ## Преобразование словаря Python обратно в строку JSON
        return json.dumps(json_data)
    except (json.JSONDecodeError, AttributeError, TypeError):
        ## В случае ошибки возвращаем исходную строку JSON
        return json_str

## Определение функции для обновления деталей продукта в базе данных
def update_product_details(db_path, product_name, json_path, new_value):
    ## Подключение к базе данных
    conn = sqlite3.connect(db_path)
    ## Создание объекта курсора
    cursor = conn.cursor()

    ## Получение текущих деталей для указанного продукта
    ## '?' является заполнитель для предотвращения SQL-инъекций
    cursor.execute("SELECT details FROM products WHERE name = ?", (product_name,))
    result = cursor.fetchone()
    ## Если продукт не существует, закрываем соединение и возвращаем False
    if not result:
        conn.close()
        return False

    ## Получение текущей строки деталей JSON
    current_details = result[0]

    ## Обновление строки JSON новым значением
    updated_details = update_json_value(current_details, json_path, new_value)

    ## Обновление базы данных новой строкой JSON
    cursor.execute("UPDATE products SET details = ? WHERE name = ?", (updated_details, product_name))
    ## Фиксация изменений в базе данных
    conn.commit()
    ## Закрытие соединения
    conn.close()
    return True

## Этот блок выполняется при прямом запуске скрипта
if __name__ == '__main__':
    ## Пример использования: обновление памяти ноутбука до 32 ГБ
    update_product_details('mydatabase.db', 'Laptop', 'specs.memory', '32GB')
    print("Laptop memory updated to 32GB")

    ## Проверка обновления путем повторного получения данных
    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, путь и новое значение в качестве входных данных. Она парсит строку JSON, обновляет значение по указанному пути и возвращает обновленную строку JSON.
  • update_product_details: Эта функция принимает путь к базе данных, имя продукта, путь к JSON и новое значение в качестве входных данных. Она подключается к базе данных, извлекает текущие данные 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"}}

Этот вывод подтверждает, что память ноутбука была обновлена до 32 ГБ в базе данных.

Резюме

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

Связанные SQLite Курсы
SQLite для начинающих

SQLite для начинающих

databasesqlite
SQLite: от среднего к продвинутому

SQLite: от среднего к продвинутому

databasesqlite