В этой лабораторной работе вы узнаете, как обрабатывать данные 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 в таблицу 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.
Поскольку 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'].Сохраните файл и выйдите из nano.
Теперь запустите скрипт Python.
python3 json_extractor.pyОжидаемый вывод:
Dell
Intel i7Этот скрипт подключается к базе данных, регистрирует функцию json_extract и затем использует ее для извлечения бренда и процессора ноутбука.
На этом шаге вы будете использовать пользовательскую функцию 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_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.
