How can I get all rows with keys provided in a list using SQLalchemy?
Be wary that if seq gets long enough, you may get a ‘too many SQL variables’ exception because the IN clause is parameterised and there are too many parameters.
5 Answers 5
Your code is absolutety fine.
IN is like a bunch of X=Y joined with OR and is pretty fast in contemporary databases.
However, if your list of IDs is long, you could make the query a bit more efficient by passing a sub-query returning the list of IDs.
Keep in mind, using «IN» this way does not guarantee that every listed ID will be returned. An additional check for that is needed after retrieving the results.
The code as is is completely fine. However, someone is asking me for some system of hedging between the two approaches of doing a big IN vs. using get() for individual IDs.
If someone is really trying to avoid the SELECT, then the best way to do that is to set up the objects you need in memory ahead of time. Such as, you’re working on a large table of elements. Break up the work into chunks, such as, order the full set of work by primary key, or by date range, whatever, then load everything for that chunk locally into a cache:
all_ids = [] all_ids.sort() while all_ids: chunk = all_ids[0:1000] # bonus exercise! Throw each chunk into a multiprocessing.pool()! all_ids = all_ids[1000:] my_cache = dict( Session.query(Record.id, Record).filter( Record.id.between(chunk[0], chunk[-1])) ) for id_ in chunk: my_obj = my_cache[id_]
That’s the real world use case.
But to also illustrate some SQLAlchemy API, we can make a function that does the IN for records we don’t have and a local get for those we do. Here is that:
from sqlalchemy import inspect def get_all(session, cls, seq): mapper = inspect(cls) lookup = set() for ident in seq: key = mapper.identity_key_from_primary_key((ident, )) if key in session.identity_map: yield session.identity_mapPython sqlalchemy select all else: lookup.add(ident) if lookup: for obj in session.query(cls).filter(cls.id.in_(lookup)): yield obj from sqlalchemy import Column, Integer, create_engine, String from sqlalchemy.orm import Session from sqlalchemy.ext.declarative import declarative_base import random Base = declarative_base() class A(Base): __tablename__ = 'a' primary_key=True) data = Column(String) e = create_engine("sqlite://", echo=True) Base.metadata.create_all(e) ids = range(1, 50) s = Session(e) s.add_all([A(id=i, data='a%d' % i) for i in ids]) s.commit() s.close() already_loaded = s.query(A).filter(A.id.in_(random.sample(ids, 10))).all() assert len(s.identity_map) == 10 to_load = set(random.sample(ids, 25)) all_ = list(get_all(s, A, to_load)) assert set(x.id for x in all_) == to_load 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 :