Python и SQL: практическая работа с данными

Основы Python для работы с данными

Python часто используют как клей между источниками данных (файлы, API, базы данных) и аналитикой: он помогает загрузить данные, привести их к нужному виду, проверить качество и подготовить к SQL-запросам или дальнейшей обработке.

В этом курсе Python и SQL будут работать вместе:

Python отвечает за загрузку, очистку, преобразование и автоматизацию

SQL отвечает за хранение, объединение, агрегацию и быстрые запросы к данным

!Общая картина того, как Python подготавливает данные для SQL и аналитики

Подготовка окружения

Для практики удобно иметь отдельное окружение под проект, чтобы зависимости не конфликтовали.

Установите Python 3.11+ (подойдёт и 3.10)

Создавайте виртуальное окружение для каждого проекта

Устанавливайте библиотеки через pip

Команды (macOS/Linux):

Команды (Windows PowerShell):

Полезные ссылки:

Виртуальные окружения Python (venv)

Установка пакетов (pip)

Базовые типы данных и переменные

В работе с данными важно уверенно отличать числа, строки, логические значения и отсутствие значения.

Основные типы:

int — целые числа

float — числа с дробной частью

str — строки

bool — True или False

None — значение отсутствует

Пример:

Практический смысл для данных:

None часто означает пропуск (например, пустое поле в CSV)

строки почти всегда нужно чистить: пробелы, регистр, разделители

числа нужно приводить к правильному типу до загрузки в базу

Коллекции: list, tuple, dict, set

Данные редко приходят одним значением — обычно это набор строк, записей и полей.

Когда какую коллекцию использовать

| Коллекция | Как выглядит | Ключевая идея | Частый случай в данных | |---|---|---|---| | list | [1, 2, 3] | упорядоченный список | строки таблицы, список значений | | tuple | (1, 2, 3) | неизменяемая последовательность | пары, фиксированные группы | | dict | {'id': 1} | ключ → значение | запись (строка) как набор полей | | set | {1, 2, 3} | уникальные значения | поиск уникальных категорий |

Пример: одна запись (строка таблицы) как словарь:

Пример: набор записей как список словарей:

Управляющие конструкции: условия и циклы

Для подготовки данных чаще всего нужны:

фильтрация (оставить только нужные строки)

преобразование (изменить поля)

накопление (посчитать суммы, количества)

Условия:

Циклы и полезные функции:

Генераторы списков (list comprehension)

Это компактный способ преобразовать или отфильтровать список.

Важно: читаемость важнее краткости. Если выражение сложное, лучше обычный цикл.

Функции для повторяемых шагов

В обработке данных почти всегда повторяются одни и те же действия: чистка строк, преобразование типов, проверка качества. Это удобно упаковывать в функции.

Пример функции, которая пытается привести значение к числу:

Хорошая привычка: давать функциям понятные имена и держать их маленькими.

Строки: чистка и нормализация

Большая часть проблем с данными — это проблемы со строками.

Частые операции:

strip() — убрать пробелы по краям

lower() / upper() — нормализовать регистр

replace() — заменить подстроки

split() / join() — разобрать и собрать строки

Пример:

Даты и время

Для дат используйте модуль datetime, а не строки.

Справочник форматов дат:

datetime — базовые типы даты и времени

Файлы и пути: безопасное чтение и запись

Работа с данными часто начинается с файлов: CSV, JSON, логов.

Пути через pathlib

pathlib делает код переносимым между Windows/macOS/Linux.

Ссылка:

pathlib — объектно-ориентированные пути

Контекстный менеджер with

Открывайте файлы через with, чтобы файл гарантированно закрывался.

Форматы данных: CSV и JSON

CSV

CSV часто используют для выгрузок из таблиц и передачи данных между системами.

Ссылка:

csv — чтение и запись CSV

JSON

JSON часто приходит из API или хранит вложенные структуры.

Ссылка:

json — кодирование и декодирование JSON

Ошибки и надёжность: try/except

В реальных данных бывают неожиданные значения. Лучше предусмотреть это в коде.

Пример: безопасное преобразование в число.

Ключевая идея: пусть плохая строка не ломает весь процесс, а аккуратно помечается как пропуск.

Мини-пайплайн: прочитать CSV, почистить, записать результат

Ниже пример типичного шага подготовки данных: читаем CSV, чистим поля, пишем новый CSV.

Что важно в этом примере:

Мы читаем строки как словари через csv.DictReader, чтобы обращаться к полям по имени.

Мы выносим чистку в функции, чтобы код был переиспользуемым.

Мы допускаем пропуски (None) и не падаем на плохих значениях.

Мост к SQL: загрузка подготовленных данных в базу

Дальше по курсу мы будем активно использовать SQL. Уже сейчас полезно знать, что Python умеет работать с базами.

Встроенная база для учебных примеров — SQLite, модуль sqlite3 идёт вместе с Python.

Пример вставки данных с параметрами (это безопаснее, чем склеивать строку запроса):

Ссылка:

sqlite3 — интерфейс DB-API 2.0 для SQLite

Что дальше по курсу

После этих основ мы будем углубляться в практику:

загрузка данных в SQL-таблицы и извлечение обратно в Python

более системная очистка и проверка качества данных

регулярные пайплайны: повторяемые процессы обработки

Эта статья — фундамент: типы данных, коллекции, функции, работа с файлами и обработка ошибок. Это то, на чём строится любая практическая работа с данными в Python.

Основы SQL: SELECT, фильтрация, сортировка, агрегаты

SQL нужен, когда данные уже лежат в таблицах и вы хотите быстро выбрать нужное, отфильтровать лишнее, отсортировать и посчитать итоговые метрики. В прошлом уроке мы разбирали, как Python читает файлы (CSV/JSON), чистит значения и готовит данные к загрузке в базу. Теперь мы учимся извлекать и суммировать данные на стороне базы.

Роль связки в курсе:

Python: загрузка, очистка, автоматизация пайплайнов

SQL: выборка, фильтрация, объединение, агрегации и быстрые запросы к большим данным

Мини-датасет для примеров

Чтобы примеры были практичными, будем думать о таблице заказов.

Пример строк (упрощённо):

| order_id | customer | city | amount | status | created_at | |---:|---|---|---:|---|---| | 1001 | Alice | Berlin | 199.90 | paid | 2026-01-10 | | 1002 | Bob | Berlin | 49.90 | canceled | 2026-01-11 | | 1003 | Alice | Paris | 20.00 | paid | 2026-01-11 | | 1004 | Diana | Paris | 120.00 | paid | 2026-02-01 |

В реальном проекте такие данные часто приходят из CSV, где Python делает чистку строк, приводит числа и даты к нужному виду, а затем загружает в SQL.

Как читать SQL-запрос: общая структура SELECT

Базовый запрос выглядит так:

SELECT отвечает за какие столбцы вернуть

FROM отвечает за из какой таблицы брать данные

Выбор столбцов

Выбирать можно конкретные столбцы:

Иногда используют SELECT *, но в работе с данными это хуже по нескольким причинам:

вы тянете лишние данные

запрос становится менее устойчивым к изменениям схемы

сложнее заметить ошибки в логике

Псевдонимы (alias)

Псевдонимы делают вывод понятнее и часто используются в аналитике.

Ключевая идея: имя столбца в результате не обязано совпадать с именем в таблице.

Фильтрация строк: WHERE

Чтобы оставить только нужные строки, используйте WHERE.

Сравнения и логика

Частые операторы:

= равно

!= или <> не равно

> >= < <=

AND логическое и

OR логическое или

Пример с несколькими условиями:

Если вы смешиваете AND и OR, используйте скобки, чтобы явно задать логику:

IN, BETWEEN, LIKE

IN удобно для списка значений:

BETWEEN удобно для диапазона (часто дат или чисел):

LIKE используют для поиска по шаблону в строках:

Обычно:

% означает любая последовательность символов

_ означает один любой символ

Сортировка и ограничение результата: ORDER BY и LIMIT

ORDER BY

Чтобы отсортировать результат:

ASC сортировка по возрастанию (часто по умолчанию)

DESC сортировка по убыванию

Можно сортировать по нескольким полям:

LIMIT

Чтобы взять только первые строк результата (например, для просмотра):

Агрегаты: COUNT, SUM, AVG, MIN, MAX

Агрегатные функции сворачивают много строк в меньшее число строк.

Примеры агрегатов

Количество строк:

Сумма и средний чек по оплаченным заказам:

Минимум и максимум:

Важно понимать разницу:

COUNT(*) считает строки

COUNT(column) обычно не считает строки, где column равен NULL

Группировка: GROUP BY

Если вы хотите посчитать метрики по категориям (например, по городу), используйте GROUP BY.

Пример: выручка и число оплаченных заказов по городам.

Правило, которое стоит запомнить:

всё, что в SELECT не является агрегатом (SUM, COUNT и т.д.), должно быть указано в GROUP BY

HAVING: фильтр по группам

WHERE фильтрует строки до группировки, а HAVING фильтрует группы после группировки.

Пример: показать только города, где выручка больше 100.

NULL: пропуски в данных

NULL в SQL означает значение отсутствует (похоже на None в Python), но сравнивается иначе.

Неправильно:

Правильно:

И наоборот:

Почему это важно в курсе: когда Python чистит данные, он часто превращает пустые строки в None, а при загрузке в базу это становится NULL. Значит, фильтрация по пропускам в SQL должна делаться через IS NULL.

Как база логически обрабатывает запрос

SQL декларативен: вы описываете что хотите получить, а не как именно это посчитать по шагам. Но для понимания полезно запомнить типичный логический порядок частей запроса:

FROM

WHERE

GROUP BY

HAVING

SELECT

ORDER BY

LIMIT

!Порядок, в котором части запроса логически применяются к данным

Практический вывод: если вам нужно фильтровать по агрегату (например, по SUM(amount)), это делается через HAVING, потому что на этапе WHERE агрегата ещё нет.

Мост к Python: зачем нам это в пайплайнах

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

Python читает и чистит файл

Python загружает данные в таблицу (например, SQLite)

SQL-ом вы проверяете качество данных и считаете метрики

Например, очень типичная проверка после загрузки:

Если rows_with_amount заметно меньше rows_total, значит у вас много NULL в amount и нужно вернуться к шагам чистки в Python.

Полезные ссылки по теме

SQLite: SELECT

PostgreSQL: SELECT

SQL JOIN и подзапросы: связи между таблицами

В прошлых статьях вы научились:

в Python читать данные (CSV/JSON), чистить строки, приводить типы и загружать в базу

в SQL делать SELECT, фильтрацию через WHERE, сортировку через ORDER BY и агрегаты через GROUP BY

Следующий шаг в практической работе с данными — научиться связывать таблицы и строить запросы, которые опираются на результаты других запросов. Для этого используются JOIN и подзапросы.

Зачем вообще нужно несколько таблиц

В нормальной базе данных данные хранятся не одной широкой таблицей, а несколькими связанными:

меньше дублирования (например, имя клиента хранится один раз)

проще обновлять (поменяли адрес клиента в одном месте)

понятнее логика (отдельно клиенты, отдельно заказы, отдельно позиции заказа)

Чтобы эти таблицы работали как единое целое, используются ключи.

Первичный и внешний ключ

Первичный ключ (primary key) — столбец, который уникально идентифицирует строку в таблице (например, customers.customer_id).

Внешний ключ (foreign key) — столбец, который хранит ссылку на первичный ключ другой таблицы (например, orders.customer_id).

Даже если ограничения внешних ключей в учебных примерах не включены, логика связей всё равно важна: orders.customer_id должен совпадать с существующим customers.customer_id, иначе JOIN будет давать пустые связи.

!Схема связей между таблицами: по каким полям обычно делается JOIN

JOIN: объединяем строки из разных таблиц

JOIN соединяет строки двух таблиц по условию (обычно по равенству ключей).

Ниже будем использовать примерные таблицы:

INNER JOIN: только совпавшие строки

INNER JOIN возвращает только те строки, где нашлась пара в обеих таблицах.

Пример: заказы вместе с именем клиента.

Практический смысл:

если в orders.customer_id есть “битые” значения (клиента нет в customers), такие заказы пропадут из результата

это удобно, когда вам нужны только валидные связи

LEFT JOIN: все строки слева, плюс совпадения справа

LEFT JOIN возвращает:

все строки из левой таблицы (orders)

если пара в правой таблице (customers) не нашлась, поля правой таблицы будут NULL

Пример: показать все заказы, даже если клиент не найден.

Это один из лучших способов контроля качества данных после загрузки:

Если orders_without_customer больше нуля — у вас проблема со связностью данных.

RIGHT JOIN и FULL JOIN: важное замечание про SQLite

В разных СУБД набор JOIN-ов отличается:

PostgreSQL поддерживает RIGHT JOIN и FULL JOIN

SQLite исторически ориентирован на INNER JOIN и LEFT JOIN (для учебного процесса обычно достаточно)

Практический подход:

RIGHT JOIN обычно можно переписать как LEFT JOIN, поменяв таблицы местами

FULL JOIN часто можно собрать объединением (UNION) двух LEFT JOIN запросов, но это отдельная техника

CROSS JOIN: декартово произведение (использовать осторожно)

CROSS JOIN соединяет каждую строку первой таблицы с каждой строкой второй. В аналитике это бывает нужно (например, “все даты × все города”), но чаще всего это случайная ошибка.

Если в customers 1 000 строк, а в orders 1 000 000 строк, результат будет 1 000 000 000 строк.

Как писать JOIN аккуратно

Всегда указывайте таблицу у столбца

Плохо (неясно, откуда customer_id):

Хорошо (явно):

Псевдонимы таблиц

Псевдонимы (orders AS o) делают запрос короче и читаемее. Важно, чтобы псевдонимы были понятными.

Агрегации поверх JOIN

Частая задача: посчитать выручку по городам клиентов.

Здесь соединение делается до группировки: мы сначала добавили к заказам город, а потом агрегировали.

Подзапросы: запрос внутри запроса

Подзапрос — это SELECT, результат которого используется в другом запросе.

Подзапросы полезны, когда:

нужно отфильтровать строки на основе вычисленного списка

нужно сравнить значение с агрегатом (например, “выше среднего”)

нужно проверить существование связанных строк

Подзапрос в WHERE с IN

Пример: найти клиентов, у которых есть хотя бы один оплаченный заказ.

Важно:

подзапрос в скобках возвращает список значений одного столбца

IN проверяет принадлежность этому списку

EXISTS: “существует ли хотя бы одна строка”

EXISTS часто работает лучше концептуально: нам не нужен список, нам нужен факт существования.

Здесь подзапрос коррелированный: он использует c.customer_id из внешнего запроса.

Подзапрос как “виртуальная таблица” в FROM

Иногда удобно сначала посчитать метрику, а потом соединить результат с другой таблицей.

Пример: выручка по клиентам, затем присоединяем имена.

Здесь t — подзапрос, который ведёт себя как обычная таблица.

Подзапрос с агрегатом в сравнении

Пример: найти заказы, сумма которых выше среднего по всем заказам.

Скобочный подзапрос возвращает одно значение (скаляр). Это удобно для сравнений.

JOIN vs подзапрос: что выбирать

Часто одну и ту же задачу можно решить по-разному.

JOIN хорош, когда вам нужно притащить поля из другой таблицы (имя клиента, категория товара) и дальше фильтровать/группировать.

EXISTS/IN хороши, когда вам нужно отфильтровать по факту наличия связей, не добавляя столбцы.

Подзапрос в FROM полезен, когда вы хотите разделить задачу на два логических шага: сначала агрегировать, потом соединить.

В реальной аналитике выбирают то, что:

проще читать вашей команде

даёт корректный результат при NULL

адекватно работает по производительности на вашей СУБД

Типичные ошибки и как их избегать

Случайный “взрыв строк”: если связь не “один к одному”, количество строк может вырасти.

Фильтрация после LEFT JOIN, которая превращает его в INNER JOIN: если вы делаете LEFT JOIN, а затем в WHERE пишете условие на поля правой таблицы, строки с NULL исчезнут.

Пример проблемы:

Такой WHERE уберёт строки, где c.city равен NULL. Если вам нужно сохранить “несвязанные” заказы, условие нужно писать аккуратнее (например, переносить фильтрацию в условие JOIN или явно учитывать NULL).

Мост к Python: как это выглядит в рабочем пайплайне

Типичный процесс из этого курса:

Python читает CSV, чистит данные, приводит типы

Python загружает данные в таблицы customers, orders, order_items

SQL проверяет связность (LEFT JOIN ... IS NULL) и считает метрики (JOIN + GROUP BY)

После этого результаты можно забирать обратно в Python (например, для отчёта или автоматической отправки).

Полезные ссылки

SQLite SELECT и JOIN

PostgreSQL JOIN и выражения FROM

PostgreSQL Subqueries

Проектирование БД: ключи, нормализация, типы данных

В предыдущих статьях курса вы уже делали:

в Python — чтение файлов, чистку данных, приведение типов и загрузку в SQLite через sqlite3

в SQL — выборки, фильтрацию, агрегаты, JOIN и подзапросы

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

Проектирование в этом курсе нужно для трёх вещей:

правильно связать таблицы (чтобы JOIN был корректным)

избежать дублирования и противоречий в данных (нормализация)

выбрать такие типы данных, чтобы SQL-условия, сортировка и агрегаты работали предсказуемо

Что такое схема и почему «одна большая таблица» — плохая идея

Схема базы данных — это набор таблиц, их столбцов, типов данных и правил (ограничений), которые описывают:

что именно хранится (например, заказ, клиент, товар)

как объекты связаны (например, заказ принадлежит клиенту)

какие значения допустимы (например, сумма заказа не может быть отрицательной)

Если хранить всё в одной таблице (например, orders сразу с именем клиента, городом, товаром и ценой), вы быстро получите:

дублирование (город клиента повторяется в каждом заказе)

ошибки обновления (клиент сменил город — надо обновлять тысячи строк)

проблемы с JOIN и агрегациями (сложнее гарантировать целостность)

Правильная практика — разделять сущности на отдельные таблицы и соединять их ключами.

Ключи и ограничения: фундамент целостности данных

Первичный ключ (PRIMARY KEY)

Первичный ключ — столбец (или набор столбцов), который однозначно идентифицирует строку.

Практический смысл:

у каждой строки есть стабильный идентификатор

на первичный ключ можно ссылаться из других таблиц

на уровне базы проще предотвращать дубликаты

Пример (SQLite):

Здесь:

customer_id — идентификатор клиента

PRIMARY KEY гарантирует уникальность

NOT NULL не позволит записать клиента без имени

Естественный ключ и суррогатный ключ

Часто есть два варианта ключа:

естественный ключ — значение из предметной области (например, email)

суррогатный ключ — искусственный идентификатор (например, customer_id)

Практическое правило для учебных и многих рабочих схем:

делайте customer_id как суррогатный PRIMARY KEY

на естественные идентификаторы (например, email) вешайте UNIQUE, если они действительно должны быть уникальны

Пример:

Внешний ключ (FOREIGN KEY)

Внешний ключ — поле, которое ссылается на первичный ключ другой таблицы.

Пример: заказ принадлежит клиенту.

Практический смысл внешнего ключа:

нельзя создать заказ на несуществующего клиента (если ограничения включены)

связи между таблицами становятся не «договорённостью», а правилом

Важно для SQLite: поддержка внешних ключей есть, но в некоторых окружениях её нужно включать командой PRAGMA foreign_keys = ON;. Подробности — в документации SQLite Foreign Key Support.

Составной ключ (composite key)

Иногда уникальность задаётся парой или тройкой полей. Классический пример — позиции заказа.

Если в таблице order_items один и тот же товар не должен повторяться дважды в одном заказе, то естественный ключ позиции — это пара (order_id, product_id).

Другие полезные ограничения

Ограничения помогают ловить ошибки на входе (в том числе ошибки загрузки из Python).

UNIQUE — запрещает дубликаты

NOT NULL — запрещает пропуски

CHECK — проверяет правило (например, сумма не отрицательная)

DEFAULT — значение по умолчанию

Пример:

Полезная справка по синтаксису — SQLite CREATE TABLE.

!Схема связей между таблицами и ключами (PK/FK), чтобы понимать, как строятся JOIN-запросы

Нормализация: как убрать дубли и противоречия

Нормализация — это набор правил, которые помогают разложить данные по таблицам так, чтобы:

данные не повторялись без необходимости

обновления не портили консистентность

связи были явными и проверяемыми

Полная теория включает много нормальных форм, но для практики в этом курсе достаточно понимать первые три.

Первая нормальная форма

Идея: в каждой ячейке — одно значение, без списков и повторяющихся групп.

Плохо (в одном поле список):

items = "apple, banana, orange"

Хорошо:

отдельная таблица order_items, где каждая строка — одна позиция заказа

Вторая нормальная форма

Идея: если ключ составной, то каждый неключевой столбец должен зависеть от всего ключа, а не от его части.

Пример проблемы:

в order_items(order_id, product_id) нельзя хранить product_name, потому что имя товара зависит только от product_id, а не от (order_id, product_id)

Решение:

product_name живёт в products

в order_items остаётся ссылка product_id

Третья нормальная форма

Идея: неключевые столбцы не должны зависеть от других неключевых столбцов.

Типичный пример:

если в orders хранить customer_city, то это зависит от customer_id через таблицу клиентов

Решение:

город клиента хранить в customers

в orders оставить customer_id

Для общего обзора можно посмотреть статью Database normalization.

Нормализация и аналитика: важный компромисс

В аналитике иногда намеренно делают денормализацию (например, витрины данных), чтобы ускорять запросы и упростить отчёты.

Практический подход для этого курса:

храните «источник правды» в нормализованном виде (клиенты отдельно, заказы отдельно, позиции отдельно)

для отчётов делайте запросы с JOIN или создавайте отдельные агрегированные таблицы

Типы данных: выбираем не «как получится», а по смыслу

Правильный тип данных влияет на:

сортировку (строки и числа сортируются по-разному)

фильтрацию (WHERE amount > 100 работает только если это число)

агрегаты (SUM, AVG)

размер данных и скорость

Базовые типы: что использовать чаще всего

| Смысл | Частый тип в PostgreSQL | Частый тип в SQLite | Комментарий | |---|---|---|---| | Идентификатор | INTEGER / BIGINT | INTEGER | для PRIMARY KEY | | Текст | TEXT | TEXT | имена, статусы, города | | Денежные суммы | NUMERIC(p,s) | часто INTEGER (в центах) или REAL | REAL может давать погрешности | | Количество | INTEGER | INTEGER | qty, счётчики | | Дата/время | TIMESTAMP | часто TEXT в ISO-формате | важно договориться о формате | | Логическое | BOOLEAN | обычно INTEGER 0/1 | зависит от СУБД |

Справка по типам: SQLite Datatypes, PostgreSQL Data Types.

Особенность SQLite: «гибкая типизация»

SQLite использует концепцию type affinity: даже если столбец объявлен как TEXT, в него можно записать число, и наоборот.

Практические выводы:

проектируйте типы всё равно аккуратно, чтобы документировать смысл

добавляйте CHECK, NOT NULL, FOREIGN KEY, чтобы ловить ошибки

контролируйте преобразования на стороне Python (как в прошлой статье про чистку данных)

Деньги: почему REAL — риск

REAL хранит числа с плавающей точкой, из-за чего возможны накопленные погрешности при суммировании.

Два рабочих подхода:

хранить сумму в минимальных единицах как целое (например, amount_cents INTEGER)

в СУБД, где это поддержано, использовать десятичный тип (NUMERIC / DECIMAL) для точных денег

Для учебного SQLite-проекта часто выбирают INTEGER в центах, чтобы суммы в SUM() были точными.

Дата и время: фиксируем формат

Если вы храните дату/время как TEXT в SQLite, используйте ISO-формат:

дата: YYYY-MM-DD

дата-время: YYYY-MM-DD HH:MM:SS

Тогда:

сортировка по тексту совпадает с сортировкой по времени

диапазоны (BETWEEN) работают предсказуемо

Это напрямую связано с Python-частью курса: вы парсите дату через datetime, нормализуете и записываете в единый формат.

Как связать проектирование с JOIN и качеством данных

Хорошая схема делает типовые проверки очень простыми.

Например, после загрузки данных из Python вы можете проверить «битые» ссылки:

Если orders_without_customer > 0, значит:

данные загрузились в неправильном порядке

или в CSV были некорректные customer_id

или на этапе чистки в Python вы потеряли часть клиентов

Чек-лист перед тем, как загружать данные из Python

Определите сущности: клиент, заказ, товар, позиция заказа.

Назначьте PRIMARY KEY каждой таблице.

Пропишите FOREIGN KEY для всех связей.

Выберите типы данных по смыслу, а не «как пришло в CSV».

Добавьте минимальные ограничения: NOT NULL, UNIQUE, CHECK.

Проверьте, что ваш типичный аналитический запрос делается через понятный JOIN.

После этого связка Python+SQL становится значительно надёжнее: Python отвечает за чистку и загрузку, а база — за структуру и правила, которые не дают данным «сломаться».

Python + SQL: подключение, запросы, транзакции

В предыдущих статьях курса вы научились:

в Python читать файлы, чистить данные, приводить типы и безопасно обрабатывать ошибки

в SQL писать SELECT, фильтровать через WHERE, агрегировать через GROUP BY, соединять таблицы через JOIN

проектировать схему: ключи, ограничения, типы данных и нормализацию

Теперь мы соберём это в единый практический навык: как из Python подключаться к базе, выполнять SQL-запросы и управлять транзакциями, чтобы загрузка данных была корректной и надёжной.

Ментальная модель: кто за что отвечает

Python управляет процессом: читает источник, чистит данные, открывает соединение, отправляет SQL, логирует ошибки.

SQL выполняется внутри базы: выбирает, вставляет, обновляет, удаляет, агрегирует.

Транзакции гарантируют целостность: либо все изменения применились, либо не применилось ничего.

!Жизненный цикл транзакции при работе Python с SQL

Интерфейс DB-API 2.0: общий подход для разных баз

Большинство драйверов баз данных в Python придерживаются стандарта DB-API 2.0: у вас есть соединение (connection) и курсор (cursor).

Соединение отвечает за сессию с базой и управление транзакциями.

Курсор отвечает за выполнение запросов и получение результатов.

Полезные источники:

PEP 249 — Python Database API Specification v2.0

sqlite3 — DB-API 2.0 interface for SQLite databases

В курсе мы будем опираться на SQLite, потому что она не требует отдельного сервера и работает из коробки.

Подключение к SQLite из Python

Создаём соединение и выполняем простой запрос

Что важно:

Если файла example.db нет, SQLite создаст его.

fetchone() возвращает одну строку (кортеж) или None, если строк больше нет.

Контекстный менеджер: закрывать соединение автоматически

Соединение можно открывать через with, чтобы оно корректно закрывалось.

Практический смысл: меньше риска забыть close(), особенно когда код усложняется.

Выполнение SELECT и получение результатов

Пусть у нас есть таблица orders (логика из прошлых уроков):

Пример SELECT из Python

Методы получения данных:

fetchone() забрать одну строку

fetchmany(n) забрать до n строк

fetchall() забрать все строки результата

Если таблица большая, чаще используют fetchmany() в цикле, чтобы не держать всё в памяти.

Как сделать строки более удобными, чем кортежи

По умолчанию SQLite возвращает строки как кортежи. Для аналитики и пайплайнов часто удобнее обращаться к полям по имени.

Здесь:

cursor.description содержит метаданные столбцов результата

row_factory преобразует каждую строку результата в словарь

Параметризованные запросы: безопасность и корректность

Очень частая задача: подставить в запрос значения из Python (например, статус или дату). Это нужно делать только через параметры, а не через склейку строк.

Плохо:

Почему плохо:

риск SQL-инъекций, если значение пришло извне

ошибки с кавычками и экранированием

Правильно: использовать placeholders.

SQLite placeholders: ?

Ключевые правила:

параметры передаются отдельным вторым аргументом

параметры подставляет драйвер, а не ваш код

не ставьте кавычки вокруг ? вручную

> Для других баз placeholders могут отличаться, но принцип один: параметры передаются отдельно от текста запроса.

INSERT, UPDATE, DELETE из Python

Одиночная вставка

Здесь commit() фиксирует изменения.

Массовая вставка: executemany

Когда вы грузите данные после чистки CSV в Python, обычно вставляете много строк.

Практический смысл:

меньше накладных расходов на Python-циклы

обычно быстрее и чище по коду

UPDATE и DELETE

Обратите внимание на ( "canceled", ): для одного параметра нужен кортеж из одного элемента.

Транзакции: что это и зачем нужны

Транзакция — это группа операций, которая должна выполниться как единое целое.

Пример из реального мира данных:

вы загружаете клиентов в customers

затем загружаете заказы в orders

затем загружаете позиции в order_items

Если на шаге order_items произошла ошибка, чаще всего нужно откатить всё, иначе база окажется в промежуточном, частично загруженном состоянии.

COMMIT и ROLLBACK

COMMIT фиксирует изменения транзакции

ROLLBACK откатывает изменения транзакции

Пример: транзакция с обработкой ошибки

Практический смысл:

если что-то пошло не так, база возвращается в состояние до начала транзакции

вы не получаете «полузагруженные» данные

Транзакции и with sqlite3.connect(...) as conn

В SQLite у with есть полезное поведение:

при успешном выходе из блока будет выполнен commit()

при исключении будет выполнен rollback()

Это удобно для пайплайнов загрузки.

Если вставка упадёт, изменения откатятся автоматически.

Включаем внешние ключи в SQLite

В статье про проектирование мы обсуждали FOREIGN KEY. В SQLite важно помнить: поддержку внешних ключей нужно включать на соединение.

Источник:

SQLite Foreign Key Support

Практический смысл: база начнёт ловить ошибки связности (например, заказ на несуществующего клиента) сразу при загрузке.

Типичные ошибки при работе Python + SQL

Склейка SQL-строк вместо параметров

Забыли commit() и удивились, что данных нет

Пишут SELECT * и получают нестабильный пайплайн при изменении схемы

Делают массовые вставки по одной строке с commit() после каждой строки

Не включили PRAGMA foreign_keys = ON и не заметили «битые» ссылки

Практический шаблон: загрузка данных и проверка качества

Ниже упрощённый, но реалистичный шаблон из связки тем курса.

Python подготовил rows (например, из CSV после чистки).

Python грузит данные в таблицу.

SQL проверяет качество (количество строк, пропуски, связность через LEFT JOIN).

Если у вас включены внешние ключи и customers не содержит customer_id = 9999, вставка упадёт, транзакция откатится, и вы сразу увидите проблему данных или порядка загрузки.

Что дальше

После этой статьи у вас есть полный мост между тем, что вы пишете в SQL, и тем, как это реально выполняется в Python:

соединение и курсор

параметризованные запросы

чтение результатов

массовые вставки

транзакции, commit() и rollback()

включение правил целостности данных

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

Pandas и SQL: чтение, запись и анализ данных

В предыдущих статьях вы построили фундамент:

Python читает файлы, чистит значения и обрабатывает ошибки

SQL делает выборки, фильтрацию, агрегации, JOIN и подзапросы

схема базы (ключи, типы, ограничения) защищает данные

Python через sqlite3 выполняет запросы и управляет транзакциями

Теперь добавляем в связку Pandas: библиотеку, которая превращает результат SQL-запроса в удобную табличную структуру (DataFrame) и позволяет быстро анализировать данные в Python.

!Общая схема обмена данными между SQL и Pandas

Зачем Pandas, если уже есть SQL

SQL и Pandas решают похожие задачи, но в разных средах.

SQL сильнее, когда данные большие, лежат в базе, и нужно считать агрегаты и делать JOIN близко к хранилищу.

Pandas сильнее, когда нужно быстро исследовать данные, собрать отчёт, применить Python-логику, подготовить выгрузку или прототипировать преобразования.

Практический подход курса:

тяжёлую фильтрацию, JOIN, агрегации по большим таблицам делаем в SQL

финальную обработку, расчёт дополнительных колонок и подготовку результата делаем в Pandas

Подготовка: что установить

Для работы нужно установить Pandas. Для удобной работы с разными базами обычно добавляют SQLAlchemy.

Официальные источники:

Pandas: документация

Pandas: ввод-вывод (IO tools), включая SQL

SQLAlchemy 2.0: документация

Чтение данных из SQL в Pandas

Вариант через sqlite3 (самый простой для курса)

Pandas умеет выполнять запрос и возвращать DataFrame через pd.read_sql_query.

Что вы получаете:

df содержит строки результата

типы будут определены автоматически, но даты часто придут как строки

Параметры в запросе: безопасно и удобно

Не подставляйте значения через склейку строк. В read_sql_query можно передавать параметры.

Для SQLite placeholder обычно ?.

Практический смысл:

меньше ошибок с кавычками

безопаснее, если параметры пришли извне

Автопарсинг дат

Чтобы не держать даты строками, используйте parse_dates.

Запись данных из Pandas в SQL

to_sql: быстрая загрузка DataFrame в таблицу

DataFrame.to_sql умеет создать таблицу и вставить данные.

Ключевые параметры:

if_exists:

- fail ошибка, если таблица уже есть - replace удалить и создать заново - append добавить строки

index=False обычно включают, чтобы не записывать индекс DataFrame как отдельный столбец

Важное ограничение: to_sql не заменяет проектирование схемы

to_sql удобен для витрин, отчётов и временных таблиц, но он не гарантирует:

корректные PRIMARY KEY и FOREIGN KEY

строгие типы (особенно в SQLite)

ограничения (CHECK, NOT NULL) на уровне качества данных

Практическое правило:

основные таблицы проекта создавайте SQL-ом (как в статье про проектирование)

to_sql используйте для результатов анализа или промежуточных таблиц

Производительность: большие вставки

Если DataFrame большой, запись может быть медленной. Из часто применяемых приёмов:

писать батчами через chunksize

избегать лишних колонок

предварительно фильтровать данные SQL-ом

Когда лучше SQL, а когда Pandas

| Задача | Лучше SQL | Лучше Pandas | |---|---|---| | Фильтрация и агрегация по миллионам строк | да | иногда, если данных мало | | Сложные JOIN нескольких таблиц с ключами | да | возможно через merge, но обычно хуже по памяти | | Быстрая проверка гипотез, прототип отчёта | иногда | да | | Подготовка выгрузки в Excel/CSV | иногда | да | | Контроль целостности (PK/FK, ограничения) | да | нет |

В связке это выглядит так:

SQL делает правильную выборку (минимально нужные строки и колонки)

Pandas делает финальную аналитику и подготовку результата

Анализ данных в Pandas после SQL-запроса

Предположим, вы вытащили заказы.

Фильтрация и новые колонки

.copy() полезен, чтобы избежать неожиданных эффектов при изменениях

.dt работает только если колонка распознана как дата

Группировка как аналог GROUP BY

Соответствие идее из SQL:

groupby похоже на GROUP BY

agg похоже на набор агрегатных функций (SUM, COUNT)

Контроль пропусков

Это аналог проверки в SQL через COUNT(amount) и COUNT(*), только уже в Python.

JOIN-логика: SQL JOIN и Pandas merge

Если таблицы небольшие (или вы уже отфильтровали их в SQL), можно соединять в Pandas.

Соответствия:

how="inner" похоже на INNER JOIN

how="left" похоже на LEFT JOIN

Типовая проверка качества связей (как LEFT JOIN ... IS NULL в SQL):

Практическое правило курса:

если соединение большое и таблицы крупные, делайте JOIN в SQL

если соединение маленькое и нужно быстро исследовать данные, merge удобен

Мини-пайплайн: SQL выборка → анализ в Pandas → запись результата в SQL

Ниже пример типичного сценария для работы аналитика/инженера данных.

Что здесь важно:

JOIN выполняется в SQL, чтобы не тянуть лишние данные и не делать соединение в памяти

в Pandas добавляются производные признаки (например, month)

итоговый отчёт записывается в SQL как отдельная таблица (витрина)

Типичные ошибки и как их избегать

SELECT * в read_sql_query:

- хуже контролируете схему результата - тянете лишние данные

попытка делать большие JOIN в Pandas:

- может закончиться нехваткой памяти

отсутствие parse_dates:

- даты остаются строками, а временная логика становится хрупкой

использование to_sql для «боевых» таблиц без ограничений:

- легко получить дубликаты и несвязанные записи

Что дальше

После этой статьи вы умеете:

забирать данные из базы в DataFrame через read_sql_query

безопасно параметризовать запросы

анализировать данные в Pandas (groupby, новые колонки, пропуски)

сохранять результаты анализа обратно в SQL через to_sql

Дальше эти навыки будут использоваться как стандартный рабочий цикл: SQL готовит корректную выборку, Pandas делает исследование и финальные преобразования, SQL хранит результат и отдаёт его другим системам.

Мини‑проект: ETL и аналитический отчёт

Мини‑проект нужен, чтобы собрать в один рабочий цикл всё, что вы уже изучили в курсе:

Python: чтение файлов, чистка, преобразование типов, обработка ошибок

SQL: SELECT, фильтры, агрегаты, JOIN, подзапросы

Проектирование БД: ключи, ограничения, типы данных

Python + SQL: параметризованные запросы и транзакции

Pandas + SQL: чтение из базы, анализ, запись отчёта обратно

Итогом станет небольшой, но реалистичный пайплайн: забрать сырые данные из CSV → привести к качественному виду → загрузить в SQL → построить аналитический отчёт и сохранить его как таблицу.

!Схема всего мини‑проекта от CSV до итоговой таблицы отчёта

Постановка задачи

У вас есть два CSV-файла:

customers_raw.csv — список клиентов

orders_raw.csv — заказы клиентов

Данные «грязные»:

встречаются пустые строки вместо значений

суммы могут быть строками, иногда с пробелами

даты могут быть в разном формате

в заказах могут встречаться customer_id, которых нет среди клиентов

Нужно сделать:

Extract: прочитать CSV.

Transform: нормализовать строки, типы и даты; отбросить или пометить проблемные записи.

Load: загрузить данные в SQLite в нормализованную схему с ключами.

Report: построить отчёт выручка и количество оплаченных заказов по месяцам и городам и записать его в таблицу report_month_city.

Входные данные (пример)

Ниже пример того, как могут выглядеть файлы. У вас в проекте они будут лежать в папке data/.

customers_raw.csv:

orders_raw.csv:

Схема базы данных

Сделаем две «боевые» таблицы:

customers — клиенты

orders — заказы

Идея из прошлых уроков про проектирование:

у каждой сущности есть PRIMARY KEY

заказы ссылаются на клиентов через FOREIGN KEY

важные поля помечены NOT NULL

В SQLite внешние ключи нужно включать на уровне соединения: это будет частью Python-кода.

Полезная справка:

Документация SQLite про внешние ключи

Шаг Extract + Transform в Python

Правила чистки

Определим простые и практичные правила:

name: убрать пробелы; пустое значение считать ошибкой

city: убрать пробелы; пустое значение разрешить как NULL

amount: привести к float; пустое/некорректное значение → NULL

created_at: привести к ISO-формату YYYY-MM-DD; некорректное значение → ошибка строки заказа

status: оставить как есть, но убрать пробелы и привести к нижнему регистру

Реализация чистки

Шаг Load: загрузка в SQLite с транзакцией

Ключевые идеи из прошлой статьи про Python + SQL:

используем параметризованные запросы

включаем PRAGMA foreign_keys = ON

грузим пачками через executemany

используем транзакцию, чтобы не получить «полузагрузку»

Справка по модулю:

Документация Python sqlite3

Полный скрипт загрузки

Если в orders_rows попался заказ с несуществующим customer_id, вставка упадёт из-за FOREIGN KEY, а транзакция откатится автоматически при выходе из блока with.

Контроль качества данных SQL-запросами

После загрузки полезно сделать короткие проверки.

Сколько строк и сколько пропусков

orders_total показывает общее количество заказов

orders_with_amount показывает, сколько заказов имеют непустую сумму

Проверка связности через LEFT JOIN

Если вы временно выключили внешние ключи или грузите в staging-таблицу, связность можно проверить так:

Построение отчёта в SQL

Сформируем бизнес-отчёт: по месяцам и городам посчитать выручку и число оплаченных заказов.

Предположения:

выручка считается только по status = 'paid'

месяц берём из created_at (формат ISO YYYY-MM-DD), в SQLite можно извлечь YYYY-MM через substr

Тот же отчёт через Pandas и запись результата в SQL

Этот шаг связывает последнюю статью курса (Pandas + SQL) с практикой:

SQL делает корректную выборку (включая JOIN)

Pandas делает финальную агрегацию или пост-обработку

результат записывается в таблицу отчёта

Справка:

Документация Pandas: работа с SQL

Теперь report_month_city можно использовать как готовую витрину:

в следующих скриптах Python

в BI-инструментах

в любых SQL-запросах, не пересчитывая агрегации каждый раз

Практические итоги мини‑проекта

После выполнения мини‑проекта у вас появляется рабочий шаблон, который легко переносится на реальные задачи:

входные данные приходят «как есть» (CSV, выгрузки)

Python приводит их к договорённому качеству и формату

SQL хранит данные в связанной схеме и быстро считает метрики

Pandas помогает собирать финальные отчёты и выгрузки

Этот же каркас можно расширять:

добавить staging-таблицы и отдельный этап «валидация перед загрузкой»

логировать отклонённые строки в отдельный файл

делать инкрементальную загрузку (только новые заказы)

добавлять дополнительные измерения (товары, категории) и усложнять отчёты через JOIN