CRUD-операции в SQLAlchemy ORM

Создание сессии

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

Для создания объекта session можно использовать класс Session из sqlalchemy.orm.

Копировать 
from sqlalchemy import create_engine
from sqlalchemy.orm import Session

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

Создавать объект Session нужно будет каждый раз при взаимодействии с базой.

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

Чтобы упростить этот процесс, SQLAlchemy предоставляет класс sessionmaker, который создает класс Session с аргументами для конструктора по умолчанию.

Копировать 
from sqlalchemy.orm import Session, sessionmaker

session = sessionmaker(bind=engine)

Нужно просто вызвать sessionmaker один раз в глобальной области видимости.

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

session = Session()

Обратите внимание на то, что объект Session не сразу устанавливает соединение с базой данных. Это происходит лишь при первом запросе.

Вставка(добавление) данных

Для создания новой записи с помощью SQLAlchemy ORM нужно выполнить следующие шаги:

  1. Создать объект
  2. Добавить его в сессию
  3. Сохранить сессию

Создадим два новых объекта Customer:

Копировать 
c1 = Customer(
    first_name = 'Dmitriy',
    last_name = 'Yatsenko',
    username = 'Moseend',
    email = 'moseend@mail.com'
)

c2 = Customer(
    first_name = 'Valeriy',
    last_name = 'Golyshkin',
    username = 'Fortioneaks',
    email = 'fortioneaks@gmail.com'
)

print(c1.first_name, c2.last_name)

session.add(c1)
session.add(c2)

print(session.new)

session.commit()

Первый вывод: Dmitriy Golyshkin.

Два объекта созданы. Получить доступ к их атрибутам можно с помощью оператора точки (.).

Дальше в сессию добавляются объекты.

session.add(c1)
session.add(c2)

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

Значение атрибута id обоих объектов — None. Это значит, что они еще не сохранены в базе данных.

Вместо добавления одного объекта за раз можно использовать метод add_all(). Он принимает список объектов, которые будут добавлены в сессию.

session.add_all([c1, c2])

Добавление объекта в сессию несколько раз не приводит к ошибкам. В любой момент на имеющиеся объекты можно посмотреть с помощью session.new.

IdentitySet([<__main__.Customer object at 0x000001BD25928C40>, <__main__.Customer object at 0x000001BD25928C70>])

Наконец, для сохранения данных используется метод commit():

session.commit()

После сохранения транзакции ресурсы соединения, на которые ссылается объект Session, возвращаются в пул соединений. Последующие операции будут выполняться в новой транзакции.

Сейчас таблица Customer выглядит вот так:

таблица Customer

Пока что покупатели ничего не приобрели. Поэтому c1.orders и c2.orders вернут пустой список.

[] []

Добавим еще потребителей в таблицу customers:

Копировать 
from sqlalchemy import create_engine
from sqlalchemy.orm import Session, sessionmaker

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

c3 = Customer(
    first_name = "Vadim", 
    last_name = "Moiseenko", 
    username = "Antence73", 
    email = "antence73@mail.com",
)

c4 = Customer(          
    first_name = "Vladimir", 
    last_name = "Belousov", 
    username = "Andescols", 
    email = "andescols@mail.com",       
)

c5 = Customer(
    first_name = "Tatyana", 
    last_name = "Khakimova", 
    username = "Caltin1962", 
    email = "caltin1962@mail.com",
)

c6 = Customer(
    first_name = "Pavel", 
    last_name = "Arnautov", 
    username = "Lablen", 
    email = "lablen@mail.com",
)

session.add_all([c3, c4, c5, c6])
session.commit()

Также добавим продукты в таблицу items:

Копировать 
i1 = Item(name = 'Chair', cost_price = 9.21, selling_price = 10.81, quantity = 5)
i2 = Item(name = 'Pen', cost_price = 3.45, selling_price = 4.51, quantity = 3)
i3 = Item(name = 'Headphone', cost_price = 15.52, selling_price = 16.81, quantity = 50)
i4 = Item(name = 'Travel Bag', cost_price = 20.1, selling_price = 24.21, quantity = 50)
i5 = Item(name = 'Keyboard', cost_price = 20.1, selling_price = 22.11, quantity = 50)
i6 = Item(name = 'Monitor', cost_price = 200.14, selling_price = 212.89, quantity = 50)
i7 = Item(name = 'Watch', cost_price = 100.58, selling_price = 104.41, quantity = 50)
i8 = Item(name = 'Water Bottle', cost_price = 20.89, selling_price = 25, quantity = 50)

session.add_all([i1, i2, i3, i4, i5, i6, i7, i8])
session.commit()

Создадим заказы:

Копировать 








o1 = Order(customer = c1)
o2 = Order(customer = c1)

line_item1 = OrderLine(order = o1, item = i1, quantity =  3)
line_item2 = OrderLine(order = o1, item = i2, quantity =  2)
line_item3 = OrderLine(order = o2, item = i1, quantity =  1)
line_item3 = OrderLine(order = o2, item = i2, quantity =  4)

session.add_all([o1, o2])

session.new
session.commit()

В данном случае в сессию добавляются только объекты Order (o1 и o2). Order и OrderLine связаны отношением один-ко-многим. Добавление объекта Order в сессию неявно добавляет также и объекты OrderLine. Но даже если добавить последние вручную, ошибки не будет.

Вместо передачи объекта Order при создании экземпляра OrderLine можно сделать следующее:

Копировать 
o3 = Order(customer = c1)
orderline1 = OrderLine(item = i1, quantity = 5)
orderline2 = OrderLine(item = i2, quantity = 10)

o3.line_items.append(orderline1)
o3.line_items.append(orderline2)

session.add_all([o3,])
session.new
session.commit()

После коммита таблицы orders и order_lines будут выглядеть вот так:

таблицы orders и order_lines

Если сейчас получить доступ к атрибуту orders объекта Customer, то вернется не-пустой список.

[<Order:1>, <Order:2>]

С другой стороны отношения можно получить доступ к объекту Customer, которому заказ принадлежит через атрибут customer объекта Order — o1.customer.

Сейчас у покупателя c1 три заказа. Чтобы посмотреть все пункты в заказе нужно использовать атрибут line_items объекта Order.

c1.orders[0].line_items, c1.orders[1].line_items

([<OrderLine:1>, <OrderLine:2>], [<OrderLine:3>, <OrderLine:4>])

Для получения элемента заказа используйте item.

Копировать 
for ol in c1.orders[0].line_items:
    ol.id, ol.item, ol.quantity
    
print('-------')
    
for ol in c1.orders[1].line_items:
    ol.id, ol.item, ol.quantity

Вывод:

(1, <Item:1-Chair>, 3)
(2, <Item:2-Pen>, 2)
-------
(3, <Item:1-Chair>, 1)
(4, <Item:2-Pen>, 4)

Все это возможно благодаря отношениям relationship() моделей.

Получение данных

Чтобы сделать запрос в базу данных используется метод query() объекта session. Он возвращает объект типа sqlalchemy.orm.query.Query, который называется просто Query. Он представляет собой инструкцию SELECT, которая будет использована для запроса в базу данных. В следующей таблице перечислены распространенные методы класса Query.

МетодОписание
all()Возвращает результат запроса (объект Query) в виде списка
count()Возвращает общее количество записей в запросе
first()Возвращает первый результат из запроса или None, если записей нет
scalar()Возвращает первую колонку первой записи или None, если результат пустой. Если записей несколько, то бросает исключение MultipleResultsFound
oneВозвращает одну запись. Если их несколько, бросает исключение MutlipleResultsFound. Если данных нет, бросает NoResultFound
get(pk)Возвращает объект по первичному ключу (pk) или None, если объект не был найден
filter(*criterion)Возвращает экземпляр Query после применения оператора WHERE
limit(limit)Возвращает экземпляр Query после применения оператора LIMIT
offset(offset)Возвращает экземпляр Query после применения оператора OFFSET
order_by(*criterion)Возвращает экземпляр Query после применения оператора ORDER BY
join(*props, **kwargs)Возвращает экземпляр Query после создания SQL INNER JOIN
outerjoin(*props, **kwargs)Возвращает экземпляр Query после создания SQL LEFT OUTER JOIN
group_by(*criterion)Возвращает экземпляр Query после добавления оператора GROUP BY к запросу
having(criterion)Возвращает экземпляр Query после добавления оператора HAVING

Метод all()

В базовой форме метод query() принимает в качестве аргументов один или несколько классов модели или колонок. Следующий код вернет все записи из таблицы customers.

Копировать 
from sqlalchemy import create_engine
from sqlalchemy.orm import Session

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

print(session.query(Customer).all())

[<Customer:1-Moseend>,
 <Customer:2-Fortioneaks>,
 <Customer:3-Antence73>,
 <Customer:4-Andescols>,
 <Customer:5-Caltin1962>,
 <Customer:6-Lablen>]

Так же можно получить записи из таблиц items и orders.

Чтобы получить сырой SQL, который используется для выполнения запроса в базу данных, примените sqlalchemy.orm.query.Query следующим образом: print(session.query(Customer)).

SELECT
    customers. ID AS customers_id,
    customers.first_name AS customers_first_name,
    customers.last_name AS customers_last_name,
    customers.username AS customers_username,
    customers.email AS customers_email,
    customers.address AS customers_address,
    customers.town AS customers_town,
    customers.created_on AS customers_created_on,
    customers.updated_on AS customers_updated_on
FROM
    customers

Вызов метода all() на большом объекте результата не очень эффективен. Вместо этого стоит использовать цикл for для перебора по объекту Query:


Копировать 
q = session.query(Customer)

for c in q:
    print(c.id, c.first_name)

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

Копировать 
print(session.query(Customer.id, Customer.first_name).all())

Вывод:

[(1, 'Dmitriy'), 
 (2, 'Valeriy'), 
 (3, 'Vadim'), 
 (4, 'Vladimir'), 
 (5, 'Tatyana'), 
 (6, 'Pavel')]

Обратите внимание на то, что каждый элемент списка — это кортеж, а не экземпляр модели.

Метод count()

count() возвращает количество элементов в результате.

Копировать 
session.query(Item).count()
# Вывод - 8

Метод first()

first() возвращает первый результат запроса или None, если последний не вернул данных.

Копировать 
session.query(Order).first()
# Вывод - Order:1

Метод get()

get() возвращает экземпляр с соответствующим первичным ключом или None, если такой объект не был найден.

Копировать 
session.query(Customer).get(1)
# Вывод - Customer:1-Moseend

Метод filter()

Этот метод позволяет отфильтровать результаты, добавив оператор WHERE. Он принимает колонку, оператор и значение. Например:

session.query(Customer).filter(Customer.first_name == 'Vadim').all()

Этот запрос вернет всех покупателей, чье имя — Vadim. А вот SQL-эквивалент этого запроса:

Копировать 
print(session.query(Customer).filter(Customer.first_name == 'Vadim'))

SELECT
    customers.id AS customers_id,
    customers.first_name AS customers_first_name,
    customers.last_name AS customers_last_name,
    customers.username AS customers_username,
    customers.email AS customers_email,
    customers.address AS customers_address,
    customers.town AS customers_town,
    customers.created_on AS customers_created_on,
    customers.updated_on AS customers_updated_on
FROM
    customers
WHERE
    customers.first_name = %(first_name_1)s

Строка %(first_name_1)s в операторе WHERE — это заполнитель, который будет заменен на реальное значение (Vadim) при выполнении запроса.

Можно передать несколько фильтров в метод filter() и они будут объединены с помощью оператора AND. Например:

Копировать 
session.query(Customer).filter(Customer.id <= 5, Customer.last_name == "Arnautov").all()

Этот запрос вернет всех покупателей, чей первичный ключ меньше или равен 5, а фамилия начинается с "Ar".

Копировать 
session.query(Customer).filter(Customer.id <= 5, Customer.last_name.like("Ar%")).all()

Еще один способ комбинировать условия — союзы (and_(), or_() и not_()). Некоторые примеры:

Копировать 
# все клиенты с именем Vadim и Tatyana
session.query(Customer).filter(or_(
    Customer.first_name == 'Vadim', 
    Customer.first_name == 'Tatyana'
)).all()


# найти всех с именем и Pavel фамилией НЕ Yatsenko
session.query(Customer).filter(and_(
    Customer.first_name == 'Pavel', 
    not_(
        Customer.last_name == 'Yatsenko', 
    )
)).all()

Следующий перечень демонстрирует, как использовать распространенные операторы сравнения с методом filter().

IS NULL

session.query(Order).filter(Order.date_placed == None).all()

IS NOT NULL

session.query(Order).filter(Order.date_placed != None).all()

IN

session.query(Customer).filter(Customer.first_name.in_(['Pavel', 'Vadim'])).all()

NOT INT

session.query(Customer).filter(Customer.first_name.notin_(['Pavel', 'Vadim'])).all()

BETWEEN

session.query(Item).filter(Item.cost_price.between(10, 50)).all()

NOT BETWEEN

session.query(Item).filter(not_(Item.cost_price.between(10, 50))).all()

LIKE

session.query(Item).filter(Item.name.like("%r")).all()

Метод like() выполняет поиск с учетом регистра. Для поиска совпадений без учета регистра используйте ilike().


session.query(Item).filter(Item.name.ilike("w%")).all()

NOT LIKE

session.query(Item).filter(not_(Item.name.like("W%"))).all()

Метод limit()

Метод limit() добавляет оператор LIMIT к запросу. Он принимает количество записей, которые нужно вернуть.

Копировать 
session.query(Customer).limit(2).all()
session.query(Customer).filter(Customer.username.ilike("%Andes")).limit(2).all()

SQL-эквивалент:

SELECT
    customers. id AS customers_id,
    customers.first_name AS customers_first_name,
    customers.last_name AS customers_last_name,
    customers.username AS customers_username,
    customers.email AS customers_email,
    customers.address AS customers_address,
    customers.town AS customers_town,
    customers.created_on AS customers_created_on,
    customers.updated_on AS customers_updated_on
FROM
    customers
LIMIT %(param_1)s

Метод offset()

Метод offset() добавляет оператор OFFSET к запросу. Он принимает в качестве аргумента значение смещения. Часто используется с оператором limit().

Копировать 
session.query(Customer).limit(2).offset(2).all()

SQL-эквивалент:

SELECT
    customers. ID AS customers_id,
    customers.first_name AS customers_first_name,
    customers.last_name AS customers_last_name,
    customers.username AS customers_username,
    customers.email AS customers_email,
    customers.address AS customers_addrees,
    customers.town AS customers_town,
    customers.created_on AS customers_created_on,
    customers.updated_on AS customers_updated_on
FROM
    customers
LIMIT %(param_1)s OFFSET %(param_2)s

Метод order_by()

Метод order_by() используется для сортировки результата с помощью оператора ORDER BY. Он принимает названия колонок, по которым необходимо сортировать результат. По умолчанию сортирует по возрастанию.

Копировать 
session.query(Item).filter(Item.name.ilike("wa%")).all()
session.query(Item).filter(Item.name.ilike("wa%")).order_by(Item.cost_price).all()

Чтобы сортировать по убыванию используйте функцию desc():

Копировать 
from sqlalchemy import desc
session.query(Item).filter(Item.name.ilike("wa%")).order_by(desc(Item.cost_price)).all()

Метод join()

Метод join() используется для создания SQL INNER JOIN. Он принимает название таблицы, с которой нужно выполнить SQL JOIN.

Используем join(), чтобы найти всех покупателей, у которых как минимум один заказ.

Копировать 
session.query(Customer).join(Order).all()

SQL-эквивалент:

SELECT
    customers.id AS customers_id,
    customers.first_name AS customers_first_name,
    customers.last_name AS customers_last_name,
    customers.username AS customers_username,
    customers.email AS customers_email,
    customers.address AS customers_address,
    customers.town AS customers_town,
    customers.created_on AS customers_created_on,
    customers.updated_on AS customers_updated_on
FROM
    customers
JOIN orders ON customers.id = orders.customer_id

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

Копировать 
session.query(Customer.id, Customer.username, Order.id).join(Order).all()

Можно создать SQL JOIN для более чем двух таблиц, объединив несколько методов join() следующим образом:

session.query(Table1).join(Table2).join(Table3).join(Table4).all()

Вот еще один пример, который использует 3 объединения для нахождения всех пунктов в первом заказе Dmitriy Yatsenko.

Копировать 
session.query(
    Customer.first_name, 
    Item.name, 
    Item.selling_price, 
    OrderLine.quantity
).join(Order).join(OrderLine).join(Item).filter(
    Customer.first_name == 'Dmitriy',
    Customer.last_name == 'Yatsenko',
    Order.id == 1,
).all()

Метод outerjoin()

Метод outerjoin() работает как join(), но создает LEFT OUTER JOIN.

Копировать 
session.query(        
    Customer.first_name,
    Order.id,
).outerjoin(Order).all()

В этом запросе левой таблицей является customers. Это значит, что он вернет все записи из customers и только те, которые соответствуют условию, из orders.

Создать FULL OUTER JOIN можно, передав в метод full=True. Например:

Копировать 
session.query(        
    Customer.first_name,
    Order.id,
).outerjoin(Order, full=True).all()

Метод group_by()

Результаты группируются с помощью group_by(). Этот метод принимает одну или несколько колонок и группирует записи в соответствии со значениями в колонке.

Следующий запрос использует join() и group_by() для подсчета количества заказов, сделанных Dmitriy Yatsenko.

Копировать 
from sqlalchemy import func

session.query(func.count(Customer.id)).join(Order).filter(
    Customer.first_name == 'Dmitriy',
    Customer.last_name == 'Yatsenko',    
).group_by(Customer.id).scalar()

Метод having()

Чтобы отфильтровать результаты на основе значений, которые возвращают агрегирующие функции, используется метод having(), добавляющий оператор HAVING к инструкции SELECT. По аналогии с where() он принимает условие.

Копировать 
session.query(
    func.count("*").label('username_count'),    
    Customer.town
).group_by(Customer.username).having(func.count("*") > 2).all()

Работа с дубликатами

Для работы с повторяющимися записями используется параметр DISTINCT. Его можно добавить к SELECT с помощью метода distinct(). Например:

Копировать 
from sqlalchemy import distinct

session.query(Customer.first_name).filter(Customer.id  < 10).all()
session.query(Customer.first_name).filter(Customer.id  < 10).distinct().all()

session.query(        
    func.count(distinct(Customer.first_name)),
    func.count(Customer.first_name)
).all()

Приведение

Приведение (конвертация) данных от одного типа к другому — распространенная операция, которая выполняется с помощью функции cast() из библиотеки sqlalchemy.

Копировать 
from sqlalchemy import cast, Date, distinct, union

session.query(
    cast(func.pi(), Integer),
    cast(func.pi(), Numeric(10,2)),
    cast("2010-12-01", DateTime),
    cast("2010-12-01", Date),
).all()

Объединения

Для объединения запросов используется метод union() объекта Query. Он принимает один или несколько запросов. Например:

Копировать 
s1 = session.query(Item.id, Item.name).filter(Item.name.like("Wa%"))
s2 = session.query(Item.id, Item.name).filter(Item.name.like("%e%"))
s1.union(s2).all()

[(2, 'Pen'),
 (4, 'Travel Bag'),
 (3, 'Headphone'),
 (5, 'Keyboard'),
 (7, 'Watch'),
 (8, 'Water Bottle')]

По умолчанию union() удаляет все повторяющиеся записи из результата. Для их сохранения используйте union_all().

s1.union_all(s2).all()

Обновление данных

Для обновления объекта просто установите новое значение атрибуту, добавьте объект в сессию и сохраните ее.

Копировать 
i = session.query(Item).get(8)
i.selling_price = 25.91
session.add(i)
session.commit()

Таким образом можно обновлять только один объект за раз. Для обновления нескольких записей за раз используйте метод update() объекта Query. Он возвращает общее количество обновленных записей. Например:

Копировать 
session.query(Item).filter(
    Item.name.ilike("W%")
).update({"quantity": 60}, synchronize_session='fetch')
session.commit()

Удаление данных

Для удаления объекта используйте метод delete() объекта сессии. Он принимает объект и отмечает его как удаленный для следующего коммита.

Копировать 
i = session.query(Item).filter(Item.name == 'Monitor').one()
session.delete(i)
session.commit()

<Item:6-Monitor>

Этот коммит удаляет Monitor из таблицы items.

Для удаления нескольких записей за раз используйте метод delete() объекта Query.

Копировать 
session.query(Item).filter(
    Item.name.ilike("W%")
).delete(synchronize_session='fetch')
session.commit()

Этот коммит удаляет все элементы, название которых начинается с W.

Сырые(raw) запросы

ORM предоставляет возможность использовать сырые SQL-запросы с помощью функции text(). Например:

Копировать 
from sqlalchemy import text

session.query(Customer).filter(text("first_name = 'Vladimir'")).all()
session.query(Customer).filter(text("username like 'Cal%'")).all()
session.query(Customer).filter(text("username like 'Cal%'")).order_by(text("first_name, id desc")).all()

Транзакции

Транзакция — это способ выполнения набора SQL-инструкций так, что выполняются или все вместе, или ни одна из них. Если хотя бы одна инструкция из транзакции была провалена, база данных возвращается к предыдущему состоянию.

В базе данных есть два заказа, в процессе отгрузки заказа есть такие этапы:

  1. В колонке date_placed таблицы orders устанавливается дата отгрузки.
  2. Количество заказанных товаров вычитается из items.

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

В следующем коде определяем метод dispatch_order(), который принимает order_id в качестве аргумента и выполняет описанные выше задачи в одной транзакции.

Копировать 
def dispatch_order(order_id):
    # проверка того, правильно ли указан order_id
    order = session.query(Order).get(order_id)
    
    if not order:
        raise ValueError("Недействительный order_id: {}.".format(order_id))    
 
    try:
        for i in order.line_items:
            i.item.quantity = i.item.quantity - i.quantity            
        
        order.date_placed = datetime.now()                            
        session.commit()
        print("Транзакция завершена.")

    except IntegrityError as e:
        print(e)
        print("Возврат назад...")
        session.rollback()
        print("Транзакция не удалась.")


dispatch_order(1)

В первом заказе 3 стула и 2 ручки. dispatch_order() с идентификатором заказа 1 даст следующий вывод:

Транзакция завершена.

Максим
Я создал этот блог в 2018 году, чтобы распространять полезные учебные материалы, документации и уроки на русском. На сайте опубликовано множество статей по основам python и библиотекам, уроков для начинающих и примеров написания программ.
Мои контакты: Почта
admin@pythonru.comAlex Zabrodin2018-10-26OnlinePython, Programming, HTML, CSS, JavaScript

Статьи по теме