Домой Edit me on GitHub

2020-12-05

Каналы передачи данных | Сетевое программирование | Базы данных | Основы Веб-программирования

ORM (объектно-реляционное отображение)

См.также

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

В SQLAlchemy такая связь называется «отображением», что соответствует широко известному шаблону проектирования с названием «DataMapper», описанному в книге Martin Flower с названием Patterns of Enterprise Application Architecture.

В целом, система объектно-реляционного отображения SQLAlchemy была разработана с применением большого количества приемов, которые описал в своей книге Martin Flower. Она также подверглась значительному влиянию со стороны известной системы реляционного отображения Hibernate для языка программирования Java и продукта SQLObject для языка программирования Python от Ian Bicking.

Классическое представление классов таблиц

См.также

Объект класса sqlalchemy.orm.mapper.Mapper связывает колонки из схемы таблицы и атрибуты Python класса.

2.sqlalchemy/4.orm.mapper.classic.py
 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
# -*- coding: utf-8 -*-
from sqlalchemy import Table, MetaData, Column, Integer, String, ForeignKey
from sqlalchemy.orm import mapper, relationship

metadata = MetaData()

user = Table('user', metadata,
             Column('id', Integer, primary_key=True),
             Column('name', String(50)),
             Column('fullname', String(50)),
             Column('password', String(12))
             )


address = Table('address', metadata,
                Column('id', Integer, primary_key=True),
                Column('user_id', Integer, ForeignKey('user.id')),
                Column('email_address', String(50))
                )


class User(object):
    pass


class Address(object):
    pass

print(dir(User))

mapper(
    User, user,
    properties={
        'addresses': relationship(Address, backref='user',
                                  order_by=address.c.id)
    })

print(dir(User))

mapper(Address, address)
>>> ['__class__', '__delattr__', '__dict__', '__doc__', '__format__',
'__getattribute__', '__hash__', '__init__', '__module__', '__new__',
'__reduce__', '__reduce_ex__', '__repr__', '__setattr__', '__sizeof__',
'__str__', '__subclasshook__', '__weakref__']

>>> ['__class__', '__delattr__', '__dict__', '__doc__', '__format__',
'__getattribute__', '__hash__', '__init__', '__module__', '__new__',
'__reduce__', '__reduce_ex__', '__repr__', '__setattr__', '__sizeof__',
'__str__', '__subclasshook__', '__weakref__', '_sa_class_manager',
'addresses', 'fullname', 'id', 'name', 'password']

Декларативное представление классов таблиц

См.также

Каждый класс, представляющий таблицу в БД, должен наследоваться от базового класса который создается при помощи функции sqlalchemy.ext.declarative.declarative_base().

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
>>> from sqlalchemy.ext.declarative import declarative_base
>>> Base = declarative_base()

>>> from sqlalchemy import Column, Integer, String

>>> class User(Base):
...     __tablename__ = 'user'

...     id = Column(Integer, primary_key=True)
...     name = Column(String)
...     fullname = Column(String)

...     def __repr__(self):
...         return "<User(%r, %r)>" % (
...                 self.name, self.fullname
...             )

Схема таблицы

Для каждого класса унаследованного от базового автоматически создается схема таблицы (объект класса sqlalchemy.schema.Table) и привязывается к нему через атрибут __table__.

1
2
3
4
>>> User.__table__
Table('user', MetaData(bind=None), Column('id', Integer(), table=<user>,
primary_key=True, nullable=False), Column('name', String(), table=<user>),
Column('fullname', String(), table=<user>), schema=None)

MetaData

Любой класс таблицы автоматически ассоциируется с объектом sqlalchemy.schema.Table, который автоматически добавляется в список sqlalchemy.schema.MetaData. Базовый класс Base, созданный при помощи функции sqlalchemy.ext.declarative.declarative_base(), является более высокоуровневой абстракцией над sqlalchemy.schema.MetaData, которая позволяет описывать таблицы декларативным способом. Таким образом все классы-таблицы имеют свою схему, которая хранится в атрибуте metadata базового класса Base:

1
2
3
4
5
6
>>> Base.metadata
MetaData(bind=None)
>>> Base.metadata.tables.items()
[('user', Table('user', MetaData(bind=None), Column('id', Integer(),
table=<user>, primary_key=True, nullable=False), Column('name', String(),
table=<user>), Column('fullname', String(), table=<user>), schema=None))]

Благодаря тому что Base содержит в себе объект sqlalchemy.schema.MetaData, вы можете пользоваться всеми его возможностями.

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
>>> from sqlalchemy import create_engine
>>> engine = create_engine('sqlite://')
>>> Base.metadata.create_all(engine)

[SQL]: SELECT CAST('test plain returns' AS VARCHAR(60)) AS anon_1
[SQL]: ()
[SQL]: SELECT CAST('test unicode returns' AS VARCHAR(60)) AS anon_1
[SQL]: ()
[SQL]: PRAGMA table_info("user")
[SQL]: ()
[SQL]:
CREATE TABLE user (
    id INTEGER NOT NULL,
    name VARCHAR,
    fullname VARCHAR,
    PRIMARY KEY (id)
)


[SQL]: ()
[SQL]: COMMIT

Mapper

Объект класса sqlalchemy.orm.mapper.Mapper связывает колонки из схемы таблицы и атрибуты из класса таблицы унаследованного от Base.

2.sqlalchemy/4.orm.mapper.declarative.py
 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
# -*- coding: utf-8 -*-
from sqlalchemy import Column, ForeignKey, Integer, String
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.orm import relationship

Base = declarative_base()


class User(Base):
    __tablename__ = 'user'

    id = Column(Integer, primary_key=True)
    name = Column(String)
    fullname = Column(String)
    password = Column(String)

    addresses = relationship("Address", backref="user",
                             order_by="Address.id")


class Address(Base):
    __tablename__ = 'address'

    id = Column(Integer, primary_key=True)
    user_id = Column(ForeignKey('user.id'))
    email_address = Column(String)

address1 = Address(email_address="vas@example.com")
address2 = Address(email_address="vas2@example.com")
address3 = Address(email_address="vasya@example.com")

print("Mapper relationship: " + str(User.__mapper__.relationships))
print("Mapper columns: " + str(User.__mapper__.c.items()))
print

user1 = User(name="Вася")
user1.addresses = [address1, address2, address3]
print("User1 columns: " + str(user1.__table__.c.items()))
print
print(address1.user.name)
 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
Mapper relationship: <sqlalchemy.util._collections.ImmutableProperties object at 0x7ffeae32da28>
Mapper columns: [('id', Column('id', Integer(), table=<user>,
primary_key=True, nullable=False)), ('name', Column('name', String(),
table=<user>)), ('fullname', Column('fullname', String(), table=<user>)),
('password', Column('password', String(), table=<user>))]

User1 columns: [('id', Column('id', Integer(), table=<user>,
primary_key=True, nullable=False)), ('name', Column('name', String(),
table=<user>)), ('fullname', Column('fullname', String(), table=<user>)),
('password', Column('password', String(), table=<user>))]

Вася

Конструктор класса

Декларативно описанный класс таблицы содержит в себе конструктор по умолчанию.

1
>>> ed_user = User(name='ed', fullname='Edward Jones')

Можно переопределить конструктор вручную

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
class User(Base):
    __tablename__ = 'user'

    def __init__(self, name, fullname):
       self.name = name
       self.fullname = fullname

    id = Column(Integer, primary_key=True)
    name = Column(String)
    fullname = Column(String)
    password = Column(String)

    addresses = relationship("Address", backref="user",
                             order_by="Address.id")

Поле User.id является первичным ключом, если его значение не указанно явно или такой id не существует в БД, то объект считается новым. После записи объекта в БД, значение поля id автоматически присваивается.

1
2
3
4
>>> print(ed_user.name, ed_user.fullname)
('ed', 'Edward Jones')
>>> print(ed_user.id)
None

Сессии

См.также

Сессии являются более абстрактным уровнем над механизмом соединения с СУБД sqlalchemy.engine.Engine. Они включают в себя функции хранения состояния объектов таблиц и записи этого состояния, по требованию, в БД.

Примечание

Анологию сессий в SQLAlchemy можно провести с системой контроля версий Git.

  • Ресурсы

    • git управляет файлами.
    • SQAlchemy манипулирует объектам таблиц (будущие записи в таблицах).

  • Состояние ресурсов

    • Область подготовленных файлов (staging area) — это обычный файл, обычно хранящийся в каталоге Git, который содержит информацию о том, какие файлы должны войти в следующий коммит.

      git add README.txt

    • В SQLAlchemy это сессия которая хранить в себе объекты для дальнейшей записи в БД.

      session.add(ed_user)

  • Запись состояния

    • Создает рабочую копию файлов, добавленных в staging area.

      git commit

    • Записывает объекты, добавленные ранее в сессию, в базу данных.

      session.commit()

Существуют даже расширения для SQLAlchemy которые позволяют хранить данные в git репозитории вместо СУБД, используя при этом только возможности ORM библиотеки SQLAlchemy, т.к. модуль соединений с БД и конструктор SQL выражения для git не нужен (https://github.com/matthias-k/gitdb2).

Сессии создаются при помощи экземпляра класса sqlalchemy.orm.session.Session.

1
2
>>> from sqlalchemy.orm import Session
>>> session = Session(bind=engine)

Для добавления объекта (представляющего таблицу) в сессию, необходимо использовать метод sqlalchemy.orm.session.Session.add().

1
>>> session.add(ed_user)

Перед выполнением любого запроса из сессии, состояние сессии автоматически переносится в БД. В нашем случае, не сохраненный объект ed_user добавляется в БД.

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
>>> our_user = session.query(User).filter_by(name='ed').first()

[SQL]: BEGIN (implicit)
[SQL]: INSERT INTO user (name, fullname) VALUES (?, ?)
[SQL]: ('ed', 'Edward Jones')
[SQL]: SELECT user.id AS user_id, user.name AS user_name, user.fullname AS user_fullname
FROM user
WHERE user.name = ?
 LIMIT ? OFFSET ?
[SQL]: ('ed', 1, 0)

>>> our_user
<User('ed', 'Edward Jones')>

Теперь у пользователя ed_user появилось значение id.

1
2
3
4
5
6
>>> our_user.id
1
>>> ed_user.id
1
>>> ed_user == our_user is ed_user
True

Добавление нескольких объектов в сессию за раз:

1
2
3
4
5
>>> session.add_all([
...     User(name='wendy', fullname='Wendy Weathersmith'),
...     User(name='mary', fullname='Mary Contrary'),
...     User(name='fred', fullname='Fred Flinstone')
>>> ])

Если объект, находящийся в сессии, поменялся, то он помечается как dirty. Все измененные объекты в сессии доступны через атрибут sqlalchemy.orm.session.Session.dirty

1
2
3
>>> ed_user.fullname = 'Ed Jones'
>>> session.dirty
IdentitySet([<User('ed', 'Ed Jones')>])

Новые объекты, попавшие в сессию после ее сохранения или в новую сессию, доступны через атрибут sqlalchemy.orm.session.Session.new

1
2
3
4
>>> session.new
IdentitySet([<User('fred', 'Fred Flinstone')>,
             <User('wendy', 'Wendy Weathersmith')>,
             <User('mary', 'Mary Contrary')>])

Метод sqlalchemy.orm.session.Session.commit() сохраняет состояние сессии в БД и подтверждает SQL транзакцию, в рамках которой выполнялись все предыдущие запросы.

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
>>> session.commit()

[SQL]: UPDATE user SET fullname=? WHERE user.id = ?
[SQL]: ('Ed Jones', 1)
[SQL]: INSERT INTO user (name, fullname) VALUES (?, ?)
[SQL]: ('wendy', 'Wendy Weathersmith')
[SQL]: INSERT INTO user (name, fullname) VALUES (?, ?)
[SQL]: ('mary', 'Mary Contrary')
[SQL]: INSERT INTO user (name, fullname) VALUES (?, ?)
[SQL]: ('fred', 'Fred Flinstone')
[SQL]: COMMIT

После выполнения COMMIT сессия не привязана ни к одной транзакции в СУБД. Любые изменения объектов в сессии создадут новую транзакцию.

1
2
3
4
5
6
7
8
>>> ed_user.fullname

[SQL]: BEGIN (implicit)
[SQL]: SELECT user.id AS user_id, user.name AS user_name, user.fullname AS user_fullname
FROM user
WHERE user.id = ?
[SQL]: (1,)
u'Ed Jones'

Создадим новые изменения объектов и отравим SQL запрос с этими изменениями.

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
>>> ed_user.name = 'Edwardo'
>>> fake_user = User(name='fakeuser', fullname='Invalid')
>>> session.add(fake_user)

>>> ed_user
<User('Edwardo', u'Ed Jones')>

>>> session.query(User).filter(User.name.in_(['Edwardo', 'fakeuser'])).all()

[SQL]: UPDATE user SET name=? WHERE user.id = ?
[SQL]: ('Edwardo', 1)
[SQL]: INSERT INTO user (name, fullname) VALUES (?, ?)
[SQL]: ('fakeuser', 'Invalid')
[SQL]: SELECT user.id AS user_id, user.name AS user_name, user.fullname AS user_fullname
FROM user
WHERE user.name IN (?, ?)
[SQL]: ('Edwardo', 'fakeuser')
[<User('Edwardo', u'Ed Jones')>, <User('fakeuser', 'Invalid')>]

Несмотря на то что SQL запросы были выполнены в СУБД, мы все еще находимся в транзакции. Поэтому любые изменения в сессии, даже если они выполнили SQL запрос в СУБД, всегда можно отметить при помощи метода sqlalchemy.orm.session.Session.rollback().

1
2
>>> session.rollback()
[SQL]: ROLLBACK

После ROLLBACK сессия не привязана ни к одной транзакции в СУБД. Поэтому при изменении объектов в сессии создастся новая транзакция. Причем данные предыдущей сессии не были записаны.

1
2
3
4
5
6
7
8
>>> ed_user.name

[SQL]: BEGIN (implicit)
[SQL]: SELECT user.id AS user_id, user.name AS user_name, user.fullname AS user_fullname
FROM user
WHERE user.id = ?
[SQL]: (1,)
u'ed'
 1
 2
 3
 4
 5
 6
 7
 8
 9
10
>>> fake_user in session
False

>>> session.query(User).filter(User.name.in_(['ed', 'fakeuser'])).all()

[SQL]: SELECT user.id AS user_id, user.name AS user_name, user.fullname AS user_fullname
FROM user
WHERE user.name IN (?, ?)
[SQL]: ('ed', 'fakeuser')
[<User(u'ed', u'Ed Jones')>]

SQL запросы через ORM

Операции над атрибутами класса таблицы равносильны операциям над объектом sqlalchemy.schema.Column. Поэтому их можно использовать в конструкторе SQL запросов. Результатом выполнения SQL выражения будет список значений записи в БД.

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
>>> print(User.name == "ed")
"user".name = :name_1

>>> from sqlalchemy import select
>>> sel = select([User.name, User.fullname]).\
...         where(User.name == 'ed').\
...         order_by(User.id)
>>> session.connection().execute(sel).fetchall()

[SQL]: SELECT user.name, user.fullname
FROM user
WHERE user.name = ? ORDER BY user.id
[SQL]: ('ed',)
[(u'ed', u'Ed Jones')]

ORM позволяет конструировать запросы при помощи метода sqlalchemy.orm.session.Session.query(). Этот метод создает объект класса sqlalchemy.orm.query.Query, который является более высокой абстракцией конструктора SQL выражения в SQLAlchemy.

ORM, в отличии от стандартного конструктора SQL выражения, позволяет создавать запросы более наглядно и возвращать результат в виде объектов которые привязаны к сессии.

1
2
3
4
5
6
7
8
>>> query = session.query(User).filter(User.name == 'ed').order_by(User.id)
>>> query.all()

[SQL]: SELECT user.id AS user_id, user.name AS user_name, user.fullname AS user_fullname
FROM user
WHERE user.name = ? ORDER BY user.id
[SQL]: ('ed',)
[<User(u'ed', u'Ed Jones')>]

Можно также возвращать чистые значения полей, как это делают SQL выражения.

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
>>> for name, fullname in session.query(User.name, User.fullname):
...     print(name, fullname)

[SQL]: SELECT user.name AS user_name, user.fullname AS user_fullname
FROM user
[SQL]: ()
(u'ed', u'Ed Jones')
(u'wendy', u'Wendy Weathersmith')
(u'mary', u'Mary Contrary')
(u'fred', u'Fred Flinstone')

Или комбинировать значения полей с объектами.

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
>>> for row in session.query(User, User.name):
...     print(row.User, row.name)

[SQL]: SELECT user.id AS user_id, user.name AS user_name, user.fullname AS user_fullname
FROM user
[SQL]: ()
(<User(u'ed', u'Ed Jones')>, u'ed')
(<User(u'wendy', u'Wendy Weathersmith')>, u'wendy')
(<User(u'mary', u'Mary Contrary')>, u'mary')
(<User(u'fred', u'Fred Flinstone')>, u'fred')

Ограничения и условия

LIMIT, OFFSET

Выбор конкретной строки запроса делается не средствами языка Python, а на стороне СУБД, за счет конструкции LIMIT ? OFFSET ?, что значительно ускоряет выполнение запроса. Для программиста это выглядит прозрачно, как будто он работает с Python списком.

1
2
3
4
5
6
7
8
>>> u = session.query(User).order_by(User.id)[2]
>>> print(u)

[SQL]: SELECT user.id AS user_id, user.name AS user_name, user.fullname AS user_fullname
FROM user ORDER BY user.id
 LIMIT ? OFFSET ?
[SQL]: (1, 2)
<User(u'mary', u'Mary Contrary')>

Аналогично работают и Python срезы.

1
2
3
4
5
6
7
8
9
>>> for u in session.query(User).order_by(User.id)[1:3]:
...     print(u)

[SQL]: SELECT user.id AS user_id, user.name AS user_name, user.fullname AS user_fullname
FROM user ORDER BY user.id
 LIMIT ? OFFSET ?
[SQL]: (2, 1)
<User(u'wendy', u'Wendy Weathersmith')>
<User(u'mary', u'Mary Contrary')>

WHERE

Условие WHERE соответствует методу sqlalchemy.orm.query.Query.filter_by().

1
2
3
4
5
6
7
8
>>> for name, in session.query(User.name).\
...                 filter_by(fullname='Ed Jones'):
...     print(name)

[SQL]: SELECT user.name AS user_name
FROM user
WHERE user.fullname = ?
[SQL]: ('Ed Jones',)

Или более функциональному методу sqlalchemy.orm.query.Query.filter().

1
2
3
4
5
6
7
8
9
>>> for name, in session.query(User.name).\
...                 filter(User.fullname == 'Ed Jones'):
...     print(name)

[SQL]: SELECT user.name AS user_name
FROM user
WHERE user.fullname = ?
[SQL]: ('Ed Jones',)
ed
 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
>>> from sqlalchemy import or_
>>> for name, in session.query(User.name).\
...                 filter(or_(User.fullname == 'Ed Jones', User.id < 5)):
...     print(name)

[SQL]: SELECT user.name AS user_name
FROM user
WHERE user.fullname = ? OR user.id < ?
[SQL]: ('Ed Jones', 5)
ed
wendy
mary
fred

Последовательное выполнение методов sqlalchemy.orm.query.Query.filter() соединяет условия WHERE при помощи оператора AND, аналогично конструкции select().where().

1
2
3
4
5
6
7
8
9
>>> for user in session.query(User).\
...                         filter(User.name == 'ed').\
...                         filter(User.fullname == 'Ed Jones'):
...     print(user)
[SQL]: SELECT user.id AS user_id, user.name AS user_name, user.fullname AS user_fullname
FROM user
WHERE user.name = ? AND user.fullname = ?
[SQL]: ('ed', 'Ed Jones')
<User(u'ed', u'Ed Jones')>

Выполнение SQL выражений

Сам объект класса sqlalchemy.orm.query.Query не выполняет обращений к БД.

1
2
>>> query = session.query(User).filter_by(fullname='Ed Jones')
>>>

all()

Для этого существуют специальные методы этого класса, например sqlalchemy.orm.query.Query.all().

1
2
3
4
5
6
7
>>> query.all()

[SQL]: SELECT user.id AS user_id, user.name AS user_name, user.fullname AS user_fullname
FROM user
WHERE user.fullname = ?
[SQL]: ('Ed Jones',)
[<User(u'ed', u'Ed Jones')>]

first()

sqlalchemy.orm.query.Query.first() - выполнит запрос и вернет первую строку запроса или None.

1
2
3
4
5
6
7
8
>>> query.first()

[SQL]: SELECT user.id AS user_id, user.name AS user_name, user.fullname AS user_fullname
FROM user
WHERE user.fullname = ?
 LIMIT ? OFFSET ?
[SQL]: ('Ed Jones', 1, 0)
<User(u'ed', u'Ed Jones')>

one()

sqlalchemy.orm.query.Query.one() - выполнит запрос, вернет первую строку запроса и проверит что она одна и только одна, иначе вызовет исключение sqlalchemy.orm.exc.NoResultFound.

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
>>> query.one()

[SQL]: SELECT user.id AS user_id, user.name AS user_name, user.fullname AS user_fullname
FROM user
WHERE user.fullname = ?
[SQL]: ('Ed Jones',)
<User(u'ed', u'Ed Jones')>

>>>
>>> query = session.query(User).filter_by(fullname='nonexistent')
>>> query.one()

[SQL]: SELECT user.id AS user_id, user.name AS user_name, user.fullname AS user_fullname
FROM user
WHERE user.fullname = ?
[SQL]: ('nonexistent',)
Traceback (most recent call last):
  File "/home/user/.virtualenvs/lectures/local/lib/python2.7/site-packages/sliderepl/core.py", line 291, in run
    exec_(co, environ)
  File "/home/user/.virtualenvs/lectures/local/lib/python2.7/site-packages/sliderepl/compat.py", line 24, in exec_
    exec("""exec _code_ in _globs_, _locs_""")
  File "<string>", line 1, in <module>
  File "<input>", line 1, in <module>
  File "/home/user/.virtualenvs/lectures/local/lib/python2.7/site-packages/sqlalchemy/orm/query.py", line 2478, in one
    raise orm_exc.NoResultFound("No row was found for one()")
NoResultFound: No row was found for one()

Если результат запроса вернет больше строк чем одну, это тоже расценивается как ошибка sqlalchemy.orm.exc.MultipleResultsFound.

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
>>> query = session.query(User)
>>> query.one()

[SQL]: SELECT user.id AS user_id, user.name AS user_name, user.fullname AS user_fullname
FROM user
[SQL]: ()
Traceback (most recent call last):
  File "/home/uralbash/.virtualenvs/sacrud/local/lib/python2.7/site-packages/sliderepl/core.py", line 291, in run
    exec_(co, environ)
  File "/home/uralbash/.virtualenvs/sacrud/local/lib/python2.7/site-packages/sliderepl/compat.py", line 24, in exec_
    exec("""exec _code_ in _globs_, _locs_""")
  File "<string>", line 1, in <module>
  File "<input>", line 1, in <module>
  File "/home/uralbash/.virtualenvs/sacrud/local/lib/python2.7/site-packages/sqlalchemy/orm/query.py", line 2481, in one
    "Multiple rows were found for one()")
MultipleResultsFound: Multiple rows were found for one()

Связи между таблиц

См.также

Новый класс Address имеет связь Many-To-One с таблицей User. Связь между Python классов осуществляется при помощи функции sqlalchemy.orm.relationship().

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
>>> from sqlalchemy import ForeignKey
>>> from sqlalchemy.orm import relationship

>>> class Address(Base):
...     __tablename__ = 'address'

...     id = Column(Integer, primary_key=True)
...     email_address = Column(String, nullable=False)
...     user_id = Column(Integer, ForeignKey('user.id'))

...     user = relationship("User", backref="addresses")

...     def __repr__(self):
...         return "<Address(%r)>" % self.email_address
 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
>>> Base.metadata.create_all(engine)

[SQL]: PRAGMA table_info("user")
[SQL]: ()
[SQL]: PRAGMA table_info("address")
[SQL]: ()
[SQL]:
CREATE TABLE address (
    id INTEGER NOT NULL,
    email_address VARCHAR NOT NULL,
    user_id INTEGER,
    PRIMARY KEY (id),
    FOREIGN KEY(user_id) REFERENCES user (id)
)

[SQL]: ()
[SQL]: COMMIT

Благодаря параметру backref, класс User получает обратную ссылку на класс Adress.

1
2
3
>>> jack = User(name='jack', fullname='Jack Bean')
>>> jack.addresses
[]

Добавим пользователю адреса.

1
2
3
4
5
>>> jack.addresses = [
...                 Address(email_address='jack@gmail.com'),
...                 Address(email_address='j25@yahoo.com'),
...                 Address(email_address='jack@hotmail.com'),
...                 ]

sqlalchemy.orm.backref() добавляет ссылки друг на друга для каждого объекта.

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
>>> jack
<User('jack', 'Jack Bean')>

>>> jack.addresses[1]
<Address('j25@yahoo.com')>

>>> jack.addresses[1].user
<User('jack', 'Jack Bean')>

>>> jack.addresses[1].user.addresses[1]
<Address('j25@yahoo.com')>

>>> jack.addresses[1].user.addresses[1].user
<User('jack', 'Jack Bean')>

>>> jack.addresses[1].user.addresses[1].user.addresses[1].user.addresses[1].user
<User('jack', 'Jack Bean')>

>>> jack.addresses[1].user.addresses[1].user.addresses[2].user.addresses[0].user
<User('jack', 'Jack Bean')>

Добавление в сессию объекта, который ссылается на другие объекты, автоматически включает их тоже.

1
2
3
4
5
6
>>> session.add(jack)
>>> session.new
IdentitySet([<Address('jack@hotmail.com')>,
             <Address('jack@gmail.com')>,
             <User('jack', 'Jack Bean')>,
             <Address('j25@yahoo.com')>])
 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
>>> session.commit()

[SQL]: INSERT INTO user (name, fullname) VALUES (?, ?)
[SQL]: ('jack', 'Jack Bean')
[SQL]: INSERT INTO address (email_address, user_id) VALUES (?, ?)
[SQL]: ('jack@gmail.com', 5)
[SQL]: INSERT INTO address (email_address, user_id) VALUES (?, ?)
[SQL]: ('j25@yahoo.com', 5)
[SQL]: INSERT INTO address (email_address, user_id) VALUES (?, ?)
[SQL]: ('jack@hotmail.com', 5)
[SQL]: COMMIT

После подтверждения транзакции (COMMIT), обращение по ссылке создаст новую транзакцию и считает значения из БД.

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
>>> jack.addresses

[SQL]: BEGIN (implicit)
[SQL]: SELECT user.id AS user_id, user.name AS user_name, user.fullname AS user_fullname
FROM user
WHERE user.id = ?
[SQL]: (5,)
[SQL]: SELECT address.id AS address_id, address.email_address AS address_email_address, address.user_id AS address_user_id
FROM address
WHERE ? = address.user_id
[SQL]: (5,)
[<Address(u'jack@gmail.com')>, <Address(u'j25@yahoo.com')>, <Address(u'jack@hotmail.com')>]

Теперь, считанные объекты находятся в памяти, до тех пор пока мы опять не подтвердим транзакцию (COMMIT) или отменим ее (ROLLBACK).

1
2
>>> jack.addresses
[<Address(u'jack@gmail.com')>, <Address(u'j25@yahoo.com')>, <Address(u'jack@hotmail.com')>]

Привяжем адрес к другому пользователю.

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
>>> fred = session.query(User).filter_by(name='fred').one()
>>> jack.addresses[1].user = fred
>>> fred.addresses
>>> session.commit()

[SQL]: SELECT user.id AS user_id, user.name AS user_name, user.fullname AS user_fullname
FROM user
WHERE user.name = ?
[SQL]: ('fred',)
[SQL]: UPDATE address SET user_id=? WHERE address.id = ?
[SQL]: (4, 2)
[SQL]: SELECT address.id AS address_id, address.email_address AS address_email_address, address.user_id AS address_user_id
FROM address
WHERE ? = address.user_id
[SQL]: (4,)
[<Address(u'j25@yahoo.com')>]
[SQL]: COMMIT

Выполнение операции implicit JOIN.

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
>>> session.query(User, Address).filter(User.id == Address.user_id).all()

[SQL]: SELECT user.id AS user_id, user.name AS user_name, user.fullname AS
user_fullname, address.id AS address_id, address.email_address AS
address_email_address, address.user_id AS address_user_id FROM user,
address
WHERE user.id = address.user_id
[SQL]: ()
[(<User(u'jack', u'Jack Bean')>, <Address(u'jack@gmail.com')>),
(<User(u'fred', u'Fred Flinstone')>, <Address(u'j25@yahoo.com')>),
(<User(u'jack', u'Jack Bean')>, <Address(u'jack@hotmail.com')>)]

Явный JOIN.

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
>>> session.query(User, Address).join(Address, User.id == Address.user_id).all()

[SQL]: SELECT user.id AS user_id, user.name AS user_name, user.fullname AS
user_fullname, address.id AS address_id, address.email_address AS
address_email_address, address.user_id AS address_user_id FROM user JOIN
address ON user.id = address.user_id
[SQL]: ()
[(<User(u'jack', u'Jack Bean')>, <Address(u'jack@gmail.com')>),
(<User(u'fred', u'Fred Flinstone')>, <Address(u'j25@yahoo.com')>),
(<User(u'jack', u'Jack Bean')>, <Address(u'jack@hotmail.com')>)]

Более краткий и понятный способ использовать ссылку на таблицу для связи.

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
>>> session.query(User, Address).join(User.addresses).all()

[SQL]: SELECT user.id AS user_id, user.name AS user_name, user.fullname AS
user_fullname, address.id AS address_id, address.email_address AS
address_email_address, address.user_id AS address_user_id FROM user JOIN
address ON user.id = address.user_id
[SQL]: ()
[(<User(u'jack', u'Jack Bean')>, <Address(u'jack@gmail.com')>),
(<User(u'fred', u'Fred Flinstone')>, <Address(u'j25@yahoo.com')>),
(<User(u'jack', u'Jack Bean')>, <Address(u'jack@hotmail.com')>)]

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

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
>>> session.query(User, Address).join(Address).all()

[SQL]: SELECT user.id AS user_id, user.name AS user_name, user.fullname AS
user_fullname, address.id AS address_id, address.email_address AS
address_email_address, address.user_id AS address_user_id FROM user JOIN
address ON user.id = address.user_id
[SQL]: ()
[(<User(u'jack', u'Jack Bean')>, <Address(u'jack@gmail.com')>),
(<User(u'fred', u'Fred Flinstone')>, <Address(u'j25@yahoo.com')>),
(<User(u'jack', u'Jack Bean')>, <Address(u'jack@hotmail.com')>)]

JOIN с условием WHERE.

1
2
3
4
5
6
7
8
9
>>> session.query(User.name).join(User.addresses).\
...     filter(Address.email_address == 'jack@gmail.com').first()

[SQL]: SELECT user.name AS user_name
FROM user JOIN address ON user.id = address.user_id
WHERE address.email_address = ?
 LIMIT ? OFFSET ?
[SQL]: ('jack@gmail.com', 1, 0)
(u'jack',)

Явный вызов конструкции SELECT FROM JOIN используя метод sqlalchemy.orm.query.Query.select_from().

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
>>> session.query(User, Address).select_from(Address).join(Address.user).all()

[SQL]: SELECT user.id AS user_id, user.name AS user_name, user.fullname AS
user_fullname, address.id AS address_id, address.email_address AS
address_email_address, address.user_id AS address_user_id FROM address
JOIN user ON user.id = address.user_id
[SQL]: ()
[(<User(u'jack', u'Jack Bean')>, <Address(u'jack@gmail.com')>),
(<User(u'fred', u'Fred Flinstone')>, <Address(u'j25@yahoo.com')>),
(<User(u'jack', u'Jack Bean')>, <Address(u'jack@hotmail.com')>)]

Запросы ссылающиеся на одну сущность более чем один раз, нуждаются в алиасах. Алиасы задаются при помощи функции sqlalchemy.orm.aliased().

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
>>> from sqlalchemy.orm import aliased
>>> a1, a2 = aliased(Address), aliased(Address)
>>> session.query(User).\
...         join(a1).\
...         join(a2).\
...         filter(a1.email_address == 'jack@gmail.com').\
...         filter(a2.email_address == 'jack@hotmail.com').\
...         all()

[SQL]: SELECT user.id AS user_id, user.name AS user_name, user.fullname AS user_fullname
FROM user JOIN address AS address_1 ON user.id = address_1.user_id JOIN
address AS address_2 ON user.id = address_2.user_id WHERE
address_1.email_address = ? AND address_2.email_address = ?
[SQL]: ('jack@gmail.com', 'jack@hotmail.com')
[<User(u'jack', u'Jack Bean')>]

Подзапросы автоматически использую алиасы.

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
>>> from sqlalchemy import func
>>> subq = session.query(
...                 func.count(Address.id).label('count'),
...                 User.id.label('user_id')
...                 ).\
...                 join(Address.user).\
...                 group_by(User.id).\
...                 subquery()
>>> session.query(User.name, func.coalesce(subq.c.count, 0)).\
...             outerjoin(subq, User.id == subq.c.user_id).all()

[SQL]: SELECT user.name AS user_name, coalesce(anon_1.count, ?) AS coalesce_1
FROM user LEFT OUTER JOIN (SELECT count(address.id) AS count, user.id AS user_id
FROM address JOIN user ON user.id = address.user_id GROUP BY user.id) AS anon_1
ON user.id = anon_1.user_id [SQL]: (0,)
[(u'ed', 0), (u'wendy', 0), (u'mary', 0), (u'fred', 1), (u'jack', 2)]

При каждом обращении к ссылкам объекта в цикле, вызывается новый запрос:

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
>>> for user in session.query(User):
...     print(user, user.addresses)

[SQL]: SELECT user.id AS user_id, user.name AS user_name, user.fullname AS
user_fullname FROM user
[SQL]: ()
[SQL]: SELECT address.id AS address_id, address.email_address AS
address_email_address, address.user_id AS address_user_id FROM address
WHERE ? = address.user_id
[SQL]: (1,)
(<User(u'ed', u'Ed Jones')>, [])
[SQL]: SELECT address.id AS address_id, address.email_address AS
address_email_address, address.user_id AS address_user_id FROM address
WHERE ? = address.user_id
[SQL]: (2,)
(<User(u'wendy', u'Wendy Weathersmith')>, [])
[SQL]: SELECT address.id AS address_id, address.email_address AS
address_email_address, address.user_id AS address_user_id FROM address
WHERE ? = address.user_id
[SQL]: (3,)
(<User(u'mary', u'Mary Contrary')>, [])
[SQL]: SELECT address.id AS address_id, address.email_address AS
address_email_address, address.user_id AS address_user_id FROM address
WHERE ? = address.user_id
[SQL]: (4,)
(<User(u'fred', u'Fred Flinstone')>, [<Address(u'j25@yahoo.com')>])
(<User(u'jack', u'Jack Bean')>, [<Address(u'jack@gmail.com')>,
<Address(u'jack@hotmail.com')>])

Чтобы этого избежать нужно использовать опцию предварительной загрузки sqlalchemy.orm.subqueryload().

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
>>> session.rollback()  # so we can see the load happen again.
>>> from sqlalchemy.orm import subqueryload
>>> for user in session.query(User).options(subqueryload(User.addresses)):
...     print(user, user.addresses)

[SQL]: ROLLBACK
[SQL]: BEGIN (implicit)
[SQL]: SELECT user.id AS user_id, user.name AS user_name, user.fullname AS user_fullname
FROM user
[SQL]: ()
[SQL]: SELECT address.id AS address_id, address.email_address AS
address_email_address, address.user_id AS address_user_id, anon_1.user_id
AS anon_1_user_id FROM (SELECT user.id AS user_id
FROM user) AS anon_1 JOIN address ON anon_1.user_id = address.user_id ORDER BY anon_1.user_id
[SQL]: ()
(<User(u'ed', u'Ed Jones')>, [])
(<User(u'wendy', u'Wendy Weathersmith')>, [])
(<User(u'mary', u'Mary Contrary')>, [])
(<User(u'fred', u'Fred Flinstone')>, [<Address(u'j25@yahoo.com')>])
(<User(u'jack', u'Jack Bean')>, [<Address(u'jack@gmail.com')>, <Address(u'jack@hotmail.com')>])

Или sqlalchemy.orm.joinedload() чтобы уместить все в один запрос.

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
>>> session.rollback()
>>> from sqlalchemy.orm import joinedload
>>> for user in session.query(User).options(joinedload(User.addresses)):
...     print(user, user.addresses)

[SQL]: ROLLBACK
[SQL]: BEGIN (implicit)
[SQL]: SELECT user.id AS user_id, user.name AS user_name, user.fullname AS
user_fullname, address_1.id AS address_1_id, address_1.email_address AS
address_1_email_address, address_1.user_id AS address_1_user_id FROM user
LEFT OUTER JOIN address AS address_1 ON user.id = address_1.user_id
[SQL]: ()
(<User(u'ed', u'Ed Jones')>, [])
(<User(u'wendy', u'Wendy Weathersmith')>, [])
(<User(u'mary', u'Mary Contrary')>, [])
(<User(u'fred', u'Fred Flinstone')>, [<Address(u'j25@yahoo.com')>])
(<User(u'jack', u'Jack Bean')>, [<Address(u'jack@gmail.com')>, <Address(u'jack@hotmail.com')>])

Удаление адреса из списка пользователя User.addresses, поменяет значение поля FOREIGN KEY на NULL, но не удалит саму запись.

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
>>> jack = session.query(User).filter_by(name='jack').one()
>>> del jack.addresses[0]
>>> session.commit()

[SQL]: SELECT user.id AS user_id, user.name AS user_name, user.fullname AS user_fullname
FROM user
WHERE user.name = ?
[SQL]: ('jack',)
[SQL]: UPDATE address SET user_id=? WHERE address.id = ?
[SQL]: (None, 1)
[SQL]: COMMIT

Мы можем настроить связи между таблицами на каскадное удаление.

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
>>> User.addresses.property.cascade = "all, delete, delete-orphan"

>>> fred = session.query(User).filter_by(name='fred').one()
>>> del fred.addresses[0]
>>> session.commit()

[SQL]: BEGIN (implicit)
[SQL]: SELECT user.id AS user_id, user.name AS user_name, user.fullname AS user_fullname
FROM user
WHERE user.name = ?
[SQL]: ('fred',)
[SQL]: SELECT address.id AS address_id, address.email_address AS
address_email_address, address.user_id AS address_user_id FROM address
WHERE ? = address.user_id
[SQL]: (4,)
[SQL]: DELETE FROM address WHERE address.id = ?
[SQL]: (2,)
[SQL]: COMMIT

delete-orphan означает что дети не могут существовать без родителей. Поэтому при удалении родителя вся связанные с ним записи тоже удалятся.

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
>>> session.delete(jack)
>>> session.commit()

[SQL]: BEGIN (implicit)
[SQL]: SELECT user.id AS user_id, user.name AS user_name, user.fullname AS user_fullname
FROM user
WHERE user.id = ?
[SQL]: (5,)
[SQL]: SELECT address.id AS address_id, address.email_address AS address_email_address, address.user_id AS address_user_id
FROM address
WHERE ? = address.user_id
[SQL]: (5,)
[SQL]: DELETE FROM address WHERE address.id = ?
[SQL]: (3,)
[SQL]: DELETE FROM user WHERE user.id = ?
[SQL]: (5,)
[SQL]: COMMIT

Полный пример

2.sqlalchemy/4.orm.py
  1
  2
  3
  4
  5
  6
  7
  8
  9
 10
 11
 12
 13
 14
 15
 16
 17
 18
 19
 20
 21
 22
 23
 24
 25
 26
 27
 28
 29
 30
 31
 32
 33
 34
 35
 36
 37
 38
 39
 40
 41
 42
 43
 44
 45
 46
 47
 48
 49
 50
 51
 52
 53
 54
 55
 56
 57
 58
 59
 60
 61
 62
 63
 64
 65
 66
 67
 68
 69
 70
 71
 72
 73
 74
 75
 76
 77
 78
 79
 80
 81
 82
 83
 84
 85
 86
 87
 88
 89
 90
 91
 92
 93
 94
 95
 96
 97
 98
 99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
251
252
253
254
255
256
257
258
259
260
261
262
263
264
265
266
267
268
269
270
271
272
273
274
275
276
277
278
279
280
281
282
283
284
285
286
287
288
289
290
291
292
293
294
295
296
297
298
299
300
301
302
303
304
305
306
307
308
309
310
311
312
313
314
315
316
317
318
319
320
321
322
323
324
325
326
327
328
329
330
331
332
333
334
335
336
337
338
339
340
341
342
343
344
345
346
347
348
349
350
351
352
353
354
355
356
357
358
359
360
361
362
363
364
365
366
367
368
369
370
371
372
373
374
375
376
377
378
379
380
381
382
383
384
385
386
387
388
389
390
391
392
393
394
395
396
397
398
399
400
401
402
403
404
405
406
407
408
409
410
411
412
413
414
415
416
417
418
419
420
421
422
423
424
425
426
427
428
429
430
431
432
433
434
435
436
437
438
439
440
441
442
443
444
445
446
447
448
449
450
451
452
453
454
455
456
457
458
459
460
461
462
463
464
465
466
467
468
469
470
471
472
473
474
475
476
477
478
479
480
481
482
483
484
485
486
487
488
489
490
491
492
493
494
495
496
497
498
499
500
501
502
503
504
505
506
507
508
509
510
511
512
513
514
515
516
517
518
519
520
521
522
523
524
525
526
527
528
529
530
531
532
533
534
535
536
537
538
539
540
541
542
543
544
545
546
547
548
549
550
551
552
553
554
555
556
557
558
559
560
561
# ## slide::
# ## title:: Object Relational Mapping
# The *declarative* system is normally used to configure
# object relational mappings.

from sqlalchemy.ext.declarative import declarative_base
Base = declarative_base()

# ## slide::
# a basic mapping.  __repr__() is optional.

from sqlalchemy import Column, Integer, String


class User(Base):
    __tablename__ = 'user'

    id = Column(Integer, primary_key=True)
    name = Column(String)
    fullname = Column(String)

    def __repr__(self):
        return "<User(%r, %r)>" % (self.name, self.fullname)

# ## slide::
# the User class now has a Table object associated with it.

User.__table__

# ## slide::
# The Mapper object mediates the relationship between User
# and the "user" Table object.

User.__mapper__

# ## slide::
# User has a default constructor, accepting field names
# as arguments.

ed_user = User(name='ed', fullname='Edward Jones')

# ## slide::
# The "id" field is the primary key, which starts as None
# if we didn't set it explicitly.

print(ed_user.name, ed_user.fullname)
print(ed_user.id)

# ## slide:: p
# The MetaData object is here too, available from the Base.

from sqlalchemy import create_engine
engine = create_engine('sqlite://')
Base.metadata.create_all(engine)

# ## slide::
# To persist and load User objects from the database, we
# use a Session object.

from sqlalchemy.orm import Session
session = Session(bind=engine)

# ## slide::
# new objects are placed into the Session using add().
session.add(ed_user)

# ## slide:: pi
# the Session will *flush* *pending* objects
# to the database before each Query.

our_user = session.query(User).filter_by(name='ed').first()
our_user

# ## slide::
# the User object we've inserted now has a value for ".id"
print(ed_user.id)

# ## slide::
# the Session maintains a *unique* object per identity.
# so "ed_user" and "our_user" are the *same* object

ed_user is our_user

# ## slide::
# Add more objects to be pending for flush.

session.add_all([User(name='wendy',
                      fullname='Wendy Weathersmith'),
                 User(name='mary',
                      fullname='Mary Contrary'),
                 User(name='fred',
                      fullname='Fred Flinstone')])

# ## slide::
# modify "ed_user" - the object is now marked as *dirty*.

ed_user.fullname = 'Ed Jones'

# ## slide::
# the Session can tell us which objects are dirty...

session.dirty

# ## slide::
# and can also tell us which objects are pending...

session.new

# ## slide:: p i
# The whole transaction is committed.  Commit always triggers
# a final flush of remaining changes.

session.commit()

# ## slide:: p
# After a commit, theres no transaction.  The Session
# *invalidates* all data, so that accessing them will automatically
# start a *new* transaction and re-load from the database.

ed_user.fullname

# ## slide::
# Make another "dirty" change, and another "pending" change,
# that we might change our minds about.

ed_user.name = 'Edwardo'
fake_user = User(name='fakeuser', fullname='Invalid')
session.add(fake_user)

# ## slide:: p
# run a query, our changes are flushed; results come back.

session.query(User).filter(User.name.in_(['Edwardo', 'fakeuser'])).all()

# ## slide::
# But we're inside of a transaction.  Roll it back.
session.rollback()

# ## slide:: p
# ed_user's name is back to normal
ed_user.name

# ## slide::
# "fake_user" has been evicted from the session.
fake_user in session

# ## slide:: p
# and the data is gone from the database too.

session.query(User).filter(User.name.in_(['ed', 'fakeuser'])).all()

# ## slide::
# ## title:: Exercises - Basic Mapping
#
# 1. Create a class/mapping for this table, call the class Network
#
# CREATE TABLE network (
#      network_id INTEGER PRIMARY KEY,
#      name VARCHAR(100) NOT NULL,
# )
#
# 2. emit Base.metadata.create_all(engine) to create the table
#
# 3. commit a few Network objects to the database:
#
# Network(name='net1'), Network(name='net2')
#
#

# ## slide::
# ## title:: ORM Querying
# The attributes on our mapped class act like Column objects, and
# produce SQL expressions.

print(User.name == "ed")

# ## slide:: p
# These SQL expressions are compatible with the select() object
# we introduced earlier.

from sqlalchemy import select

sel = select([User.name, User.fullname]).\
    where(User.name == 'ed').\
    order_by(User.id)

session.connection().execute(sel).fetchall()

# ## slide:: p
# but when using the ORM, the Query() object provides a lot more functionality,
# here selecting the User *entity*.

query = session.query(User).filter(User.name == 'ed').order_by(User.id)

query.all()

# ## slide:: p
# Query can also return individual columns

for name, fullname in session.query(User.name, User.fullname):
    print(name, fullname)

# ## slide:: p
# and can mix entities / columns together.

for row in session.query(User, User.name):
    print(row.User, row.name)

# ## slide:: p
# Array indexes will OFFSET to that index and LIMIT by one...

u = session.query(User).order_by(User.id)[2]
print(u)

# ## slide:: pi
# and array slices work too.

for u in session.query(User).order_by(User.id)[1:3]:
    print(u)

# ## slide:: p
# the WHERE clause is either by filter_by(), which is convenient

for name, in session.query(User.name).\
        filter_by(fullname='Ed Jones'):
    print(name)

# ## slide:: p
# or filter(), which is more flexible

for name, in session.query(User.name).\
        filter(User.fullname == 'Ed Jones'):
    print(name)

# ## slide:: p
# conjunctions can be passed to filter() as well

from sqlalchemy import or_

for name, in session.query(User.name).\
        filter(or_(User.fullname == 'Ed Jones', User.id < 5)):
    print(name)

# ## slide::
# multiple filter() calls join by AND just like select().where()

for user in session.query(User).\
        filter(User.name == 'ed').\
        filter(User.fullname == 'Ed Jones'):
    print(user)

# ## slide::
# Query has some variety for returning results

query = session.query(User).filter_by(fullname='Ed Jones')

# ## slide:: p
# all() returns a list

query.all()

# ## slide:: p
# first() returns the first row, or None

query.first()

# ## slide:: p
# one() returns the first row and verifies that there's one and only one

query.one()

# ## slide:: p
# if there's not one(), you get an error

query = session.query(User).filter_by(fullname='nonexistent')
query.one()

# ## slide:: p
# if there's more than one(), you get an error

query = session.query(User)
query.one()

# ## slide::
# ## title:: Exercises - ORM Querying
# 1. Produce a Query object representing the list of "fullname" values for
#    all User objects in alphabetical order.
#
# 2. call .all() on the query to make sure it works!
#
# 3. build a second Query object from the first that also selects
#    only User rows with the name "mary" or "ed".
#
# 4. return only the second row of the Query from #3.

# ## slide::
# ## title:: Joins and relationships
# A new class called Address, with a *many-to-one* relationship to User.

from sqlalchemy import ForeignKey
from sqlalchemy.orm import relationship


class Address(Base):
    __tablename__ = 'address'

    id = Column(Integer, primary_key=True)
    email_address = Column(String, nullable=False)
    user_id = Column(Integer, ForeignKey('user.id'))

    user = relationship("User", backref="addresses")

    def __repr__(self):
        return "<Address(%r)>" % self.email_address

# ## slide:: p
# create the new table.

Base.metadata.create_all(engine)

# ## slide::
# a new User object also gains an empty "addresses" collection now.

jack = User(name='jack', fullname='Jack Bean')
jack.addresses

# ## slide::
# populate this collection with new Address objects.

jack.addresses = [Address(email_address='jack@gmail.com'),
                  Address(email_address='j25@yahoo.com'),
                  Address(email_address='jack@hotmail.com'), ]

# ## slide::
# the "backref" sets up Address.user for each User.address.

jack.addresses[1]
jack.addresses[1].user

# ## slide::
# adding User->jack will *cascade* each Address into the Session as well.

session.add(jack)
session.new

# ## slide:: p
# commit.
session.commit()

# ## slide:: p
# After expiration, jack.addresses emits a *lazy load* when first
# accessed.
jack.addresses

# ## slide:: i
# the collection stays in memory until the transaction ends.
jack.addresses

# ## slide:: p
# collections and references are updated by manipulating objects,
# not primary / foreign key values.

fred = session.query(User).filter_by(name='fred').one()
jack.addresses[1].user = fred

fred.addresses

session.commit()

# ## slide:: p
# Query can select from multiple tables at once.
# Below is an *implicit join*.

session.query(User, Address).filter(User.id == Address.user_id).all()

# ## slide:: p
# join() is used to create an explicit JOIN.

session.query(User, Address).join(Address, User.id == Address.user_id).all()

# ## slide:: p
# The most succinct and accurate way to join() is to use the
# the relationship()-bound attribute to specify ON.

session.query(User, Address).join(User.addresses).all()

# ## slide:: p
# join() will also figure out very simple joins just using entities.

session.query(User, Address).join(Address).all()

# ## slide:: p
# Either User or Address may be referred to anywhere in the query.

session.query(User.name).join(User.addresses).\
    filter(Address.email_address == 'jack@gmail.com').first()

# ## slide:: p
# we can specify an explicit FROM using select_from().

session.query(User, Address).select_from(Address).join(Address.user).all()

# ## slide:: p
# A query that refers to the same entity more than once in the FROM
# clause requires *aliasing*.

from sqlalchemy.orm import aliased

a1, a2 = aliased(Address), aliased(Address)
session.query(User).\
    join(a1).\
    join(a2).\
    filter(a1.email_address == 'jack@gmail.com').\
    filter(a2.email_address == 'jack@hotmail.com').\
    all()

# ## slide:: p
# We can also join with subqueries.  subquery() returns
# an "alias" construct for us to use.

from sqlalchemy import func

subq = session.query(
    func.count(Address.id).label('count'),
    User.id.label('user_id')
).\
    join(Address.user).\
    group_by(User.id).\
    subquery()

session.query(User.name, func.coalesce(subq.c.count, 0)).\
    outerjoin(subq, User.id == subq.c.user_id).all()

# ## slide::
# ## title:: Exercises
# 1. Run this SQL JOIN:
#
#    SELECT user.name, address.email_address FROM user
#    JOIN address ON user.id=address.user_id WHERE
#    address.email_address='j25@yahoo.com'
#
# 2. Tricky Bonus!  Select all pairs of distinct user names.
#    Hint: "... ON user_alias1.name < user_alias2.name"
#

# ## slide:: p
# ## title:: Eager Loading
# the "N plus one" problem refers to the many SELECT statements
# emitted when loading collections against a parent result

for user in session.query(User):
    print(user, user.addresses)

# ## slide:: p
# *eager loading* solves this problem by loading *all* collections
# at once.

session.rollback()  # so we can see the load happen again.

from sqlalchemy.orm import subqueryload

for user in session.query(User).options(subqueryload(User.addresses)):
    print(user, user.addresses)

# ## slide:: p
# joinedload() uses a LEFT OUTER JOIN to load parent + child in one query.

session.rollback()

from sqlalchemy.orm import joinedload

for user in session.query(User).options(joinedload(User.addresses)):
    print(user, user.addresses)

# ## slide:: p
# eager loading *does not* change the *result* of the Query.
# only how related collections are loaded.

for address in session.query(Address).\
        join(Address.user).\
        filter(User.name == 'jack').\
        options(joinedload(Address.user)):
    print(address, address.user)

# ## slide:: p
# to join() *and* joinedload() at the same time without using two
# JOIN clauses, use contains_eager()

from sqlalchemy.orm import contains_eager

for address in session.query(Address).\
        join(Address.user).\
        filter(User.name == 'jack').\
        options(contains_eager(Address.user)):
    print(address, address.user)

# ## slide:: p
# ## title:: Delete Cascades
# removing an Address sets its foreign key to NULL.
# We'd prefer it gets deleted.

jack = session.query(User).filter_by(name='jack').one()

del jack.addresses[0]
session.commit()

# ## slide::
# This can be configured on relationship() using
# "delete-orphan" cascade on the User->Address
# relationship.

User.addresses.property.cascade = "all, delete, delete-orphan"

# ## slide:: p
# Removing an Address from a User will now delete it.

fred = session.query(User).filter_by(name='fred').one()

del fred.addresses[0]
session.commit()

# ## slide:: p
# Deleting the User will also delete all Address objects.

session.delete(jack)
session.commit()

# ## slide::
# ## title:: Exercises - Final Exam !
# 1. Create a class called 'Account', with table "account":
#
#      id = Column(Integer, primary_key=True)
#      owner = Column(String(50), nullable=False)
#      balance = Column(Numeric, default=0)
#
# 2. Create a class "Transaction", with table "transaction":
#      * Integer primary key
#      * numeric "amount" column
#      * Integer "account_id" column with ForeignKey('account.id')
#
# 3. Add a relationship() on Transaction named "account", which refers
#    to "Account", and has a backref called "transactions".
#
# 4. Create a database, create tables, then insert these objects:
#
#      a1 = Account(owner='Jack Jones', balance=5000)
#      a2 = Account(owner='Ed Rendell', balance=10000)
#      Transaction(amount=500, account=a1)
#      Transaction(amount=4500, account=a1)
#      Transaction(amount=6000, account=a2)
#      Transaction(amount=4000, account=a2)
#
# 5. Produce a report that shows:
#     * account owner
#     * account balance
#     * summation of transaction amounts per account (should match balance)
#       A column can be summed using func.sum(Transaction.amount)
#
# from sqlalchemy import Integer, String, Numeric

# ## slide::
Previous: SQL выражения Next: Пагинация