使用 pyodbc 連接 Access 資料庫：完整實用指南

如果你每天都要處理數據，而且在 Windows 和其他系統，你可能想知道如何結合最好的 Microsoft Access 具有 Python 的靈活性。 ODBC 和 pyodbc 模組的組合可讓您開啟、讀取和寫入 存取資料庫 以簡單和多平台的方式，其語法與 SQLite 或 MySQL 使用的語法非常相似。

另外，它不僅僅是 Access：使用相同的模組，您可以與 SQL Server（本地或雲端）對話，如果您有正確的驅動程序，甚至可以與其他引擎對話。 在本實用指南中，您將了解需要安裝的內容、如何避免最常見的錯誤（例如 32/64 位元錯誤），並且可以執行程式碼片段。，既適用於 Access，也適用於使用環境變數和參數化查詢來安全工作的 SQL Server 的比較範例。

什麼是 Microsoft Access 以及它如何與 ODBC 配合？

Microsoft Access 是一種關聯式資料庫引擎，自 90 年代以來一直是 Office 套件的一部分。 蘇 數據庫 它們儲存在副檔名為 .mdb 和 .accdb 的檔案中，除了引擎之外，它還包含一個視覺化環境來建立表格、欄位、表單和 關聯表。雖然圖形環境只存在於 Windows 中，但可以從其他 OS 使用 ODBC。

這個細節很關鍵： 由於內容位於 .mdb/.accdb 檔案中，因此知道其路徑並擁有相容的 ODBC 驅動程式就足夠了。 從 Python 開啟資料庫。因此，同樣的 腳本 您可以無縫地查詢、插入和更新 Access 資料。

當我們談論 Access 時，IDE 常常與引擎混淆。 請記住：IDE 是視覺化部分，而引擎（JET/ACE）才是實際讀取和寫入資料的部分。.pyodbc透過系統的ODBC驅動程式連接到引擎。

所使用的引擎版本取決於您的電腦的控制器。 如今，通常是 ACE（用於 .accdb）和 JET（用於較舊的 .mdb），如果您安裝了正確的庫，則都可以存取。

Python相容性：pyodbc 適用於 Python 2.6+ 和 3.x。也就是說，如果您使用的是現代 Python（3.x），則可以使用。

什麼是 ODBC 以及為什麼使用 pyodbc

ODBC（開放資料庫連線）是 SQL Access Group 在 1992 年創建的標準，其目標明確： 允許同一應用程式使用特定的驅動程式連接到不同的資料庫引擎。您更改驅動程序，但保留相同的程式碼庫。

使用 ODBC，您的程式不需要知道每個引擎內部是什麼樣子； 您只需要一個指示驅動程式和參數的連接字串。 存取（Access 中的檔案路徑、SQL Server 中的伺服器和資料庫等）。

pyodbc 是 Python 和 ODBC 之間的橋樑。 它是一個免費的 MIT 許可軟體包，與 Windows 相容， Linux 和 macOS，它公開了一個與其他流行連接器非常相似的 API：打開一個連接，創建一個遊標，運行查詢，就這樣。

如果您之前使用過 Python 中的 sqlite3 或其他 SQL 連接器，那麼您會非常熟悉。 pyodbc 支援帶有 ? 標記的參數，並使用 fetchone/fetchall/fetchval 處理結果以及與 Python 生態系統的無縫整合。

一個重要的優點： 使用相同的 pyodbc，您可以與 Access 和 SQL Server 對話，只需更改連接字串和 ODBC 驅動程式。這可以節省時間並降低混合項目的複雜性。

先決條件和版本相容性

在開始之前，請確保您的環境一致。 ODBC 和 Access 的黃金法則是架構必須匹配如果您的 Access ODBC 驅動程式是 32 位，則您的 Python 解釋器也必須是 32 位，即使您的 Windows 是 64 位元。

在 Windows 上，如果您已經安裝了 Access，通常可以使用 ODBC ACE 驅動程式。 如果沒有，您可以安裝微軟驅動程式套件 與您的架構（x86 或 x64）相對應。

在 Linux 和其他類 Unix 裝置上，Microsoft 並未正式分發 Access 驅動程式。 還有其他選擇，例如 MDB Tools 或 Easysoft Access ODBC 驅動程式。，它可以透過 ODBC 讀取/寫入 MDB/ACCDB 檔案。

至於 Python，pyodbc 在 2.6+ 和 3.x 上受支持，但建議使用 3.x。 您可以同時安裝多個解釋器而不會出現任何問題。 隔離專案並解決相容性問題。

如果您不想觸摸您的設備，您還有另一種選擇： 使用 GitHub Codespaces 將專案作為 devcontainer 打開 並在即用型環境中工作，非常適合快速測試或演示。

安裝 Access 的 ODBC 驅動程式

Windows 是 Access 的天然家。 如果您安裝了 Microsoft Access，則 ODBC 驅動程式可能已經存在。如果沒有，請下載並安裝適合您的架構的 Microsoft Access 驅動程式套件 (ACE)。

請記住：如果您的驅動程式是 32 位，請使用 32 位元 Python；如果您的驅動程式是 64 位，請使用 64 位元 Python。 混合架構會導致諸如“找不到驅動程式”或無法打開連接等錯誤.

在 Linux 上，您必須選擇替代方案。 MDB 工具提供用於處理 MDB 格式的實用程式和程式庫。，並且 Easysoft 有一個用於 Access 的 ODBC 驅動程序，可以透過 ODBC 從 Python 進行連接。

如果您要在多個環境中部署應用程序，請提前規劃此部分（例如，如何 壓縮和修復資料庫). 確保驅動程式存在於所有目標上並記錄連接字串 針對每個平台。

列出已安裝的 ODBC 驅動程式的快速方法是使用 ODBC API 本身。 它將幫助您驗證「Microsoft Access Driver (*.mdb, *.accdb)」是否可執行 在編程之前。

安裝 pyodbc 和實用程式

目前安裝 pyodbc 最方便的方法是使用 pip。 打開一個 終端 並執行：

pip install pyodbc

在 Windows 上，如果您願意，可以使用安裝程式讓生活變得更輕鬆。 在其他系統上，您也可以使用經典的來源進行安裝:

python setup.py install

為了驗證安裝是否順利，請嘗試在 Python 控制台中匯入模組。 如果執行「import pyodbc」時沒有出現錯誤，則表示套件安裝正確。:

python -c "import pyodbc; print(pyodbc.version)"

另一個好的做法是使用環境變數來管理秘密和連接字串。 python-dotenv 套件可讓您從 .env 檔案載入變數。 無需在程式碼中暴露憑證：

pip install python-dotenv

如果您想確認已安裝哪些軟體包，請使用命令列工具。 命令 來自 PyPI。 一個簡單的“點數列表”將顯示當前庫存:

pip list

使用 pyodbc 和 Access 建立第一個腳本

讓我們來看一個帶有 Access 的完整範例。 目標：開啟 .accdb，插入一行帶有參數的行並讀取所有行 顯示其內容。

首先，定義Access驅動程式名稱和資料庫檔案路徑。 我們將使用 os.getcwd() 以簡單的方式建立絕對路徑:

# -*- coding: utf-8 -*-
import os
import pyodbc

DRIVER_NAME = "Microsoft Access Driver (*.mdb, *.accdb)"
DB_PATH = os.path.join(os.getcwd(), "database.accdb")

# Establecer conexión (cadena DSN-less)
conn = pyodbc.connect("Driver={%s};DBQ=%s;" % (DRIVER_NAME, DB_PATH))

cursor = conn.cursor()

準備一些測試資料並建立參數化插入。 ? 標記可防止字串連接並保護您免受 SQL 注入。:

nombre = "Guido"
apellido = "van Rossum"
telefono = "No disponible"
fecha = "03/10/2014"   # Ajusta el formato a tu configuración regional en Access
remuneracion = "$ 1.300"
paga_pendiente = False

cursor.execute(
    u"INSERT INTO empleados (nombre, apellido, teléfono, fecha, remuneración, paga_pendiente) "
    u"VALUES (?, ?, ?, ?, ?, ?)",
    nombre, apellido, telefono, fecha, remuneracion, paga_pendiente
)

conn.commit()

然後，查閱表格並捲動結果。 使用 fetchall 可以得到可以列印或轉換的行列表:

rows = cursor.execute("SELECT * FROM empleados").fetchall()

if rows:
    for r in rows:
        print(r)
else:
    print("La tabla no contiene datos.")

cursor.close()
conn.close()

這種工作模式（連接、遊標、執行、提交和關閉）是 pyodbc 的常用模式。 如果發生故障，則在 finally 區塊中捕獲異常並關閉資源。 以防止 .accdb/.mdb 檔案洩漏或崩潰。

使用 pyodbc 連線 SQL Server（實際比較）

為了讓您了解 pyodbc 的多功能性，讓我們針對 SQL Server 建立一個小範例。 我們將使用帶有 python-dotenv 的環境變數和帶有 AdventureWorks2022 Lightweight 模式的資料庫。 （在本機 SQL Server、Azure SQL 資料庫或 Fabric SQL 資料庫上有效）。

1）建立一個名為 app.py 的檔案並新增模組文件字串。 這有助於從程式碼本身記錄腳本的意圖。:

"""Conecta a una base de datos SQL con pyodbc"""

2）導入必要的：load_dotenv、getenv和connect。 這將從 .env 加載連接字串並使用 pyodbc 打開連接:

from os import getenv
from dotenv import load_dotenv
from pyodbc import connect

load_dotenv()
conn = connect(getenv("SQL_CONNECTION_STRING"))

3）在同一目錄中，建立一個包含連接字串的.env檔。 使用加密的 SQL Server ODBC 驅動程式 18 的範例:

SQL_CONNECTION_STRING="Driver={ODBC Driver 18 for SQL Server};Server=<server_name>;Database={<database_name>};Encrypt=yes;TrustServerCertificate=no;Authentication=ActiveDirectoryInteractive"

4）執行查詢以檢索樣本資料（TOP 5）。 遊標可讓您輕鬆迭代記錄:

SQL_QUERY = (
    "SELECT TOP 5 "
    "c.CustomerID, c.CompanyName, COUNT(soh.SalesOrderID) AS OrderCount "
    "FROM SalesLT.Customer AS c "
    "LEFT OUTER JOIN SalesLT.SalesOrderHeader AS soh ON c.CustomerID = soh.CustomerID "
    "GROUP BY c.CustomerID, c.CompanyName "
    "ORDER BY OrderCount DESC;"
)

cursor = conn.cursor()
cursor.execute(SQL_QUERY)

for r in cursor.fetchall():
    print(f"{r.CustomerID}\t{r.OrderCount}\t{r.CompanyName}")

5）插入一行作為事務並檢索產生的標識符。 我們使用 INSERT 和 fetchval() 的參數來讀取傳回的第一列:

from random import randrange

product_number = randrange(1000)

SQL_INSERT = (
    "INSERT SalesLT.Product (Name, ProductNumber, StandardCost, ListPrice, SellStartDate) "
    "OUTPUT INSERTED.ProductID "
    "VALUES (?, ?, ?, ?, CURRENT_TIMESTAMP)"
)

cursor.execute(
    SQL_INSERT,
    (
        f"Example Product {product_number}",
        f"EXAMPLE-{product_number}",
        100,
        200,
    ),
)

new_id = cursor.fetchval()
print(f"Inserted Product ID : {new_id}")

conn.commit()

cursor.close()
conn.close()

透過比較可以看出，從 Access 切換到 SQL Server 主要是連接字串的問題。 pyodbc API 相同：連線、遊標、執行、取得、提交和關閉如果您的機器上沒有安裝 Python，您可以使用 Codespaces 在開發容器中啟動快速示範。

良好實踐、安全性和性能

• 始終參數化你的語句。 pyodbc 使用 ? 作為佔位符，從而避免字串連接並減輕 SQL 注入。. 不要透過連接使用者提供的值來建立查詢。
• 管理代碼之外的憑證和路由。 .env（或系統環境變數）非常適合儲存連接字串 以及其他敏感密鑰。避免將機密資訊上傳至版本控制。
• 確認或恢復更改。 使用 conn.commit() 來持久化，並使用 try/except 處理錯誤，如果合適，也可以使用 conn.rollback()當您執行多個鍊式操作時，這一點尤其重要。
• 即使有例外也要關閉資源。 如果合適，實作 try/finally 區塊或使用上下文管理器. 當有開啟的連線時，Access 會鎖定文件，因此釋放文件句柄至關重要。
• 觀察 32/64 位元架構。 如果您看到奇怪的錯誤或驅動程式“未顯示”，請在驅動程式為 32 位元時嘗試使用 32 位元解釋器。。在同一台電腦上安裝多個版本的Python是沒問題的。
• 了解 Access 的特性。 日期和數字的格式取決於區域設定。，並且某些資料類型可能需要仔細轉換（例如布林值和貨幣）。
• 決定是否使用 DSN 或無 DSN 的連接字串。 無 DSN 鏈對於自動化部署非常實用，因為它們不需要在每個伺服器上註冊 DSN。
• 對於更高的容量或多個並髮用戶，請考慮使用引擎。 Access 非常適合桌面或部門場景；如果要擴展，最好遷移到 SQL Server 並保留 pyodbc 作為連接器。

常見問題故障排除

• 司機沒有出現。 檢查 Access 驅動程式是否已安裝，以及其架構是否與您的 Python 的架構相符。. 在 Windows 上，根據需要開啟 32 位元或 64 位元 ODBC 管理員。
• 開啟檔案 .accdb/.mdb 時發生錯誤。 檢查絕對路徑（使用 os.path.join 和 os.getcwd()）和讀取/寫入權限如果檔案正在使用中，則它可能被另一個應用程式鎖定；對於損壞的情況，請參閱 如何修復和預防基地受損.
• 插入日期或小數時發生錯誤。 將格式設為區域設置，如果可能的話，將其作為參數傳遞給驅動程式進行轉換。。 避免 格式 當不需要時將日期作為字串。
• Linux 中的不相容性。 如果您使用 MDB 工具或 驅動程序 第三方，驗證它們支援哪些操作（讀取、寫入、進階類型）並非所有驅動程式都提供與 Windows 上的 ACE 相同的覆蓋範圍。
• 性能損失。 將操作分組到事務中並減少對資料庫的訪問. 在可行的情況下使用 executemany 進行批次插入，並避免在緊密循環中開啟/關閉連線。
• 包裹驗證。 如果您不確定安裝，「pip list」將顯示 pyodbc 和 python-dotenv. 為了進行診斷，列印 pyodbc.version 和系統上可用的驅動程式清單。
• 指南和參考。 官方 pyodbc 文件、其程式碼庫以及 PyPI 上的包 它們是深入研究和報告事件或貢獻想法的起點。
• 學習和社區。 如果你剛開始學習或有疑問，Reddit 有像 r/Python 和 r/LearnPython 這樣的社區 您可以在這裡回答問題並了解最新的新聞和包裹資訊。

完成以上所有操作後，您就有了一條清晰的路徑：安裝適當的 ODBC 驅動程式、新增 pyodbc（如果您想管理機密，則新增 python-dotenv）、驗證您的架構是否匹配，然後開始編寫參數化查詢。 當您需要包含 SQL 表的本機檔案時，Access 非常有用，如果您決定明天跳到 SQL Server，pyodbc 會為您提供相同的 API。；這樣您就不需要重寫程式碼，只需調整連接字串就可以運行。