Все приложения взаимодействуют с данными, чаще всего через систему управления базами данных (СУБД). Одни языки программирования поставляются с модулями для работы с СУБД, другие требуют использования сторонних пакетов. Из этого подробного руководства вы узнаете о различных библиотеках Python для работы с SQL-базами данных. Мы разработаем простое приложение для взаимодействия с БД SQLite, MySQL и PostgreSQL.
Примечание. Если вы не разбираетесь в базах данных, советуем обратить внимание на следующие публикации Библиотеки программиста: 11 типов современных баз данных, SQL за 20 минут, Подборка материалов для изучения баз данных и SQL.
Из этого пособия вы узнаете:
- как подключиться к различным СУБД с помощью библиотек Python для работы с SQL базами данных;
- как управлять базами данных SQLite, MySQL и PostgreSQL;
- как выполнять запросы к базе данных внутри приложения Python;
- как разрабатывать приложения для разных баз данных.
Чтобы получить максимальную отдачу от этого учебного пособия, необходимо знать основы Python, SQL и работы с СУБД. Вы также должны иметь возможность загружать и импортировать пакеты в Python и знать, как устанавливать и запускать серверы БД локально или удаленно.
Содержание статьи:
- Схема базы данных
- Подключение к базам данных
- Создание таблиц
- Вставка записей
- Извлечение записей
- Обновление содержания
- Удаление записей таблицы
В каждом разделе по три подраздела: SQLite, MySQL и PostgreSQL.
В этом уроке мы разработаем очень маленькую базу данных приложения для социальных сетей. База данных будет состоять из четырех таблиц:
- users
- posts
- comments
- likes
Схема базы данных показана на рисунке ниже.
Пользователи ( users ) и публикации ( posts ) будут находиться иметь тип связи один-ко-многим: одному читателю может понравиться несколько постов. Точно так же один и тот же юзер может оставлять много комментариев ( comments ), а один пост может иметь несколько комментариев. Таким образом, и users , и posts по отношению к comments имеют тот же тип связи. А лайки ( likes ) в этом плане идентичны комментариям.
Прежде чем взаимодействовать с любой базой данных через SQL-библиотеку, с ней необходимо связаться. В этом разделе мы рассмотрим, как подключиться из приложения Python к базам данных SQLite , MySQL и PostgreSQL. Рекомендуем сделать собственный .py файл для каждой из трёх баз данных.
Примечание. Для выполнения разделов о MySQL и PostgreSQL необходимо самостоятельно запустить соответствующие серверы. Для быстрого ознакомления с тем, как запустить сервер MySQL, ознакомьтесь с разделом MySQL в публикации Запуск проекта Django (англ.). Чтобы узнать, как создать базу данных в PostgreSQL, перейдите к разделу Setting Up a Database в публикации Предотвращение атак SQL-инъекций с помощью Python (англ.).
SQLite
SQLite, вероятно, является самой простой базой данных, к которой можно подключиться с помощью Python, поскольку для этого не требуется устанавливать какие-либо внешние модули. По умолчанию стандартная библиотека Python уже содержит модуль sqlite3.
Более того, SQLite база данных не требует сервера и самодостаточна, то есть просто читает и записывает данные в файл. Подключимся с помощью sqlite3 к базе данных:
Вот как работает этот код:
- Строки 1 и 2 – импорт sqlite3 и класса Error .
- Строка 4 определяет функцию create_connection() , которая принимает путь к базе данных SQLite.
- Строка 7 использует метод connect() и принимает в качестве параметра путь к базе данных SQLite. Если база данных в указанном месте существует, будет установлено соединение. В противном случае по указанному пути будет создана новая база данных и так же установлено соединение.
- В строке 8 выводится состояние успешного подключения к базе данных.
- Строка 9 перехватывает любое исключение, которое может быть получено, если методу .connect() не удастся установить соединение.
- В строке 10 отображается сообщение об ошибке в консоли.
sqlite3.connect(path) возвращает объект connection . Этот объект может использоваться для выполнения запросов к базе данных SQLite. Следующий скрипт формирует соединение с базой данных SQLite:
Выполнив вышеуказанный скрипт, вы увидите, как в корневом каталоге диска E появится файл базы данных sm_app.sqlite . Конечно, вы можете изменить местоположение в соответствии с вашими интересами.
MySQL
В отличие от SQLite, в Python по умолчанию нет модуля, который можно использовать для подключения к базе данных MySQL. Для этого вам нужно установить драйвер Python для MySQL. Одним из таких драйверов является mysql-connector-python . Вы можете скачать этот модуль Python SQL с помощью pip:
Обратите внимание, что MySQL – это серверная система управления базами данных. Один сервер MySQL может хранить несколько баз данных. В отличие от SQLite, где соединение равносильно порождению БД, формирование базы данных MySQL состоит из двух этапов:
- Установка соединения с сервером MySQL.
- Выполнение запроса для создания БД.
Определим функцию, которая будет подключаться к серверу MySQL и возвращать объект подключения:
В приведенном выше коде мы определили новую функцию create_connection() , которая принимает три параметра:
- host_name
- user_name
- user_password
Модуль mysql.connector определяет метод connect() , используемый в седьмой строке для подключения к серверу MySQL. Как только соединение установлено, объект connection возвращается вызывающей функции. В последней строке функция create_connection() вызывается с именем хоста, именем пользователя и паролем.
Пока мы только установили соединение. Самой базы ещё нет. Для этого мы определим другую функцию – create_database() , которая принимает два параметра:
- Объект connection ;
- query – строковый запрос о создании базу данных.
Вот как выглядит эта функция:
Для выполнения запросов используется объект cursor .
Создадим базу данных sm_app для нашего приложения на сервере MySQL:
Теперь у нас есть база данных на сервере. Однако объект connection , возвращаемый функцией create_connection() подключен к серверу MySQL. А нам необходимо подключиться к базе данных sm_app . Для этого нужно изменить create_connection() следующим образом:
Функция create_connection() теперь принимает дополнительный параметр с именем db_name . Этот параметр указывает имя БД, к которой мы хотим подключиться. Имя теперь можно передать при вызове функции:
Скрипт успешно вызывает create_connection() и подключается к базе данных sm_app .
PostgreSQL
Как и в случае MySQL, для PostgreSQL в стандартной библиотеке Python нет модуля для взаимодействия с базой данных. Но и для этой задачи есть решение – модуль psycopg2 :
Определим функцию create_connection() для подключения к базе данных PostgreSQL:
Подключение осуществляется через интерфейс psycopg2.connect() . Далее используем написанную нами функцию:
Теперь внутри дефолтной БД postgres нужно создать базу данных sm_app . Ниже определена соответствующая функция create_database() :
Запустив вышеприведенный скрипт, мы увидим базу данных sm_app на своем сервере PostgreSQL. Подключимся к ней:
Здесь 127.0.0.1 и 5432 это соответственно IP-адресу и порт хоста сервера.
В предыдущем разделе мы увидели, как подключаться к серверам баз данных SQLite, MySQL и PostgreSQL, используя разные библиотеки Python. Мы создали базу данных sm_app на всех трех серверах БД. В данном разделе мы рассмотрим, как формировать таблицы внутри этих трех баз данных.
Как обсуждалось ранее, нам нужно получить и связать четыре таблицы:
- users
- posts
- comments
- likes
SQLite
Для выполнения запросов в SQLite используется метод cursor.execute() . В этом разделе мы определим функцию execute_query() , которая использует этот метод. Функция будет принимать объект connection и строку запроса. Далее строка запроса будет передаваться методу execute( ) . В этом разделе он будет использоваться для формирования таблиц, а в следующих – мы применим его для выполнения запросов на обновление и удаление.
Примечание. Описываемый далее скрипт – часть того же файла, в котором мы описали соединение с базой данных SQLite.
Итак, начнем с определения функции execute_query() :
Теперь напишем передаваемый запрос ( query ):
В запросе говорится, что нужно создать таблицу users со следующими пятью столбцами:
- id
- name
- age
- gender
- nationality
Наконец, чтобы появилась таблица, вызываем execute_query() . Передаём объект connection , который мы описали в предыдущем разделе, вместе с только что подготовленной строкой запроса create_users_table :
Следующий запрос используется для создания таблицы posts:
Поскольку между users и posts имеет место отношение один-ко-многим, в таблице появляется ключ user_id , который ссылается на столбец id в таблице users . Выполняем следующий скрипт для построения таблицы posts :
Наконец, формируем следующим скриптом таблицы comments и likes :
Вы могли заметить, что создание таблиц в SQLite очень похоже на использование чистого SQL. Все, что вам нужно сделать, это сохранить запрос в строковой переменной и затем передать эту переменную cursor.execute() .
MySQL
Так же, как с SQLite, чтобы создать таблицу в MySQL, нужно передать запрос в cursor.execute() . Создадим новый вариант функции execute_query() :
Описываем таблицу users :
Запрос для реализации отношения внешнего ключа в MySQL немного отличается от SQLite. Более того, MySQL использует ключевое слово AUTO_INCREMENT для указания столбцов, значения которых автоматически увеличиваются при вставке новых записей.
Следующий скрипт составит таблицу posts , содержащую внешний ключ user_id , который ссылается на id столбца таблицы users :
Аналогично для создания таблиц comments и likes , передаём соответствующие CREATE -запросы функции execute_query() .
PostgreSQL
Применение библиотеки psycopg2 в execute_query() также подразумевает работу с cursor :
Мы можем использовать эту функцию для организации таблиц, вставки, изменения и удаления записей в вашей базе данных PostgreSQL.
Создадим внутри базы данных sm_app таблицу users :
Запрос на создание таблицы users в PostgreSQL немного отличается от SQLite и MySQL. Здесь для указания столбцов с автоматическим инкрементом используется ключевое слово SERIAL . Кроме того, отличается способ указания ссылок на внешние ключи:
В предыдущем разделе мы разобрали, как развертывать таблицы в базах данных SQLite, MySQL и PostgreSQL с использованием различных модулей Python. В этом разделе мы узанем, как вставлять записи.
SQLite
Чтобы вставить записи в базу данных SQLite, мы можем использовать ту же execute_query() функцию, что и для создания таблиц. Для этого сначала нужно сохранить в виде строки запрос INSERT INTO . Затем нужно передать объект connection и строковый запрос в execute_query() . Вставим для примера пять записей в таблицу users :
Поскольку мы установили автоинкремент для столбца id , нам не нужно указывать его дополнительно. Таблица users будет автоматически заполнена пятью записями со значениями id от 1 до 5.
Вставим в таблицу posts шесть записей:
Важно отметить, что столбец user_id таблицы posts является внешним ключом, который ссылается на столбец таблицы users . Это означает, что столбец user_id должен содержать значение, которое уже существует в столбце id таблицы users . Если его не существует, мы получим сообщение об ошибке.
Следующий скрипт вставляет записи в таблицы comments и likes :
MySQL
Есть два способа вставить записи в базы данных MySQL из приложения Python. Первый подход похож на SQLite. Можно сохранить запрос INSERT INTO в строке, а затем использовать для вставки записей cursor.execute() .
Ранее мы определили функцию-оболочку execute_query() , которую использовали для вставки записей. Мы можем использовать ту же функцию:
Второй подход использует метод cursor.executemany() , который принимает два параметра:
- Строка query , содержащая заполнители для вставляемых записей.
- Список записей, которые мы хотим вставить.
Посмотрите на следующий пример, который вставляет две записи в таблицу likes :
Какой подход выбрать – зависит от вас. Если вы не очень хорошо знакомы с SQL, проще использовать метод курсора executemany() .
PostgreSQL
В предыдущем подразделе мы познакомились с двумя подходами для вставки записей в таблицы баз данных MySQL. В psycopg2 используется второй подход: мы передаем SQL-запрос с заполнителями и списком записей методу execute() . Каждая запись в списке должна являться кортежем, значения которого соответствуют значениям столбца в таблице БД. Вот как мы можем вставить пользовательские записи в таблицу users :
Список users содержит пять пользовательских записей в виде кортежей. Затем мы создаём строку с пятью элементами-заполнителями ( %s ), соответствующими пяти пользовательским записям. Строка-заполнитель объединяется с запросом, который вставляет записи в таблицу users . Наконец, строка запроса и пользовательские записи передаются в метод execute() .
Следующий скрипт вставляет записи в таблицу posts :
По той же методике можно вставить записи в таблицы comments и likes .
SQLite
Чтобы выбрать записи в SQLite, можно снова использовать cursor.execute() . Однако после этого потребуется вызвать метод курсора fetchall() . Этот метод возвращает список кортежей, где каждый кортеж сопоставлен с соответствующей строкой в извлеченных записях. Чтобы упростить процесс, напишем функцию execute_read_query() :
Эта функция принимает объект connection и SELECT -запрос, а возвращает выбранную запись.
SELECT
Давайте выберем все записи из таблицы users :
В приведенном выше скрипте запрос SELECT забирает всех пользователей из таблицы users . Результат передается в написанную нами функцию execute_read_query() , возвращающую все записи из таблицы users .
Примечание. Не рекомендуется использовать SELECT * для больших таблиц, так как это может привести к большому числу операций ввода-вывода, которые увеличивают сетевой трафик.
Результат вышеприведенного запроса выглядит следующим образом:
Таким же образом вы можете извлечь все записи из таблицы posts :
Вывод выглядит так:
Вы также можете выполнять более сложные запросы, включающие операции типа JOIN для извлечения данных из двух связанных таблиц. Например, следующий скрипт возвращает идентификаторы и имена пользователей, а также описание сообщений, опубликованных этими пользователями:
Следующий скрипт возвращает все сообщения вместе с комментариями к сообщениям и именами пользователей, которые разместили комментарии:
Вывод выглядит так:
Из вывода понятно, что имена столбцов не были возвращены методом fetchall() . Чтобы вернуть имена столбцов, нужно забрать атрибут description объекта cursor . Например, следующий список возвращает все имена столбцов для вышеуказанного запроса:
Вывод выглядит так:
WHERE
Теперь мы выполним SELECT -запрос, который возвращает текст поста и общее количество лайков, им полученных:
То есть используя запрос WHERE , вы можете возвращать более конкретные результаты.
MySQL
Процесс выбора записей в MySQL абсолютно идентичен процессу выбора записей в SQLite:
Теперь выберем все записи из таблицы users :
Вывод будет похож на то, что мы видели с SQLite.
PostgreSQL
Процесс выбора записей из таблицы PostgreSQL с помощью модуля psycopg2 тоже похож на SQLite и MySQL. Снова используем cursor.execute() , затем метод fetchall() для выбора записей из таблицы. Следующий скрипт выбирает все записи из таблицы users :
Опять же, результат будет похож на то, что мы видели раньше.
SQLite
Обновление записей в SQLite выглядит довольно просто. Снова можно применить execute_query() . В качестве примера обновим текст поста с id равным 2. Сначала создадим описание для SELECT :
Увидим следующий вывод:
Следующий скрипт обновит описание:
Теперь, если мы выполним SELECT -запрос еще раз, увидим следующий результат:
То есть запись была обновлена.
MySQL
Процесс обновления записей в MySQL с помощью модуля mysql-connector-python является точной копией модуля sqlite3 :
PostgreSQL
Запрос на обновление PostgreSQL аналогичен SQLite и MySQL.
SQLite
В качестве примера удалим комментарий с id равным 5:
Теперь, если мы извлечем все записи из таблицы comments , то увидим, что пятый комментарий был удален. Процесс удаления в MySQL и PostgreSQL идентичен SQLite:
В этом руководстве мы разобрались, как применять три распространенные библиотеки Python для работы с реляционными базами данных. Научившись работать с одним из модулей sqlite3 , mysql-connector-python и psycopg2 , вы легко сможете перенести свои знания на другие модули и оперировать любой из баз данных SQLite, MySQL и PostgreSQL.
Однако это лишь вершина айсберга! Существуют также библиотеки для работы с SQL и объектно-реляционными отображениями, такие как SQLAlchemy и Django ORM, которые автоматизируют задачи взаимодействия Python с базами данных.
Если вам интересна тематика работы с базами данных с помощью Python, напишите об этом в комментариях – мы подготовим дополнительные материалы.