Condividi tramite

Connettersi al database SQL di Azure ed eseguire query usando Python e il driver pyodbc

  • Articolo

Si applica a: Database SQL di Azure

Questa guida di avvio rapido descrive come connettere un'applicazione a un database in Database SQL di Azure ed eseguire query usando Python e il driver SQL Python - pyodbc. Questa guida di avvio rapido segue l'approccio senza password consigliato per connettersi al database. Altre informazioni sulle connessioni senza password sono disponibili nell'hub senza password.

Prerequisiti

Configurare il database

La connessione sicura senza password a Database SQL di Azure richiede determinate configurazioni del database. Verificare le impostazioni seguenti nel server logico in Azure per connettersi correttamente a Database SQL di Azure in ambienti locali e ospitati:

  1. Per le connessioni di sviluppo locale, verificare che il server logico sia configurato per consentire all'indirizzo IP del computer locale e ad altri servizi di Azure di connettersi:

    • Passare alla pagina Rete del server.

    • Attivare o disattivare il pulsante di opzione Reti selezionate per visualizzare ulteriori opzioni di configurazione.

    • Selezionare Aggiungere l'indirizzo IPv4 client (xx.xx.xx.xx.xx) per aggiungere una regola del firewall che consentirà le connessioni dall'indirizzo IPv4 del computer locale. In alternativa, è anche possibile selezionare + Aggiungi una regola del firewall per immettere un indirizzo IP specifico a propria scelta.

    • Selezionare la casella di controllo Consenti alle risorse e ai servizi di Azure di accedere a questo server.

      Screenshot che mostra come configurare le regole del firewall.

      Avviso

      L'abilitazione dell'impostazione Consenti a servizi e risorse di Azure di accedere a questo server non è una procedura di protezione consigliata per gli scenari di produzione. Le applicazioni reali devono implementare approcci più sicuri, ad esempio restrizioni firewall più avanzate o configurazioni di reti virtuali.

      Per altre informazioni sulle configurazioni di sicurezza del database, vedere le seguenti risorse:

  2. Al server deve essere abilitata anche l'autenticazione Microsoft Entra e deve essere assegnato un account amministratore di Microsoft Entra. Per le connessioni di sviluppo locali, l'account amministratore di Microsoft Entra deve essere un account con il quale è possibile accedere localmente anche a Visual Studio o all'interfaccia della riga di comando di Azure. È possibile verificare se nel server è abilitata l'autenticazione Microsoft Entra dalla pagina Microsoft Entra ID del server logico.

    Screenshot che mostra come abilitare l'autenticazione di Microsoft Entra.

  3. Se si usa un account Azure personale, assicurarsi di avere impostato Microsoft Entra e che sia configurato per Database SQL di Azure per poter assegnare l'account come amministratore del server. Se si usa un account aziendale, è probabile che Microsoft Entra ID sia già configurato automaticamente.

Creare il progetto

Creare un nuovo progetto Python con Visual Studio Code.

  1. Aprire Visual Studio Code, creare una nuova cartella per il progetto e usarla per modificare la directory.

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

  2. Creare un ambiente virtuale per l’app.

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

  3. Creare un nuovo file Python denominato app.py.

Installare il driver pyodbc

Per connettersi a database SQL di Azure usando Python, installare il driver pyodbc. Questo pacchetto funge da provider di dati per effettuare una connessione a database, eseguire comandi e recuperare risultati. In questa guida introduttiva verranno anche installati pacchetti flask, uvicorn e pydantic per creare ed eseguire un'API.

Per informazioni dettagliate e istruzioni specifiche per l'installazione del driver pyodbc in tutti i sistemi operativi, vedere Configurare l'ambiente di sviluppo per lo sviluppo con Python pyodbc.

  1. Creare un file requirements.txt con le righe seguenti:

    pyodbc
fastapi
uvicorn[standard]
pydantic
azure-identity

  2. Installare i requisiti.

    pip install -r requirements.txt

Configurare la stringa di connessione locale

Per lo sviluppo locale e la connessione a database SQL di Azure, aggiungere la seguente variabile di ambiente AZURE_SQL_CONNECTIONSTRING. Sostituire i segnaposto <database-server-name> e <database-name> con i propri valori. Le variabili di ambiente di esempio vengono visualizzate per la shell Bash.

L'autenticazione interattiva offre un'opzione senza password quando l’esecuzione avviene localmente. Questa opzione è consigliata perché non è necessario archiviare o gestire i segreti di autenticazione nel sistema locale.

In Windows, l’autenticazione interattiva di Microsoft Entra può usare la tecnologia di autenticazione a più fattori di Microsoft Entra per configurare la connessione. In questa modalità, specificando l'ID di accesso, viene attivata una finestra di dialogo di autenticazione di Azure che consente all'utente di immettere la password per completare la connessione.

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'

Per maggiori informazioni, vedere la sezione Usare Microsoft Entra ID con il driver ODBC. Se si usa questa opzione, cercare la finestra in cui vengono richieste le credenziali.

È possibile ottenere i dettagli per creare la stringa di connessione dal portale di Azure:

  1. Passare ad Azure SQL Server, selezionare la pagina Database SQL per trovare il nome del database, quindi selezionare il database.

  2. Nel database, passare alla pagina Stringhe di connessione per ottenere informazioni sulla stringa di connessione. Cercare nella scheda ODBC.

Nota

Se Azure Arc è stato installato e associato alla sottoscrizione di Azure, è anche possibile usare l'approccio dell'identità gestita illustrato per l'app distribuita in Servizio app.

Aggiungere codice per connettersi al database SQL di Azure

Nella cartella del progetto creare un file app.py e aggiungere il codice di esempio. Tale codice crea un'API che:

  • Recupera una stringa di connessione del database SQL di Azure da una variabile di ambiente.
  • Crea una tabella Persons nel database durante l'avvio (solo per scenari di test).
  • Definisce una funzione per recuperare tutti i record Person dal database.
  • Definisce una funzione per recuperare un record Person dal database.
  • Definisce una funzione per aggiungere nuovi record Person al database.
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

Avviso

Il codice di esempio mostra istruzioni SQL non elaborate che non devono essere usate nel codice di produzione. Usare invece un pacchetto ORM (Object Relational Mapper) come SqlAlchemy, che genera un livello oggetto più sicuro per accedere al database.

Eseguire e testare l'app in locale

L'app è pronta per essere testata a livello locale.

  1. Eseguire il file app.py in Visual Studio Code.

    uvicorn app:app --reload

  2. Nella pagina dell'interfaccia utente di Swagger per l’app http://127.0.0.1:8000/docs, espandere il metodo POST e selezionare Prova.

    È possibile usare anche try /redoc per visualizzare un altro formato di documentazione generata per l'API.

  3. Modificare il campione JSON affinché includa i valori di nome e cognome. Selezionare Esegui per aggiungere un nuovo record al database. L'API restituisce una risposta di esito positivo.

  4. Espandere il metodo GET nella pagina dell'interfaccia utente di Swagger, quindi selezionare Prova. Scegliere Esegui e, in questo modo, viene restituita la persona appena creata.

Distribuire nel Servizio app di Azure

L'app è pronta per essere distribuita in Azure.

  1. Creare un file start.sh in modo che gunicorn in Servizio app di Azure possa eseguire uvicorn. start.sh ha una riga:

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

  2. Usare az webapp up per distribuire il codice in Servizio app. (È possibile usare l'opzione -dryrun per visualizzare le operazioni del comando senza creare la risorsa.)

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

  3. Usare il comando az webapp config set per configurare Servizio app in modo che possa usare il file start.sh.

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

  4. Usare il comando az webapp identity assign per abilitare un'identità gestita assegnata dal sistema per Servizio app.

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

    In questa guida introduttiva viene usata un'identità gestita assegnata dal sistema per scopi dimostrativi. Un'identità gestita assegnata dall'utente è più efficiente in una vasta gamma di scenari. Per altre informazioni, vedere Raccomandazioni sulle procedure consigliate per l'identità gestita. Per un esempio di uso di identità gestita assegnata dall'utente con pyodbc, vedere Eseguire la migrazione di un'applicazione Python per usare connessioni senza password con database SQL di Azure.

Connettere il servizio app al database SQL di Azure

Nella sezione Configurare il database è stata configurata l'autenticazione di rete e Microsoft Entra per il server di database SQL di Azure. In questa sezione viene completata la configurazione del database e viene configurato il Servizio app con un stringa di connessione per accedere al server database.

Per eseguire questi comandi è possibile usare qualsiasi strumento o IDE in grado di connettersi a database SQL di Azure, tra cui SQL Server Management Studio (SSMS), Azure Data Studio e Visual Studio Code con l'estensione mssql di SQL Server. Inoltre, è possibile usare il portale di Azure come illustrato in Avvio rapido: usare l'editor di query del portale di Azure per eseguire query sul database SQL di Azure.

  1. Aggiungere un utente al database SQL di Azure con i comandi SQL per creare un utente e un ruolo per l'accesso senza password.

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

    Per altre informazioni, vedere Utenti di database indipendente: rendere portabile un database. Per trovare un esempio che illustra lo stesso principio, ma applicato alla macchina virtuale di Azure, vedere Esercitazione: usare un'identità gestita assegnata dal sistema di macchine virtuali Windows per accedere ad Azure SQL. Per altre informazioni sui ruoli assegnati, vedere Ruoli predefiniti del database.

    Se si disabilita e quindi si abilita l'identità gestita assegnata dal sistema di Servizio app, eliminare l'utente e ricrearlo. Eseguire DROP USER [<web-app-name>], quindi eseguire di nuovo i comandi CREATE e ALTER. Per visualizzare gli utenti, usare SELECT * FROM sys.database_principals.

  2. Usare il comando az webapp config appsettings set per aggiungere un’impostazione app per la stringa di connessione.

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

    Per l'app distribuita, la stringa di connessione dovrebbe essere simile a quanto segue:

    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

    Compilare <dabaser-server-name> e <database-name> con i propri valori.

    La stringa di connessione senza password non contiene un nome utente o una password. Al contrario, quando l'app viene eseguita in Azure, il codice usa DefaultAzureCredential dalla libreria di identità di Azure per ottenere un token da usare con pyodbc.

Testare l'applicazione distribuita

Passare all'URL dell'app per testare il funzionamento della connessione al database SQL di Azure. È possibile individuare l'URL dell'app nella pagina di panoramica del servizio app.

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

Accodare /docs all'URL per visualizzare l'interfaccia utente di Swagger e testare i metodi API.

Complimenti. L'applicazione è ora connessa al database SQL di Azure in ambienti locali e ospitati.

Contenuto correlato