Гибкое использование запросов ORM в SQLAlchemy

задняя часть Python ORM SQL

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

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

Для удобства пояснений предполагается, что следующие данные

книги

+----+--------+--------------------------+-------+
| id | cat_id | name                     | price |
+----+--------+--------------------------+-------+
|  1 |      1 | 生死疲劳                 | 40.40 |
|  2 |      1 | 皮囊                     | 31.80 |
|  3 |      2 | 半小时漫画中国史         | 33.60 |
|  4 |      2 | 耶路撒冷三千年           | 55.60 |
|  5 |      2 | 国家宝藏                 | 52.80 |
|  6 |      3 | 时间简史                 | 31.10 |
|  7 |      3 | 宇宙简史                 | 22.10 |
|  8 |      3 | 自然史                   | 26.10 |
|  9 |      3 | 人类简史                 | 40.80 |
| 10 |      3 | 万物简史                 | 33.20 |
+----+--------+--------------------------+-------+

категории категории

+----+--------------+
| id | name         |
+----+--------------+
|  1 | 文学         |
|  2 | 人文社科     |
|  3 | 科技         |
+----+--------------+

Объекты ORM определяются следующим образом

Примечание. Код Python в этой статье прошел тестирование в следующей среде.

  • Python 3.6.0
  • PyMySQL 0.8.1
  • SQLAlchemy 1.2.8
# coding=utf-8

from sqlalchemy import create_engine
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy import Column, Integer, String, Numeric
from sqlalchemy.orm import sessionmaker

Base = declarative_base()
engine = create_engine('mysql+pymysql://username:password'
                       '@127.0.0.1:3306/db_name?charset=utf8')
Session = sessionmaker(bind=engine)

session = Session()


def to_dict(self):
    return {c.name: getattr(self, c.name, None)
            for c in self.__table__.columns}
Base.to_dict = to_dict


class Book(Base):
    __tablename__ = 'books'

    id = Column(Integer, primary_key=True)
    cat_id = Column(Integer)
    name = Column('name', String(120))
    price = Column('price', Numeric)


class Category(Base):
    __tablename__ = 'categories'

    id = Column(Integer, primary_key=True)
    name = Column('name', String(30))

Ладно, давайте к делу.

1 Получить записи по первичному ключу

Это легко использовать, когда мы получаем детали книги.

book_id = 1
book = session.query(Book).get(book_id)
print(book and book.to_dict())

Получите результат напрямую с помощью get(primary_key)

{'id': 1, 'cat_id': 1, 'name': '生死疲劳',
 'price': Decimal('40.40')}

Конечно, это также может

book_id = 1
book = session.query(Book) \
    .filter(Book.id == book_id) \
    .first()
print(book and book.to_dict())

Однако первый способ более лаконичен.

2 И запрос

Чаще всего используется запрос такого типа, например, я хочу получитьcat_idКниги с ценой 1 и выше 35

books = session.query(Book) \
    .filter(Book.cat_id == 1,
            Book.price > 35) \
    .all()
print([v.to_dict() for v in books])

После выполнения получить результат

[{'id': 1, 'cat_id': 1, 'name': '生死疲劳',
  'price': Decimal('40.40')}]

Условия в filter() связаны по И по умолчанию, в конце концов, это чаще всего используется. Так что заменить его на это не проблема.

from sqlalchemy import and_
books = session.query(Book) \
    .filter(and_(Book.cat_id == 1,
                 Book.price > 35)) \
    .all()
print([v.to_dict() for v in books])

Однако в общем случае, если все условия связаны с И, нет необходимости явно использоватьand_.

Если все условия равны сравнениям, вы можете использоватьfilter_by()метод, который принимает аргументы ключевого слова.

Запросcat_idКнига, равная 1, и цена, равная 31,8, могут быть

books = session.query(Book) \
    .filter_by(cat_id=1, price=31.8) \
    .all()
print([v.to_dict() for v in books])

результат

[{'id': 2, 'cat_id': 1, 'name': '皮囊',
  'price': Decimal('31.80')}]

По сравнению с filter() этот метод проще в написании, но условия ограничены равным сравнением.

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

3 распространенных метода

В дополнение к методам get(), first() и all(), использованным выше, чаще используются следующие методы.

  • one() получает только одну запись, если запись не найдена или найдено несколько записей, будет сообщено об ошибке
# 找不到记录会抛如下错误
# sqlalchemy.orm.exc.NoResultFound: No row was found for one()
book = session \
    .query(Book).filter(Book.id > 10) \
    .one()
print(book and book.to_dict())

# 找到多条记录会抛如下错误
# sqlalchemy.orm.exc.MultipleResultsFound: Multiple rows were found for one()
book = session \
    .query(Book).filter(Book.id < 10) \
    .one()
print(book and book.to_dict())

# 正常，得到如下结果
# {'id': 10, 'cat_id': 3, 'name': '万物简史',
#  'price': Decimal('33.20')}
book = session \
    .query(Book).filter(Book.id == 10) \
    .one()
print(book and book.to_dict())
  • count () Возвращает количество записей
count = session \
    .query(Book) \
    .filter(Book.cat_id == 3) \
    .count()
print(count)

результат

5
  • limit() ограничивает количество возвращаемых записей
books = session \
    .query(Book) \
    .filter(Book.cat_id == 3) \
    .limit(3) \
    .all()
print([v.to_dict() for v in books])

результат

[{'id': 6, 'cat_id': 3, 'name': '时间简史',
  'price': Decimal('31.10')},
 {'id': 7, 'cat_id': 3, 'name': '宇宙简史',
  'price': Decimal('22.10')},
 {'id': 8, 'cat_id': 3, 'name': '自然史',
  'price': Decimal('26.10')}]
  • Different() имеет то же поведение, что и отдельный оператор SQL.
books = session \
    .query(Book.cat_id) \
    .distinct(Book.cat_id) \
    .all()
print([dict(zip(v.keys(), v)) for v in books])

результат

[{'cat_id': 1}, {'cat_id': 2},
 {'cat_id': 3}]
  • order_by()Сортировка записей по полю
# 图书按 ID 降序排列
# 如果要升序排列，去掉 .desc() 即可
books = session \
    .query(Book.id, Book.name) \
    .filter(Book.id > 8) \
    .order_by(Book.id.desc()) \
    .all()
print([dict(zip(v.keys(), v)) for v in books])

результат

[{'id': 10, 'name': '万物简史'},
 {'id': 9, 'name': '人类简史'}]
  • scalar() возвращает значение первого столбца результата, полученного после вызова one()
book_name = session \
    .query(Book.name) \
    .filter(Book.id == 10)\
    .scalar()
print(book_name)

результат

万物简史
  • exists(), чтобы узнать, существует ли запись
# 查看 ID 大于 10 的图书是否存在
from sqlalchemy.sql import exists
is_exist = session \
    .query(exists().where(Book.id > 10)) \
    .scalar()
print(is_exist)

результат

False

4 ИЛИ запрос

Так же много случаев соединения условий через ИЛИ, типа хочу получитьcat_idКниги с ценой равной 1 или выше 35

from sqlalchemy import or_
books = session.query(Book) \
    .filter(or_(Book.cat_id == 1,
                Book.price > 35)) \
    .all()
print([v.to_dict() for v in books])

выполнить, получить результат

[{'id': 1, 'cat_id': 1, 'name': '生死疲劳',
  'price': Decimal('40.40')},
 {'id': 2, 'cat_id': 1, 'name': '皮囊',
  'price': Decimal('31.80')},
 {'id': 4, 'cat_id': 2, 'name': '耶路撒冷三千年',
  'price': Decimal('55.60')},
 {'id': 5, 'cat_id': 2, 'name': '国家宝藏',
  'price': Decimal('52.80')},
 {'id': 9, 'cat_id': 3, 'name': '人类简史',
  'price': Decimal('40.80')}]

Аналогично запросу AND, импортированному из sqlalchemyor_, затем введите условие, и все в порядке.

5 запросов с И и ИЛИ

На самом деле мы можем легко столкнуться с запросами на сосуществование И и ИЛИ. Например, теперь я хочу запросить, больше ли цена 55 или меньше 25, и в то же времяcat_idКниги, не равные 1

from sqlalchemy import or_
books = session.query(Book) \
    .filter(or_(Book.price > 55,
                Book.price < 25),
            Book.cat_id != 1) \
    .all()
print([v.to_dict() for v in books])

результат

[{'id': 4, 'cat_id': 2, 'name': '耶路撒冷三千年',
  'price': Decimal('55.60')},
 {'id': 7, 'cat_id': 3, 'name': '宇宙简史',
  'price': Decimal('22.10')}]

В другом примере, чтобы запросить количество книг, книги могут соответствовать одному из двух требований:cat_idбольше 5, втораяcat_idМеньше 2 и цена больше 40. может быть так

from sqlalchemy import or_, and_
count = session.query(Book) \
    .filter(or_(Book.cat_id > 5,
                and_(Book.cat_id < 2,
                     Book.price > 40))) \
    .count()
print(count)

результат

1

6 Умело используйте распаковку списков или словарей для передачи параметров в методы запросов

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

  • Если ненулевой ноль полученcat_id, надо ограничитьcat_idравно 0
  • Если вы получаете цену, отличную от 0, вам нужно ограничить цену, чтобы она была равна входящей цене.
  • Если получен ненулевойmin_price, вам нужно ограничить цену, чтобы она была больше или равнаmin_price
  • Если получен ненулевойmax_price, вам нужно ограничить цену, чтобы она была меньше или равнаmax_price

Мы можем написать код как

# 请求参数，这里只是占位，实际由用户提交的请求决定
params = {'cat_id': 1}

conditions = []
if params.get('cat_id', 0):
    conditions.append(Book.cat_id == params['cat_id'])
if params.get('price', 0):
    conditions.append(Book.price == params['price'])
if params.get('min_price', 0):
    conditions.append(Book.price >= params['min_price'])
if params.get('max_price', 0):
    conditions.append(Book.price <= params['max_price'])
books = session.query(Book).filter(*conditions).all()

print([v.to_dict() for v in books])

результат

[{'id': 1, 'cat_id': 1, 'name': '生死疲劳',
  'price': Decimal('40.40')},
 {'id': 2, 'cat_id': 1, 'name': '皮囊',
  'price': Decimal('31.80')}]

ИЛИ запрос аналогичен, распакуйте список и передайте егоor_()Вот и все.

Если требования более сложные, могут появиться как И, так и ИЛИ.В это время строятся еще несколько списков по ситуации. Здесь мы только объясняем всем общую идею, а не приводим конкретных примеров.

Конечно, если все они являются эквивалентными запросами, например, только эти два случая

  • Если получен ненулевойcat_id, нужно ограничитьcat_idравно 0
  • Если вы получаете цену, отличную от 0, вам нужно ограничить цену, чтобы она была равна входящей цене.

Вы можете использовать распаковку словаря, чтобы датьfilter_by()передать параметры

# 请求参数，这里只是占位，实际由用户提交的请求决定
params = {'price': 31.1}

condition_dict = {}
if params.get('cat_id', 0):
    condition_dict['cat_id'] = params['cat_id']
if params.get('price', 0):
    condition_dict['price'] = params['price']
books = session.query(Book) \
    .filter_by(**condition_dict) \
    .all()

print([v.to_dict() for v in books])

результат

[{'id': 6, 'cat_id': 3, 'name': '时间简史',
  'price': Decimal('31.10')}]

7 Другие распространенные операторы

В дополнение к ==, >, >=,

  • IN
# 查询 ID 在 1、3、5 中的记录
books = session.query(Book) \
        .filter(Book.id.in_([1, 3, 5])) \
        .all()
  • INSTR()
# 查询名称包含「时间简史」的图书
books = session.query(Book) \
    .filter(Book.name.contains('时间简史')) \
    .all()
  • FIN_IN_SET()
# 查询名称包含「时间简史」的图书
# 这里显然应该用 INSTR() 的用法
# FIND_IN_SET() 一般用于逗号分隔的 ID 串查找
# 这里使用 FIND_IN_SET()，旨在说明用法

from sqlalchemy import func
books = session.query(Book) \
    .filter(func.find_in_set('时间简史', Book.name)) \
    .all()
  • LIKE
# 查询名称以「简史」结尾的图书
books = session.query(Book) \
        .filter(Book.name.like('%简史')) \
        .all()
  • NOT

IN, INSTR, вышеFIN_IN_SET, LIKE можно инвертировать с помощью символа ~. Например

# 查询 ID 不在 1 到 9 之间的记录
books = session.query(Book) \
    .filter(~Book.id.in_(range(1, 10))) \
    .all()

8 Запросить указанный столбец

Найдите идентификаторы и названия книг, названия которых содержат «Краткую историю». следующее

books = session.query(Book.id, Book.name) \
    .filter(Book.name.contains('简史')) \
    .all()
print([dict(zip(v.keys(), v)) for v in books])

результат

[{'id': 6, 'name': '时间简史'},
 {'id': 7, 'name': '宇宙简史'},
 {'id': 9, 'name': '人类简史'},
 {'id': 10, 'name': '万物简史'}]

9 Внутреннее соединение, внешнее соединение

9.1 Внутреннее соединение

Получите книги категории "Технология" по цене выше 40

# 如果 ORM 对象中定义有外键关系
# 那么 join() 中可以不指定关联关系
# 否则，必须要	
books = session \
    .query(Book.id,
           Book.name.label('book_name'),
           Category.name.label('cat_name')) \
    .join(Category, Book.cat_id == Category.id) \
    .filter(Category.name == '科技',
            Book.price > 40) \
    .all()
print([dict(zip(v.keys(), v)) for v in books])

результат

[{'id': 9, 'book_name': '人类简史',
  'cat_name': '科技'}]

Подсчитайте количество книг в каждой категории

from sqlalchemy import func
books = session \
    .query(Category.name.label('cat_name'),
           func.count(Book.id).label('book_num')) \
    .join(Book, Category.id == Book.cat_id) \
    .group_by(Category.id) \
    .all()
print([dict(zip(v.keys(), v)) for v in books])

результат

[{'cat_name': '文学', 'book_num': 2},
 {'cat_name': '人文社科', 'book_num': 3},
 {'cat_name': '科技', 'book_num': 5}]

9.2 Внешние соединения

Для удобства пояснений мы лишь добавляем в таблицу books в этом разделе следующие данные

+----+--------+-----------------+-------+
| id | cat_id | name            | price |
+----+--------+-----------------+-------+
| 11 |      5 | 人性的弱点      | 54.40 |
+----+--------+-----------------+-------+

Просмотр информации о категории для книг с идентификаторами больше или равными 9

# outerjoin 默认是左连接
# 如果 ORM 对象中定义有外键关系
# 那么 outerjoin() 中可以不指定关联关系
# 否则，必须要
books = session \
    .query(Book.id.label('book_id'),
           Book.name.label('book_name'),
           Category.id.label('cat_id'),
           Category.name.label('cat_name')) \
    .outerjoin(Category, Book.cat_id == Category.id) \
    .filter(Book.id >= 9) \
    .all()
print([dict(zip(v.keys(), v)) for v in books])

результат

[{'book_id': 9, 'book_name': '人类简史',
  'cat_id': 3, 'cat_name': '科技'},
 {'book_id': 10, 'book_name': '万物简史',
  'cat_id': 3, 'cat_name': '科技'},
 {'book_id': 11, 'book_name': '人性的弱点',
  'cat_id': None, 'cat_name': None}]

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

10 Печать SQL

При столкновении со сложными запросами, такими как И, ИЛИ и запросы на соединение, иногда ожидаемые результаты могут быть не получены.В это время мы можем ввести окончательный SQL, чтобы помочь нам найти ошибки.

Возьмите внешнее соединение из предыдущего раздела в качестве примера, чтобы показать, как напечатать окончательный SQL.

q = session \
    .query(Book.id.label('book_id'),
           Book.name.label('book_name'),
           Category.id.label('cat_id'),
           Category.name.label('cat_name')) \
    .outerjoin(Category, Book.cat_id == Category.id) \
    .filter(Book.id >= 9)

raw_sql = q.statement \
    .compile(compile_kwargs={"literal_binds": True})
print(raw_sql)

где q — объект класса sqlalchemy.orm.query.Query.

результат

SELECT books.id AS book_id, books.name AS book_name, categories.id AS cat_id, categories.name AS cat_name 
FROM books LEFT OUTER JOIN categories ON books.cat_id = categories.id 
WHERE books.id >= 9

До сих пор были представлены некоторые методы и приемы запросов, обычно используемые SQLAlchemy ORM, и я надеюсь помочь нуждающимся друзьям.

Подпишитесь на официальный аккаунт «Little Backend», чтобы читать больше интересных статей.

Категории