Работа с базами данных SQL в Python: подробный гайд

В современном мире, где объем информации стремительно растет, ее эффективная обработка становится ключевым моментом в любой сфере человеческой деятельности. В этом контексте базы данных (БД) становятся своего рода хранилищем, позволяя не только сохранять информацию, но и грамотно управлять ей. В инструкции ниже мы ознакомимся с тем, как происходит работа с базами данных в Python.

Значимую роль в организации БД играет SQL (Structured Query Language). Этот язык запросов предоставляет удобный способ взаимодействия с данными, позволяя создавать, изменять и извлекать информацию. Сочетание Python и SQL предоставляет удобные средства для автоматизации процессов, а также позволяет разработчикам создавать гибкие и масштабируемые приложения. 

Работа с SQL в Python осуществляется через системы управления базами данных. Существует множество СУБД, каждая со своими особенностями и назначениями. От классических реляционных баз данных, таких как MySQL и PostgreSQL, до NoSQL-решений, таких как MongoDB, выбор подходящей СУБД зависит от конкретных требований проекта. В рамках данной инструкции мы рассмотрим такие системы, как:

• SQLite;
• MySQL;
• PostgreSQL.

В этом гайде мы разберем ключевые шаги взаимодействия с базами данных SQL в Python, предоставив подробную инструкцию по использованию основных операций на примере различных СУБД. Начнем с установки библиотек и подключения к базе данных.

dbaas

Установка необходимых библиотек

До начала работы с SQL базами данных в Python необходимо установить соответствующие библиотеки. Каждая БД имеет свой пакет, рассмотрим некоторые из них.

Для работы с SQLite необходимо библиотека sqlite3. Эта библиотека входит в стандартную библиотеку Python, поэтому ее нет необходимости устанавливать отдельно. Но для работы с MySQL и PostgreSQL установим библиотеки.

Для MySQL:

pip install mysql-connector-python

Для PostgreSQL:

pip install psycopg2

Общий способ установки для большинства БД:

pip install SQLAlchemy

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

1. Операция 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() применяет изменения. 

2. Операция 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-запрос с помощью курсора, получаем результат выборки и выводим результат:

3. Операция UPDATE применяется для изменения существующих записей. Для этого используется оператор UPDATE. Изменим цену продукта с product_id = 1 на новое значение 109.99:

new_price = 109.99
product_id_to_update = 1

SQL-запрос для добавления записей для 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()

4. Операция DELETE позволяет удалить записи из таблицы. Для этого также используется оператор DELETE.

Пример для SQLite

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();
'''

Подготовили для вас выгодные тарифы на DBaaS

Заключение

В данной инструкции мы детально исследовали основы взаимодействия с базами данных, используя язык программирования Python. Теперь вы умеете подключаться к БД и создавать таблицы, а также использовать основные операции CRUD для эффективного управления данными. Мы также рассмотрели аспекты работы с индексами, проведение транзакций и использование триггеров.

Этот гайд служит введением в Python SQL. Дальнейшие шаги могут включать в себя изучение более сложных запросов, оптимизацию производительности и использование более продвинутых функций, специфичных для каждой конкретной СУБД.