靈活使用 SQLAlchemy 中的 ORM 查詢

kevinbai_cn發表於2020-09-21

之前做查詢一直覺得直接拼 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)

結果

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)

結果

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 其它常用運算子

除了上面看到的 ==、>、>=、<、<=、!= 之外，還有幾個比較常用

# 查詢 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()

# 查詢名稱以「簡史」結尾的圖書
books = session.query(Book) \
        .filter(Book.name.like('%簡史')) \
        .all()

上面的 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 常用的一些查詢方法和技巧已介紹完畢，希望能幫助到有需要的朋友。

原文連結：http://www.kevinbai.com/articles/30.html

關注公眾號「小小後端」獲取最新文章推送！

靈活的查詢設計方案
2008-04-30
sqlalchemy在python中的使用(關於查詢)二
2019-01-03
SQLPython
靈活的API查詢語言——GraphQL
2018-11-07
API
SQLAlchemy 和其他的 ORM 框架
2016-01-12
SQLORM框架
在Sqlalchemy的查詢中新共享filter
2022-03-07
SQLFilter
hisql ORM 查詢語句使用教程
2022-01-08
SQLORM
如何在Django ORM中進行not查詢？
2021-09-11
DjangoORM
python中類中引數的靈活使用
2016-06-01
Python
ORM多表查詢下
2018-12-20
ORM
如何在 Laravel 中靈活的使用 Trait
2019-02-26
LaravelAI
Python之ORM框架SQLAlchemy
2021-09-11
PythonORM框架SQL
Flask框架之八SQLAlchemy -orm
2020-10-29
Flask框架SQLORM
SQLAlchemy in 查詢空列表問題分析
2019-02-16
SQL
Laravel ORM 中，根據關聯查詢的欄位值，對主查詢排名
2020-10-20
LaravelORM
陣列的靈活使用
2019-03-29
陣列
Django框架：8、聚合查詢、分組查詢、F與Q查詢、ORM查詢最佳化、ORM事務操作、ORM常用欄位型別、ORM常用欄位引數
2022-12-19
Django框架ORM型別
python的ORM技術：使用sqlalchemy操作mysql資料庫
2018-08-13
PythonORMMySql資料庫
如何修復使用 Python ORM 工具 SQLAlchemy 時的常見陷阱
2019-11-19
PythonORMSQL
Django-ORM 之查詢排序
2020-01-02
DjangoORM排序
golang beego orm 查詢條件 or and
2020-11-11
GolangORM
在Dreamweaver中靈活使用網頁表格(轉)
2007-08-13
網頁
Hibernate-ORM:13.Hibernate中的連線查詢
2018-06-04
ORM
Tornado中sqlalchemy使用
2015-10-14
SQL
第三方ORM 之 SQLAlchemy
2019-02-20
ORMSQL
day95:flask:SQLAlchemy資料庫查詢進階&關聯查詢
2020-11-23
FlaskSQL資料庫
sqlplus中靈活使用sqlprompt提示符
2011-07-03
SQL
beego orm join 聯合查詢的問題
2017-09-05
GoORM
Django-ORM---查詢集介紹
2020-12-19
DjangoORM
Django中ORM的使用
2020-12-21
DjangoORM
Go語言ORM包：使用worm構造查詢條件
2022-07-18
GoWorm
Django vs SQLAlchemy：哪個 Python ORM 更好
2017-10-13
DjangoSQLPythonORM
靈活的JavaScript
2014-10-16
JavaScript
sqlalchemy在python中的使用(基本使用)一
2019-01-03
SQLPython
比較爽的導航查詢功能 - SqlSugar ORM
2022-04-14
SqlSugarORM
建立一個更高階別的查詢 API：正確使用Django ORM 的方式
2013-02-25
APIDjangoORM
Python-SQLAlchemy：第2節：查詢條件設定
2019-02-16
PythonSQL
beego的orm針對postgresql的jsonb欄位的查詢
2016-10-27
GoORMSQLJSON
如題，執行原生的關聯查詢使用 Laravel 的 ORM 或者 Db 如何寫呢
2019-08-24
LaravelORM