Подключение к базе данных Azure SQL и выполнение запросов с помощью Python и драйвера pyodbc.

Применимо к: База данных SQL Azure

В этом кратком руководстве описывается, как подключить приложение к базе данных в База данных SQL Azure и выполнять запросы с помощью Python и драйвера SQL Python — pyodbc. В этом кратком руководстве описан рекомендуемый подход без пароля для подключения к базе данных. Подробнее о подключениях без паролей вы можете узнать на центре без паролей.

Предварительные условия

• Подписка Azure
• База данных Azure SQL, настроенная с использованием проверки подлинности Microsoft Entra. Вы можете создать базу данных с помощью краткого руководства по созданию базы данных.
• Последняя версия Azure CLI.
• Visual Studio Code с расширением "Python".
• Python 3.8 или более поздней версии. Если вы используете клиентский компьютер Linux, см. раздел "Установка драйвера ODBC".

Настройка базы данных

Безопасные подключения без паролей к Службам баз данных Azure SQL требуют определённых конфигураций базы данных. Проверьте следующие параметры на логическом сервере в Azure, чтобы правильно подключиться к База данных SQL Azure в локальных и размещенных средах:

1. Для локальных подключений разработки убедитесь, что логический сервер настроен, чтобы разрешить подключение ip-адреса локального компьютера и других служб Azure:

   • Перейдите на страницу "Сеть " сервера.

   • Переключите радиокнопку Выбранные сети, чтобы отобразить дополнительные параметры конфигурации.

   • Выберите " Добавить ip-адрес клиента" (xx.xx.xx.xx.xx), чтобы добавить правило брандмауэра, которое будет включать подключения с адреса IPv4 локального компьютера. Кроме того, можно выбрать + Добавить правило брандмауэра, чтобы ввести конкретный IP-адрес.

   • Убедитесь, что установлен флажок разрешить службам и ресурсам Azure доступ к этому серверу .

     

     Предупреждение

     Активизация параметра Разрешить службам и ресурсам Azure доступ к этому серверу не является рекомендуемой практикой безопасности для производственных сценариев. Реальные приложения должны реализовать более безопасные подходы, такие как более строгие ограничения брандмауэра или конфигурации виртуальной сети.

     Дополнительные сведения о конфигурациях безопасности базы данных см. в следующих ресурсах:

     • Настройте правила брандмауэра База данных SQL Azure.
     • Настройте виртуальную сеть с частными конечными точками.

2. На сервере также должна быть включена проверка подлинности Microsoft Entra и назначена учетная запись администратора Microsoft Entra. Для локальной разработки учетная запись администратора Microsoft Entra должна быть такой учетной записью, в которую вы также можете войти с помощью Azure CLI или Visual Studio. Вы можете проверить, включена ли аутентификация Microsoft Entra на странице Microsoft Entra ID вашего логического сервера.

   

3. ** Если вы используете личную учетную запись Azure, убедитесь, что вы настроили Microsoft Entra и настроили её для Azure SQL Database, чтобы назначить свою учетную запись администратором сервера. Если вы используете корпоративный аккаунт, Microsoft Entra ID, скорее всего, будет настроен для вас.

Создание проекта

Создайте проект Python с помощью Visual Studio Code.

1. Откройте Visual Studio Code, создайте новую папку для вашего проекта и перейдите в неё.

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

2. Создайте виртуальную среду для приложения.

   • Windows
   • macOS/Linux

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

3. Создайте новый файл Python с именем app.py.

Установка драйвера pyodbc

Чтобы подключиться к База данных SQL Azure с помощью Python, установите pyodbc драйвер. Этот пакет выступает в качестве поставщика данных для подключения к базам данных, выполнения команд и получения результатов. В этом кратком руководстве вы также устанавливаете пакеты flask, uvicorn, и pydantic для создания и запуска API.

Дополнительные сведения и конкретные инструкции по установке pyodbc драйвера во всех операционных системах см. в разделе "Настройка среды разработки для разработки pyodbc Python".

1. Создайте файл requirements.txt со следующими строками:

   pyodbc
   fastapi
   uvicorn[standard]
   pydantic
   azure-identity
   

2. Установите требования.

   pip install -r requirements.txt
   

Настройте строку локального подключения

Для локальной разработки и подключения к База данных SQL Azure добавьте следующую AZURE_SQL_CONNECTIONSTRING переменную среды. Замените заполнители <database-server-name> и <database-name> собственными значениями. Примеры переменных среды отображаются для оболочки Bash.

Интерактивная проверка подлинности предоставляет возможность обходиться без пароля при локальном запуске. Этот параметр рекомендуется, так как вам не нужно хранить секреты проверки подлинности или управлять ими в локальной системе.

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'

Сведения о создании строки подключения можно получить из портала Azure.

1. Перейдите на сервер SQL Server Azure, выберите страницу баз данных SQL, чтобы найти имя базы данных и выбрать базу данных.

2. На странице базы данных перейдите на страницу строк подключения, чтобы получить сведения о строках подключения. Перейдите на вкладку ODBC .

Добавление кода для подключения к База данных SQL Azure

В папке проекта создайте файл app.py и добавьте пример кода. Этот код создает API, который:

• Извлекает строку подключения для базы данных "SQL Azure" из переменной окружения.
• Создает таблицу 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

Локальный запуск и проверка приложения

Приложение готово к локальному тестированию.

1. Запустите файл app.py в Visual Studio Code.

   uvicorn app:app --reload
   

2. На странице пользовательского интерфейса Swagger для приложения http://127.0.0.1:8000/docsразверните метод POST и выберите "Попробовать".

   Вы также можете использовать / redoc , чтобы просмотреть другую форму созданной документации для API.

3. Измените пример JSON, чтобы включить значения для имени и фамилии. Выберите "Выполнить" , чтобы добавить новую запись в базу данных. API возвращает успешный ответ.

4. Разверните метод GET на странице пользовательского интерфейса Swagger и выберите "Попробовать". Выберите «Выполнить», и только что созданный человек будет возвращён.

Развертывание в Службу приложений Azure

Приложение готово к развертыванию в Azure.

1. Создайте файл start.sh, чтобы gunicorn в службе приложение Azure может запускать uvicorn. Start.sh имеет одну строку:

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

2. Используйте az webapp up для развертывания кода в службу приложений. (Этот параметр -dryrun можно использовать для просмотра того, что выполняет команда, не создавая ресурс.)

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

3. Используйте команду az webapp config set, чтобы настроить Служба приложений для использования файла start.sh.

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

4. Используйте команду az webapp identity assign, чтобы включить управляемое удостоверение, назначаемое системой, для службы приложений.

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

   В этом кратком руководстве для демонстрации используется управляемое удостоверение, назначаемое системой. Управляемое удостоверение, назначаемое пользователем, более эффективно в более широком спектре сценариев. Для получения дополнительной информации см. Рекомендации по передовым методам использования управляемых удостоверений. Для примера использования управляемого удостоверения, назначаемого пользователем, совместно с pyodbc, см., например, статью «Миграция приложения Python на использование подключений без паролей в базе данных Azure SQL».

Подключение службы приложений к базе данных Azure SQL

В разделе "Настройка базы данных" настроена сеть и проверка подлинности Microsoft Entra для сервера базы данных SQL Azure. В этом разделе вы завершаете настройку базы данных и настраиваете Службу приложений, используя строку подключения для доступа к серверу базы данных.

Для выполнения этих команд можно использовать любое средство или интегрированную среду разработки, которые могут подключаться к Azure SQL Database, включая SQL Server Management Studio (SSMS), Azure Data Studio и Visual Studio Code с расширением SQL server mssql. Кроме того, можно использовать портал 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, см. в Руководстве: Использование управляемого удостоверения, назначаемого системой, для виртуальной машины Windows для доступа к Azure SQL. Дополнительные сведения о назначенных ролях см. в разделе "Роли фиксированной базы данных".

   Если отключить, а затем включить системное управляемое удостоверение в Службе приложений, тогда удалите пользователя и создайте его снова. Запустите 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
   

   Заполните <database-server-name> и <database-name> своими значениями.

   Без пароля строка подключения не содержит имя пользователя или пароль. Вместо этого, когда приложение выполняется в Azure, код использует DefaultAzureCredential из библиотеки Azure Identity для получения токена для работы с pyodbc.

Тестирование развернутого приложения

Перейдите по URL-адресу приложения, чтобы проверить, работает ли подключение к База данных SQL Azure. На странице обзора Службы приложений вы можете найти URL-адрес вашего приложения.

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

Добавьте /docs к URL-адресу, чтобы просмотреть пользовательский интерфейс Swagger и проверить методы API.

Поздравляем! Теперь приложение подключено к База данных SQL Azure как в локальных, так и в размещенных средах.

Связанный контент

• Перенос приложения Python на использование подключений без пароля с Azure SQL Database — отображает управляемую удостоверение, назначаемую пользователем.
• Бессерверные подключения для служб Azure
• Рекомендации по передовому опыту управляемых удостоверений