часть 1/2: Используем DB-API часть 2/2: Используем ORM
Это вторая часть моей статьи по работе с базой данных в 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 прежде чем импортировать надо установить:
pip install peewee

Для начала рассмотрим самый базовый шаблон DB-API, который будем использовать во всех дальнейших примерах:
# Импортируем библиотеку, соответствующую типу нашей базы данных 
# В данном случае импортируем все ее содержимое, чтобы при обращении не писать каждый раз имя библиотеки, как мы делали в первой статье
from peewee import *

# Создаем соединение с нашей базой данных
# В нашем примере у нас это просто файл базы
conn = SqliteDatabase('Chinook_Sqlite.sqlite')

# ТУТ БУДЕТ КОД НАШИХ МОДЕЛЕЙ

# Создаем курсор - специальный объект для запросов и получения данных с базы
cursor = conn.cursor()

# ТУТ БУДЕТ НАШ КОД РАБОТЫ С БАЗОЙ ДАННЫХ

# Не забываем закрыть соединение с базой данных
conn.close()

Собственно говоря, этот шаблон крайне похож на тот, который мы использовали в первой статье, отличие в методе соединения с базой данных. Теперь мы подключаемся через метод библиотеки peewee:
conn = SqliteDatabase('Chinook_Sqlite.sqlite')

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

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

# Делаем SELECT запрос к базе данных, используя обычный SQL-синтаксис
cursor.execute("SELECT Name FROM Artist ORDER BY Name LIMIT 3")

# Получаем результат сделанного запроса
results = cursor.fetchall()
print(results)   # [('A Cor Do Som',), ('AC/DC',), ('Aaron Copland & London Symphony Orchestra',)]

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

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


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

Классы моделей, поля экземпляров и экземпляры моделей peewee соответствуют следующим концепциям базы данных:
ORM концепция Концепция базы данных
Класс модели Таблица базы данных
Поле экземпляра (атрибут объекта) Колонка в таблице базы данных
Экземпляр модели (объект) Строка в таблице базы данных
Для реальных проектов, имеет смысл вынести модели в отдельный файл или файлы, в нашем упрощенном учебном примере мы просто вставляем код определения модели сразу после строки соединения с базой данных, вместо строки # ТУТ БУДЕТ КОД НАШИХ МОДЕЛЕЙ
# Определяем базовую модель о которой будут наследоваться остальные
class BaseModel(Model):
    class Meta:
        database = conn  # соединение с базой, из шаблона выше

# Определяем модель исполнителя
class Artist(BaseModel):
    artist_id = AutoField(column_name='ArtistId')
    name = TextField(column_name='Name', null=True)

    class Meta:
        table_name = 'Artist'

В данной статье не будем заострять внимание на различные типы полей и их связь с типами данных в различных базах данных. В документации к 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 = Artist.get(Artist.artist_id == 1)
Теперь у нас есть объект artist, с полями соответствующим данным исполнителя в конкретной строке, а также доступными методами модели исполнителя.
Этот объект можно использовать не только для чтения данных, но и для их обновления и удаления данной записи, в чем убедимся позже.
print('artist: ', artist.artist_id, artist.name)  # artist:  1 AC/DC

5.2) Получение набора записей через нашу модель Model.select()
Это похоже на стандартный select запрос к базе, но осуществляемый через нашу модель.
Обратите внимание, что к какой таблице обращаться и какие поля у нее есть
уже определено в нашей модели и нам не надо это указывать в нашем запросе.
Формируем запрос к базе с помощью нашей ORM прослойки и смотрим как этот запрос будет выглядеть:
query = Artist.select()
print(query)
# SELECT "t1"."ArtistId", "t1"."Name" FROM "Artist" AS "t1"

Полезно добавить дополнительные параметры, уточняющие запрос, они очень похожи на SQL инструкции:
query = Artist.select().where(Artist.artist_id < 10).limit(5).order_by(Artist.artist_id.desc())
print(query)
# SELECT "t1"."ArtistId", "t1"."Name" FROM "Artist" AS "t1" WHERE ("t1"."ArtistId" < 10) ORDER BY "t1"."ArtistId" DESC LIMIT 5

Теперь, определившись с запросом к базе, мы можем получить от нее ответ, для удобства делаем это сразу в виде словаря
artists_selected = query.dicts().execute()
print(artists_selected)  # <peewee.ModelDictCursorWrapper object at 0x7f6fdd9bdda0>

Мы получили итератор по полученным из базы записям, который можно обходить в цикле
for artist in artists_selected и получать сразу словари, соответствующие структуре нашего исполнителя, каждая итерация соответствует одной строке таблицы и соответственно одному исполнителю:
for artist in artists_selected:
    print('artist: ', artist)   # artist:  {'artist_id': 9, 'name': 'BackBeat'}

Для упрощения дальнейшей визуализации изменений в базе при дальнейших наших операциях добавим в наш шаблон под определением моделей код следующей функции:
def print_last_five_artists():
    """ Печатаем последние 5 записей в таблице испольнителей"""
    print('#######################################################')
    cur_query = Artist.select().limit(5).order_by(Artist.artist_id.desc())
    for item in cur_query.dicts().execute():
        print('artist: ', item)

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

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

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


6.1) Первый способ: Model.create() — передаем все требуемые параметры сразу
Artist.create(name='1-Qwerty')

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

6.3) Третий способ — массовое добавление из коллекции методом модели Model.insert_many()
Обратите внимание, что первые два метода не требуют добавления .execute(), а этот требует!
artists_data = [{'name': '3-qaswed'}, {'name': '4-yhnbgt'}]
Artist.insert_many(artists_data).execute()

Визуализируем последние 5 записей в таблице исполнителей, чтобы убедится, что три примера выше доавили нам 4 новые записи:
print_last_five_artists()
print_last_five_artists()
# artist:  {'artist_id': 279, 'name': '4-yhnbgt'}
# artist:  {'artist_id': 278, 'name': '3-qaswed'}
# artist:  {'artist_id': 277, 'name': '2-asdfg'}
# artist:  {'artist_id': 276, 'name': '1-Qwerty'}
# artist:  {'artist_id': 275, 'name': 'Philip Glass Ensemble'}

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


7.1) Первый способ обновления записей.
Выше, способом 6.2 мы создавали новую запись, но так можно не только создавать новую запись, но и обновлять существующую. Для этого нам надо для нашего объекта указать уже существующий в таблице первичный ключ.
artist = Artist(name='2-asdfg+++++')
artist.artist_id = 277  # Тот самый первичный ключ
# который связывает наш объект с конкретной строке таблицы базы данных
artist.save()

print_last_five_artists()
print_last_five_artists()
# artist:  {'artist_id': 279, 'name': '4-yhnbgt'}
# artist:  {'artist_id': 278, 'name': '3-qaswed'}
# artist:  {'artist_id': 277, 'name': '2-asdfg+++++'}
# artist:  {'artist_id': 276, 'name': '1-Qwerty'}
# artist:  {'artist_id': 275, 'name': 'Philip Glass Ensemble'}

7.2) Для обновления многих записей сразу, можно испольщовать метод модели Model.update(), в котором указываем что именно у нас меняется, а метод .where() определяет по каким критериям отбираются записи для изменения
query = Artist.update(name=Artist.name + '!!!').where(Artist.artist_id > 275)
query.execute()

print_last_five_artists()
print_last_five_artists()
# artist:  {'artist_id': 279, 'name': '4-yhnbgt!!!'}
# artist:  {'artist_id': 278, 'name': '3-qaswed!!!'}
# artist:  {'artist_id': 277, 'name': '2-asdfg+++!!!'}
# artist:  {'artist_id': 276, 'name': '1-Qwerty!!!'}
# artist:  {'artist_id': 275, 'name': 'Philip Glass Ensemble'}

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


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

print_last_five_artists()
print_last_five_artists()
# artist:  {'artist_id': 278, 'name': '3-qaswed!!!'}
# artist:  {'artist_id': 277, 'name': '2-asdfg+++!!!'}
# artist:  {'artist_id': 276, 'name': '1-Qwerty!!!'}
# artist:  {'artist_id': 275, 'name': 'Philip Glass Ensemble'}
# artist:  {'artist_id': 274, 'name': 'Nash Ensemble'}

8.2) Для удаления набора строк можно использовать Model.delete() метод
query = Artist.delete().where(Artist.artist_id > 275)
query.execute()

print_last_five_artists()

print_last_five_artists()
# artist:  {'artist_id': 275, 'name': 'Philip Glass Ensemble'}
# artist:  {'artist_id': 274, 'name': 'Nash Ensemble'}
# artist:  {'artist_id': 273, 'name': 'C. Monteverdi, Nigel Rogers - Chiaroscuro; London Baroque; London Cornett & Sackbu'}
# artist:  {'artist_id': 272, 'name': 'Emerson String Quartet'}
# artist:  {'artist_id': 271, 'name': 'Mela Tenenbaum, Pro Musica Prague & Richard Kapp'}

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


Показать полный код всех примеров и шаблона
# Импортируем нашу ORM библиотеку
from peewee import *

# Создаем соединение с нашей базой данных
conn = SqliteDatabase('Chinook_Sqlite.sqlite')


################ 3, ОПРЕДЕЛЯЕМ МОДЕЛИ ######################

# Определяем базовую модель о которой будут наследоваться остальные
class BaseModel(Model):
    class Meta:
        database = conn


# Определяем модель исполнителя
class Artist(BaseModel):
    artist_id = AutoField(column_name='ArtistId')
    name = TextField(column_name='Name', null=True)

    class Meta:
        table_name = 'Artist'


def print_last_five_artists():
    """ Печатаем последние 5 записей в таблице исполнителей"""
    print('########################################################')
    cur_query = Artist.select().limit(5).order_by(Artist.artist_id.desc())
    for item in cur_query.dicts().execute():
        print('artist: ', item)


# Создаем курсор - это специальный объект который делает запросы
# и получает их результаты
cursor = conn.cursor()

################ 2, ИСПОЛЬЗУЕМ КУРСОР  ###################

# Делаем SELECT запрос к базе данных, используя обычный SQL-синтаксис
cursor.execute("SELECT Name FROM Artist ORDER BY Name LIMIT 3")

# Получаем результат сделанного запроса
results = cursor.fetchall()
print(results)   # [('A Cor Do Som',), ('AC/DC',), ('Aaron Copland & London Symphony Orchestra',)]


######################## 5, ЧИТАЕМ ИЗ БАЗЫ ########################

# 5.1 Получение одиночной записи с методом модели Model.get()
artist = Artist.get(Artist.artist_id == 1)
# теперь у нас есть объект artist,
# с полями соответствующим данным исполнителя в конкретной строке
# а также доступными методами модели исполнителя
# этот объект можно использовать не только для чтения данных,
# но и для их обновления и удаления данной записи, в чем убедимся позже
print('artist: ', artist.artist_id, artist.name)  # artist:  1 AC/DC

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

# Формируем запрос к базе с помощью нашей ORM прослойки
# и смотрим как этот запрос будет выглядеть
query = Artist.select()
print(query)
# SELECT "t1"."ArtistId", "t1"."Name" FROM "Artist" AS "t1"

# Полезно добавить дополнительные параметры,
# уточняющие запрос, они очень похожи на SQL инструкции:
query = Artist.select().where(Artist.artist_id < 10).                        limit(5).order_by(Artist.artist_id.desc())
print(query)
# SELECT "t1"."ArtistId", "t1"."Name" FROM "Artist" AS "t1"
#   WHERE ("t1"."ArtistId" < 10) ORDER BY "t1"."ArtistId" DESC LIMIT 5

# Теперь, определившись с запросом к базе, мы можем получить от нее ответ,
# для удобства делаем это сразу в виде словаря
artists_selected = query.dicts().execute()
print(artists_selected)
# <peewee.ModelDictCursorWrapper object at 0x7f6fdd9bdda0>
# это итератор по полученным из базы записям, который можно обходить в цикле
for artist in artists_selected:
    print('artist: ', artist)   # artist:  {'artist_id': 9, 'name': 'BackBeat'}
    # То есть, каждая итерация соответствует одной строке таблицы
    # и соответственно одному исполнителю


################  6, СОЗДАЕМ НОВУЮ ЗАПИСЬ В БАЗЕ  #######################

# 6.1 Первый способ: Model.create() - передаем все требуемые параметры сразу
Artist.create(name='1-Qwerty')

# 6.2 Второй способ: Мы создаем объект класса нашей модели,
# работаем в коде в содержимым его полей,
# а в конце вызываем его метод .save()
artist = Artist(name='2-asdfg')
artist.save()  # save() returns the number of rows modified.
# обратите внимание, что здесь метод вызываем у объекта класса модели,
# а не у самой модели, как в первом способе

# 6.3 Третий способ - массовое добавление из коллекции
# методом модели Model.insert_many()
# Обратите внимание, что первые два метода не требуют добавления .execute(),
# а этот требует!
artists_data = [{'name': '3-qaswed'}, {'name': '4-yhnbgt'}]
Artist.insert_many(artists_data).execute()

# Визуализируем последние 5 записей в таблице исполнителей,
# чтобы убедится. что к последней добавлены 4 новые
print_last_five_artists()
# artist:  {'artist_id': 279, 'name': '4-yhnbgt'}
# artist:  {'artist_id': 278, 'name': '3-qaswed'}
# artist:  {'artist_id': 277, 'name': '2-asdfg'}
# artist:  {'artist_id': 276, 'name': '1-Qwerty'}
# artist:  {'artist_id': 275, 'name': 'Philip Glass Ensemble'}


############### 7, ОБНОВЛЯЕМ ДАННЫЕ СУЩЕСТВУЮЩЕЙ ЗАПИСИ ##############

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

artist = Artist(name='2-asdfg+++++')
artist.artist_id = 277  # Тот самый первичный ключ
# который связывает наш объект с конкретной строке таблицы базы данных
artist.save()

print_last_five_artists()
# artist:  {'artist_id': 279, 'name': '4-yhnbgt'}
# artist:  {'artist_id': 278, 'name': '3-qaswed'}
# artist:  {'artist_id': 277, 'name': '2-asdfg+++++'}
# artist:  {'artist_id': 276, 'name': '1-Qwerty'}
# artist:  {'artist_id': 275, 'name': 'Philip Glass Ensemble'}

# 7.2 Для обновления многих записей сразу,
# можно использовать метод модели Model.update(),
# в котором указываем что именно у нас меняется,
# а метод .where() определяет по каким критериям отбираются записи
query = Artist.update(name=Artist.name + '!!!').where(Artist.artist_id > 275)
query.execute()

print_last_five_artists()
# artist:  {'artist_id': 279, 'name': '4-yhnbgt!!!'}
# artist:  {'artist_id': 278, 'name': '3-qaswed!!!'}
# artist:  {'artist_id': 277, 'name': '2-asdfg+++!!!'}
# artist:  {'artist_id': 276, 'name': '1-Qwerty!!!'}
# artist:  {'artist_id': 275, 'name': 'Philip Glass Ensemble'}


###################### 8. УДАЛЯЕМ ЗАПИСЬ #######################

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

print_last_five_artists()
# artist:  {'artist_id': 278, 'name': '3-qaswed!!!'}
# artist:  {'artist_id': 277, 'name': '2-asdfg+++!!!'}
# artist:  {'artist_id': 276, 'name': '1-Qwerty!!!'}
# artist:  {'artist_id': 275, 'name': 'Philip Glass Ensemble'}
# artist:  {'artist_id': 274, 'name': 'Nash Ensemble'}

# 8.2 Для удаления набора строк можно использовать Model.delete() метод
query = Artist.delete().where(Artist.artist_id > 275)
query.execute()

print_last_five_artists()
# artist:  {'artist_id': 275, 'name': 'Philip Glass Ensemble'}
# artist:  {'artist_id': 274, 'name': 'Nash Ensemble'}
# artist:  {'artist_id': 273, 'name': 'C. Monteverdi, Nigel Rogers - Chiaroscuro; London Baroque; London Cornett & Sackbu'}
# artist:  {'artist_id': 272, 'name': 'Emerson String Quartet'}
# artist:  {'artist_id': 271, 'name': 'Mela Tenenbaum, Pro Musica Prague & Richard Kapp'}


# Не забываем закрыть соединение с базой данных в конце работы
conn.close()


10. Список литературы


The Active Record and Data Mappers of ORM Pattern (на английском)
What's the difference between Active Record and Data Mapper? (на английском)
Active Record против Data Mapper-а для сохранения данных
Chinook — Sample database for SQL Server, Oracle, MySQL, PostgreSQL, SQLite, DB2
• peewee официальные страницы репозитория и документации (на английском)
Peewee tutorial (на английском)
Peewee – лёгкая, гибкая и очень быстрая ORM на Python
часть 1/2: Используем DB-API часть 2/2: Используем ORM

Приглашаю к обсуждению:


  • Если я где-то допустил неточность или не учёл что-то важное — пишите в комментариях, важные комментарии будут позже добавлены в статью с указанием вашего авторства.
  • Если какие-то моменты не понятны и требуется уточнение — пишите ваши вопросы в комментариях — или я или другие читатели дадут ответ, а дельные вопросы с ответами будут позже добавлены в статью.