Dela via

Anslut till och fråga Azure SQL Database med python och pyodbc-drivrutinen

  • Artikel

Gäller för:Azure SQL Database

Den här snabbstarten beskriver hur du ansluter ett program till en databas i Azure SQL Database och utför frågor med Python och Python SQL Driver – pyodbc. Den här snabbstarten följer den rekommenderade metoden för lösenordsfri anslutning till databasen. Du kan lära dig mer om lösenordslösa anslutningar på den lösenordslösa hubben.

Förutsättningar

  • En Azure-prenumeration.
  • En Azure SQL-databas som konfigurerats med Microsoft Entra-autentisering. Du kan skapa en med hjälp av snabbstarten Skapa databas.
  • Den senaste versionen av Azure CLI.
  • Visual Studio Code med Python-tillägget.
  • Python 3.8 eller senare.

Konfigurera databasen

Säkra, lösenordslösa anslutningar till Azure SQL Database kräver vissa databaskonfigurationer. Kontrollera följande inställningar på den logiska servern i Azure för att ansluta till Azure SQL Database korrekt i både lokala och värdbaserade miljöer:

  1. För lokala utvecklingsanslutningar kontrollerar du att den logiska servern är konfigurerad så att din lokala dators IP-adress och andra Azure-tjänster kan ansluta:

    • Gå till sidan Nätverk på servern.

    • Växla alternativknappen Valda nätverk för att visa ytterligare konfigurationsalternativ.

    • Välj Lägg till din klient-IPv4-adress(xx.xx.xx.xx) för att lägga till en brandväggsregel som aktiverar anslutningar från den lokala datorns IPv4-adress. Du kan också välja + Lägg till en brandväggsregel för att ange en specifik IP-adress.

    • Kontrollera att kryssrutan Tillåt Azure-tjänster och resurser att komma åt den här servern är markerad.

      A screenshot showing how to configure firewall rules.

      Varning

      Att aktivera inställningen Tillåt Azure-tjänster och resurser att komma åt den här serverinställningen är inte en rekommenderad säkerhetspraxis för produktionsscenarier. Verkliga program bör implementera säkrare metoder, till exempel starkare brandväggsbegränsningar eller konfigurationer av virtuella nätverk.

      Du kan läsa mer om databassäkerhetskonfigurationer på följande resurser:

  2. Servern måste också ha Microsoft Entra-autentisering aktiverat och ha ett Microsoft Entra-administratörskonto tilldelat. För lokala utvecklingsanslutningar ska Microsoft Entra-administratörskontot vara ett konto som du också kan logga in på Visual Studio eller Azure CLI med lokalt. Du kan kontrollera om din server har Microsoft Entra-autentisering aktiverat på sidan Microsoft Entra-ID för den logiska servern.

    A screenshot showing how to enable Microsoft Entra authentication.

  3. Om du använder ett personligt Azure-konto kontrollerar du att microsoft Entra har konfigurerats och konfigurerats för Azure SQL Database för att tilldela ditt konto som serveradministratör. Om du använder ett företagskonto är Microsoft Entra-ID förmodligen redan konfigurerat åt dig.

Skapa projektet

Skapa ett nytt Python-projekt med Visual Studio Code.

  1. Öppna Visual Studio Code och skapa en ny mapp för projektet och ändra katalogen till den.

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

  2. Skapa en virtuell miljö för appen.

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

  3. Skapa en ny Python-fil med namnet app.py.

Installera pyodbc-drivrutinen

Installera drivrutinen för att ansluta till Azure SQL Database med Python pyodbc . Det här paketet fungerar som en dataprovider för att ansluta till databaser, köra kommandon och hämta resultat. I den här snabbstarten installerar flaskdu även , uvicornoch pydantic paket för att skapa och köra ett API.

Mer information och specifika instruktioner för att pyodbc installera drivrutinen på alla operativsystem finns i Konfigurera utvecklingsmiljö för pyodbc Python-utveckling.

  1. Skapa en requirements.txt-fil med följande rader:

    pyodbc
fastapi
uvicorn[standard]
pydantic
azure-identity

  2. Installera kraven.

    pip install -r requirements.txt

Konfigurera den lokala anslutningssträng

Lägg till följande AZURE_SQL_CONNECTIONSTRING miljövariabel för lokal utveckling och anslutning till Azure SQL Database. <database-server-name> Ersätt platshållarna och <database-name> med dina egna värden. Exempel på miljövariabler visas för Bash-gränssnittet.

Interaktiv autentisering ger ett lösenordslöst alternativ när du kör lokalt.

I Windows kan Microsoft Entra Interactive Authentication använda Microsoft Entra multifaktorautentiseringsteknik för att konfigurera anslutning. I det här läget utlöses en Azure-autentiseringsdialogruta genom att ange inloggnings-ID:t och gör att användaren kan ange lösenordet för att slutföra anslutningen.

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'

Mer information finns i Använda Microsoft Entra-ID med ODBC-drivrutinen. Om du använder det här alternativet letar du efter det fönster som frågar efter autentiseringsuppgifter.

Du kan få information om hur du skapar anslutningssträng från Azure-portalen:

  1. Gå till Azure SQL Server, välj sidan SQL-databaser för att hitta databasnamnet och välj databasen.

  2. Gå till sidan Anslut ionssträngar i databasen för att hämta anslutningssträng information. Titta under fliken ODBC .

Kommentar

Om du har installerat Azure Arc och associerat det med din Azure-prenumeration kan du också använda den hanterade identitetsmetod som visas för appen som distribueras till App Service.

Lägga till kod för att ansluta till Azure SQL Database

I projektmappen skapar du en app.py-fil och lägger till exempelkoden. Den här koden skapar ett API som:

  • Hämtar en Azure SQL Database-anslutningssträng från en miljövariabel.
  • Skapar en Persons tabell i databasen under start (endast för testningsscenarier).
  • Definierar en funktion för att hämta alla Person poster från databasen.
  • Definierar en funktion för att hämta en Person post från databasen.
  • Definierar en funktion för att lägga till nya Person poster i databasen.
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

Varning

Exempelkoden visar råa SQL-uttryck som inte ska användas i produktionskoden. Använd i stället ett ORM-paket (Object Relational Mapper) som SqlAlchemy som genererar ett säkrare objektlager för att få åtkomst till databasen.

Köra och testa appen lokalt

Appen är redo att testas lokalt.

  1. app.py Kör filen i Visual Studio Code.

    uvicorn app:app --reload

  2. På swagger-användargränssnittssidan för appen http://127.0.0.1:8000/docsexpanderar du POST-metoden och väljer Prova.

    Du kan också använda try /redoc för att se en annan form av genererad dokumentation för API:et.

  3. Ändra JSON-exemplet så att det innehåller värden för för- och efternamn. Välj Kör för att lägga till en ny post i databasen. API:et returnerar ett lyckat svar.

  4. Expandera GET-metoden på swagger-användargränssnittssidan och välj Prova. Välj Kör och den person som du nyss skapade returneras.

Distribuera till Azure App Service

Appen är redo att distribueras till Azure.

  1. Skapa en start.sh-fil så att gunicorn i Azure App Service kan köra uvicorn. Start.sh har en rad:

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

  2. Använd az webapp up för att distribuera koden till App Service. (Du kan använda alternativet -dryrun för att se vad kommandot gör utan att skapa resursen.)

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

  3. Använd kommandot az webapp config set för att konfigurera App Service att använda filen start.sh.

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

  4. Använd kommandot az webapp identity assign för att aktivera en systemtilldelad hanterad identitet för App Service.

    az webapp identity assign \
    --resource-group <resource-group-name> \
    --name <web-app-name>

    I den här snabbstarten används en systemtilldelad hanterad identitet för demonstration. En användartilldelad hanterad identitet är effektivare i ett bredare spektrum av scenarier. Mer information finns i Rekommenderade metoder för hanterad identitet. Ett exempel på hur du använder en användartilldelad hanterad identitet med pyodbc finns i Migrera ett Python-program för att använda lösenordslösa anslutningar med Azure SQL Database.

Anslut App Service till Azure SQL Database

I avsnittet Konfigurera databasen konfigurerade du nätverk och Microsoft Entra-autentisering för Azure SQL-databasservern. I det här avsnittet slutför du databaskonfigurationen och konfigurerar App Service med en anslutningssträng för att få åtkomst till databasservern.

Om du vill köra dessa kommandon kan du använda valfritt verktyg eller IDE som kan ansluta till Azure SQL Database, inklusive SQL Server Management Studio (SSMS), Azure Data Studio och Visual Studio Code med SQL Server mssql-tillägget. Du kan också använda Azure-portalen enligt beskrivningen i Snabbstart: Använd Frågeredigeraren för Azure-portalen för att fråga Azure SQL Database.

  1. Lägg till en användare i Azure SQL Database med SQL-kommandon för att skapa en användare och roll för lösenordslös åtkomst.

    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>]

    Mer information finns i Användare av oberoende databas – göra databasen portabel. Ett exempel som visar samma princip men som tillämpas på virtuella Azure-datorer finns i Självstudie: Använda en systemtilldelad hanterad identitet för en virtuell Windows-dator för att få åtkomst till Azure SQL. Mer information om tilldelade roller finns i Roller med fast databas.

    Om du inaktiverar och sedan aktiverar den systemtilldelade hanterade identiteten för App Service släpper du användaren och återskapar den. Kör DROP USER [<web-app-name>] och kör kommandona CREATE och ALTER igen. Om du vill se användare använder du SELECT * FROM sys.database_principals.

  2. Använd kommandot az webapp config appsettings set för att lägga till en appinställning för anslutningssträng.

    az webapp config appsettings set \
    --resource-group <resource-group-name> \
    --name <web-app-name> \
    --settings AZURE_SQL_CONNECTIONSTRING="<connection-string>"

    För den distribuerade appen bör anslutningssträng likna:

    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

    Fyll i <dabaser-server-name> och <database-name> med dina värden.

    Den lösenordslösa anslutningssträng innehåller inget användarnamn eller lösenord. När appen körs i Azure används DefaultAzureCredential i stället koden från Azure Identity-biblioteket för att hämta en token som ska användas med pyodbc.

Testa det distribuerade programmet

Bläddra till appens URL för att testa att anslutningen till Azure SQL Database fungerar. Du kan hitta url:en för din app på översiktssidan för App Service.

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

Lägg till /docs i URL:en för att se Swagger-användargränssnittet och testa API-metoderna.

Klar! Ditt program är nu anslutet till Azure SQL Database i både lokala och värdbaserade miljöer.

Relaterat innehåll