Conexión y consulta a Azure SQL Database mediante Python y el controlador pyodbc

Se aplica a: Azure SQL Database

En este inicio rápido se describe cómo conectar una aplicación a una base de datos en Azure SQL Database y realizar consultas mediante Python y el controlador de SQL para Python: pyodbc. En este inicio rápido se sigue el enfoque recomendado sin contraseña para conectarse a la base de datos. Puede consultar más información sobre las conexiones sin contraseña en Conexiones sin contraseña para servicios de Azure.

Requisitos previos

• Una suscripción de Azure.
• Una base de datos Azure SQL configurada con autenticación Microsoft Entra. Puede crear una mediante el inicio rápido Creación de una base de datos.
• La versión más reciente de la CLI de Azure.
• Visual Studio Code con la extensión de Python.
• Python 3.8 o versiones posteriores. Si usa una máquina cliente Linux, consulte Instalación del controlador ODBC.

Configuración de la base de datos

Las conexiones seguras sin contraseña a Azure SQL Database requieren una cierta configuración de la base de datos. Compruebe la siguiente configuración en el servidor lógico de Azure para conectarse correctamente a Azure SQL Database tanto en el entorno local como en un entorno hospedado:

1. En el caso de las conexiones de desarrollo local, asegúrese de que el servidor lógico está configurado para permitir que la dirección IP de la máquina local y otros servicios de Azure se conecten:

   • Vaya a la página Redes del servidor.

   • Active o desactive el botón de radio Redes seleccionadas para mostrar opciones de configuración adicionales.

   • Seleccione Agregar la dirección IPv4 de cliente (xx.xx.xx.xx) para agregar una regla de firewall que habilitará las conexiones desde la dirección IPv4 del equipo local. Como alternativa, también puede seleccionar + Agregar una regla de firewall para especificar una dirección IP específica de su elección.

   • Asegúrese de que la casilla Permitir que los servicios y recursos de Azure accedan a este servidor esté seleccionada.

     

     Advertencia

     La habilitación de la opción Permitir que los servicios y recursos de Azure accedan a este servidor no es un procedimiento de seguridad recomendado para escenarios de producción. Las aplicaciones reales deben implementar enfoques más seguros, como restricciones de firewall más fuertes o configuraciones de red virtual.

     Puede consultar más información sobre las configuraciones de seguridad de la base de datos en los siguientes recursos:

     • Configuración de reglas de firewall de Azure SQL Database.
     • Configuración de una red virtual con puntos de conexión privados.

2. El servidor también debe tener habilitada la autenticación de Microsoft Entra y tener asignada una cuenta de administrador de Microsoft Entra. Para las conexiones de desarrollo local, la cuenta de administrador de Microsoft Entra debe ser una cuenta que también puede iniciar sesión en Visual Studio o en la CLI de Azure localmente. Puede comprobar si el servidor tiene habilitada la autenticación de Microsoft Entra en la página de Microsoft Entra ID del servidor lógico.

   

3. Si usa una cuenta personal de Azure, asegúrese de que ha instalado y configurado Microsoft Entra para Azure SQL Database con el fin de asignar su cuenta como administrador del servidor. Si usa una cuenta corporativa, es probable que Microsoft Entra ID ya esté configurado.

Creación del proyecto

Cree un nuevo proyecto de Python en Visual Studio Code.

1. Abra Visual Studio Code, cree una nueva carpeta para el proyecto y cambie ahí el directorio.

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

2. Cree un entorno virtual para la aplicación.

   • Windows
   • macOS y Linux

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

3. Cree un nuevo archivo de Python denominado app.py.

Instalación del controlador pyodbc

Para conectarse a Azure SQL Database mediante Python, instale el controlador pyodbc. Este paquete actúa como proveedor de datos para conectarse a bases de datos, ejecutar comandos y recuperar resultados. En este inicio rápido, también instalará los paquetes flask, uvicorn y pydantic para crear y ejecutar una API.

Para información detallada e instrucciones específicas para instalar el controlador pyodbc en todos los sistemas operativos, consulte Configuración del entorno de desarrollo para el desarrollo de Python con pyodbc.

1. Cree un archivo llamado requirements.txt con las siguientes líneas:

   pyodbc
   fastapi
   uvicorn[standard]
   pydantic
   azure-identity
   

2. Instale los requisitos.

   pip install -r requirements.txt
   

Configuración de la cadena de conexión local

Para el desarrollo local y la conexión a Azure SQL Database, agregue la variable de entorno AZURE_SQL_CONNECTIONSTRING. Reemplace los marcadores de posición <database-server-name> y <database-name> por sus propios valores. Se muestran variables de entorno de ejemplo para el shell de Bash.

La autenticación interactiva proporciona una opción sin contraseña cuando se ejecuta localmente.

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'

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'

Puede obtener los detalles para crear la cadena de conexión en Azure Portal:

1. Vaya a Azure SQL Server, seleccione la página Bases de datos SQL para buscar el nombre de la base de datos y seleccione la base de datos.

2. En la base de datos, vaya a la página Cadenas de conexión para obtener información de la cadena de conexión. Mire en la pestaña ODBC.

Adición de código para conectarse a Azure SQL Database

En la carpeta del proyecto, cree un archivo app.py y agregue el código de ejemplo. Este código crea una API que:

• Recupera una cadena de conexión de Azure SQL Database de una variable de entorno.
• Crea una tabla Persons en la base de datos durante el inicio (solo para escenarios de prueba).
• Define una función para recuperar todos los registros Person de la base de datos.
• Define una función para recuperar un registro Person de la base de datos.
• Define una función para agregar nuevos registros Person a la base de datos.

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

Ejecución y prueba local de la aplicación

La aplicación está lista para probarse localmente.

1. Ejecute el archivo app.py en Visual Studio Code.

   uvicorn app:app --reload
   

2. En la página de la interfaz de usuario de Swagger de la aplicación http://127.0.0.1:8000/docs, expanda el método POST y seleccione Probar.

   También puede usar try /redoc para ver otra forma de documentación generada para la API.

3. Modifique el código JSON de ejemplo para incluir los valores de nombre y el apellido. Seleccione Ejecutar para agregar un nuevo registro a la base de datos. La API devuelve una respuesta correcta.

4. Expanda el método GET en la página de la interfaz de usuario de Swagger y seleccione Pruébelo. Elija Ejecutar y se devolverá la persona que acaba de crear.

Implementación en Azure App Service

La aplicación está lista para implementarse en Azure.

1. Cree un archivo start.sh para que gunicorn en Azure App Service pueda ejecutar uvicorn. El archivo start.sh tiene una línea:

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

2. Use az webapp up para implementar el código en App Service. (Puede usar la opción -dryrun para ver lo que hace el comando sin crear el recurso).

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

3. Use el comando az webapp config set para configurar App Service para usar el archivo start.sh.

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

4. Use el comando az webapp identity assign para habilitar una identidad administrada asignada por el sistema para la instancia de App Service.

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

   En este inicio rápido, se usa una identidad administrada asignada por el sistema como demostración. Una identidad administrada asignada por el usuario es más eficaz en una mayor variedad de escenarios. Para más información, consulte Procedimientos recomendados para identidades administradas. Para ver un ejemplo del uso de una identidad administrada asignada por el usuario con pyodbc, consulte Migración de una aplicación de Python para usar conexiones sin contraseña con Azure SQL Database.

Conexión de App Service a Azure SQL Database

En la sección Configurar la base de datos, ha configurado las redes y la autenticación de Microsoft Entra para el servidor de bases de datos de Azure SQL. En esta sección, realizará la configuración de la base de datos y configurará App Service con una cadena de conexión para acceder al servidor de bases de datos.

Para ejecutar estos comandos, puede usar cualquier herramienta o IDE que pueda conectarse a Azure SQL Database, incluidos SQL Server Management Studio (SSMS), Azure Data Studio y Visual Studio Code con la extensión mssql de SQL Server. También puede usar Azure Portal como se describe en Inicio rápido: Uso del editor de consultas de Azure Portal para consultar Azure SQL Database.

1. Agregue un usuario a la base de datos de Azure SQL con comandos SQL para crear un usuario y un rol para el acceso sin contraseña.

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

   Para obtener más información, vea Usuarios de base de datos independiente: hacer que la base de datos sea portátil. Para ver un ejemplo que muestra el mismo principio pero aplicado a la máquina virtual de Azure, consulte Tutorial: Uso de una identidad administrada asignada por el sistema de una máquina virtual Windows para acceder a Azure SQL. Para más información sobre los roles asignados, consulte Roles fijos de base de datos.

   Si deshabilita y luego habilita la identidad administrada asignada por el sistema de App Service, quite el usuario y vuelva a crearlo. Ejecute DROP USER [<web-app-name>] y vuelva a ejecutar los comandos CREATE y ALTER. Para ver a los usuarios, use SELECT * FROM sys.database_principals.

2. Use el comando az webapp config appsettings set para agregar una configuración de aplicación para la cadena de conexión.

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

   Para la aplicación implementada, la cadena de conexión debe ser similar a la siguiente:

   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
   

   Reemplace <dabaser-server-name> y <database-name> por sus propios valores.

   La cadena de conexión sin contraseña no contiene un nombre de usuario ni una contraseña. En su lugar, cuando la aplicación se ejecuta en Azure, el código usa DefaultAzureCredential de la biblioteca de identidades de Azure para obtener un token que se usará con pyodbc.

Prueba de la aplicación implementada

Vaya a la dirección URL de la aplicación para probar que funciona la conexión a Azure SQL Database. Puede encontrar la dirección URL de la aplicación en la página de información general de App Service.

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

Anexe /docs a la dirección URL para ver la interfaz de usuario de Swagger y probar los métodos de API.

¡Enhorabuena! La aplicación ahora está conectada a Azure SQL Database en entornos locales y hospedados.

Contenido relacionado

• Migración de una aplicación de Python para usar conexiones sin contraseña con Azure SQL Database: muestra la identidad administrada asignada por el usuario.
• Conexiones sin contraseña para servicios de Azure
• Procedimientos recomendados para identidades administradas