Материал подготовлен командой Simple-Server для администраторов VPS и выделенных серверов. Команды и пути проверяйте на тестовой машине перед production.
Установка необходимых библиотек
До начала**** работы с SQL базами данных в Python необходимо установить соответствующие библиотеки. Каждая БД имеет свой пакет, рассмотрим некоторые из них.
Для работы с SQLite необходимо библиотека sqlite3. Эта библиотека входит в стандартную библиотеку Python, поэтому ее нет необходимости устанавливать отдельно. Но для работы с MySQL и PostgreSQL установим библиотеки.
pip install mysql-connector-pythonОбщий способ установки для большинства БД:
Библиотека SQLAlchemy предоставляет уровень абстракции над различными СУБД, что дает возможность разработчикам взаимодействовать с разными системами, не требуя изменения основного кода. Кроме того, она включает механизм ORM (Object-Relational Mapping), который необходим для обеспечения работы с БД, представляя информацию в виде объектов Python. Вместо написания прямых SQL-запросов, вы можете взаимодействовать с БД, используя объекты Python, которые отображают таблицы в базе данных.
После установки соответствующих библиотек мы сможем устанавливать соединение с базой данных SQL прямо из среды выполнения Python.
Установка соединения с базой данных
Установка связи с БД SQL является значимым этапом перед началом работы с хранилищем информации. Этот шаг обеспечивает взаимодействие между вашим кодом на Python и физическим хранилищем информации. Процесс подключения зависит от используемой СУБД.
Для взаимодействия с БД SQLite необходимо импортировать библиотеку sqlite3, а затем выполнить подключение к БД, хранящейся в файле:
import sqlite3 as sq
connection = sq.connect('your_database_name.db')Если файл БД не существует, то он автоматически создастся.
Для взаимодействия с БД MySQL в Python применяется библиотека mysql.connector. Рассмотрим пример использования этой библиотеки для установки соединения:
import mysql.connector as con
connection = con.connect(
host='your_actual_host',
user='your_actual_user',
password='your_actual_password',
database='your_actual_database'
)your_actual_host— это ваш адрес хоста в БД MySQL;your_actual_user— это ваше имя пользователя для доступа к БД;your_actual_password— это ваш пароль для пользователя;your_actual_database— это ваше имя БД, с которой вы хотите взаимодействовать.
Для взаимодействия с БД PostgreSQL в Python применяется библиотека psycopg2. Пример использования psycopg2 для создания соединения:
import psycopg2 as ps
connection = ps.connect(
host='your_actual_host',
user='your_actual_user',
password='your_actual_password',
database='your_actual_database'
)В случае использования SQLAlchemy подключение может быть выполнено следующим образом:
from sqlalchemy import create_engine
database_url = 'sqlite:///your_database.db'
engine = create_engine(database_url)Если вы используете SQLAlchemy, URL — это стандартный способ представления параметров подключения. Он может включать информацию о хосте, пользователе, пароле, порте и других параметрах, зависящих от типа БД.
После успешного создания соединения (connection) следующим шагом является создание объекта курсора. Курсор обеспечивает программу интерфейсом для перемещения по результатам запроса, извлечения информации и внесения изменений в записи. Этот шаг не зависит от используемой БД, поскольку он обеспечивает общий интерфейс для выполнения SQL-запросов:
cursor = connection.cursor()Теперь все готово для выполнения SQL-запросов в Python и взаимодействия с базой данных.
Когда вы завершаете работу с курсором, например, после выполнения всех операций, важно закрыть как курсор, так и соединение:
cursor.close()
connection.close()Это критически важно для эффективного управления ресурсами и предотвращения утечек памяти.
После успешного установления связи с базой данных вы готовы приступить к выполнению SQL-запросов и различных операций.
Формирование таблиц и схем в СУБД
Перед началом взаимодействия с БД потребуется создать таблицу SQL в Python. Таблицы представляют собой сущности, где каждая строка соответствует конкретной записи, а столбцы определяют различные атрибуты этой записи. Это важный этап проектирования базы данных, где определяются структура и типы записей, которые будут храниться в БД.
Рассмотрим пример создания для SQLite:
products_tb = '''
CREATE TABLE IF NOT EXISTS products (
product_id INTEGER PRIMARY KEY AUTOINCREMENT,
product_nm TEXT NOT NULL,
price REAL CHECK (price >= 0),
stock_quantity INTEGER CHECK(stock_quantity >= 0)
)
'''Этот SQL-запрос определяет таблицу products с полями:
product_id, уникальный идентификатор продукта, который служит в качестве первичного ключа;product_nm, текстовое поле для наименования продукта.NOT NULLуказывает, что это поле обязательно должно содержать информацию;price, цена продукта, может содержать десятичные значения;stock_quantity, количество продуктов в наличии на складе, это поле может содержать только целые числа.
Комбинация всех этих полей создает таблицу, в которой каждая запись (строка) представляет отдельный продукт, а каждый столбец содержит информацию об этом продукте.
После создания таблицы необходимо выполнить SQL-запрос в Python и применить изменения:
cursor.execute(products_tb)
connection.commit()После выполнения этих шагов у вас будет создана таблица products в БД SQLite, готовая к использованию. Мы можем открыть базу данных в каком-нибудь браузере баз данных SQLite, например, в DB Browser for SQLite и увидеть созданную таблицу:
Обратите внимание, что создание таблицы требуется выполнить только один раз, из-за этого существует проверка IF NOT EXISTS (если не существует), перед началом работы с БД.
Аналогичным образом напишем SQL-запрос для создания таблицы для MySQL:
products_tb = '''
CREATE TABLE IF NOT EXISTS products (
product_id INT AUTO_INCREMENT PRIMARY KEY,
product_nm VARCHAR(255) NOT NULL,
price DECIMAL(10, 2),
stock_quantity INT
)
'''
cursor.execute(products_tb)
connection.commit()И рассмотрим создание таблицы products в БД PostgreSQL:
products_tb = '''
CREATE TABLE IF NOT EXISTS products (
product_id SERIAL PRIMARY KEY,
product_nm VARCHAR(255) NOT NULL,
price DECIMAL(10, 2),
stock_quantity INT
);
'''
cursor.execute(products_tb)
connection.commit()Все эти примеры демонстрируют создание таблиц для разных СУБД. Вы можете адаптировать SQL-запросы в зависимости от ваших требований к структуре БД.
Выполнение запросов SQL с использованием Python
После успешного формирования таблиц мы переходим к осуществлению основных операций, известных как операции CRUD (Create, Read, Update, Delete). Эти процедуры предоставляют возможность добавлять, читать, обновлять и удалять информацию в БД. Давайте рассмотрим каждую из этих операций более подробно.
- Операция
CREATEв SQL отвечает за создание новых записей в таблице. Она позволяет добавлять записи, указывая значения для каждого столбца. Для этого мы используем оператор INSERT. Приведем примеры добавления новой записи для каждой из рассматриваемых баз данных.
Добавим информацию о новом продукте в таблицу, которую мы создавали в предыдущем разделе:
new_product = ('Laptop', 99.999, 10)SQL-запрос для добавления записей для SQLite:
request_to_insert_data = '''
INSERT INTO products (product_nm, price, stock_quantity) VALUES (?, ?, ?);
'''Если мы теперь посмотрим на содержимое нашей базы данных, то найдем там добавленный объект:
SQL-запрос для добавления записей для MySQL и PostgeSQL будет одинаковым:
request_to_insert_data = '''
INSERT INTO products (product_nm, price, stock_quantity) VALUES (%s, %s, %s);
'''После написания SQL-запроса для любой СУБД необходимо прописать команду для выполнения SQL-запроса:
cursor.execute(requst_to_insert_data, new_product)
connection.commit()Команда cursor.execute() выполняет запрос, используя переданный SQL-запрос и данные, и добавляет новую запись в БД, а команда connection.commit() применяет изменения.
- Операция READ предоставляет возможность извлечения данных из таблицы. Для выполнения процедуры чтения записей из таблицы воспользуемся оператором
SELECT. Общий синтаксис для всех трех СУБД (SQLite, MySQL и PostgreSQL):
request_to_read_data = "SELECT * FROM products"
cursor.execute(request_to_read_data)
data = cursor.fetchall()
for row in data:
print(data)В приведенном выше коде мы формируем SQL-запрос для выборки всех данных из таблицы products, выполняем SQL-запрос с помощью курсора, получаем результат выборки и выводим результат:
- Операция
UPDATEприменяется для изменения существующих записей. Для этого используется операторUPDATE. Изменим цену продукта сproduct_id = 1на новое значение 109.99:
new_price = 109.99
product_id_to_update = 1SQL-запрос для добавления записей для SQLite:
request_to_update_data = "UPDATE products SET price = ? WHERE product_id = ?"
cursor.execute(request_to_update_data, (new_price, product_id_to_update))
connection.commit()Можем убедиться, что данные в БД изменились:
SQL-запрос для добавления записей для MySQL и PostgreSQL будет одинаковым:
request_to_update_data = "UPDATE products SET price = %s WHERE product_id = %s"
cursor.execute(request_to_update_data, (new_price, product_id_to_update))
connection.commit()- Операция
DELETEпозволяет удалить записи из таблицы. Для этого также используется операторDELETE.
product_id_to_delete = 1
request_to_delete_data = "DELETE FROM products WHERE product_id = ?"
cursor.execute(request_to_delete_data, (product_id_to_delete,))Теперь запись, которую мы сделали до этого, отсутсвует:
Для MySQL и PostgreSQL синтаксис будет одинаковый:
product_id_to_delete = 1
request_to_delete_data = "DELETE FROM products WHERE product_id = %s"
cursor.execute(request_to_delete_data, (product_id_to_delete,))
connection.commit()Мы ранее упоминали, как важно правильно завершать взаимодействие с БД. После выполнения всех операций CRUD не забывайте о завершающих действиях: закрытие курсора и разъединение:
cursor.close()
connection.close()Теперь, когда мы освоили основы операций CRUD, давайте подробнее рассмотрим возможности**** SQL в Python. Рассмотрим варианты улучшения эффективности запросов с применением индексов, обеспечим сохранность информации при помощи транзакций, и также рассмотрим вопрос о создании триггеров для автоматизации определенных процессов.
Оптимизация поиска в базах данных
Индексы — это структуры данных, связанные с значениями в одном или нескольких столбцах таблицы. Эти структуры обеспечивают быстрый доступ БД к конкретным значениям, что существенно улучшает выполнение запросов. Создание индекса дополняет БД дополнительной структурой данных, представляющей собой отсортированный список уникальных значений выбранных столбцов. Такой подход сокращает количество строк, которые необходимо просматривать для поиска конкретной информации.
Для создания индекса используется оператор CREATE INDEX. Пример создания индекса для оптимизации поиска по столбцу product_nm в таблице products:
request_to_index = "CREATE INDEX idx_product_nm ON products (product_nm(255))"
cursor.execute(request_to_index)
connection.commit()idx_product_nm— название индекса;products— название таблицы;product_nm— название столбца, для которого создается индекс.
Обеспечение целостности данных
Транзакции отвечают за целостность информации в базе данных. Они гарантируют, что серия операций выполняется атомарно, то есть, либо все изменения применяются успешно, либо ни одно из них не выполняется. Рассмотрим, как работать с транзакциями в контексте SQLite.
В SQLite транзакции автоматически выполняются каждый раз, когда вы выполняете операцию изменения записей (INSERT, UPDATE, DELETE). Если действия выполнены успешно, изменения сохраняются, в противном случае — откатываются:
try:
update_query = "UPDATE products SET price = ? WHERE product_id = ?"
new_price = 123.456
product_id_to_update = 1
cursor.execute(update_query, (new_price, product_id_to_update))
connection.commit()
print("Транзакция успешно завершена.")
except Exception as e:
connection.rollback()
print(f"Произошла ошибка: {str(e)} Транзакция откатывается.")
finally:
connection.close()В этом примере мы используем блок try-except-finally для обработки транзакций. Если какая-либо операция в блоке try вызывает исключение, транзакция откатывается rollback(). В противном случае изменения подтверждаются методом commit(). При успешном выполнении в БД произойдет изменение записи:
На консоль будет выведен текст об успешном выполнении транзакции:
Для MySQL и PostgreSQL синтаксис транзакций будет аналогичным с SQLite. Оба языка поддерживают стандартные операции COMMIT и ROLLBACK для управления транзакциями. Не забудьте о том, что для подстановки параметров в MySQL и PostgreSQL используется %s.
Автоматизация реакций на события
Триггеры представляют собой уникальный вид хранимых процедур, которые автоматически активируются при возникновении определенных событий в базе данных. Они служат механизмом для автоматизации реакции на изменения информации, будь то вставка новых записей, обновление существующих или удаление определенных записей.
В SQLite триггеры могут быть активированы для событий INSERT, UPDATE и DELETE. Например, у нас есть таблица orders с полями order_id, product_nm и quantity, и мы хотим создать триггер, который при каждом добавлении нового заказа автоматически уменьшает количество товара в таблице inventory:
trigger_request = '''
CREATE TRIGGER decrease_inventory
AFTER INSERT ON orders
BEGIN
UPDATE inventory
SET stock_quantity = stock_quantity - NEW.quantity
WHERE product_nm = NEW.product_nm;
END;
'''Триггер decrease_inventory создается после операции INSERT на таблице orders. Он автоматически вызывается при добавлении нового заказа. Внутри триггера происходит обновление количества товара в таблице inventory на основе данных, вставленных в таблицу orders.
Синтаксис для аналогичного триггера, который будет выполнять ту же самую задачу, но уже в MySQL не отличается от синтаксиса SQLite, за исключением ключевого слова FOR EACH ROW, которое указывает на то, что триггер должен выполняться отдельно для каждой строки, затронутой операцией, которую вызвал триггер (в SQLite триггеры по умолчанию рассматриваются, как триггеры для каждой строки FOR EACH ROW, поэтому не требуется явно указывать это). Данное ключевое слово необходимо прописать перед началом блока BEGIN … END.
В PostgreSQL синтаксис триггеров немного отличается. Для начала необходимо создать функцию для триггера:
function_request = '''
CREATE OR REPLACE FUNCTION decrease_inventory()
RETURNS TRIGGER AS $
BEGIN
UPDATE inventory
SET stock_quantity = stock_quantity - NEW.quantity
WHERE product_nm = NEW.product_nm;
RETURN NEW;
END;
$ LANGUAGE plpgsql;
'''После этого происходит создание самого триггера:
trigger_request = '''
CREATE TRIGGER decrease_inventory
AFTER INSERT ON orders
FOR EACH ROW
EXECUTE FUNCTION decrease_inventory();
'''Нужен сервер для практики? Арендуйте VPS/VDS в России — root-доступ, NVMe, DDoS-защита и поддержка 24/7.