Share via

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

  • 發行項

適用於：Azure SQL 資料庫

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

必要條件

設定 資料庫

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

  1. 針對本機開發連線，請確定您的邏輯伺服器已設定為允許本機電腦 IP 位址及其他 Azure 服務連線：

    • 瀏覽至伺服器的 [網路] 頁面。

    • 切換 [選取的網路] 選項按鈕以顯示其他組態選項。

    • 選取 [新增用戶端 IPv4 位址 (xx.xx.xx.xx)]，以新增防火牆規則，啟用來自本機電腦 IPv4 位址的連線。 或者您也可以選取 [+ 新增防火牆規則] 來輸入您選擇的特定 IP 位址。

    • 確定選取 [允許 Azure 服務和資源存取此伺服器] 核取方塊。

      顯示如何設定防火牆規則的螢幕擷取畫面。

      警告

      對於實際執行場景，出於安全考量，不建議啟用 [允許 Azure 服務和資源存取此伺服器] 設定。 實際應用程式應該實作更安全的方法，例如更強大的防火牆限制或虛擬網路設定。

      可以存取下列資源，深入了解資料庫安全設定：

  2. 伺服器也必須啟用 Microsoft Entra 驗證，並已指派 Microsoft Entra 管理帳戶。 針對本機開發連線，Microsoft Entra 管理帳戶應該也可以用於本機登入 Visual Studio 或 Azure CLI 的帳戶。 您可以在邏輯伺服器的 [Microsoft Entra ID] 頁面確認伺服器是否已啟用 Microsoft Entra 驗證。

    顯示如何啟用 Microsoft Entra 驗證的螢幕快照。

  3. 如果您使用個人 Azure 帳戶，請確定您已針對 Azure SQL 資料庫設定 Microsoft Entra，將您的帳戶指派為伺服器管理員。如果您使用公司帳戶，很可能已為您設定好 Microsoft Entra ID。

建立專案

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

  1. 開啟 Visual Studio Code，然後為專案建立新資料夾，並將目錄變更為它。

    mkdir python-sql-azure
cd python-sql-azure

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

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

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

安裝 pyodbc 驅動程式

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

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

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

    pyodbc
fastapi
uvicorn[standard]
pydantic
azure-identity

  2. 安裝需求。

    pip install -r requirements.txt

設定本機連接字串

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

若您在本機執行，互動式驗證會提供無密碼選項。

在 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 驅動程式。 如果您使用此選項，請找到提示您輸入認證的視窗。

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

  1. 移至 Azure SQL Server，選取 [SQL Database] 頁面以找到資料庫名稱，然後選取資料庫。

  2. 在資料庫上，移至 [連線字串] 頁面以取得連接字串資訊。 在 [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，以產生更安全的物件層來存取資料庫。

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

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

  1. 在 Visual Studio Code 中執行 app.py 檔案。

    uvicorn app:app --reload

  2. 在應用程式 http://127.0.0.1:8000/docs 的 Swagger UI 頁面上，展開 POST 方法，然後選取 [試用]。

    也可以使用 try /redoc 來查看產生的另一種形式的 API 文件。

  3. 修改範例 JSON 以包含名字和姓氏的值。 選取 [執行] 以將新記錄新增至資料庫。 API 會傳回成功的回應。

  4. 在 Swagger UI 頁面上，展開 GET 方法，然後選取 [試用]。 選取 [執行]，隨即傳回您剛才建立的人員。

部署到 Azure App Service

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

  1. 建立 start.sh 檔案，以便 Azure App Service 中的 gunicorn 可以執行 uvicorn。 start.sh 有一行：

    gunicorn -w 4 -k uvicorn.workers.UvicornWorker app:app

  2. 使用 az webapp up 將程式碼部署至 App Service。 (可以使用 -dryrun 選項來查看命令的用途，而不建立資源。)

    az webapp up \
    --resource-group <resource-group-name> \
    --name <web-app-name>

  3. 使用 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

  4. 使用 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 入口網站。

  1. 使用 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。

  2. 使用 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 資料庫建立連線。

相關內容