使用 Python 和 pyodbc 驅動程式連接和查詢 Azure SQL 資料庫

發行項
03/21/2024

本快速入門說明如何在 Azure SQL 資料庫中將應用程式連線到資料庫，並使用 .NET 和 Python SQL 驅動程式 - pyodbc 執行查詢。本快速入門遵循建議的無密碼方法與資料庫連線。您可以在無密碼中樞上深入了解無密碼連線。

必要條件

Azure 訂用帳戶。
帶 Microsoft Entra 驗證的 Azure SQL 資料庫。您可以使用建立資料庫快速入門建立資料庫。
最新版的 Azure CLI。
帶 Python 延伸模組的 Visual Studio Code。
Python 3.8 或更高版本。如果您使用的是 Linux 用戶端機器，請參閱安裝 ODBC 驅動程式。

設定資料庫

Azure SQL 資料庫的安全無密碼連線需要特定資料庫設定。確認 Azure 中邏輯伺服器上的下列設定，以在本機和託管環境中正確連線到 Azure SQL 資料庫：

針對本機開發連線，請確定您的邏輯伺服器已設定為允許本機電腦 IP 位址及其他 Azure 服務連線：
- 瀏覽至伺服器的 [網路] 頁面。
- 切換 [選取的網路]選項按鈕以顯示其他組態選項。
- 選取 [新增用戶端 IPv4 位址 (xx.xx.xx.xx)]，以新增防火牆規則，啟用來自本機電腦 IPv4 位址的連線。或者您也可以選取 [+ 新增防火牆規則] 來輸入您選擇的特定 IP 位址。
- 確定選取 [允許 Azure 服務和資源存取此伺服器] 核取方塊。
  警告
  
  對於實際執行場景，出於安全考量，不建議啟用 [允許 Azure 服務和資源存取此伺服器] 設定。實際應用程式應該實作更安全的方法，例如更強大的防火牆限制或虛擬網路設定。
  
  可以存取下列資源，深入了解資料庫安全設定：
  - 設定 Azure SQL 資料庫防火牆規則。
  - 設定具有私人端點的虛擬網路。
伺服器也必須啟用 Microsoft Entra 驗證，並已指派 Microsoft Entra 管理帳戶。針對本機開發連線，Microsoft Entra 管理帳戶應該也可以用於本機登入 Visual Studio 或 Azure CLI 的帳戶。您可以在邏輯伺服器的 [Microsoft Entra ID] 頁面確認伺服器是否已啟用 Microsoft Entra 驗證。
如果您使用個人 Azure 帳戶，請確定您已針對 Azure SQL 資料庫設定 Microsoft Entra，將您的帳戶指派為伺服器管理員。如果您使用公司帳戶，很可能已為您設定好 Microsoft Entra ID。

建立專案

使用 Visual Studio Code 建立新的 Python 專案。

開啟 Visual Studio Code，然後為專案建立新資料夾，並將目錄變更為它。
```
mkdir python-sql-azure
cd python-sql-azure
```

建立應用程式的虛擬環境。

Windows
macOS/Linux

py -m venv .venv
.venv\scripts\activate

python3 -m venv .venv
source .venv/bin/activate

建立名為 app.py 的新 Python 檔案。

安裝 pyodbc 驅動程式

若要使用 Python 連線到 Azure SQL 資料庫，請安裝 pyodbc 驅動程式。此套件可作為資料提供者，用來連線到資料庫、執行命令，以及擷取結果。在本快速入門中，您也會安裝 flask、 uvicorn和 pydantic 套件，以建立和執行 API。

如需在所有作業系統上安裝 pyodbc 驅動程式的詳細資料和特定指示，請參閱設定 pyodbc Python 開發的開發環境。

建立包含以下行的 requirements.txt 檔案：

pyodbc
fastapi
uvicorn[standard]
pydantic
azure-identity

安裝需求。
```
pip install -r requirements.txt
```

設定本機連接字串

若要進行本機開發並連線至 Azure SQL 資料庫，請新增下列 AZURE_SQL_CONNECTIONSTRING 環境變數。將 <database-server-name> 和 <database-name> 預留位置替換為您自己的值。 Bash 命令介面的環境變數範例將顯示。

若您在本機執行，互動式驗證會提供無密碼選項。建議您使用此選項，因為您不需要在本機系統上儲存或管理驗證秘密。

互動式驗證
SQL 驗證

在 Windows 中，Microsoft Entra 互動式驗證可以使用 Microsoft Entra 多重要素驗證技術來設定連線。在此模式中，透過提供登入 ID，系統會觸發 Azure 驗證對話方塊，並允許使用者輸入密碼以完成連線。

export AZURE_SQL_CONNECTIONSTRING='Driver={ODBC Driver 18 for SQL Server};Server=tcp:<database-server-name>.database.windows.net,1433;Database=<database-name>;Encrypt=yes;TrustServerCertificate=no;Connection Timeout=30'

如需詳細資訊，請參閱搭配使用 Microsoft Entra ID 與 ODBC 驅動程式。如果您使用此選項，請找到提示您輸入認證的視窗。

可以利用使用者名稱與密碼直接向 SQL Server 執行個體驗證。

export AZURE_SQL_CONNECTIONSTRING='Driver={ODBC Driver 18 for SQL Server};Server=tcp:<database-server-name>.database.windows.net,1433;Database=<database-name>;UID=<user-name>;PWD=<user-password>;Encrypt=yes;TrustServerCertificate=no;Connection Timeout=30'

警告

管理包含使用者名稱、密碼、存取金鑰等祕密的連接字串時，請小心。這些祕密不應提交至原始檔控制，或存放於非預期使用者可能存取的不安全位置。本機開發期間，在實際應用程式上，您通常會連線到不需要儲存祕密或直接連線到 Azure 的本機資料庫。

您可以從 Azure 入口網站取得建立連接字串的詳細資訊：

移至 Azure SQL Server，選取 [SQL Database] 頁面以找到資料庫名稱，然後選取資料庫。
在資料庫上，移至 [連線字串] 頁面以取得連接字串資訊。在 [ODBC] 索引標籤下查找。

注意

如果已安裝 Azure Arc，並將其與您的 Azure 訂用帳戶關聯，也可以使用為部署至 App Service 之應用程式顯示的受控識別方法。

新增程式碼以連線至 Azure SQL 資料庫

在專案資料夾中，建立 app.py 檔案並新增範例程式碼。此程式碼會建立一個 API，以：

從環境變數擷取 Azure SQL 資料庫連接字串。
啟動期間在資料庫中建立 Persons 資料表 (僅限測試場景)。
定義從資料庫擷取全部 Person 記錄的函數。
定義從資料庫擷取一條 Person 記錄的函數。
定義函數以將新 Person 記錄新增至資料庫。

import os
import pyodbc, struct
from azure import identity

from typing import Union
from fastapi import FastAPI
from pydantic import BaseModel

class Person(BaseModel):
    first_name: str
    last_name: Union[str, None] = None
    
connection_string = os.environ["AZURE_SQL_CONNECTIONSTRING"]

app = FastAPI()

@app.get("/")
def root():
    print("Root of Person API")
    try:
        conn = get_conn()
        cursor = conn.cursor()

        # Table should be created ahead of time in production app.
        cursor.execute("""
            CREATE TABLE Persons (
                ID int NOT NULL PRIMARY KEY IDENTITY,
                FirstName varchar(255),
                LastName varchar(255)
            );
        """)

        conn.commit()
    except Exception as e:
        # Table may already exist
        print(e)
    return "Person API"

@app.get("/all")
def get_persons():
    rows = []
    with get_conn() as conn:
        cursor = conn.cursor()
        cursor.execute("SELECT * FROM Persons")

        for row in cursor.fetchall():
            print(row.FirstName, row.LastName)
            rows.append(f"{row.ID}, {row.FirstName}, {row.LastName}")
    return rows

@app.get("/person/{person_id}")
def get_person(person_id: int):
    with get_conn() as conn:
        cursor = conn.cursor()
        cursor.execute("SELECT * FROM Persons WHERE ID = ?", person_id)

        row = cursor.fetchone()
        return f"{row.ID}, {row.FirstName}, {row.LastName}"

@app.post("/person")
def create_person(item: Person):
    with get_conn() as conn:
        cursor = conn.cursor()
        cursor.execute(f"INSERT INTO Persons (FirstName, LastName) VALUES (?, ?)", item.first_name, item.last_name)
        conn.commit()

    return item

def get_conn():
    credential = identity.DefaultAzureCredential(exclude_interactive_browser_credential=False)
    token_bytes = credential.get_token("https://database.windows.net/.default").token.encode("UTF-16-LE")
    token_struct = struct.pack(f'<I{len(token_bytes)}s', len(token_bytes), token_bytes)
    SQL_COPT_SS_ACCESS_TOKEN = 1256  # This connection option is defined by microsoft in msodbcsql.h
    conn = pyodbc.connect(connection_string, attrs_before={SQL_COPT_SS_ACCESS_TOKEN: token_struct})
    return conn

警告

範例程式碼顯示原始 SQL 陳述式，不應用於實際執行程式碼。請改用物件關聯式對應 (ORM) 套件，例如 SqlAlchemy，以產生更安全的物件層來存取資料庫。

在本機執行和測試應用程式

應用程式已準備好在本機進行測試。

在 Visual Studio Code 中執行 app.py 檔案。
```
uvicorn app:app --reload
```
在應用程式 http://127.0.0.1:8000/docs 的 Swagger UI 頁面上，展開 POST 方法，然後選取 [試用]。

也可以使用 try /redoc 來查看產生的另一種形式的 API 文件。
修改範例 JSON 以包含名字和姓氏的值。選取 [執行] 以將新記錄新增至資料庫。 API 會傳回成功的回應。
在 Swagger UI 頁面上，展開 GET 方法，然後選取 [試用]。選取 [執行]，隨即傳回您剛才建立的人員。

部署到 Azure App Service

如此應用程式即已就緒，可開始部署至 Azure。

建立 start.sh 檔案，以便 Azure App Service 中的 gunicorn 可以執行 uvicorn。 start.sh 有一行：
```
gunicorn -w 4 -k uvicorn.workers.UvicornWorker app:app
```
使用 az webapp up 將程式碼部署至 App Service。 (可以使用 -dryrun 選項來查看命令的用途，而不建立資源。)
```
az webapp up \
    --resource-group <resource-group-name> \
    --name <web-app-name>         
```

使用 az webapp config set 命令來設定 App Service 以使用 start.sh 檔案。

az webapp config set \
    --resource-group <resource-group-name> \
    --name <web-app-name> \
    --startup-file start.sh

使用 az webapp identity assign 命令為 App Service 啟用系統指派的受控識別。
```
az webapp identity assign \
    --resource-group <resource-group-name> \
    --name <web-app-name>
```
在本快速入門中，系統將使用系統指派的受控識別來進行示範。在更廣泛的案例中，使用者指派的受控識別效率更高。如需詳細資訊，請參閱受控識別最佳做法建議。如需將使用者指派的受控識別與 pyodbc 搭配使用的範例，請參閱移轉 Python 應用程式以對 Azure SQL 資料庫使用無密碼連線。

將 App Service 連線到 Azure SQL 資料庫

在 [設定資料庫] 區段中，您已為 Azure SQL 資料庫伺服器設定網路和 Microsoft Entra 驗證。在本節中，您將完成資料庫設定，並使用連接字串設定 App Service 以存取資料庫伺服器。

若要執行這些命令，可以使用任何可連線到 Azure SQL 資料庫的工具或 IDE，包括 SQL Server Management Studio (SSMS)、Azure Data Studio，以及具有 SQL Server mssql 延伸項目的 Visual Studio Code。此外，也可以如快速入門：使用 Azure 入口網站的查詢編輯器來查詢 Azure SQL 資料庫中所述，使用 Azure 入口網站。

使用 SQL 命令將使用者新增至 Azure SQL 資料庫，以建立用於無密碼存取的使用者和角色。
```
CREATE USER [<web-app-name>] FROM EXTERNAL PROVIDER
ALTER ROLE db_datareader ADD MEMBER [<web-app-name>]
ALTER ROLE db_datawriter ADD MEMBER [<web-app-name>]
```
如需詳細資訊，請參閱自主資料庫使用者 - 使資料庫可攜。如需顯示相同原則但套用至 Azure VM 的範例，請參閱教學課程：使用 Windows VM 系統指派的受控識別來存取 Azure SQL。如需指派的角色的詳細資訊，請參閱固定資料庫角色。

如果先停用再啟用 App Service 系統指派的受控識別，則刪除使用者並重新建立。執行 DROP USER [<web-app-name>]，然後重新執行 CREATE 和 ALTER 命令。若要檢視使用者，請使用 SELECT * FROM sys.database_principals。
使用 az webapp config appsettings set 命令來新增連接字串的應用程式設定。
```
az webapp config appsettings set \
    --resource-group <resource-group-name> \
    --name <web-app-name> \
    --settings AZURE_SQL_CONNECTIONSTRING="<connection-string>"
```
對於已部署的應用程式，連接字串應類似於：
```
Driver={ODBC Driver 18 for SQL Server};Server=tcp:<database-server-name>.database.windows.net,1433;Database=<database-name>;Encrypt=yes;TrustServerCertificate=no;Connection Timeout=30
```
在 <dabaser-server-name> 和 <database-name> 中填入您的值。

無密碼連接字串不包含使用者名稱或密碼。相反地，當應用程式在 Azure 中執行時，程式碼會從 Azure 身分識別程式庫使用 DefaultAzureCredential，以取得搭配 pyodbc 使用的權杖。

測試已部署的應用程式

瀏覽至應用程式的 URL，以測試與 Azure SQL 資料庫間的連線是否正常運作。您可以在 App Service 概觀頁面上找到您應用程式的 URL。

https://<web-app-name>.azurewebsites.net

將 /docs 附加至 URL，以查看 Swagger UI 並測試 API 方法。

恭喜！您的應用程式現在已與本機和託管環境中的 Azure SQL 資料庫建立連線。

移轉 Python 應用程式以使用與 Azure SQL 資料庫的無密碼連線 - 顯示使用者指派的受控識別。
Azure 服務的無密碼連線
受控識別最佳做法建議

共用方式為

使用 Python 和 pyodbc 驅動程式連接和查詢 Azure SQL 資料庫

必要條件

設定資料庫

建立專案

安裝 pyodbc 驅動程式

設定本機連接字串

新增程式碼以連線至 Azure SQL 資料庫

在本機執行和測試應用程式

部署到 Azure App Service

將 App Service 連線到 Azure SQL 資料庫

測試已部署的應用程式

意見反應

其他資源

共用方式為

使用 Python 和 pyodbc 驅動程式連接和查詢 Azure SQL 資料庫

必要條件

設定 資料庫

建立專案

安裝 pyodbc 驅動程式

設定本機連接字串

新增程式碼以連線至 Azure SQL 資料庫

在本機執行和測試應用程式

部署到 Azure App Service

將 App Service 連線到 Azure SQL 資料庫

測試已部署的應用程式

相關內容

意見反應

其他資源

設定資料庫