灵活使用 SQLAlchemy 中的 ORM 查询

之前做查询一直觉得直接拼 SQL 比较方便，用了 SQLAlchemy 的 ORM 查询之后，发现也还可以，还提高了可读性。

这篇文章主要说说 SQLAlchemy 常用的 ORM 查询方式，偏实践。看了之后，对付开发中的查询需求，我觉得可以满足不少。

为方便说明，假设有如下数据

图书表 books

+----+--------+--------------------------+-------+
| 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 |
+----+--------+--------------------------+-------+

分类表 categories

+----+--------------+
| 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 AND 查询

我们最常用到的就是这种查询，比如我要获取 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() 里面的条件默认是使用 AND 进行连接，毕竟这最常用嘛。所以说，换成这样用也是没有问题的

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 连接的话，没必要显式的去用 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')}]
  • distinct() 与 SQL 的 distinct 语句行为一致
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)

结果

万物简史
  • exist() 查看记录是否存在
# 查看 ID 大于 10 的图书是否存在
from sqlalchemy.sql import exists
is_exist = session \
    .query(exists().where(Book.id > 10)) \
    .scalar()
print(is_exist)

结果

False

4 OR 查询

通过 OR 连接条件的情况也多，比如我要获取 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 查询类似，从 sqlalchemy 引入 or_，然后将条件放入就 OK 了。

5 AND 和 OR 并存的查询

现实情况下，我们很容易碰到 AND 和 OR 并存的查询。比如，我现在要查询价格大于 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 巧用列表或者字典的解包给查询方法传参

开发中，我们经常会碰到根据传入的参数构造查询条件进行查询。比如

  • 如果接收到非 0 的 cat_id，需要限制 cat_id 等于 0
  • 如果接收到非 0 的 price，需要限制 price 等于传入的 price
  • 如果接收到非 0 的 min_price，需要限制 price 大于等于 min_price
  • 如果接收到非 0 的 max_price，需要限制 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 查询类似，将列表解包传给 or_() 即可。

如果需求更复杂，AND 和 OR 都可能出现，这个时候根据情况多建几个列表实现。这里只向大家说明大致的思路，就不举具体的例子了。

当然，如果都是等值查询的话，比如只有这两种情况

  • 如果接收到非 0 的 cat_id，需要限制 cat_id 等于 0
  • 如果接收到非 0 的 price，需要限制 price 等于传入的 price

可以使用字典的解包给 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 查询指定列

查询名称包含「简史」的图书的 ID 和名称。如下

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 |
+----+--------+-----------------+-------+

查看 ID 大于等于 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

当碰到复杂的查询，比如有 AND、有 OR、还有连接查询时，有时可能得不到预期的结果，这时我们可以打出最终的 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 常用的一些查询方法和技巧已介绍完毕，希望能帮助到有需要的朋友。

查看最新文章请关注公众号「小小后端」。

©著作权归作者所有,转载或内容合作请联系作者
  • 人面猴
    序言：七十年代末，一起剥皮案震惊了整个滨河市，随后出现的几起案子，更是在滨河造成了极大的恐慌，老刑警刘岩，带你破解...
    沈念sama阅读 217,406评论 6赞 503
  • 死咒
    序言：滨河连续发生了三起死亡事件，死亡现场离奇诡异，居然都是意外死亡，警方通过查阅死者的电脑和手机，发现死者居然都...
    沈念sama阅读 92,732评论 3赞 393
  • 救了他两次的神仙让他今天三更去死
    文/潘晓璐 我一进店门，熙熙楼的掌柜王于贵愁眉苦脸地迎上来，“玉大人，你说我怎么就摊上这事。” “怎么了？”我有些...
    开封第一讲书人阅读 163,711评论 0赞 353
  • 道士缉凶录：失踪的卖姜人
    文/不坏的土叔 我叫张陵，是天一观的道长。 经常有香客问我，道长，这世上最难降的妖魔是什么？ 我笑而不...
    开封第一讲书人阅读 58,380评论 1赞 293
  • 港岛之恋（遗憾婚礼）
    正文 为了忘掉前任，我火速办了婚礼，结果婚礼上，老公的妹妹穿的比我还像新娘。我一直安慰自己，他们只是感情好，可当我...
    茶点故事阅读 67,432评论 6赞 392
  • 恶毒庶女顶嫁案：这布局不是一般人想出来的
    文/花漫 我一把揭开白布。 她就那样静静地躺着，像睡着了一般。 火红的嫁衣衬着肌肤如雪。 梳的纹丝不乱的头发上，一...
    开封第一讲书人阅读 51,301评论 1赞 301
  • 城市分裂传说
    那天，我揣着相机与录音，去河边找鬼。 笑死，一个胖子当着我的面吹牛，可吹牛的内容都是我干的。 我是一名探鬼主播，决...
    沈念sama阅读 40,145评论 3赞 418
  • 双鸳鸯连环套：你想象不到人心有多黑
    文/苍兰香墨 我猛地睁开眼，长吁一口气：“原来是场噩梦啊……” “哼！你这毒妇竟也来了？” 一声冷哼从身侧响起，我...
    开封第一讲书人阅读 39,008评论 0赞 276
  • 万荣杀人案实录
    序言：老挝万荣一对情侣失踪，失踪者是张志新（化名）和其女友刘颖，没想到半个月后，有当地人在树林里发现了一具尸体，经...
    沈念sama阅读 45,443评论 1赞 314
  • 护林员之死
    正文 独居荒郊野岭守林人离奇死亡，尸身上长有42处带血的脓包…… 初始之章·张勋 以下内容为张勋视角 年9月15日...
    茶点故事阅读 37,649评论 3赞 334
  • 白月光启示录
    正文 我和宋清朗相恋三年，在试婚纱的时候发现自己被绿了。 大学时的朋友给我发了我未婚夫和他白月光在一起吃饭的照片。...
    茶点故事阅读 39,795评论 1赞 347
  • 活死人
    序言：一个原本活蹦乱跳的男人离奇死亡，死状恐怖，灵堂内的尸体忽然破棺而出，到底是诈尸还是另有隐情，我是刑警宁泽，带...
    沈念sama阅读 35,501评论 5赞 345
  • 日本核电站爆炸内幕
    正文 年R本政府宣布，位于F岛的核电站，受9级特大地震影响，放射性物质发生泄漏。R本人自食恶果不足惜，却给世界环境...
    茶点故事阅读 41,119评论 3赞 328
  • 男人毒药：我在死后第九天来索命
    文/蒙蒙 一、第九天 我趴在偏房一处隐蔽的房顶上张望。 院中可真热闹，春花似锦、人声如沸。这庄子的主人今日做“春日...
    开封第一讲书人阅读 31,731评论 0赞 22
  • 一桩弑父案，背后竟有这般阴谋
    文/苍兰香墨 我抬头看了看天上的太阳。三九已至，却和暖如春，着一层夹袄步出监牢的瞬间，已是汗流浃背。 一阵脚步声响...
    开封第一讲书人阅读 32,865评论 1赞 269
  • 情欲美人皮
    我被黑心中介骗来泰国打工， 没想到刚下飞机就差点儿被人妖公主榨干…… 1. 我叫王不留，地道东北人。 一个月前我还...
    沈念sama阅读 47,899评论 2赞 370
  • 代替公主和亲
    正文 我出身青楼，却偏偏与公主长得像，于是被迫代替她去往敌国和亲。 传闻我的和亲对象是个残疾皇子，可洞房花烛夜当晚...
    茶点故事阅读 44,724评论 2赞 354

推荐阅读更多精彩内容