Kết nối với cơ sở dữ liệu Access bằng pyodbc: hướng dẫn đầy đủ và thực tế

Nếu bạn làm việc với dữ liệu hàng ngày và di chuyển giữa Cửa sổ và các hệ thống khác, bạn có thể đã tự hỏi làm thế nào để kết hợp những điều tốt nhất của Microsoft Access với tính linh hoạt của Python. Sự kết hợp của ODBC và mô-đun pyodbc cho phép bạn mở, đọc và ghi vào Truy cập cơ sở dữ liệu theo cách đơn giản và đa nền tảng, với cú pháp rất giống với cú pháp bạn sử dụng với SQLite hoặc MySQL.

Ngoài ra, không chỉ có Access: với cùng một mô-đun, bạn có thể giao tiếp với SQL Server (cục bộ hoặc đám mây) hoặc thậm chí các công cụ khác nếu bạn có trình điều khiển phù hợp. Trong hướng dẫn thực tế này, bạn sẽ thấy những gì cần cài đặt, cách tránh các lỗi phổ biến nhất (chẳng hạn như lỗi 32/64-bit) và bạn sẽ có các đoạn mã sẵn sàng để chạy., cho cả Access và ví dụ so sánh với SQL Server sử dụng các biến môi trường và truy vấn có tham số để hoạt động an toàn.

Microsoft Access là gì và nó phù hợp với ODBC như thế nào?

Microsoft Access là một công cụ cơ sở dữ liệu quan hệ đã trở thành một phần của bộ Office kể từ những năm 90. Sus cơ sở dữ liệu Chúng được lưu trữ trong các tệp có phần mở rộng như .mdb và .accdbvà ngoài công cụ, nó còn kết hợp một môi trường trực quan để tạo các bảng, trường, biểu mẫu và bảng liên quanMặc dù môi trường đồ họa chỉ tồn tại trong Windows, việc truy cập dữ liệu có thể được thực hiện từ các môi trường khác hệ điều hành sử dụng ODBC.

Chi tiết đó là chìa khóa: Vì nội dung nằm trong tệp .mdb/.accdb nên bạn chỉ cần biết đường dẫn của tệp và có trình điều khiển ODBC tương thích. để mở cơ sở dữ liệu từ Python. Vì vậy, cùng kịch bản Bạn có thể truy vấn, chèn và cập nhật dữ liệu vào Access một cách liền mạch.

Khi chúng ta nói về Access, IDE thường bị nhầm lẫn với công cụ. Hãy nhớ: IDE là phần trực quan, trong khi engine (JET/ACE) mới thực sự đọc và ghi dữ liệu.. pyodbc kết nối với engine thông qua trình điều khiển ODBC của hệ thống.

Phiên bản động cơ được sử dụng phụ thuộc vào bộ điều khiển máy tính của bạn. Ngày nay, thông thường là ACE (cho .accdb) và JET (cho .mdb cũ hơn), cả hai đều có thể truy cập được nếu bạn đã cài đặt đúng thư viện.

Khả năng tương thích của Python: pyodbc hoạt động với Python 2.6 trở lên và 3.x. Nghĩa là, nếu bạn đang làm việc với Python hiện đại (3.x), bạn sẽ được hỗ trợ.

ODBC là gì và tại sao nên sử dụng pyodbc

ODBC (Kết nối cơ sở dữ liệu mở) là một tiêu chuẩn được SQL Access Group tạo ra vào năm 1992 với mục tiêu rõ ràng: cho phép cùng một ứng dụng kết nối với các công cụ cơ sở dữ liệu khác nhau bằng trình điều khiển cụ thể. Bạn thay đổi trình điều khiển, bạn vẫn giữ nguyên cơ sở mã.

Với ODBC, chương trình của bạn không cần biết từng động cơ trông như thế nào bên trong; Bạn chỉ cần một chuỗi kết nối chỉ ra trình điều khiển và các tham số. truy cập (đường dẫn tệp trong Access, máy chủ và cơ sở dữ liệu trong SQL Server, v.v.).

pyodbc là cầu nối giữa Python và ODBC. Đây là một gói được cấp phép miễn phí của MIT, tương thích với Windows, Linux và macOS, cung cấp API rất giống với các trình kết nối phổ biến khác: bạn mở kết nối, tạo con trỏ, chạy truy vấn và thế là xong.

Nếu bạn đã từng sử dụng sqlite3 hoặc trình kết nối SQL khác trong Python trước đây, bạn sẽ thấy quen thuộc. pyodbc hỗ trợ các tham số với dấu ? và xử lý kết quả bằng fetchone/fetchall/fetchvalcũng như tích hợp liền mạch với hệ sinh thái Python.

Một điểm cộng quan trọng: Với cùng một pyodbc bạn có thể giao tiếp với Access và SQL Server, chỉ cần thay đổi chuỗi kết nối và trình điều khiển ODBC. Điều này giúp tiết kiệm thời gian và giảm độ phức tạp trong các dự án kết hợp.

Điều kiện tiên quyết và khả năng tương thích phiên bản

Trước khi bắt đầu, hãy đảm bảo môi trường của bạn nhất quán. Nguyên tắc vàng với ODBC và Access là kiến ​​trúc phải phù hợpNếu trình điều khiển Access ODBC của bạn là 32-bit, trình thông dịch Python của bạn phải là 32-bit, ngay cả khi Windows của bạn là 64-bit.

Trên Windows, nếu bạn đã cài đặt Access, trình điều khiển ODBC ACE thường có sẵn. Nếu không, bạn có thể cài đặt gói trình điều khiển của Microsoft tương ứng với kiến ​​trúc của bạn (x86 hoặc x64).

Trên Linux và các thiết bị tương tự Unix khác, Microsoft không phân phối chính thức trình điều khiển Access. Có những giải pháp thay thế như Công cụ MDB hoặc trình điều khiển Easysoft Access ODBC., cho phép đọc/ghi vào các tệp MDB/ACCDB thông qua ODBC.

Đối với Python, pyodbc được hỗ trợ trên 2.6+ và 3.x, mặc dù 3.x được khuyến nghị. Bạn có thể cài đặt nhiều trình thông dịch song song mà không gặp vấn đề gì. để cô lập các dự án và giải quyết các vấn đề về khả năng tương thích.

Nếu bạn không muốn chạm vào thiết bị của mình, bạn có một lựa chọn khác: Mở dự án dưới dạng devcontainer với GitHub Codespaces và làm việc trong môi trường sẵn sàng sử dụng, lý tưởng cho việc thử nghiệm nhanh hoặc trình diễn.

Cài đặt trình điều khiển ODBC cho Access

Windows là nơi lưu trữ tự nhiên của Access. Nếu bạn đã cài đặt Microsoft Access, trình điều khiển ODBC có thể đã có sẵn.Nếu không, hãy tải xuống và cài đặt Gói trình điều khiển Microsoft Access (ACE) phù hợp với kiến ​​trúc của bạn.

Lưu ý: Nếu trình điều khiển của bạn là 32-bit, hãy sử dụng Python 32-bit; nếu trình điều khiển của bạn là 64-bit, hãy sử dụng Python 64-bit. Việc trộn lẫn các kiến ​​trúc gây ra lỗi như "không tìm thấy trình điều khiển" hoặc không mở được kết nối.

Trên Linux, bạn phải lựa chọn giải pháp thay thế. MDB Tools cung cấp các tiện ích và thư viện để làm việc với định dạng MDB.và Easysoft có trình điều khiển ODBC cho Access cho phép kết nối từ Python qua ODBC.

Nếu bạn định triển khai ứng dụng của mình trong nhiều môi trường, hãy lập kế hoạch trước cho phần này (ví dụ: cách cơ sở dữ liệu nhỏ gọn và sửa chữa). Đảm bảo trình điều khiển tồn tại trên tất cả các mục tiêu và ghi lại chuỗi kết nối cho mỗi nền tảng.

Một cách nhanh chóng để liệt kê các trình điều khiển ODBC đã cài đặt là sử dụng chính API ODBC. Nó sẽ giúp bạn xác thực rằng “Trình điều khiển Microsoft Access (*.mdb, *.accdb)” đang hoạt động trước khi lập trình.

Cài đặt pyodbc và các tiện ích

Cách thuận tiện nhất để cài đặt pyodbc hiện nay là sử dụng pip. mở một cái thiết bị đầu cuối và thực hiện:

pip install pyodbc

Trên Windows, nếu bạn muốn, có những trình cài đặt giúp mọi việc dễ dàng hơn. Trên các hệ thống khác, bạn cũng có thể cài đặt từ các nguồn với trình cài đặt cổ điển:

python setup.py install

Để xác minh quá trình cài đặt diễn ra tốt đẹp, hãy thử nhập mô-đun vào bảng điều khiển Python. Nếu không có lỗi nào xuất hiện khi chạy “import pyodbc”, thì gói đã được cài đặt đúng.:

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

Một biện pháp tốt khác là quản lý bí mật và chuỗi kết nối bằng các biến môi trường. Gói python-dotenv cho phép bạn tải các biến từ tệp .env. mà không tiết lộ thông tin xác thực trong mã:

pip install python-dotenv

Nếu bạn muốn xác nhận những gói nào đã cài đặt, hãy sử dụng công cụ dòng lệnh. lệnh từ PyPI. Một "danh sách pip" đơn giản sẽ hiển thị cho bạn hàng tồn kho hiện tại:

pip list

Tạo tập lệnh đầu tiên với pyodbc và Access

Chúng ta hãy xem một ví dụ đầy đủ với Access. Mục tiêu: mở tệp .accdb, chèn một hàng có tham số và đọc tất cả các hàng để hiển thị nội dung của nó.

Đầu tiên, hãy xác định tên trình điều khiển Access và đường dẫn tệp cơ sở dữ liệu. Chúng ta sẽ sử dụng os.getcwd() để xây dựng đường dẫn tuyệt đối theo cách đơn giản:

# -*- 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()

Chuẩn bị một số dữ liệu thử nghiệm và tạo một đoạn chèn có tham số. Dấu ? ngăn chặn việc nối chuỗi và bảo vệ bạn khỏi tấn công SQL injection.:

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()

Sau đó, hãy tham khảo bảng và cuộn qua các kết quả. Với fetchall bạn sẽ có được danh sách các hàng mà bạn có thể in hoặc chuyển đổi:

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()

Mẫu làm việc này (kết nối, trỏ chuột, thực thi, xác nhận và đóng) là mẫu thông thường với pyodbc. Nếu có lỗi gì xảy ra, hãy phát hiện ngoại lệ và đóng tài nguyên trong khối finally. để ngăn chặn rò rỉ hoặc sập tệp .accdb/.mdb.

Kết nối với SQL Server bằng pyodbc (so sánh thực tế)

Để bạn có thể hiểu rõ hơn về tính linh hoạt của pyodbc, chúng ta hãy tạo một ví dụ nhỏ so với SQL Server. Chúng tôi sẽ sử dụng các biến môi trường với python-dotenv và cơ sở dữ liệu với lược đồ AdventureWorks2022 Lightweight. (có hiệu lực trên SQL Server tại chỗ, Cơ sở dữ liệu SQL Azure hoặc Cơ sở dữ liệu SQL Fabric).

1) Tạo một tệp có tên app.py và thêm một chuỗi tài liệu mô-đun. Điều này giúp ghi lại mục đích của tập lệnh từ chính mã.:

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

2) Nhập các lệnh cần thiết: ​​load_dotenv, getenv và connect. Điều này sẽ tải chuỗi kết nối từ .env và mở kết nối với pyodbc:

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

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

3) Trong cùng thư mục, tạo tệp .env có chuỗi kết nối. Ví dụ sử dụng trình điều khiển ODBC 18 cho SQL Server với mã hóa:

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

4) Chạy truy vấn để lấy dữ liệu mẫu (TOP 5). Con trỏ cho phép bạn lặp lại các bản ghi một cách dễ dàng:

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) Chèn một hàng dưới dạng giao dịch và lấy mã định danh đã tạo. Chúng tôi sử dụng các tham số cho INSERT và fetchval() để đọc cột đầu tiên được trả về:

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()

So sánh này cho thấy việc chuyển đổi từ Access sang SQL Server chủ yếu là vấn đề về chuỗi kết nối. API pyodbc giống nhau: kết nối, con trỏ, thực thi, tìm nạp, cam kết và đóngNếu máy của bạn chưa cài đặt Python, bạn có thể khởi chạy bản demo nhanh trong vùng chứa dev bằng Codespaces.

Thực hành tốt, bảo mật và hiệu suất

• Luôn luôn tham số hóa câu lệnh của bạn. pyodbc sử dụng ? làm ký tự giữ chỗ, do đó tránh được việc nối chuỗi và giảm thiểu lỗi SQL injection.. Không xây dựng truy vấn bằng cách nối các giá trị do người dùng cung cấp.
• Quản lý thông tin xác thực và tuyến đường bên ngoài mã. .env (hoặc biến môi trường hệ thống) là hoàn hảo để lưu trữ chuỗi kết nối và các khóa nhạy cảm khác. Tránh tải bí mật lên hệ thống kiểm soát phiên bản.
• Xác nhận hoặc hoàn nguyên thay đổi. Sử dụng conn.commit() để duy trì và xử lý lỗi bằng try/except và nếu phù hợp, conn.rollback()Điều này đặc biệt quan trọng khi bạn thực hiện nhiều thao tác nối tiếp nhau.
• Đóng tài nguyên ngay cả khi có ngoại lệ. Triển khai các khối try/finally hoặc sử dụng trình quản lý ngữ cảnh nếu phù hợp. Access sẽ khóa tệp khi có kết nối mở, do đó, việc giải phóng quyền xử lý tệp là rất quan trọng.
• Hãy chú ý đến kiến ​​trúc 32/64 bit. Nếu bạn thấy lỗi lạ hoặc trình điều khiển "không hiển thị", hãy thử trình thông dịch 32 bit khi trình điều khiển là 32 bit.. Bạn có thể sử dụng nhiều phiên bản Python trên cùng một máy tính.
• Tìm hiểu về những đặc điểm riêng của Access. Định dạng ngày tháng và số tùy thuộc vào cài đặt khu vực.và một số kiểu dữ liệu có thể yêu cầu chuyển đổi cẩn thận (ví dụ: boolean và tiền tệ).
• Quyết định xem bạn sẽ sử dụng chuỗi kết nối có DSN hay không có DSN. Chuỗi không có DSN rất thiết thực cho việc triển khai tự động, vì chúng không yêu cầu phải đăng ký DSN trên mỗi máy chủ.
• Đối với khối lượng lớn hơn hoặc nhiều người dùng cùng lúc, hãy cân nhắc đến công cụ này. Access lý tưởng cho máy tính để bàn hoặc các tình huống phòng ban; nếu bạn mở rộng quy mô, tốt nhất là nên di chuyển sang SQL Server và giữ pyodbc làm trình kết nối.

Xử lý sự cố thường gặp

• Tài xế không xuất hiện. Kiểm tra xem trình điều khiển Access đã được cài đặt chưa và kiến ​​trúc của nó có phù hợp với Python của bạn không.. Trên Windows, hãy mở Trình quản trị ODBC 32-bit hoặc 64-bit tùy theo nhu cầu.
• Lỗi khi mở tệp .accdb/.mdb. Kiểm tra đường dẫn tuyệt đối (sử dụng os.path.join và os.getcwd()) và quyền đọc/ghiNếu tệp đang được sử dụng, nó có thể bị khóa bởi một ứng dụng khác; đối với các trường hợp hỏng, hãy xem Cách sửa chữa và ngăn ngừa các căn cứ bị hư hỏng.
• Có lỗi khi chèn ngày tháng hoặc số thập phân. Đặt định dạng theo cài đặt khu vực và nếu có thể, hãy truyền nó làm tham số để trình điều khiển thực hiện chuyển đổi.. Tránh xa định dạng ngày tháng dưới dạng chuỗi khi không cần thiết.
• Sự không tương thích trong Linux. Nếu bạn làm việc với Công cụ MDB hoặc trình điều khiển bên thứ ba, xác thực các hoạt động mà họ hỗ trợ (đọc, ghi, các loại nâng cao)Không phải tất cả trình điều khiển đều cung cấp phạm vi bảo vệ giống như ACE trên Windows.
• Mất hiệu suất. Nhóm các hoạt động thành các giao dịch và giảm số lần truy cập vào cơ sở dữ liệu. Sử dụng executemany để chèn số lượng lớn khi có thể và tránh mở/đóng các kết nối trong các vòng lặp chặt chẽ.
• Xác minh gói hàng. Nếu bạn không chắc chắn về cài đặt, "pip list" sẽ hiển thị cho bạn pyodbc và python-dotenv. Để chẩn đoán, hãy in pyodbc.version và danh sách trình điều khiển có sẵn trên hệ thống.
• Hướng dẫn và tài liệu tham khảo. Tài liệu chính thức của pyodbc, kho lưu trữ mã của nó và gói trên PyPI Chúng là điểm khởi đầu để đào sâu hơn và báo cáo sự cố hoặc đóng góp ý tưởng.
• Học tập và cộng đồng. Nếu bạn mới bắt đầu hoặc có thắc mắc, Reddit có các cộng đồng như r/Python và r/LearnPython nơi bạn có thể trả lời các câu hỏi và cập nhật tin tức và các gói dịch vụ.

Với tất cả những điều trên, bạn có một hướng đi rõ ràng: cài đặt trình điều khiển ODBC phù hợp, thêm pyodbc (và python-dotenv nếu bạn muốn quản lý bí mật), xác thực kiến ​​trúc của bạn phù hợp và bắt đầu viết các truy vấn có tham số. Access hoạt động tốt khi bạn cần một tệp cục bộ có các bảng SQL và pyodbc cung cấp cho bạn cùng một API nếu bạn quyết định chuyển sang SQL Server vào ngày mai.; theo cách này, bạn không cần phải làm lại mã, chỉ cần điều chỉnh chuỗi kết nối và nó sẽ chạy.