Как связать python и sql

Обновлено: 27.04.2024

Рассказываем, как связать Python и реляционные базы данных трёх типов: SQLite, MySQL и PostgreSQL. Зная одну библиотеку для работы с SQL, вы легко разберетесь в остальных.

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

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

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

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

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

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

Схема базы данных
Подключение к базам данных
Создание таблиц
Вставка записей
Извлечение записей
Обновление содержания
Удаление записей таблицы

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

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

Схема базы данных показана на рисунке ниже.

Пользователи ( 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 к базе данных:

Вот как работает этот код:

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

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

SQLite

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

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

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

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

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

Наконец, чтобы появилась таблица, вызываем 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 шесть записей:

Следующий скрипт вставляет записи в таблицы 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 :

Вывод выглядит так:

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

Больше полезной информации вы найдете на наших телеграм-каналах «Библиотека питониста» и «Библиотека data scientist’а».

Пишем класс на Python для работы с MS SQL Server и другими БД с интерфейсом ODBC. Использование класса рассмотрим на примере импорта информации из множества csv-файлов.

Ограничения SQL берут своё начало в декларативности языка – мы указываем SQL что мы хотим получить, а SQL извлекает нам это из указанной базы. Для простой обработки данных этого достаточно. Но что делать, если мы хотим большего? Приведённый ниже класс – наша основа для оптимизации сервера MS SQL, далее мы дополним его несколькими методами. Сторонний модуль pyodbc упрощает доступ к базам данных через программный интерфейс ODBC (Open Database Connectivity).

Чтобы подключиться к базе данных из Python с помощью этого класса, достаточно создать объект и передать имя базы данных, к примеру, sql = Sql('database123') .

Давайте разберёмся, что происходит внутри класса. В метод инициализации __init__ мы передаём строку server="XXVIR00012,55000" . Это строковое значение – имя нашего сервера, которое можно найти в диалоговом окне "Connect to Server" или в верхней части окна в среде MS SQL в Server Management Studio :

Диалоговое окно Connect to Server

Все трудности подключения берёт на себя модуль pyodbc. Нам лишь нужно передать строку подключения в функцию pyodbc.connect() .

Подробнее о передаваемых в ODBC-интерфейс значениях читайте в официальном хелпе.

В конце класса создаётся строка, обновляемая с каждым передаваемым запросом:

Это позволяет нам собирать логи и создавать более читабельный вывод. Для записи времени мы используем стандартную библиотеку datetime .

Есть несколько важных функций, направленных на передачу данных в базу данных или из неё. Для примера мы возьмём каталог, в котором имеется множество однотипных csv-файлов.

В текущем проекте мы хотим:

Импортировать файлы в SQL-server.
Объединить их в одну таблицу.
Динамически создать несколько таблиц на основе категорий внутри столбца.

Как мы видим, кроме инициализации в класс Sql нужно добавить методы push_dataframe и manual , union и drop . Опишем их.

Метод push_dataframe

Функция push_dataframe позволит поместить в базу данных датафрейм Pandas.

Это полезно, когда нужно загрузить много файлов.

Метод manual

Метод manual используется выше как отдельно, так и внутри функций union и drop . Она позволяет упростить выполнение SQL-кода.

Аргумент response даёт возможность вставить в датафрейм исходящую информацию нашего запроса. Извлечь все уникальные значения из colX в таблице generic_jan можно с помощью следующей строки:

Метод union

Теперь на основе метода manual создадим метод union :

Это «объединяющий» запрос с перебором списка имён таблиц из table_list .

Метод drop

Метод drop выполняет удаление таблиц:

Функция drop позволяет удалить одну или несколько таблицу, поместив строку в tables , либо несколько таблиц, поместив туда же весь список.

Сочетая описанные несложные методы мы значительно облегчили работу с большим количеством файлов в SQL Server. Если вас заинтересовала тема взаимодействия Python и SQL, почитайте наш пост «Как подружить Python и базы данных SQL. Подробное руководство». Успехов в развитии!

Больше полезной информации вы можете найти на наших телеграм каналах «Библиотека питониста» и «Библиотека data scientist’а».

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

В статье рассмотрены основные методы DB-API, позволяющие полноценно работать с базой данных. Полный список можете найти по ссылкам в конец статьи.

Требуемый уровень подготовки: базовое понимание синтаксиса SQL и Python.

Готовим инвентарь для дальнейшей комфортной работы

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

Вы можете использовать (последние два варианта кросс-платформенные и бесплатные):

Привычную вам утилиту для работы с базой в составе вашей IDE;

Python DB-API модули в зависимости от базы данных

База данных	DB-API модуль
SQLite	sqlite3
PostgreSQL	psycopg2
MySQL	mysql.connector
ODBC	pyodbc

Соединение с базой, получение курсора

Для начала рассмотрим самый базовый шаблон DB-API, который будем использовать во всех дальнейших примерах:

При работе с другими базами данных, используются дополнительные параметры соединения, например для PostrgeSQL:

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

Обратите внимание: После получения результата из курсора, второй раз без повторения самого запроса его получить нельзя — вернется пустой результат!

Запись в базу

Примечание: Если к базе установлено несколько соединений и одно из них осуществляет модификацю базы, то база SQLite залочивается до завершения (метод соединения .commit()) или отмены (метод соединения .rollback()) транзакции.

Разбиваем запрос на несколько строк в тройных кавычках

Длинные запросы можно разбивать на несколько строк в произвольном порядке, если они заключены в тройные кавычки — одинарные ('''…''') или двойные (""". """)

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

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

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

Для решения такой задачи можно либо несколько раз вызывать метод курсора .execute()

Либо использовать метод курсора .executescript()

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

Делаем подстановку значения в запрос

Важно! Никогда, ни при каких условиях, не используйте конкатенацию строк (+) или интерполяцию параметра в строке (%) для передачи переменных в SQL запрос. Такое формирование запроса, при возможности попадания в него пользовательских данных – это ворота для SQL-инъекций!

Правильный способ – использование второго аргумента метода .execute()

Возможны два варианта:

Примечание 1: В PostgreSQL (UPD: и в MySQL) вместо знака '?' для подстановки используется: %s

UPD: Примечание 3: Благодарю Igelko за упоминание параметра paramstyle — он определяет какой именно стиль используется для подстановки переменных в данном модуле.
Вот ссылка с полезным приемом для работы с разными стилями подстановок.

Делаем множественную вставку строк проходя по коллекции с помощью метода курсора .executemany()

Получаем результаты по одному, используя метод курсора .fetchone()

Он всегда возвращает кортеж или None. если запрос пустой.

Важно! Стандартный курсор забирает все данные с сервера сразу, не зависимо от того, используем мы .fetchall() или .fetchone()

Курсор как итератор

UPD: Повышаем устойчивость кода

Благодарю paratagas за ценное дополнение:
Для большей устойчивости программы (особенно при операциях записи) можно оборачивать инструкции обращения к БД в блоки «try-except-else» и использовать встроенный в sqlite3 «родной» объект ошибок, например, так:

UPD: Использование with в psycopg2

Благодарю KurtRotzke за ценное дополнение:
Последние версии psycopg2 позволяют делать так:

Некоторые объекты в Python имеют __enter__ и __exit__ методы, что позволяет «чисто» взаимодействовать с ними, как в примере выше.

UPD: Ипользование row_factory

Благодарю remzalp за ценное дополнение:
Использование row_factory позволяет брать метаданные из запроса и обращаться в итоге к результату, например по имени столбца.
По сути — callback для обработки данных при возврате строки. Да еще и полезнейший cursor.description, где есть всё необходимое.

Пример из документации:

Дополнительные материалы (на английском)

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

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

Требуемый уровень подготовки: базовое понимание SQL и Python (код статьи проверялся под Python 3.6). Желательно ознакомится с первой частью, так как к ней будут неоднократные отсылки и сравнения. В конце статьи есть весь код примеров под спойлером в едином файле и список ссылок для более углубленного изучения материала.

1. Общие понятия ORM

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

Хорошо было бы иметь некий механизм автоматического генерирования этих запросов исходя из заранее определенной структуры наших данных и приведения ответа к этой же структуре. Именно таким механизмом является добавление дополнительной ORM-прослойки между кодом нашего приложения и SQL базой.

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

Существуют два основных подхода к реализации ORM:
Active Record – более простой для понимания и реализации подход, суть которого сводится к отображению объекта данных на строку базы данных.
Data Mapper – в отличии от предыдущего подхода полностью разделяет представление данных в программе от его представления в базе данных.

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

В данном руководстве будет проиллюстрирован более простой и понятный для старта подход Active Record. Мы будем рассматривать основы работы с peewee – лёгкой, быстрой, гибкой ORM на Python, которая поддерживает SQLite, MySQL и PostgreSQL.

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

2. Установка ORM, соединение с базой, получение курсора

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

В отличии от модуля sqlite из стандартной библиотеки, peewee прежде чем импортировать надо установить:

Для начала рассмотрим самый базовый шаблон DB-API, который будем использовать во всех дальнейших примерах:
Собственно говоря, этот шаблон крайне похож на тот, который мы использовали в первой статье, отличие в методе соединения с базой данных. Теперь мы подключаемся через метод библиотеки peewee:

В зависимости от типа нашей базы методы подключения отличаются: SqliteDatabase(),
MySQLDatabase(), PostgresqlDatabase() — какой для какой базы очевидно из имени, в скобках передаются параметры подключения, в нашем примере это просто имя файла базы.

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

Это может быть очень удобно при постепенном переходе на ORM, так как мы можем сменить способ соединения с базой на работу через peewee и потом постепенно менять запросы к базе на новые, не нарушая работу старого кода!

3. Описание моделей и их связь с базой данных

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

Классы моделей, поля экземпляров и экземпляры моделей peewee соответствуют следующим концепциям базы данных:

ORM концепция	Концепция базы данных
Класс модели	Таблица базы данных
Поле экземпляра (атрибут объекта)	Колонка в таблице базы данных
Экземпляр модели (объект)	Строка в таблице базы данных

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

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

Замечание: Есть возможность автоматической генерации моделей из существующей базы данных, а также возможность генерации таблиц базы данных из заранее определенных моделей. Для подобных задач в peewee есть набор инструментария, так называемый Playhouse.

4. CRUD операции и общие подходы

Ниже мы рассмотрим так называемые CRUD операции с базой – создание (Create), чтение (Read), обновление (Update) и удаления (Delete) объектов/записей в базе. Мы не будем пытаться охватить все многообразие возможностей которые предоставляет нам peewee, рассмотрим только самые базовые вещи, которые позволят нам начать решать реальные задачи разработки. Более детальные описания можно найти в официальной документации.

Есть два основных подхода при работе с ORM peewee, в зависимости от того, какую задачу мы решаем и как нам удобней это делать:

1) Мы можем вызывать общие методы у класса модели, такие как .select(), .update(), .delete(), .create() и т.д., передвать дополнительные параметры и делать массовые операции. В данном случае, логика нашей работы похожа на логику работы с SQL запросами, которую мы рассматривали в первой статье. Основное отличие в том, что работая через модели у нас уже есть привязки к таблицам и известны имеющиеся поля, поэтому нам не надо это все явно прописывать в запросе.

2) Второй подход, состоит в том, что мы получаем объект класса модели, который соответствует одной строке таблицы базы данных, работаем с этим объектом, в том числе меняя значения его атрибутов, а по завершению работы сохраняем / обновляем — .save() или удаляем строку его представления в таблице базы данных — .delete_instance().

Как это работает будем понятней из примеров CRUD операций ниже.

5. Чтение записей

5.1) Получение одиночной записи с методом модели Model.get()
Теперь у нас есть объект artist, с полями соответствующим данным исполнителя в конкретной строке, а также доступными методами модели исполнителя.
Этот объект можно использовать не только для чтения данных, но и для их обновления и удаления данной записи, в чем убедимся позже.

5.2) Получение набора записей через нашу модель Model.select()
Это похоже на стандартный select запрос к базе, но осуществляемый через нашу модель.
Обратите внимание, что к какой таблице обращаться и какие поля у нее есть
уже определено в нашей модели и нам не надо это указывать в нашем запросе.
Формируем запрос к базе с помощью нашей ORM прослойки и смотрим как этот запрос будет выглядеть:

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

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

Мы получили итератор по полученным из базы записям, который можно обходить в цикле
for artist in artists_selected и получать сразу словари, соответствующие структуре нашего исполнителя, каждая итерация соответствует одной строке таблицы и соответственно одному исполнителю:

Для упрощения дальнейшей визуализации изменений в базе при дальнейших наших операциях добавим в наш шаблон под определением моделей код следующей функции:

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

Обращаем внимание, что вывод будет совпадать с примерами в статье, только если их выполнять последовательно, начиная с неизмененной Chinook базы, так как как примеры модифицируют базу!

6. Создание записи

6.1) Первый способ: Model.create() — передаем все требуемые параметры сразу

6.2) Второй способ: Мы создаем объект класса нашей модели, работаем в коде в содержимым его полей, а в конце вызываем его метод .save()
Обратите внимание, что здесь метод вызываем у объекта класса модели, а не у самой модели, как в первом способе.

6.3) Третий способ — массовое добавление из коллекции методом модели Model.insert_many()
Обратите внимание, что первые два метода не требуют добавления .execute(), а этот требует!

Визуализируем последние 5 записей в таблице исполнителей, чтобы убедится, что три примера выше доавили нам 4 новые записи:

7. Обновление записей

7.1) Первый способ обновления записей.
Выше, способом 6.2 мы создавали новую запись, но так можно не только создавать новую запись, но и обновлять существующую. Для этого нам надо для нашего объекта указать уже существующий в таблице первичный ключ.

7.2) Для обновления многих записей сразу, можно испольщовать метод модели Model.update(), в котором указываем что именно у нас меняется, а метод .where() определяет по каким критериям отбираются записи для изменения

8. Удаление записей

8.1) Первый способ удаления записи — это получение объекта записи методом Model.get() как в 5.1 выше и вызова метода удаления этой записи .delete_instance():

8.2) Для удаления набора строк можно использовать Model.delete() метод

Если вы — программист, то я полагаю, что вы, наверняка, знаете о существовании чрезвычайно компактной и нетребовательной к ресурсам СУБД SQLite, или даже пользовались ей. Эта система обладает практически всеми возможностями, которых можно ожидать от реляционной СУБД, но при этом всё хранится в единственном файле. Вот некоторые сценарии использования SQLite, упомянутые на официальном сайте этой системы:

Встраиваемые устройства и IoT.
Анализ данных.
Перенос данных из одной системы в другую.
Архивирование данных и (или) упаковка данных в контейнеры.
Хранение данных во внешней или временной БД.
Заменитель корпоративной БД, используемый в демонстрационных или испытательных целях.
Обучение, освоение начинающими практических приёмов работы с БД.
Прототипирование и исследование экспериментальных расширений языка SQL.

Данный материал посвящён использованию SQLite в Python-разработке. Поэтому для нас особенно важно то, что эта СУБД, представленная модулем sqlite3 , входит в стандартную библиотеку языка. То есть оказывается, что для работы с SQLite из Python-кода не нужно устанавливать некое клиент-серверное ПО, не нужно поддерживать работу какого-то сервиса, отвечающего за работу с СУБД. Достаточно лишь импортировать модуль sqlite3 и приступить к его использованию в программе, получив в своё распоряжение систему управления реляционными базами данных.

Импорт модуля

Выше я говорил о том, что SQLite — это СУБД, встроенная в Python. Это значит, что для того чтобы приступить к работе с ней, достаточно импортировать соответствующий модуль, не выполняя предварительно его установку с помощью команды вроде pip install . Команда импорта SQLite выглядит так:

Создание подключения к БД

Для организации подключения к базе данных SQLite не нужно беспокоиться об установке драйверов, о подготовке строк подключения и о прочих подобных вещах. Создать базу данных и получить в своё распоряжение объект подключения к ней можно очень просто и быстро:

Выполнив эту строку кода, мы создадим базу данных и подключимся к ней. Дело тут в том, что база данных, к которой мы подключаемся, пока не существует, поэтому система автоматически создаёт новую пустую БД. Если же база данных уже создана (предположим, это my-test.db из предыдущего примера), для того чтобы к ней подключиться, достаточно воспользоваться точно таким же кодом.

Файл только что созданной базы данных

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

Теперь давайте создадим таблицу в нашей новой БД:

Тут описано добавление в БД таблицы USER с тремя столбцами. Как видите, SQLite — это и правда очень простая в работе СУБД, но она обладает всеми основными возможностями, наличия которых можно ожидать от обычной системы управления реляционными базами данных. Речь идёт о поддержке типов данных, в том числе — типов, допускающих значение null , о поддержке первичного ключа и автоинкремента.

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

Вставка записей в таблицу

Вставим несколько записей в таблицу USER , которую мы только что создали. Это, кроме прочего, даст нам доказательство того, что таблица, и правда, была создана вышеприведённой командой.

Представим, что нам нужно добавить в таблицу несколько записей одной командой. В SQLite сделать это очень просто:

Здесь нам нужно определить SQL-выражение со знаками вопроса ( ? ) в виде местозаполнителей. Учитывая то, что в нашем распоряжении есть объект подключения к базе данных, мы, подготовив выражение и данные, можем вставить записи в таблицу:

Выполнение запросов к базе данных

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

Результат выполнения запроса к БД

Как видите, то, что было нужно, получить удалось. И сделать это было очень просто.

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

Я пользуюсь DBeaver. Предлагаю взглянуть на то, как это выглядит.

Подключение к базе данных SQLite из SQL-клиента (DBeaver)

Я пользуюсь облачным сервисом Google Colab и хочу загрузить файл my-test.db на свой компьютер. Если же вы экспериментируете с SQLite на компьютере, то это значит, что вы, без необходимости скачивать откуда-то файл базы данных, можете подключиться к ней, используя SQL-клиент.

В случае с DBeaver для подключения к БД SQLite нужно создать новое подключение и выбрать, в качества типа базы данных, SQLite.

Подготовка подключения в DBeaver

Затем надо найти файл базы данных.

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

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

Выполнение запросов к базе данных

Интеграция с pandas

Думаете, на этом мы завершим разговор о поддержке SQLite в Python? Нет, нам ещё есть о чём поговорить. А именно, так как SQLite — это стандартный Python-модуль, эта СУБД легко интегрируется с дата-фреймами pandas.

Датафрейм pandas

Для сохранения датафрейма в БД можно просто воспользоваться его методом to_sql() :

Вот и всё! Нам даже не нужно заранее создавать таблицу. Типы данных и характеристики полей будут настроены автоматически, на основании характеристик датафрейма. Конечно, вы, если надо, можете настроить всё самостоятельно.

Теперь, предположим, нам нужно получить объединение таблиц USER и SKILL и записать полученные данные в датафрейм pandas. Это тоже очень просто:

Чтение данных из БД в датафрейм pandas

Замечательно! А теперь давайте запишем то, что у нас получилось, в новую таблицу с именем USER_SKILL :

С этой таблицей, конечно, можно работать и пользуясь SQL-клиентом.

Применение SQL-клиента для работы с базой данных

Итоги

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

Здесь я рассказал о том, как использовать встроенную в Python библиотеку sqlite3 для создания баз данных и для работы с ними. Конечно, такие БД поддерживают не только операцию добавления данных, но и операции изменения и удаления информации. Полагаю, вы, узнав о sqlite3 , испытаете всё это сами.

Очень важно то, что SQLite отлично стыкуется с pandas. Данные из БД очень легко считывать, помещая в датафреймы. Не менее проста и операция по сохранению содержимого датафреймов в базу данных. Это ещё сильнее упрощает использование SQLite.

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

Читайте также: