Partager via

Se connecter à et interroger Azure SQL Database à l’aide de Python et du pilote pyodbc

  • Article

S’applique à : Azure SQL Database

Ce démarrage rapide explique comment connecter une application à une base de données dans Azure SQL Database et effectuer des requêtes à l’aide de Python et du Pilote SQL Python - pyodbc. Ce guide de démarrage rapide suit l’approche sans mot de passe recommandée pour se connecter à la base de données. Vous pouvez en apprendre plus sur les connexions sans mot de passe sur le Hub des connexions sans mot de passe.

Prérequis

Configurer la base de données

Les connexions sécurisées et sans mot de passe à Azure SQL Database nécessitent certaines configurations de base de données. Vérifiez les paramètres suivants sur votre serveur logique dans Azure pour vous connecter correctement à Azure SQL Database dans les environnements locaux et hébergés :

  1. Pour les connexions de développement local, vérifiez que votre serveur logique est configuré pour autoriser l’adresse IP de votre ordinateur local et d’autres services Azure à se connecter :

    • Accédez à la page Réseau de votre serveur.

    • Activez la case d’option Réseaux sélectionnés pour voir des options de configuration supplémentaires.

    • Sélectionnez Ajouter l’adresse IPv4 de votre client (xx.xx.xx.xx.xx.xx) pour ajouter une règle de pare-feu qui activera les connexions à partir de l’adresse IPv4 de votre ordinateur local. Vous pouvez également sélectionner + Ajouter une règle de pare-feu pour entrer une adresse IP spécifique de votre choix.

    • Vérifiez que la case Autoriser les services et les ressources Azure à accéder à ce serveur est cochée.

      Capture d’écran montrant comment configurer des règles de pare-feu.

      Avertissement

      L’activation du paramètre Autoriser les services et les ressources Azure à accéder à ce serveur n’est pas une pratique de sécurité recommandée pour les scénarios de production. Les applications réelles doivent implémenter des approches plus sécurisées, telles que des restrictions de pare-feu ou des configurations de réseau virtuel plus strictes.

      Vous pouvez en apprendre davantage sur les configurations de sécurité de base de données avec les ressources suivantes :

  2. Le serveur doit également activer l’authentification Microsoft Entra et disposer d’un compte d’administrateur Microsoft Entra affecté. Pour les connexions de développement local, le compte d'administrateur de Microsoft Entra doit être un compte que vous pouvez également connecter localement à Visual Studio ou à Azure CLI. Vous pouvez vérifier si l'authentification Microsoft Entra est activée sur la page Microsoft Entra ID de votre serveur logique.

    Capture d’écran montrant l’activation de l’authentification Microsoft Entra.

  3. Si vous utilisez un compte Azure personnel, assurez-vous d'avoir Microsoft Entra installé et configuré dans la base de données Azure SQL afin d'assigner votre compte en tant qu'administrateur de serveur. En revanche, si vous utilisez un compte d'entreprise, il est fort probable que Microsoft Entra ID soit déjà configuré pour vous.

Créer le projet

Créez un projet de rapport à l’aide de Visual Studio Code.

  1. Ouvrez Visual Studio Code et créez un dossier pour votre projet et modifiez-y le répertoire.

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

  2. Créez un environnement virtuel pour l’application.

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

  3. Créer un fichier Python appelé app.py.

Installer le pilote pyodbc

Pour vous connecter à Azure SQL Database à l’aide de Python, installez le pilote pyodbc. Ce package joue le rôle de fournisseur de données pour la connexion aux bases de données, l’exécution de commandes et la récupération des résultats. Dans ce démarrage rapide, vous installez également les packages flask, uvicorn et pydantic pour créer et exécuter une API.

Pour plus d’informations et des instructions spécifiques sur l’installation du pilote pyodbc sur tous les systèmes d’exploitation, consultez Configurer l’environnement de développement pour le développement Python pyodbc.

  1. Créez un fichier requirements.txt avec les lignes suivantes :

    pyodbc
fastapi
uvicorn[standard]
pydantic
azure-identity

  2. Installez les exigences.

    pip install -r requirements.txt

Configurer la chaîne de connexion locale

Pour le développement local et la connexion à Azure SQL Database, ajoutez la variable d’environnement suivanteAZURE_SQL_CONNECTIONSTRING. Remplacez les espaces réservés <database-server-name> et <database-name> par vos valeurs. Des exemples de variables d’environnement sont affichés pour l’interpréteur de commandes Bash.

L’authentification interactive fournit une option sans mot de passe lorsque vous exécutez localement.

Dans Windows, l’authentification interactive Microsoft Entra peut utiliser la technologie d’authentification multifacteur Microsoft Entra pour configurer la connexion. Dans ce mode, lorsque l’ID de connexion est fourni, une boîte de dialogue d’authentification Azure est déclenchée, permettant ainsi à l’utilisateur d’entrer le mot de passe pour établir la connexion.

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'

Pour plus d’informations, consultez Utilisation de Microsoft Entra ID avec le Pilote ODBC. Si vous utilisez cette option, recherchez la fenêtre qui vous invite à entrer des informations d’identification.

Vous pouvez obtenir les détails de la création de votre chaîne de connexion à partir du Portail Azure :

  1. Accédez à Azure SQL Server, sélectionnez la page Bases de données SQL pour trouver le nom de votre base de données, puis sélectionnez la base de données.

  2. Dans la base de données, accédez à la page Chaînes de connexion pour obtenir des informations sur la chaîne de connexion. Regardez sous l’onglet ODBC.

Notes

Si vous avez installé Azure Arc et l’avez associé à votre abonnement Azure, vous pouvez également utiliser l’approche d’identité managée indiquée pour l’application déployée sur App Service.

Ajouter du code pour se connecter à Azure SQL Database

Dans le dossier du projet, créez un fichier app.py et ajoutez l’exemple de code. Ce code crée une API qui :

  • récupère une chaîne de connexion de base Azure SQL Database à partir d’une variable d’environnement
  • crée une table Persons dans la base de données au démarrage (pour les scénarios de test uniquement)
  • définit une fonction pour récupérer tous les enregistrements Person de la base de données.
  • définit une fonction pour récupérer un enregistrement Person de la base de données
  • Définit une fonction pour ajouter de nouveaux enregistrements Person à la base de données.
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

Avertissement

L’exemple de code montre des instructions SQL brutes, qui ne doivent pas être utilisées dans le code de production. Utilisez plutôt un package ORM (Object Relational Mapper) comme SqlAlchemy qui génère une couche d’objets plus sécurisée pour accéder à votre base de données.

Exécuter et tester l’application localement

L’application est prête à être testée localement.

  1. Ouvrez le fichier app.py dans Visual Studio Code.

    uvicorn app:app --reload

  2. Dans la page de l’interface utilisateur Swagger pour l’application http://127.0.0.1:8000/docs, développez la méthode POST, puis sélectionnez Essayer.

    Vous pouvez également utiliser essayer /redoc pour afficher une autre forme de documentation générée pour l’API.

  3. Modifiez l’exemple JSON pour inclure des valeurs pour le prénom et le nom. Sélectionnez Exécuter pour ajouter un nouvel enregistrement à la base de données. L’API retourne une réponse de succès.

  4. Développez la méthode GET sur la page de l’interface utilisateur Swagger, puis sélectionnez Essayer. Choisissez Exécuter, et la personne que vous venez de créer est retournée.

Déployer dans Azure App Service

L’application est prête à être déployée sur Azure.

  1. Créez un fichier start.sh afin que gunicorn dans Azure App Service puisse exécuter uvicorn. Le fichier start.sh a une ligne :

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

  2. Utilisez az webapp up pour déployer le code sur App Service. (Vous pouvez utiliser l’option -dryrun pour voir ce que fait la commande sans créer la ressource.)

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

  3. Utilisez la commande az webapp config set pour configurer App Service et utiliser le fichier start.sh.

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

  4. Utilisez la commande az webapp identity assign pour activer une identité managée affectée par le système pour le App Service.

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

    Dans ce démarrage rapide, une identité managée affectée par le système est utilisée pour la démonstration. Une identité managée affectée par l’utilisateur est plus efficace dans un plus large éventail de scénarios. Pour plus d’informations, consultez les suggestions relatives aux meilleures pratiques d’identités managées. Pour obtenir un exemple d’utilisation d’une identité managée affectée par l’utilisateur avec pyodbc, consultez Migrer une application Python pour utiliser des connexions sans mot de passe avec Azure SQL Database.

Connecter App Service à Azure SQL Database

Dans la section Configurer la base de données, vous avez configuré la mise en réseau et l’authentification Microsoft Entra pour le serveur SQL Database Azure. Dans cette section, vous terminez la configuration de la base de données et configurez App Service avec une chaîne de connexion pour accéder au serveur de base de données.

Pour exécuter ces commandes, vous pouvez utiliser n’importe quel outil ou IDE qui peut se connecter à Azure SQL Database, y compris SQL Server Management Studio (SSMS),Azure Data Studio et Visual Studio Code avec l’extension mssql SQL Server. Vous pouvez également utiliser le Portail Azure comme décrit dans Démarrage rapide : utiliser l’éditeur de requêtes Portail Azure pour interroger Azure SQL Database.

  1. Ajoutez un utilisateur à Azure SQL Database avec des commandes SQL pour créer un utilisateur et un rôle pour un accès sans mot de passe.

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

    Pour plus d’informations, voir Utilisateurs de base de données autonome - Rendre votre base de données portable. Pour obtenir un exemple qui montre le même principe, mais appliqué à une machine virtuelle Azure, consultez Tutoriel : utiliser une identité managée affectée par le système d’une machine virtuelle Windows pour accéder à Azure SQL. Pour plus d’informations sur les rôles attribués, consultez Rôles de base de données fixes.

    Si vous désactivez puis activez l’identité managée affectée par le système App Service, supprimez l’utilisateur et recréez-le. Exécutez DROP USER [<web-app-name>] et réexécutez les commandes CREATE et ALTER. Pour afficher les utilisateurs, utilisez SELECT * FROM sys.database_principals.

  2. Utilisez la commande az webapp config appsettings set pour ajouter un paramètre d’application pour la chaîne de connexion.

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

    Pour l’application déployée, la chaîne de connexion doit ressembler à :

    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

    Remplacez <dabaser-server-name> et <database-name> par vos valeurs.

    La chaîne de connexion sans mot de passe ne contient pas de nom d’utilisateur ou de mot de passe. Au lieu de cela, lorsque l’application s’exécute dans Azure, le code utilise DefaultAzureCredential à partir de la bibliothèque Azure Identity pour obtenir un jeton à utiliser avec pyodbc.

Tester l’application déployée

Accédez à l’URL de l’application pour tester que la connexion à Azure SQL Database fonctionne. Vous pouvez localiser l’URL de votre application dans la page de vue d’ensemble d’App Service.

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

Ajoutez /docs à l’URL pour afficher l’interface utilisateur Swagger et tester les méthodes d’API.

Félicitations ! Votre application est maintenant connectée à Azure SQL Database dans les environnements locaux et hébergés.

Contenu connexe