HOWTO · Python

Python 中的 fetchall()

本教程演示 Python 的游標物件方法 fetchall() 以及如何在應用程式中使用它從 Python 中的資料庫中檢索資料。

本頁內容

在 Python 中查詢資料庫時,游標物件提供多種讀取結果資料列的方法。fetchall() 是其中最直接的一種:呼叫一次,就會回傳目前結果集中所有剩餘的資料列。本教學將說明 fetchall() 的回傳內容、如何在執行查詢後使用它、如何處理回傳的資料列,以及何時應改用其他擷取方法。

範例使用 Python 內建的 sqlite3 模組,因此不必安裝任何套件即可執行。其他 DB-API 驅動程式(例如 mysql-connector 或 psycopg)也有相同的 fetchall() 語意。

fetchall() 會回傳什麼

fetchall() 是 Python 資料庫 API(PEP 249)定義的游標物件方法。使用 execute() 執行查詢後,便可在該游標上呼叫它。此方法會擷取查詢結果集中所有剩餘的資料列,並以元組串列的形式回傳;每個元組存放一筆資料列的欄位值,順序與查詢選取的欄位相同。

實際使用時,有兩個細節需要留意:

  • 呼叫此方法會消耗結果集。fetchall() 讀完所有資料列後,在你使用 execute() 執行新查詢之前,再次呼叫它或呼叫 fetchone() 都不會取得更多資料。
  • 如果查詢沒有符合的資料列,fetchall() 會回傳空串列([]),而不是 None,因此可以直接安全地迭代結果。

執行查詢並擷取資料列

典型流程是:連線至資料庫、建立游標、執行 SELECT 查詢,然後在同一個游標上呼叫 fetchall()。以下範例會在記憶體中的 SQLite 資料庫建立小型 users 資料表、插入三筆資料、查詢這些資料,並印出 fetchall() 的回傳內容。範例程式中的姓名是英文,輸出會照原樣顯示。

本節中的程式碼以及顯示的標籤或輸出仍為英文。

import sqlite3

connection = sqlite3.connect(':memory:')
cursor = connection.cursor()

cursor.execute('CREATE TABLE users (id INTEGER PRIMARY KEY, name TEXT)')
cursor.executemany(
    'INSERT INTO users (name) VALUES (?)',
    [('Alice',), ('Bob',), ('Charlie',)]
)

cursor.execute('SELECT * FROM users ORDER BY id')
results = cursor.fetchall()
print(results)

connection.close()

輸出:

[(1, 'Alice'), (2, 'Bob'), (3, 'Charlie')]

results 的每個元素都是元組,例如 (1, 'Alice');其中的值依照選取欄位的順序排列。此時再次呼叫 fetchall() 會回傳 [],因為第一次呼叫已經讀完所有資料列。

處理回傳的資料列

fetchall() 回傳一般串列,因此你可以像處理其他串列一樣迭代、索引或切片。將每筆資料列元組拆解到具名變數中,能讓迴圈更容易閱讀,也能清楚表示各值所對應的欄位。

下一個範例會選取兩個欄位,並在逐筆印出資料時拆解每個元組。請注意,元組中的位置一律依照 SELECT 陳述式指定的欄位順序排列;元組本身不包含欄位名稱。輸出中的姓名與句子是程式碼內的英文文字,因此會維持英文。

本節中的程式碼以及顯示的標籤或輸出仍為英文。

import sqlite3

connection = sqlite3.connect(':memory:')
cursor = connection.cursor()

cursor.execute('CREATE TABLE users (id INTEGER PRIMARY KEY, name TEXT, age INTEGER)')
cursor.executemany(
    'INSERT INTO users (name, age) VALUES (?, ?)',
    [('Alice', 30), ('Bob', 25), ('Charlie', 35)]
)

cursor.execute('SELECT name, age FROM users ORDER BY id')
rows = cursor.fetchall()
for name, age in rows:
    print(f'{name} is {age} years old')

connection.close()

輸出:

Alice is 30 years old
Bob is 25 years old
Charlie is 35 years old

如果你需要欄位名稱而不是位置,可以在建立游標之前設定連線的 row_factory(例如 sqlite3.Row),或檢查 cursor.description 屬性。無論採用哪一種方式,擷取方法的運作方式都相同。

依結果大小選擇擷取方法

fetchall() 會將整個結果集讀入記憶體,並存成一個串列。結果集小到足以輕鬆容納時,這種方式很方便;但若查詢回傳數百萬筆資料列,驅動程式一次傳輸所有資料時,可能會耗盡記憶體或讓程式停頓。

結果集可能很大時,請改用逐步擷取:

  • fetchone() 會回傳下一筆資料列;沒有剩餘資料列時則回傳 None。直接迭代游標也會一次取得一筆資料列。
  • fetchmany(size) 每次最多回傳 size 筆資料列;到最後一批時,回傳數量可能較少。這樣就能分批處理資料,而不必一次將整個結果集載入串列。

以下範例使用 fetchmany(),每批處理三筆資料列,因此同一時間只會有一批資料保留在記憶體中。範例輸出中的標籤文字是程式碼內的英文內容,會維持英文。

本節中的程式碼以及顯示的標籤或輸出仍為英文。

import sqlite3

connection = sqlite3.connect(':memory:')
cursor = connection.cursor()

cursor.execute('CREATE TABLE events (id INTEGER PRIMARY KEY, label TEXT)')
cursor.executemany(
    'INSERT INTO events (label) VALUES (?)',
    [(f'event {i}',) for i in range(1, 8)]
)

cursor.execute('SELECT id, label FROM events ORDER BY id')
while True:
    batch = cursor.fetchmany(3)
    if not batch:
        break
    print(batch)

connection.close()

輸出:

[(1, 'event 1'), (2, 'event 2'), (3, 'event 3')]
[(4, 'event 4'), (5, 'event 5'), (6, 'event 6')]
[(7, 'event 7')]

對已透過 fetchall() 取得的串列進行切片,並不能節省記憶體,因為完整結果早已載入。若結果確實很大,也可以用更具選擇性的 WHERE 子句或 LIMIT 縮小查詢範圍。

處理空結果與資料庫錯誤

查詢沒有符合的資料列並不代表發生錯誤:fetchall() 只會回傳 []。迭代結果的程式碼可以自然處理這種情況;但如果要對結果索引或直接拆解,請先確認串列不是空的。

資料庫操作也可能失敗,例如資料表名稱拼錯、連線中斷或違反條件約束。這些情況可能讓 execute() 或擷取呼叫本身引發 sqlite3.Error 等錯誤。請將操作放在 try/except 中,並在 finally 關閉連線,以清楚回報錯誤並釋放資源。對 sqlite3 連線使用 with 區塊不能取代這項清理工作:它只會提交或回復目前交易,連線本身仍會保持開啟,因此仍須明確呼叫 close();也可以用 contextlib.closing() 包裝連線,確保它會關閉。

以下範例展示這兩種情況:沒有符合資料的 WHERE 子句會得到空串列,而查詢不存在的資料表則會捕捉並回報錯誤。範例輸出的錯誤訊息是程式碼產生的英文原文,會維持英文。

本節中的程式碼以及顯示的標籤或輸出仍為英文。

import sqlite3

connection = sqlite3.connect(':memory:')
try:
    cursor = connection.cursor()

    cursor.execute('CREATE TABLE users (id INTEGER PRIMARY KEY, name TEXT)')
    cursor.executemany(
        'INSERT INTO users (name) VALUES (?)',
        [('Alice',), ('Bob',)]
    )

    cursor.execute('SELECT * FROM users WHERE id = ?', (99,))
    print(cursor.fetchall())

    cursor.execute('SELECT * FROM missing_table')
    cursor.fetchall()
except sqlite3.Error as e:
    print(f'Database error: {e}')
finally:
    connection.close()

輸出:

[]
Database error: no such table: missing_table

except 子句應限定為驅動程式的錯誤型別(此處是 sqlite3.Error),不要捕捉所有例外,這樣無關的程式錯誤仍會顯現。

總而言之,fetchall() 是讀取查詢所有資料列最簡單的方法:它會回傳元組串列、在沒有符合資料時回傳空串列,並消耗結果集。結果量小到中等時可使用此方法;若資料量令人顧慮,則改用 fetchone() 或 fetchmany()。資料庫操作也應加入錯誤處理,讓問題能被看見,並確保連線關閉。