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 не сразу устанавливает соединение с базой данных. Это происходит лишь при первом запросе.
Вставка(добавление) данных
Для создания новой записи с помощью SQLAlchemy ORM нужно выполнить следующие шаги:
Создадим два новых объекта 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.new .
Наконец, для сохранения данных используется метод commit() :
После сохранения транзакции ресурсы соединения, на которые ссылается объект Session , возвращаются в пул соединений. Последующие операции будут выполняться в новой транзакции.
Сейчас таблица 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 объекта Customer , то вернется не-пустой список.
С другой стороны отношения можно получить доступ к объекту Customer , которому заказ принадлежит через атрибут customer объекта Order — o1.customer .
Сейчас у покупателя c1 три заказа. Чтобы посмотреть все пункты в заказе нужно использовать атрибут line_items объекта Order .
c1.orders[0].line_items, c1.orders[1].line_items ([, ], [, ])Для получения элемента заказа используйте 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Все это возможно благодаря отношениям 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())Так же можно получить записи из таблиц 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 :