首页 > 代码库 > Python SQLAlchemy --2
Python SQLAlchemy --2
本文為 Python SQLAlchemy ORM 一系列教學文:
接下來會更深入地探討查詢的使用。
查詢的基本使用法為 session.query(Mapped Class)
,其後可加 .group_by()
, .order_by()
, .delete
等方法。
只是要注意的是 query()
接受的參數為 Mapped Class (例如先前定義的 User 類別),而不是表格名稱。
例如在 範例 2 中是以 User
做為參數,而不是以 user
這個表格名稱做為參數。
如果查詢成功就會回傳 Query Object,若無則會回傳 None 。
首先,query()
可以使用 from_statement()
方法,直接以完整的 SQL 指令進行查詢,更可以搭配 params()
將變數代入 SQL 指令中。
例如:
1 | rows = session.query(User).from_statement(‘SELECT * FROM user WHERE name=:name‘).params(name=‘user1‘) |
其中,上述 SQL 指令中的 :name
代表會有一個參數名 name ,然後在 params(name=‘user1‘)
中就指明了 name 的值為 user1 。
基本上這樣就能夠進行大多數的查詢,但若希望能夠更深入地學會運用 SQLAlchemy 進行查詢,就可以閱讀以下的文章。
以下是幾個關於 query()
的使用範例(由於 User 類別已在先前已經定義過了,因此以下範例會直接省略)。
SELECT
範例的 SQL 指令 ─ SELECT * FROM user
1 | for r in session.query(User): |
執行結果如下:
1 | [class main__.user] |
上述的範例程式碼,是列出每一列 SELECT 查詢出來的結果,可以注意到的是每一列的 r 都是一個 User 實例,因此我們就可以依照先前定義 User 類別的欄位屬性,將這些屬性列印出來。
SELECT user.name FROM user
query()
也能針對個別欄位進行 SELECT ,只要將 Mapped Class 及其屬性做為參數即可,例如只想查詢全部的 username ,那麼 query()
的參數就會是 User.username
。
1 | for r in session.query(User.username): |
執行結果如下:
1 | [class sqlalchemy.util._collections.keyedtuple] |
此處值得注意的是,當有只針對個別欄位 SELECT 的情況時,就是回傳 KeyedTuple
實例了,而不是我們先前的定義的 User 類別的實例。
WHERE
範例 SQL 指令 ─ SELECT * FROM user WHER id = 1
若要使用 WHERE 條件查詢,則是使用 filter_by()
或 filter()
方法。
兩者的差異在於 filter_by()
必須使用屬性名稱做為參數名稱,然後加上查詢條件。例如查詢 id 為 1 的資料為 filter_by(id=1)
。\
而 filter()
則是以 Mapped Class 及其屬性加上查詢條件做為參數,而且支援 Python 運算子。例如同樣查詢 id 為 1 的資料為 filter(User.id == 1)
。
不過,SQLAlchemy 其實也有提供 filter()
以字串為參數的用法,例如查詢 id 為 1 也可以表示成為 filter(id=1)
。
此外,多個查詢條件可以用附加 .filter(…)
的形式達成。
1 | for r in session.query(User.id).filter_by(id=1): |
更多的查詢條件可以看 Common Filter Operators ,如 LIKE, IN, NOT IN 等等。
ORDER BY
範例 SQL 指令 ─ SELECT user.id FROM user ORDER BY user.id DESC
排序可以使用 order_by()
,參數為 Mapped Class 及其屬性。例如想依照 id 欄位排序,那麼參數就會是 User.id
。此外, 預設的排序為遞增排序 。
遞減排序的部份則要記得 import desc
模組:
1 | from sqlalchemy import desc |
以下範例示範遞增及遞減排序:
1 | # 遞減 |
LIMIT
有時會需要使用 LIMIT 來限制查詢的範圍,例如分頁功能,最直接的方法就是用 Python slice 的功能,SQLAlchemy 就會自動將 LIMIT 的查詢條件附加到查詢的 SQL 語法後面。
範例:
1 | for row in session.query(User)[500:520]: |
如上述加上 [500:520]
,就能夠看到 SQLAlchemy 的查詢指令多了 LIMIT ? OFFSET ?
的訊息。
其中 520 減 500 的數值為 LIMIT 的數值,限制 20 筆;而 500 為 OFFSET 從第 500 筆資料開始查詢。
GROUP BY
範例 SQL 指令 ─ SELECT * FROM user GROUP BY user.name
如果要使用 GROUP BY
,則是使用 group_by()
。
1 | for r in session.query(User).group_by(User.name): |
HAVING
範例 SQL 指令 ─ SELECT user.name FROM user GROUP BY user.name HAVING count(user.id) > 2
與 GROUP BY 常一起使用的通常會有 HAVING
, COUNT()
, SUM()
, MAX()
… 等語法。
在 SQLAlchemy 中 HAVING 是接在 group_by()
後的 having()
方法,而 COUNT(), SUM(), MAX() 等函數則是由 SQLAlchemy 的 func
模組所實作,因此要使用 COUNT()
, SUM()
, MAX()
等函數須先 import func
模組。
1 | from sqlalchemy import func |
以下是結合 HAVING 及 COUNT() 的範例,用來列印出資料表內相同名字大於 2 個以上使用者名稱:
1 | for row in session.query(User).group_by(User.name).having(func.count(User.id) > 2): |
Alias(AS)
範例 SQL 指令 ─ SELECT u.id FROM user AS u
表格的 AS
別名用法是使用 aliasd()
方法,可以將 Mapped Class 及表格名稱以別名進行查詢。
此一方法需要 import aliasd
模組。
1 | from sqlalchemy.orm import aliased |
表格名稱別名範例:
1 | user_alias = aliased(User, name=‘user_alias‘) |
如果成功,應該會看到 SELECT 的過程多了FROM user AS user_alias
的訊息出現,如下所示。
1 | 2013-08-25 11:52:45,177 INFO sqlalchemy.engine.base.Engine SELECT user_alias.id AS user_alias_id, user_alias.name AS user_alias_name, user_alias.username AS user_alias_username, user_alias.password AS user_alias_password |
此外,欄位名稱也能夠使用 AS 的 SQL 語法,不過在 SQLAlchemy 中則是用 label()
來命名。
範例如下:
1 | for row in session.query(User.name.label(‘n‘)): |
計算查詢結果筆數
如要計算查詢結果筆數,使用 count()
即可。
例如:
1 | print( |
注意:此處的 count()
與 func.count()
不同
first, scalar(), one(), all()
除了查詢結果筆數之外,SQLAlchemy 也提供了一些方便的方法確認查詢結果或回傳查詢結果。
first()
只回傳第一筆結果,即使有多筆結果也只會回傳第一筆
scalar()
只回傳第一筆結果,查無結果則回傳 None ,若有查詢有多筆結果則會產生例外錯誤
scalar()
範例如下:
1 | try: |
one()
回傳一筆結果,如有多筆或查無結果的情況則會產生例外錯誤
one()
範例:
1 | try: |
all()
將所有結果匯出成一個 list 回傳
all()
範例:
1 | allres = session.query(User).all() |
參考資料:
http://docs.sqlalchemy.org/en/latest/
Python SQLAlchemy --2