Связь Python с базой данных: SQLite, MySQL, PostgreSQL, SQL-запросы, подключение и работа с данными

Краткая памятка по интеграции Python с базами данных

1. Определите, какая СУБД (SQLite, MySQL, PostgreSQL) подходит для вашего проекта.
2. Установите соответствующую библиотеку-драйвер (sqlite3, mysql-connector-python, psycopg2).
3. Создайте соединение с базой данных, используя хост, порт, имя пользователя и пароль (если требуется).
4. Создайте объект курсора для выполнения SQL-команд.
5. Спроектируйте и создайте таблицы с помощью команды CREATE TABLE.
6. Используйте параметризованные запросы для вставки, обновления и удаления данных.
7. Для чтения данных применяйте SELECT-запросы и методы fetchone()/fetchall().
8. Всегда фиксируйте изменения методом commit() после модификации данных.
9. Обрабатывайте возможные исключения с помощью блоков try-except.
10. Закрывайте курсор и соединение после завершения работы.
11. Используйте индексы для оптимизации производительности поиска.
12. Регулярно делайте резервное копирование вашей базы данных.

Зачем программистам на Python нужны базы данных

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

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

SQLite

Более того, SQLite база данных не требует сервера и самодостаточна, то есть просто читает и записывает данные в файл. Подключимся с помощью sqlite3 к базе данных:

• Строки 1 и 2 – импорт sqlite3 и класса Error.
• Строка 4 определяет функцию create_connection(), которая принимает путь к базе данных SQLite.
• Строка 7 использует метод connect() и принимает в качестве параметра путь к базе данных SQLite. Если база данных в указанном месте существует, будет установлено соединение. В противном случае по указанному пути будет создана новая база данных и так же установлено соединение.
• В строке 8 выводится состояние успешного подключения к базе данных.
• Строка 9 перехватывает любое исключение, которое может быть получено, если методу.connect() не удастся установить соединение.
• В строке 10 отображается сообщение об ошибке в консоли.

(path) возвращает объект connection. Этот объект может использоваться для выполнения запросов к базе данных SQLite. Следующий скрипт формирует соединение с базой данных SQLite:

Выполнив вышеуказанный скрипт, вы увидите, как в корневом каталоге диска E появится файл базы данных sm_app.sqlite. Конечно, вы можете изменить местоположение в соответствии с вашими интересами.

MySQL

В отличие от SQLite, в Python по умолчанию нет модуля, который можно использовать для подключения к базе данных MySQL. Для этого вам нужно установить драйвер Python для MySQL. Одним из таких драйверов является mysql-connector-python. Вы можете скачать этот модуль Python SQL с помощью pip:

Обратите внимание, что MySQL – это серверная система управления базами данных. Один сервер MySQL может хранить несколько баз данных. В отличие от SQLite, где соединение равносильно порождению БД, формирование базы данных MySQL состоит из двух этапов:

1. Установка соединения с сервером MySQL.
2. Выполнение запроса для создания БД.

Определим функцию, которая будет подключаться к серверу MySQL и возвращать объект подключения:

В приведенном выше коде мы определили новую функцию create_connection(), которая принимает три параметра:

1. host_name
2. user_name
3. user_password

Модуль определяет метод connect(), используемый в седьмой строке для подключения к серверу MySQL. Как только соединение установлено, объект connection возвращается вызывающей функции. В последней строке функция create_connection() вызывается с именем хоста, именем пользователя и паролем.

Пока мы только установили соединение. Самой базы ещё нет. Для этого мы определим другую функцию – create_database(), которая принимает два параметра:

1. Объект connection;
2. query – строковый запрос о создании базу данных.

Теперь у нас есть база данных на сервере. Однако объект connection, возвращаемый функцией create_connection() подключен к серверу MySQL. А нам необходимо подключиться к базе данных sm_app. Для этого нужно изменить create_connection() следующим образом:

Функция create_connection() теперь принимает дополнительный параметр с именем db_name. Этот параметр указывает имя БД, к которой мы хотим подключиться. Имя теперь можно передать при вызове функции:

Скрипт успешно вызывает create_connection() и подключается к базе данных sm_app.

PostgreSQL

Как и в случае MySQL, для PostgreSQL в стандартной библиотеке Python нет модуля для взаимодействия с базой данных. Но и для этой задачи есть решение – модуль psycopg2:

Определим функцию create_connection() для подключения к базе данных PostgreSQL:

Подключение осуществляется через интерфейс (). Далее используем написанную нами функцию:

Теперь внутри дефолтной БД postgres нужно создать базу данных sm_app. Ниже определена соответствующая функция create_database():

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

В предыдущем разделе мы увидели, как подключаться к серверам баз данных SQLite, MySQL и PostgreSQL, используя разные библиотеки Python. Мы создали базу данных sm_app на всех трех серверах БД. В данном разделе мы рассмотрим, как формировать таблицы внутри этих трех баз данных.

1. users
2. posts
3. comments
4. likes

Для выполнения запросов в SQLite используется метод (). В этом разделе мы определим функцию execute_query(), которая использует этот метод. Функция будет принимать объект connection и строку запроса. Далее строка запроса будет передаваться методу execute(). В этом разделе он будет использоваться для формирования таблиц, а в следующих – мы применим его для выполнения запросов на обновление и удаление.

Примечание. Описываемый далее скрипт – часть того же файла, в котором мы описали соединение с базой данных SQLite.

В запросе говорится, что нужно создать таблицу users со следующими пятью столбцами:

1. id
2. name
3. age
4. gender
5. nationality

Поскольку между users и posts имеет место отношение один-ко-многим, в таблице появляется ключ user_id, который ссылается на столбец id в таблице users. Выполняем следующий скрипт для построения таблицы posts:

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

Так же, как с SQLite, чтобы создать таблицу в MySQL, нужно передать запрос в (). Создадим новый вариант функции execute_query():

Аналогично для создания таблиц comments и likes, передаём соответствующие CREATE-запросы функции execute_query().

Применение библиотеки psycopg2 в execute_query() также подразумевает работу с cursor:

Мы можем использовать эту функцию для организации таблиц, вставки, изменения и удаления записей в вашей базе данных PostgreSQL.

Запрос на создание таблицы users в PostgreSQL немного отличается от SQLite и MySQL. Здесь для указания столбцов с автоматическим инкрементом используется ключевое слово SERIAL. Кроме того, отличается способ указания ссылок на внешние ключи:

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

Чтобы вставить записи в базу данных SQLite, мы можем использовать ту же execute_query() функцию, что и для создания таблиц. Для этого сначала нужно сохранить в виде строки запрос INSERT INTO. Затем нужно передать объект connection и строковый запрос в execute_query(). Вставим для примера пять записей в таблицу users:

Поскольку мы установили автоинкремент для столбца id, нам не нужно указывать его дополнительно. Таблица users будет автоматически заполнена пятью записями со значениями id от 1 до 5.

Есть два способа вставить записи в базы данных MySQL из приложения Python. Первый подход похож на SQLite. Можно сохранить запрос INSERT INTO в строке, а затем использовать для вставки записей ().

Ранее мы определили функцию-оболочку execute_query(), которую использовали для вставки записей. Мы можем использовать ту же функцию:

1. Строка query, содержащая заполнители для вставляемых записей.
2. Список записей, которые мы хотим вставить.

Какой подход выбрать – зависит от вас. Если вы не очень хорошо знакомы с SQL, проще использовать метод курсора executemany().

Чтобы выбрать записи в SQLite, можно снова использовать (). Однако после этого потребуется вызвать метод курсора fetchall(). Этот метод возвращает список кортежей, где каждый кортеж сопоставлен с соответствующей строкой в ​​извлеченных записях. Чтобы упростить процесс, напишем функцию execute_read_query():

MongoDB

MongoDB — популярная NoSQL база данных, основанная на документах (JSON-подобные объекты). Для работы с MongoDB в Python существуют библиотеки, такие как `pymongo`. MongoDB хорошо подходит для проектов, которым необходима гибкая схема данных и возможность масштабирования горизонтально.

Redis

Redis — высокопроизводительная система хранения данных в памяти. Redis предоставляет удобные структуры данных, такие как строки, списки, хэши, множества и сортированные множества. Для работы с Redis в Python существует библиотека `redis`. Redis широко используется для кэширования и быстрого доступа к данным.

Oracle

Oracle — мощная реляционная база данных, широко применяемая в корпоративных средах. Для работы с Oracle в Python существуют библиотеки, такие как `cx_Oracle`. Oracle обычно выбирают для крупных корпоративных приложений с высокими требованиями к производительности и безопасности.

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

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

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

Библиотека SQLAlchemy предоставляет уровень абстракции над различными СУБД, что дает возможность разработчикам взаимодействовать с разными системами, не требуя изменения основного кода. Кроме того, она включает механизм ORM (Object-Relational Mapping), который необходим для обеспечения работы с БД, представляя информацию в виде объектов Python. Вместо написания прямых SQL-запросов, вы можете взаимодействовать с БД, используя объекты Python, которые отображают таблицы в базе данных.

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

Установка соединения с базой данных

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

Для взаимодействия с БД SQLite необходимо импортировать библиотеку sqlite3, а затем выполнить подключение к БД, хранящейся в файле:

Для взаимодействия с БД MySQL в Python применяется библиотека. Рассмотрим пример использования этой библиотеки для установки соединения:

• your_actual_host — это ваш адрес хоста в БД MySQL;
• your_actual_user — это ваше имя пользователя для доступа к БД;
• your_actual_password — это ваш пароль для пользователя;
• your_actual_database — это ваше имя БД, с которой вы хотите взаимодействовать.

Для взаимодействия с БД PostgreSQL в Python применяется библиотека psycopg2. Пример использования psycopg2 для создания соединения:

Если вы используете SQLAlchemy, URL — это стандартный способ представления параметров подключения. Он может включать информацию о хосте, пользователе, пароле, порте и других параметрах, зависящих от типа БД.

После успешного создания соединения (connection) следующим шагом является создание объекта курсора. Курсор обеспечивает программу интерфейсом для перемещения по результатам запроса, извлечения информации и внесения изменений в записи. Этот шаг не зависит от используемой БД, поскольку он обеспечивает общий интерфейс для выполнения SQL-запросов:

Теперь все готово для выполнения и взаимодействия с базой данных.
SQL-запросов в Python

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

Это критически важно для эффективного управления ресурсами и предотвращения утечек памяти.

После успешного установления связи с базой данных вы готовы приступить к выполнению SQL-запросов и различных операций.

Формирование таблиц и схем в СУБД

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

• product_id, уникальный идентификатор продукта, который служит в качестве первичного ключа;
• product_nm, текстовое поле для наименования продукта. NOT NULL указывает, что это поле обязательно должно содержать информацию;
• price, цена продукта, может содержать десятичные значения;
• stock_quantity, количество продуктов в наличии на складе, это поле может содержать только целые числа.

После создания таблицы необходимо выполнить и применить изменения:
SQL-запрос в Python

После выполнения этих шагов у вас будет создана таблица products в БД SQLite, готовая к использованию. Мы можем открыть базу данных в каком-нибудь браузере баз данных SQLite, например, в DB Browser for SQLite и увидеть созданную таблицу:

Обратите внимание, что создание таблицы требуется выполнить только один раз, из-за этого существует проверка IF NOT EXISTS (если не существует), перед началом работы с БД.

Аналогичным образом напишем SQL-запрос для создания таблицы для MySQL:

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

Создание базы данных в Python

Существует несколько способов создания базы данных в Python с использованием различных модулей. Рассмотрим создание SQLite базы данных, так как SQLite не требует установки отдельного сервера. После создания базы данных можно адаптировать код для работы с другими базами данных, такими как MySQL, PostgreSQL, MongoDB и др.

Готовы начать свой путь в мире программирования? FoxmindEd представляет онлайн-курс Python для начинающих, который поможет вам освоить этот язык и усилить ваш навык!

Начните с минимальных знаний и прокачайтесь до уровня профессионала вместе с нашими менторами-практиками!

Наши студенты проходят этот курс за 4-5 месяцев в среднем.

А еще у нас есть 7 дней тестового периода, бесплатная заморозка курса, и вы можете выбрать подходящий вам уровень менторинга.

Даже если вы абсолютно новичок и начинаете с нуля, не волнуйтесь! У нас есть специальный стартовый курс Python Start, который подойдет для тех, кто только начинает свой путь в программировании.

Шаг 1: Установка модуля SQLite

Python поставляется с модулем `sqlite3`, который обеспечивает встроенную поддержку для работы с SQLite. Нет необходимости устанавливать его отдельно.

Шаг 2: Создание и подключение к базе данных

При выполнении этого кода будет создан файл «, если он не существует, или установлено подключение к уже существующей базе данных.

Шаг 3: Создание таблицы

В этом шаге мы создаем таблицу с именем `users`, имеющую столбцы `id`, `name` и `age`. Важно использовать `IF NOT EXISTS`, чтобы не вызывать ошибку, если таблица уже существует.

Шаг 4: Вставка данных

Мы используем метод `executemany`, чтобы вставить несколько записей одновременно.

Шаг 6: Закрытие соединения

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

Вы можете применить аналогичные шаги для работы с другими базами данных, заменив соответствующие части кода. Обратите внимание, что рекомендуется использовать контекстные менеджеры (`with`), чтобы автоматически закрывать соединение и курсоры.

Подключение базы данных к Python

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

Подключение к базе данных MySQL

Для подключения к базе данных MySQL, используя библиотеку `PyMySQL`, убедитесь, что у вас установлен `PyMySQL`. Если нет, установите его с помощью `pip install pymysql`.

Подключение к базе данных PostgreSQL

Для подключения к базе данных PostgreSQL, используя библиотеку `psycopg2`, убедитесь, что у вас установлен `psycopg2`. Если нет, установите его с помощью `pip install psycopg2`.

Подключение к базе данных MongoDB

Для подключения к базе данных MongoDB, используйте библиотеку `pymongo`. Если она не установлена, установите ее с помощью `pip install pymongo`.

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

Обратите внимание, что коды приведены в общем формате. Для успешного подключения к конкретной базе данных, замените `your_host`, `your_username`, `your_password`, `your_database_name`, `your_collection_name` и другие параметры на соответствующие значения вашей базы данных.

SQLite в Python

В отличие от других баз данных SQL, которые мы будем рассматривать дальше, у Python’a уже есть встроенная поддержка для SQLite — компактной встраиваемой СУБД. Для этой БД API-модулем будет sqlite3. Поэтому для корректной работы достаточно добавить импортирование стандартной библиотеки и ничего заранее устанавливать не нужно:

Подключение к базе данных

Здесь мы указываем путь до файла базы данных. Следующим шагом требуется создать объект курсора:

Чтение из базы

После вызова метода execute() мы уже пользуемся привычным синтаксисом SQL. Для получения ответа воспользуемся fetchall():

Так мы получаем все строки результата сделанного запроса. Важно помнить, что после того, как мы получили ответ на запрос из курсора, чтобы получить этот результат еще раз, необходимо повторно выполнить запрос. Иначе вернется пустой результат (null).

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

Запись в базу

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

Когда к базе установлено не одно соединение, а одно из них пытается как-то модифицировать данные, база данных SQLite блокируется до завершения или отмены текущей транзакции. Закончить транзакцию можно методом commit(), а отменить ее — методом rollback().

MySQL в Python

MySQL — это СУБД с открытым исходным кодом (open source). Ее можно подключить несколькими способами. Один из наиболее распространенных — это использование пакета MySQLdb, у которого существует несколько версий. Из-за наличия различных версий, часть из которых несовместима с конкретными версиями Python, может возникнуть путаница.Поэтому рассмотрим подробно один конкретный пакет mysqlclient — ответвление MySQL-Python (как раз MySQLdb), которое предоставляет поддержание Python 3. Важно отметить, что нам понадобится MySQL или MySQL Client для его успешной установки:

После этого пакет mysqlclient будет установлен. Теперь посмотрим, как работа с этим пакетом будет реализована в коде:

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

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

PostgreSQL в Python

PostgresSQL является еще одной БД, распространяемой как свободное программное обеспечение, широко используемое в разработке. У Python существует несколько пакетов, которые поддерживают этот бэкэнд, но мы изучим работу с одним из них — Psycopg. Аналогично другим пакетам для начала необходимо его установить:

После этого аналогично работе с MySQL мы передаем в переменную connect соединение:

С помощью метода execute() мы делаем запрос к БД. После этого получаем одну строку результата с помощью fetchone(). После чего разрываем подключение к базе данных, закрывая и курсор, и соединение.

За исключением нескольких особенностей, работа с Psycopg практически не отличается от работы с другими пакетами. Мы помним, что и mysqlclient, и Psycopg следуют стандартному API, которому, на самом деле, следует большая часть пакетов. Именно поэтому код взаимодействия с разными БД практически не отличается между собой. Так мы можем подтвердить, что несмотря на существующие различия, работа с разными пакетами сводится к нескольким одинаковым командам.

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

Перед нами стоит простая задача: создать таблицу. На примере этой задачи мы окончательно разберемся в принципе работы с БД в Python. Ниже посмотрим на функцию, задача которой будет осуществлять работу с такими БД, как SQLite и MySQL (для PostgreSQL будет незначительно отличаться несколькими строчками):

В аргументы execute_query() мы передаем соединение и сам запрос. Дальше с помощью курсора мы исполняем запрос и сохраняем транзакцию. Также сразу обрабатываем ошибки, которые могут возникнуть.

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

В этом запросе мы создаем таблицу клиентов, у каждого из которых будет свой id (генерирующийся самостоятельно), имя, фамилия и возраст. Осталось вызвать execute_query():

Работа с записями в БД с помощью Python

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

Мы уже знаем, что в зависимости от конкретной задачи будет изменяться только SQL-запрос. Поэтому ниже посмотрим на соответствующие запросы для разных подзадач.

Добавление записей

Внутри запроса мы добавляем в таблицу customers трех человек, для каждого указывая имя, фамилию и возраст. Осталось передать этот запрос в execute_query(), как мы делали выше.

Обновление данных

В этом запросе мы изменяем имя клиенту с id равном 2. При передаче этого запроса в функцию мы успешно обновим информацию в таблице.

Чтение данных

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

Работа с базами данных в Python

Работа с базами данных в Python включает основные операции CRUD: создание (Create), чтение (Read), обновление (Update) и удаление (Delete) данных.Мы рассмотрели SQLite базу данных, но эти операции могут быть адаптированы для других баз данных с использованием соответствующих библиотек.

Создание данных (Create)

Для создания данных в базе данных используется операция вставки. Вставка добавляет новые записи в таблицу базы данных. Вот пример, как это делается с использованием SQLite:

Чтение данных позволяет извлечь информацию из таблицы базы данных. Вот пример чтения всех данных из таблицы:

Обновление данных (Update)

Обновление данных позволяет изменить существующие записи в таблице. Вот пример обновления данных:

Удаление данных (Delete)

Удаление данных позволяет удалить записи из таблицы. Вот пример удаления данных:

Заметьте, что перед выполнением операций CRUD необходимо подключиться к базе данных и получить курсор. После выполнения операций необходимо сохранить изменения в базе данных, вызвав метод `commit()`, и закрыть курсор и соединение. Эти операции помогают в управлении данными в базе данных и формируют основу для разработки сложных приложений на Python.

Подпишись на наш Ютуб-канал! Полезные видео для программистов уже ждут тебя!

Выбери свой курс программирования! Путь к карьере программиста начинается здесь!

Миграция в облако

Перенесем вашу инфраструктуру в облако — быстро, безопасно и с гарантией результата. Предоставим грант до 1 000 000 ₽ на облачную инфраструктуру и возьмем на себя весь процесс.

SQL-запросы

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

Основные команды SQL

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

WHERE

Теперь мы выполним SELECT-запрос, который возвращает текст поста и общее количество лайков, им полученных:

Процесс выбора записей из таблицы PostgreSQL с помощью модуля psycopg2 тоже похож на SQLite и MySQL. Снова используем (), затем метод fetchall() для выбора записей из таблицы. Следующий скрипт выбирает все записи из таблицы users:

Обновление записей в SQLite выглядит довольно просто. Снова можно применить execute_query(). В качестве примера обновим текст поста с id равным 2. Сначала создадим описание для SELECT:

Теперь, если мы выполним SELECT-запрос еще раз, увидим следующий результат:

Процесс обновления записей в MySQL с помощью модуля mysql-connector-python является точной копией модуля sqlite3:

Теперь, если мы извлечем все записи из таблицы comments, то увидим, что пятый комментарий был удален. Процесс удаления в MySQL и PostgreSQL идентичен SQLite:

В этом руководстве мы разобрались, как применять три распространенные библиотеки Python для работы с реляционными базами данных. Научившись работать с одним из модулей sqlite3, mysql-connector-python и psycopg2, вы легко сможете перенести свои знания на другие модули и оперировать любой из баз данных SQLite, MySQL и PostgreSQL.

Однако это лишь вершина айсберга! Существуют также библиотеки для работы с SQL и объектно-реляционными отображениями, такие как SQLAlchemy и Django ORM, которые автоматизируют задачи взаимодействия Python с базами данных.

Если вам интересна тематика работы с базами данных с помощью Python, напишите об этом в комментариях – мы подготовим дополнительные материалы.

GROUP BY

Оператор GROUP BY применяется для группировки выходных значений, в которых присутствуют агрегатные функции (например, COUNT, MAX, SUM, AVG и другие).

HAVING

По своей сути HAVING является аналогом WHERE. Это ключевое слово применяется вместе с GROUP BY. Это связано с тем, что команда WHERE не может использоваться с вышеперечисленными агрегатными функциями.

ORDER BY

ORDER BY применяется в том случае, когда к результату запроса нужно применить сортировку. По умолчанию команда отсортирует выходной результат в порядке возрастания. Однако можно указать способ сортировки с помощью ASC (по возрастанию) и DESC (по убыванию).

Инициализация таблицы в SQL

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

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

Важно внимательно работать с типами столбцов, так как они могут отличаться. В нашем примере есть такие типы, как INTEGER для чисел, VARCHAR для строк и DATE для дат. Перед тем как использовать разные типы данных лучше ознакомиться с документацией, так как VARCHAR и DATE могут иметь свои особенности.

Обновление данных в SQL

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

После команды UPDATE мы обязательно пишем имя таблицы, где требуется обновить данные. После мы применяем SET во всех необходимых местах для замены обновленного значения. Если мы хотим внести изменения в конкретную строку, то нужно сообщить, куда мы собираемся внести правки. Например, в данном случае применяя WHERE, мы выбираем строку с id, равным 1. В результате будет только одна строка, так как подразумевается, что id — это уникальный идентификатор.

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

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

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

BETWEEN

BETWEEN применяется для выбора значений в определенном диапазоне. Этот оператор работает для чисел и строк, а также дат.

Вложенные подзапросы

Вложенные подзапросы — это запросы, включающие стандартные выражения SQL, вложенные в другой запрос. Это удобно использовать, если задача требует написать выделить какую-то информацию из результата другого запроса.

Удаление данных

Такая команда удалит одну строку, id которой равно 1. Если необходимо удалить таблицу, то стоит применить DROP TABLE:

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

SQLite

SQLite — легкая, встроенная база данных, которая хранит все данные в одном файле без сервера. Она идеально подходит для простых приложений, которым не требуется высокая нагрузочная стойкость. Python поставляется с встроенной поддержкой SQLite, что делает его идеальным выбором для прототипирования и малых проектов.

SELECT

В приведенном выше скрипте запрос SELECT забирает всех пользователей из таблицы users. Результат передается в написанную нами функцию execute_read_query(), возвращающую все записи из таблицы users.

Примечание. Не рекомендуется использовать SELECT * для больших таблиц, так как это может привести к большому числу операций ввода-вывода, которые увеличивают сетевой трафик.

JOIN

Вы также можете выполнять более сложные запросы, включающие операции типа JOIN для извлечения данных из двух связанных таблиц. Например, следующий скрипт возвращает идентификаторы и имена пользователей, а также описание сообщений, опубликованных этими пользователями:

Следующий скрипт возвращает все сообщения вместе с комментариями к сообщениям и именами пользователей, которые разместили комментарии:

Из вывода понятно, что имена столбцов не были возвращены методом fetchall(). Чтобы вернуть имена столбцов, нужно забрать атрибут description объекта cursor. Например, следующий список возвращает все имена столбцов для вышеуказанного запроса:

MySQL

MySQL — это популярная реляционная база данных с открытым исходным кодом. С Python можно использовать MySQL с помощью различных библиотек, таких как `mysql-connector-python`, `PyMySQL` и `MySQLdb`. MySQL хорошо подходит для средних и крупных веб-приложений и может обслуживать большие объемы данных.

PostgreSQL

PostgreSQL — мощная реляционная база данных с акцентом на расширяемость и надежность. Существуют различные библиотеки для работы с PostgreSQL в Python, такие как `psycopg2` и `asyncpg` для асинхронной работы. PostgreSQL часто выбирают для проектов, которым требуется поддержка сложных запросов и расширяемость.

API-модули

Python DB-API — это свод правил, которым следуют самостоятельные модули, задача которых заключается в реализации работы с БД. API-модулем мы называем программный интерфейс, посредством которого мы взаимодействуем с данными. Один принцип позволяет применять общий подход для разных БД. Поэтому для полноценной работы не нужно углубленно изучать каждую БД — достаточно разобраться с несколькими основными моментами.

adodbapi

Если возникает такая ситуация, где обязательно надо реализовать доступ через Microsoft ADO, то идеальным решением будет пакет adodbapi. И нельзя забывать, что adodbapi зависим от уже установленного PyWin32.Чтобы установить adodbapi, делаем следующее:

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

После этого для связи с БД создаем подключение, где аргументом у метода connect() указываем строку связи:

Теперь создаем курсор — область памяти базы, предназначенная для хранения последнего оператора SQL. Иными словами, объект, отвечающий и за отправку запросов, и за получение их результатов.

Дальше мы уже можем передавать конкретные запросы и обрабатывать их вывод.

pyodbc

Сейчас ознакомимся с ODBC (Open Database Connectivity) — интерфейсом доступа к БД, разработанный в компании Microsoft. Суть ODBC заключается в разработке приложений для использования программного интерфейса доступа без опасений о различиях взаимодействия с разными источниками. Это достигается написанием драйверов, осуществляющих стандартные функции с учетом деталей реализации конкретного продукта. Самый используемый метод связи через ODBC — пакет pyodbc. Он устанавливается с помощью pip:

В данном случае строка связи состоит из нескольких частей. Хорошей практикой будет сохранить все параметры в отдельный конфигурационный файл, в котором и будут храниться все необходимые параметры. Это добавит удобства при использовании и может обезопасить систему. После соединяемся с БД и получаем курсор:

Если мы успешно подключились, то, используя это соединение, мы можем применить курсор для получения ответа на запрос:

Теперь можно получить результат, вызвав методы fetchone() и fetchall(). Способы их использования мы рассмотрим чуть ниже.Так как не только базы данных от Microsoft поддерживают такой вид соединения, то пакет pyodbc можно использовать при работе и с другими базами данных, совместимыми с ODBC.

pypyodbc

Пакет pypyodbc можно назвать скриптом, написанным на Python. Интересно, что на самом деле pyodbc — это Python, завернутый в бэкэнд С++, в то время как pypyodbc уже является чистым кодом на Python. Чаще всего эти модули взаимозаменяемы. Единственное различие будет заключаться в импорте:

Расширенные методы курсора

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

Разбивка запроса на строки

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

Объединение запросов к БД

Метод execute() позволяет выполнить за раз лишь один запрос (если написать сразу несколько запросов, разделив их точкой с запятой или другим разделителем, то мы получим ошибку). Однако чаще всего в разработке требуется обратиться к БД далеко не один раз. Конечно, можно просто вызвать этот метод несколько раз подряд:

Также этот метод может помочь в том случае, если мы сохранили тело запроса в файл или отдельную переменную.

Подстановка значения в запрос

В процессе написания кода, в котором мы тесно взаимодействуем с БД, может возникнуть ситуация, когда необходимо подставить конкретное значение в запрос. Тогда поможет применение второго аргумента в методе execute():

Важно! В PostgreSQL и в MySQL для подстановки вместо знака ‘?’ нужно писать %s.

Также стоит отметить, что этот способ не подойдет для замены названия таблицы.

Множественная вставка строк

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

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

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

Повышение устойчивости кода

Сейчас в любом проекте требуется правильно обрабатывать и отлавливать возможные ошибки во время исполнения программы. Особенно это может быть критично при записи информации в базу данных. Поэтому полезно оборачивать обращение к БД в конструкцию «try-except-else»:

Подробнее рассмотрим этот фрагмент. В try мы передаем инструкцию для SQL, после чего записываем в result весь результат. При возникновении ошибки мы используем встроенный в sqlite3 объект ошибок и печатаем его в консоль. В противном случае, если же все отработало корректно, мы сохраняем изменения. Такой подход может сильно упростить исправление ошибок в ходе разработки.

Дополнительные возможности SQLite

SQLite широко используется и легко поддерживается большинством клиентов SQL. Рассмотрим некоторые интересные возможности SQLite.

Подключение к БД из клиента SQL

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

Алгоритм работы как и раньше: сначала нужно создаем новое соединение: правой кнопкой мыши по названию БД (при установке DBeaver предлагает создать тестовую базу данных, чтобы ознакомиться с функционалом приложения) → Создать → Соединение:

После выбора подходящей нам базы данных SQLite, мы нажимаем Далее и в специальном окне настраиваем соединение нужным нам образом:

После завершения всех настроек и указания верных путей мы уже можем сделать любой SQL-запрос.

Интеграция с фреймворком Pandas

На самом деле в Python есть библиотека, специально предназначенная для обработки и анализа структурированных данных. Библиотека Pandas — это гибкий и мощный инструмент, широко применяемый в анализе данных.

В фреймворке Pandas есть множество специальных структур и операций для эффективной работы с данными. Основополагающей частью Pandas является фрейм данных (от DataFrame) — структура, представляющая собой двумерный набор данных, хранящихся в табличной форме. Но также фрейм данных непрерывно интегрируется с SQLite.

Теперь у нас есть небольшой фрейм данных, посвященный фильмам. У фрейма есть метод to_sql(), позволяющий сохранить его в БД. Воспользуемся им:

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

Если же мы хотим написать какой-то SQL-запрос к таблице, то это также легко делается с помощью метода read_sql():

Можем сделать вывод, что с помощью библиотеки Pandas можно очень просто реализовать работу с реляционной базой данных SQLite.

Выполнение запросов SQL с использованием Python

После успешного формирования таблиц мы переходим к осуществлению основных операций, известных как операции CRUD (Create, Read, Update, Delete). Эти процедуры предоставляют возможность добавлять, читать, обновлять и удалять информацию в БД. Давайте рассмотрим каждую из этих операций более подробно.

Добавим информацию о новом продукте в таблицу, которую мы создавали в предыдущем разделе:

Команда () выполняет запрос, используя переданный SQL-запрос и данные, и добавляет новую запись в БД, а команда () применяет изменения.

В приведенном выше коде мы формируем SQL-запрос для выборки всех данных из таблицы products, выполняем SQL-запрос с помощью курсора, получаем результат выборки и выводим результат:

Мы ранее упоминали, как важно правильно завершать взаимодействие с БД. После выполнения всех операций CRUD не забывайте о завершающих действиях: закрытие курсора и разъединение:

Теперь, когда мы освоили основы операций CRUD, давайте подробнее рассмотрим возможности. Рассмотрим варианты улучшения эффективности запросов с применением индексов, обеспечим сохранность информации при помощи транзакций, и также рассмотрим вопрос о создании триггеров для автоматизации определенных процессов.
SQL в Python

Оптимизация поиска в базах данных

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

Для создания индекса используется оператор CREATE INDEX. Пример создания индекса для оптимизации поиска по столбцу product_nm в таблице products:

• idx_product_nm — название индекса;
• products — название таблицы;
• product_nm — название столбца, для которого создается индекс.

Обеспечение целостности данных

Транзакции отвечают за целостность информации в базе данных. Они гарантируют, что серия операций выполняется атомарно, то есть, либо все изменения применяются успешно, либо ни одно из них не выполняется. Рассмотрим, как работать с транзакциями в контексте SQLite.

В SQLite транзакции автоматически выполняются каждый раз, когда вы выполняете операцию изменения записей (INSERT, UPDATE, DELETE). Если действия выполнены успешно, изменения сохраняются, в противном случае — откатываются:

В этом примере мы используем блок 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:

Триггер decrease_inventory создается после операции INSERT на таблице orders. Он автоматически вызывается при добавлении нового заказа. Внутри триггера происходит обновление количества товара в таблице inventory на основе данных, вставленных в таблицу orders.

Синтаксис для аналогичного триггера, который будет выполнять ту же самую задачу, но уже в MySQL не отличается от синтаксиса SQLite, за исключением ключевого слова FOR EACH ROW, которое указывает на то, что триггер должен выполняться отдельно для каждой строки, затронутой операцией, которую вызвал триггер (в SQLite триггеры по умолчанию рассматриваются, как триггеры для каждой строки FOR EACH ROW, поэтому не требуется явно указывать это). Данное ключевое слово необходимо прописать перед началом блока BEGIN … END.

Часто задаваемые вопросы о связке Python и баз данных

Вопрос: Какую библиотеку Python использовать для работы с SQLite?
Ответ: Для работы с SQLite в Python используется встроенный модуль sqlite3, который не требует дополнительной установки.

Вопрос: Как установить драйвер для подключения Python к MySQL?
Ответ: Для подключения к MySQL обычно используется библиотека mysql-connector-python, которую можно установить через pip.

Вопрос: В чем разница между SQLite, MySQL и PostgreSQL?
Ответ: SQLite — это встраиваемая база данных для небольших проектов, MySQL и PostgreSQL — полноценные серверные СУБД, где PostgreSQL считается более функциональной и надежной.

Вопрос: Как выполнить SELECT-запрос в Python?
Ответ: После создания курсора (cursor.execute(‘SELECT * FROM table_name’)) данные извлекаются методами fetchone(), fetchall() или fetchmany().

Вопрос: Что такое параметризованные запросы и зачем они нужны?
Ответ: Параметризованные запросы (подстановка значений через? или %s) защищают код от SQL-инъекций и повышают его устойчивость.

Вопрос: Как создать новую таблицу в базе данных через Python?
Ответ: Нужно выполнить SQL-команду CREATE TABLE через метод execute() вашего курсора, передав в нее строку с определением столбцов.

Вопрос: Как обновить существующую запись в таблице?
Ответ: Используйте SQL-команду UPDATE с условием WHERE, выполнив ее через cursor.execute(), и не забудьте зафиксировать изменения (connection.commit()).

Вопрос: Как подключиться к удаленной базе данных PostgreSQL?
Ответ: Установите библиотеку psycopg2 и используйте функцию connect(), передав параметры host, port, dbname, user и password.

Вопрос: Что делать, если данные не сохраняются в базе?
Ответ: Скорее всего, вы забыли вызвать connection.commit() после выполнения запросов на изменение данных (INSERT, UPDATE, DELETE).

Вопрос: Как закрыть соединение с базой данных?
Ответ: После завершения всех операций необходимо вызвать метод close() у объекта соединения и курсора, чтобы освободить ресурсы.