SQLAlchemy ORM (Object Relational Mapping или «объектно-реляционное отображение») — это способ создания таблиц и отношений между ними с помощью классов в Python. Он также предоставляет систему для создания запросов и управления базой данных с помощью объектно-ориентированного кода вместо чистого SQL. В отличие от SQLAlchemy Core, который сосредоточен на таблицах, строках и колонках, во главе угла в случае с ORM стоят объекты и модели.

ORM построен на базе SQLAlchemy Core, поэтому имеющиеся знания должны пригодиться.

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

Прежде чем двигаться дальше удалите все таблицы из sqlalchemy-tuts с помощью следующей команды: metadata.drop_all(engine).

Создание моделей

Модель — это класс Python, соответствующий таблице в базе данных, а его свойства — это колонки.

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

Наследоваться от декларативного базового класса с помощью вызова функции declarative_base().
Объявить имя таблицы с помощью атрибута __tablename__.
Объявить как минимум одну колонку, которая должна быть частью первичного ключа.

Последние два пункта говорят сами за себя, а вот для первого нужны детали.

Базовый класс управляет каталогом классов и таблиц. Другими словами, декларативный базовый класс — это оболочка над маппером и MetaData. Маппер соотносит подкласс с таблицей, а MetaData сохраняет всю информацию о базе данных и ее таблицах. По аналогии с Core в ORM методы create_all() и drop_all() объекта MetaData используются для создания и удаления таблиц.

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

Копировать

from sqlalchemy import create_engine, MetaData, Table, Integer, String, \
    Column, DateTime, ForeignKey, Numeric
from sqlalchemy.ext.declarative import declarative_base
from datetime import datetime

Base = declarative_base()

class Post(Base):
    __tablename__ = 'posts'
    id = Column(Integer, primary_key=True)
    title = Column(String(100), nullable=False)
    slug = Column(String(100), nullable=False)
    content = Column(String(50), nullable=False)
    published = Column(String(200), nullable=False, unique=True)    
    created_on = Column(DateTime(), default=datetime.now)
    updated_on = Column(DateTime(), default=datetime.now, onupdate=datetime.now)

Разберем построчно:

На 1-4 строках импортируются нужные классы и функции.
В 6 строке создается базовый класс с помощью вызова функции declarative_base().
На 10-16 строках колонки объявляются как атрибуты класса.

Стоит обратить внимание на то, что для создания колонок используется тот же класс Column, что и для SQLAlchemy Core. Единственное отличие в том, что первым аргументом является тип, а не название колонки. Аргументы-ключевые слова, в свою очередь, переданные в Column(), работают одинаково в ORM и Core.

Поскольку ORM построен на базе Core, SQLAlchemy использует определение модели для создания объекта Table и связи его с моделью с помощью функции mapper(). Это завершает процесс маппинга модели Post с соответствующим экземпляром Table. Теперь модель Post можно использовать для управления базой данных и для осуществления запросов к ней.

Классический маппинг

После прошлого раздела может создаться впечатление, что для использования SQLAlchemy ORM нужно переписать все экземпляры Table в виде моделей. Но это не так.

Можно запросто мапить любые Python классы на экземпляры Table с помощью функции mapper(). Например:

Копировать

from sqlalchemy import MetaData, Table, Integer, String, Column, Text, DateTime, Boolean
from sqlalchemy.orm import mapper
from datetime import datetime

metadata = MetaData()

post = Table('post', metadata, 
    Column('id', Integer(), primary_key=True),
    Column('title', String(200), nullable=False),
    Column('slug', String(200),  nullable=False),
    Column('content', Text(),  nullable=False),
    Column('published', Boolean(),  default=False),
    Column('created_on', DateTime(), default=datetime.now),
    Column('updated_on', DateTime(), default=datetime.now, onupdate=datetime.now)
)

class Post(object):
    pass

mapper(Post, post)

Этот класс принимает два аргумента: класс для маппинга и объект Table.

После этого у класса Post будут атрибуты, соответствующие колонкам таблицы. Таким образом у Post сейчас следующие атрибуты:

post.id
post.title
post.slug
post.content
post.published
post.created_on
post.updated_on

Код в списке выше эквивалентен модели Post, которая была объявлена выше.

Теперь вы должны лучше понимать, что делает declarative_base().

Добавление ключей и ограничений

При использовании ORM ключи и ограничения добавляются с помощью атрибута __table_args__.

Копировать

from sqlalchemy import Table, Index, Integer, String, Column, Text, \
                       DateTime, Boolean, PrimaryKeyConstraint, \
                       UniqueConstraint, ForeignKeyConstraint
from sqlalchemy.ext.declarative import declarative_base
from datetime import datetime

Base = declarative_base()


class User(Base):
    __tablename__ = 'users'
    id = Column(Integer)
    username = Column(String(100), nullable=False)
    email = Column(String(100), nullable=False)    
    password = Column(String(200), nullable=False)
    
    __table_args__ = (
        PrimaryKeyConstraint('id', name='user_pk'),
        UniqueConstraint('username'),
        UniqueConstraint('email'),
    )


class Post(Base):
    __tablename__ = 'posts'
    id = Column(Integer, primary_key=True)
    title = Column(String(100), nullable=False)
    slug = Column(String(100), nullable=False)
    content = Column(String(50), nullable=False)
    published = Column(String(200), nullable=False, default=False)
    user_id = Column(Integer(), nullable=False)
    created_on = Column(DateTime(), default=datetime.now)
    updated_on = Column(DateTime(), default=datetime.now, onupdate=datetime.now)
    
    __table_args__ = (
        ForeignKeyConstraint(['user_id'], ['users.id']),        
        Index('title_content_index' 'title', 'content'), # composite index on title and content   
    )

Отношения

Один-ко-многим

Отношение один-ко-многим создается за счет передачи внешнего ключа в дочерний класс. Например:

Копировать

class Author(Base):
    __tablename__ = 'authors'
    id = Column(Integer, primary_key=True)
    first_name = Column(String(100), nullable=False)
    last_name = Column(String(100), nullable=False)
    books = relationship("Book")

class Book(Base):
    __tablename__ = 'books'
    id = Column(Integer, primary_key=True)
    title = Column(String(100), nullable=False)
    copyright = Column(SmallInteger, nullable=False)
    author_id = Column(Integer, ForeignKey('authors.id'))

Строчка author_id = Column(Integer, ForeignKey('authors.id')) устанавливает отношение один-ко-многим между моделями Author и Book.

Функция relationship() добавляет атрибуты в модели для доступа к связанным данным. Как минимум — название класса, отвечающего за одну сторону отношения.

Строчка books = relationship("Book") добавляет атрибут books классу Author.

Имея объект a класса Author, получить доступ к его книгам можно через a.books. А если нужно получить автора книги через объект Book?

Для этого можно определить отдельное отношение relationship() в модели Author:

Копировать






class Author(Base):
    __tablename__ = 'authors'
    id = Column(Integer, primary_key=True)
    first_name = Column(String(100), nullable=False)
    last_name = Column(String(100), nullable=False)
    books = relationship("Book")

class Book(Base):
    __tablename__ = 'books'
    id = Column(Integer, primary_key=True)
    title = Column(String(100), nullable=False)
    copyright = Column(SmallInteger, nullable=False)
    author_id = Column(Integer, ForeignKey('authors.id'))
    author = relationship("Author")

Теперь через объект b класса Book можно получить автора b.author.

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

Копировать

class Author(Base):
    __tablename__ = 'authors'
    id = Column(Integer, primary_key=True)
    first_name = Column(String(100), nullable=False)
    last_name = Column(String(100), nullable=False)
    books = relationship("Book", backref="book")

Relationship можно задавать на любой стороне отношений. Поэтому предыдущий код можно записать и так:

Копировать

class Book(Base):
    __tablename__ = 'books'
    id = Column(Integer, primary_key=True)
    title = Column(String(100), nullable=False)
    copyright = Column(SmallInteger, nullable=False)
    author_id = Column(Integer, ForeignKey('authors.id'))
    author = relationship("Author", backref="books")

Один-к-одному

Установка отношения один-к-одному в SQLAlchemy почти не отличается от одного-ко-многим. Единственное отличие в том, что нужно передать дополнительный аргумент uselist=False в функцию relationship(). Например:

Копировать

class Person(Base):
    __tablename__ = 'persons'
    id = Column(Integer(), primary_key=True)
    name = Column(String(255), nullable=False)
    designation = Column(String(255), nullable=False)
    doj = Column(Date(), nullable=False)
    dl = relationship('DriverLicense', backref='person', uselist=False)


class DriverLicense(Base):
    __tablename__ = 'driverlicense'
    id = Column(Integer(), primary_key=True)
    license_number = Column(String(255), nullable=False)
    renewed_on = Column(Date(), nullable=False)
    expiry_date = Column(Date(), nullable=False)
    person_id = Column(Integer(), ForeignKey('persons.id'))

Имея объект p класса Person, p.dl вернет объект DriverLicense. Если не передать uselist=False в функцию, то установится отношение один-ко-многим между Person и DriverLicense, а p.dl вернет список объектов DriverLicense вместо одного. При этом uselist=False никак не влияет на атрибут persons объекта DriverLicense. Он вернет объект Person как и обычно.

Многие-ко-многим

Для отношения многие-ко-многим нужна отдельная таблица. Она создается как экземпляр класса Table и затем соединяется с моделью с помощью аргумента secondary функции relationship().

Копировать

author_book = Table('author_book', Base.metadata, 
    Column('author_id', Integer(), ForeignKey("authors.id")),
    Column('book_id', Integer(), ForeignKey("books.id"))
)

class Author(Base):
    __tablename__ = 'authors'
    id = Column(Integer, primary_key=True)
    first_name = Column(String(100), nullable=False)
    last_name = Column(String(100), nullable=False)


class Book(Base):
    __tablename__ = 'books'
    id = Column(Integer, primary_key=True)
    title = Column(String(100), nullable=False)
    copyright = Column(SmallInteger, nullable=False)
    author_id = Column(Integer, ForeignKey('authors.id'))
    author = relationship("Author", secondary=author_book, backref="books")

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

Для представления этого отношения была создана таблица author_book.

Через объект a класса Author можно получить все книги автора с помощью a.books. По аналогии через b класса Book можно вернуть список авторов b.authors.

В этом случае relationship() была объявлена в модели Book, но это можно было сделать и с другой стороны.

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

Копировать

class Author_Book(Base):
    __tablename__ = 'author_book'
    id = Column(Integer, primary_key=True)
    author_id =  Column(Integer(), ForeignKey("authors.id"))
    book_id =  Column(Integer(), ForeignKey("books.id"))
    extra_data = Column(String(100))

class Author(Base):
    __tablename__ = 'authors'
    id = Column(Integer, primary_key=True)
    first_name = Column(String(100), nullable=False)
    last_name = Column(String(100), nullable=False)
    books = relationship("Author_Book", backref='author')

class Book(Base):
    __tablename__ = 'books'
    id = Column(Integer, primary_key=True)
    title = Column(String(100), nullable=False)
    copyright = Column(SmallInteger, nullable=False)    
    authors = relationship("Author_Book", backref="book")

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

По аналогии с SQLAlchemy Core в ORM есть метод create_all() экземпляра MetaData, который отвечает за создание таблицы.

Base.metadata.create_all(engine)

Для удаления всех таблиц есть drop_all.

Base.metadata.drop_all(engine)

Пересоздадим таблицы с помощью моделей и сохраним их в базе данных, вызвав create_all(). Вот весь код для этой операции:

Копировать

from sqlalchemy import create_engine, MetaData, Table, Integer, String, \
    Column, DateTime, ForeignKey, Numeric, SmallInteger

from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.orm import relationship

from datetime import datetime

engine = create_engine("postgresql+psycopg2://postgres:1111@localhost/sqlalchemy_tuts")

Base = declarative_base()

class Customer(Base):
    __tablename__ = 'customers'
    id = Column(Integer(), primary_key=True)
    first_name = Column(String(100), nullable=False)
    last_name = Column(String(100), nullable=False)
    username = Column(String(50), nullable=False)
    email = Column(String(200), nullable=False)
    created_on = Column(DateTime(), default=datetime.now)
    updated_on = Column(DateTime(), default=datetime.now, onupdate=datetime.now)
    orders = relationship("Order", backref='customer')
    

class Item(Base):
    __tablename__ = 'items'
    id = Column(Integer(), primary_key=True)
    name = Column(String(200), nullable=False)
    cost_price =  Column(Numeric(10, 2), nullable=False)
    selling_price = Column(Numeric(10, 2),  nullable=False)
    quantity = Column(Integer())


class Order(Base):
    __tablename__ = 'orders'
    id = Column(Integer(), primary_key=True)
    customer_id = Column(Integer(), ForeignKey('customers.id'))
    date_placed = Column(DateTime(), default=datetime.now)
    line_items = relationship("OrderLine", backref='order')
    

class OrderLine(Base):
    __tablename__ = 'order_lines'
    id =  Column(Integer(), primary_key=True)
    order_id = Column(Integer(), ForeignKey('orders.id'))
    item_id = Column(Integer(), ForeignKey('items.id'))
    quantity = Column(SmallInteger())
    item = relationship("Item")

Base.metadata.create_all(engine)