Как подружить Python и базы данных SQL. Подробное руководство

Все приложения взаимодействуют с данными, чаще всего через систему управления базами данных (СУБД). Одни языки программирования поставляются с модулями для работы с СУБД, другие требуют использования сторонних пакетов. Из этого подробного руководства вы узнаете о различных библиотеках Python для работы с SQL-базами данных. Мы разработаем простое приложение для взаимодействия с БД SQLite, MySQL и PostgreSQL.

Примечание. Если вы не разбираетесь в базах данных, советуем обратить внимание на следующие публикации Библиотеки программиста: 11 типов современных баз данных, SQL за 20 минут, Подборка материалов для изучения баз данных и SQL.

Из этого пособия вы узнаете:

  • как подключиться к различным СУБД с помощью библиотек Python для работы с SQL базами данных;
  • как управлять базами данных SQLite, MySQL и PostgreSQL;
  • как выполнять запросы к базе данных внутри приложения Python;
  • как разрабатывать приложения для разных баз данных.

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

Содержание статьи:

  1. Схема базы данных
  2. Подключение к базам данных
  3. Создание таблиц
  4. Вставка записей
  5. Извлечение записей
  6. Обновление содержания
  7. Удаление записей таблицы

В каждом разделе по три подраздела: SQLite, MySQL и PostgreSQL.

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

  1. users
  2. posts
  3. comments
  4. 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 состоит из двух этапов:

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

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

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

  1. host_name
  2. user_name
  3. user_password

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

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

  1. Объект connection ;
  2. 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 на всех трех серверах БД. В данном разделе мы рассмотрим, как формировать таблицы внутри этих трех баз данных.

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

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

SQLite

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

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

Итак, начнем с определения функции execute_query() :

Теперь напишем передаваемый запрос ( query ):

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

  1. id
  2. name
  3. age
  4. gender
  5. 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() , который принимает два параметра:

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

Посмотрите на следующий пример, который вставляет две записи в таблицу 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, напишите об этом в комментариях – мы подготовим дополнительные материалы.

Вы пропустили

AEGIS Algorithms Android Angular Apache Airflow Apache Druid Apache Flink Apache Spark API API Canvas AppSec Architecture Artificial Intelligence Astro Authentication Authorization AutoGPT AWS AWS Aurora AWS Boto3 AWS EC2 AWS Lambda Azure Babylon.js Backend bash Beautiful Soup Bento UI Big Data Binary Tree Browser API Bun Career Cassandra Charts ChatGPT Chrome Extension Clean Code CLI ClickHouse Coding Codux Combine Compose Computer Context Fusion Copilot Cosmo Route CProgramming cron Cryptography CSS CTF Cypress DALL-E Data Analysis Data science Database dbt dbt Cloud deno Design Design Patterns Detekt Development Distributed Systems Django Docker Docker Hub Drizzle DRY DuckDB Express FastAPI Flask Flutter For Beginners Front End Development Game Development GCN GCP Geospatial Git GitHub Actions GitHub Pages Gitlab GMS GoFr Golang Google Google Sheets Google Wire GPT-3 GPT3 Gradio Gradle Grafana Graphic Design GraphQL gRPC Guidance HMS Hotwire HTML Huawei HuggingFace IndexedDB InfoSec Interview iOS Jackknife Java JavaScript Jetpack Compose JSON Kafka Kotlin Kubernetes LangChain Laravel Linux LlaMA LLM localStorage Logging Machine Learning Magento Math Mermaid Micro Frontends Mobile Mobile App Development mondayDB MongoDB Mongoose MySQL Naming NestJS NET NetMock Networks NextJS NLP Node.js Nodejs NoSQL NPM OOP OpenAI OTP Pandas PDF PHP Playwright Plotly Polars PostgreSQL Prefect Productivity Programming Prometheus Puppeteer Pushover Python Pytorch Quarkus Rabbitmq RAG Ramda Raspberry Pi React React Native Reactor Redis REST API Revolut Riverpod RProgramming Ruby Ruby on Rails Rust Scalene SCDB ScyllaDB Selenium Servers Sklearn SLO SnowFlake Snowkase Software Architecture Software Development Solara Solid Spring Boot SQL SQLite Streamlit SudoLang Supabase Swift SwiftUI Tailwind CSS Taipy Terraform Testing Transformers TURN TypeScript Ubuntu UI Design Unix UX UX Design Vim Vite VSCode Vue Web Architecture Web Components Web Development Web Frameworks Web Scraping Web-разработка Webassembly Websocket Whisper Widgets WordPress YAML YouTube Zed Наука о данных Разное Тренды

Современный подход к разработке с использованием Next.js