Краткое руководство: Использование Python для подключения и запроса данных в гибкий сервер базы данных Azure для PostgreSQL

Статья
2025-01-09

ОБЛАСТЬ ПРИМЕНЕНИЯ: База данных Azure для PostgreSQL — гибкий сервер

В этом кратком руководстве вы подключаетесь к гибкому экземпляру сервера базы данных Azure для PostgreSQL с помощью Python. Затем вы используете инструкции SQL для запроса, вставки, обновления и удаления данных в базе данных из macOS, Ubuntu Linux и платформ Windows.

Действия, описанные в этой статье, включают два метода проверки подлинности: проверку подлинности Microsoft Entra и проверку подлинности PostgreSQL. На вкладке "Без пароля" показана проверка подлинности Microsoft Entra, а на вкладке "Пароль " отображается проверка подлинности PostgreSQL.

Проверка подлинности Microsoft Entra — это механизм подключения к База данных Azure для PostgreSQL с помощью удостоверений, определенных в идентификаторе Microsoft Entra. С помощью проверки подлинности Microsoft Entra можно управлять удостоверениями пользователей базы данных и другими службами Microsoft в центральном расположении, что упрощает управление разрешениями. Дополнительные сведения см. в статье Проверка подлинности Microsoft Entra с помощью гибкого сервера базы данных Azure для PostgreSQL.

Проверка подлинности PostgreSQL использует учетные записи, хранящиеся в PostgreSQL. Если вы решили использовать пароли в качестве учетных данных для учетных записей, эти учетные данные будут храниться в user таблице. Так как эти пароли хранятся в PostgreSQL, вам нужно самостоятельно управлять сменой паролей.

В этой статье предполагается, что вы знакомы с разработкой на Python, но вы не знакомы с базой данных Azure для PostgreSQL, использующей гибкий сервер.

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

Учетная запись Azure с активной подпиской. Создайте учетную запись бесплатно .
Гибкий сервер базы данных Azure для PostgreSQL. Чтобы создать гибкий экземпляр сервера Базы данных Azure для PostgreSQL, см. статью "Создание гибкого сервера Базы данных Azure для PostgreSQL".
Python 3.8+.
Установщик пакета pip последней версии.

Добавление правил брандмауэра для рабочей станции клиента

Если вы создали гибкий экземпляр сервера База данных Azure для PostgreSQL с частным доступом (интеграция с виртуальной сетью), необходимо подключиться к серверу из ресурса в той же виртуальной сети, что и сервер. Вы можете создать виртуальную машину и добавить ее в виртуальную сеть (VNet), связанную с вашим гибким экземпляром сервера Azure Database for PostgreSQL. См. Создание и управление виртуальной сетью для гибкого сервера базы данных Azure для PostgreSQL с использованием Azure CLI.
Если вы создали гибкий экземпляр сервера База данных Azure для PostgreSQL с общедоступным доступом (разрешенными IP-адресами), вы можете добавить локальный IP-адрес в список правил брандмауэра на сервере. Ознакомьтесь с правилами брандмауэра гибкого сервера базы данных Azure для PostgreSQL и управления ими с помощью Azure CLI.

Настройка интеграции Microsoft Entra на сервере (только без пароля)

Если вы выполняете действия по проверке подлинности без пароля, проверка подлинности Microsoft Entra должна быть настроена для экземпляра сервера, и вы должны быть назначены администратором Microsoft Entra на экземпляре сервера. Выполните действия, описанные в разделе "Настройка интеграции Microsoft Entra", чтобы убедиться, что проверка подлинности Microsoft Entra настроена и что вы назначены администратором Microsoft Entra на экземпляре сервера.

Подготовка среды разработки

Перейдите в папку, в которой необходимо запустить код и создать и активировать виртуальную среду. Виртуальная среда — это автономный каталог для определенной версии Python и других пакетов, необходимых для этого приложения.

Выполните следующие команды, чтобы создать и активировать виртуальную среду:

Windows
macOS/Linux

py -3 -m venv .venv
.venv\Scripts\activate

python3 -m venv .venv
source .venv/bin/activate

Установка библиотек Python

Установите библиотеки Python, необходимые для выполнения примеров кода.

Без пароля (рекомендуется)
Пароль

Установите библиотеку azure-identity, которая обеспечивает поддержку аутентификации токенов Microsoft Entra в пакете SDK Azure.

pip install azure-identity

Установите модуль psycopg, который позволяет подключаться к базе данных PostgreSQL и запрашивать ее.

pip install psycopg

Добавление кода проверки подлинности

В этом разделе вы добавите код проверки подлинности в рабочий каталог и выполните все дополнительные действия, необходимые для проверки подлинности и авторизации с экземпляром сервера.

Без пароля (рекомендуется)
Пароль

Скопируйте следующий код в редактор и сохраните его в файле с именем get_conn.py.

import urllib.parse
import os

from azure.identity import DefaultAzureCredential

# IMPORTANT! This code is for demonstration purposes only. It's not suitable for use in production. 
# For example, tokens issued by Microsoft Entra ID have a limited lifetime (24 hours by default). 
# In production code, you need to implement a token refresh policy.

def get_connection_uri():

    # Read URI parameters from the environment
    dbhost = os.environ['DBHOST']
    dbname = os.environ['DBNAME']
    dbuser = urllib.parse.quote(os.environ['DBUSER'])
    sslmode = os.environ['SSLMODE']

    # Use passwordless authentication via DefaultAzureCredential.
    # IMPORTANT! This code is for demonstration purposes only. DefaultAzureCredential() is invoked on every call.
    # In practice, it's better to persist the credential across calls and reuse it so you can take advantage of token
    # caching and minimize round trips to the identity provider. To learn more, see:
    # https://github.com/Azure/azure-sdk-for-python/blob/main/sdk/identity/azure-identity/TOKEN_CACHING.md 
    credential = DefaultAzureCredential()

    # Call get_token() to get a token from Microsft Entra ID and add it as the password in the URI.
    # Note the requested scope parameter in the call to get_token, "https://ossrdbms-aad.database.windows.net/.default".
    password = credential.get_token("https://ossrdbms-aad.database.windows.net/.default").token

    db_uri = f"postgresql://{dbuser}:{password}@{dbhost}/{dbname}?sslmode={sslmode}"
    return db_uri

Получение сведений о подключении к базе данных.
1. В портале Azure найдите и выберите гибкий сервер вашей базы данных Azure для PostgreSQL.
2. На странице Обзор на сервере скопируйте полное Имя сервера. Полное имя сервера всегда имеет формат <имя-сервера>.postgres.database.azure.com.
3. В меню слева в разделе "Безопасность" выберите " Проверка подлинности". Убедитесь, что ваша учетная запись указана в разделе "Администраторы Microsoft Entra". Если это не так, выполните действия, описанные в разделе "Настройка интеграции Microsoft Entra" на сервере (только без пароля).
Задайте переменные среды для элементов URI подключения:
- Windows
- macOS/Linux
```
set DBHOST=<server-name>
set DBNAME=<database-name>
set DBUSER=<username>
set SSLMODE=require
```
```
export DBHOST=<server-name>
export DBNAME=<database-name>
export DBUSER=<username>
export SSLMODE=require
```
Замените следующие значения заполнителей в командах:
- <server-name> значением, скопированным из портала Azure.
- <username> с вашим именем пользователя Azure, например. [email protected].
- <database-name> с именем базы данных гибкого сервера Azure Database for PostgreSQL. При создании сервера автоматически создается база данных по умолчанию с именем postgres. Эту базу данных можно использовать или создать новую базу данных с помощью команд SQL.
Войдите в Azure на рабочей станции. Вы можете войти с помощью Azure CLI, Azure PowerShell или Azure Developer CLI. Например, чтобы войти с помощью Azure CLI, введите следующую команду:
```
az login
```
Код аутентификации используется DefaultAzureCredential для аутентификации с Microsoft Entra ID и получения токена, который дает право выполнять операции на вашем экземпляре сервера. DefaultAzureCredential поддерживает цепочку типов учетных данных проверки подлинности. Среди поддерживаемых учетных данных - те, с которыми вы вошли в средства разработчика, такие как Azure CLI, Azure PowerShell или Azure Developer CLI.

Скопируйте следующий код в редактор и сохраните его в файле с именем get_conn.py.

import urllib.parse
import os

def get_connection_uri():

    # Read URI parameters from the environment
    dbhost = os.environ['DBHOST']
    dbname = os.environ['DBNAME']
    dbuser = urllib.parse.quote(os.environ['DBUSER'])
    password = os.environ['DBPASSWORD']
    sslmode = os.environ['SSLMODE']
    db_uri = f"host={dbhost} dbname={dbname} user={dbuser} password={password} sslmode ={sslmode}"
    # Construct connection URI
    return db_uri

Получение сведений о подключении к базе данных.

В случае использования портала Azure выполните следующие действия:
1. В меню слева в портал Azure щелкните все ресурсы и найдите созданный сервер.
2. Щелкните имя сервера.
3. В меню ресурсов выберите Обзор.
4. Скопируйте значения, отображаемые как конечная точка и имя входа администратора.
5. Если вы забыли пароль для входа администратора, его можно сбросить с помощью кнопки "Сброс пароля ".
Задайте переменные среды для элементов URI подключения:
- Windows
- macOS/Linux
```
set DBHOST=<server-name>
set DBNAME=<database-name>
set DBUSER=<username>
set DBPASSWORD=<password>
set SSLMODE=require
```
```
export DBHOST=<server-name>
export DBNAME=<database-name>
export DBUSER=<username>
export DBPASSWORD=<password>
export SSLMODE=require
```
Замените следующие значения заполнителей в командах:
- Замените <server-name> и <username> на значения, скопированные с портала Azure.
- <password> с вашим паролем сервера.
- <database-name> с именем вашей базы данных гибкого сервера Azure Database for PostgreSQL. При создании сервера автоматически создается база данных по умолчанию с именем postgres. Эту базу данных можно использовать или создать новую базу данных с помощью команд SQL.

Выполнение примеров кода Python

Для каждого примера кода в этой статье сделайте следующее:

Создайте файл в текстовом редакторе.
Добавьте пример кода в файл.
Сохраните файл в папке проекта с расширением PY, например postgres-insert.py. При сохранении файла в ОС Windows обязательно выберите кодировку UTF-8.
В вашей папке проекта введите python, затем имя файла, например python postgres-insert.py.

Создание таблицы и вставка данных

Следующий пример кода подключается к гибкому серверу базы данных Azure для PostgreSQL с помощью функции psycopg.connect, и загружает данные с помощью инструкции SQL INSERT. Функция cursor.execute выполняет SQL-запрос к базе данных.

import psycopg
from get_conn import get_connection_uri

conn_string = get_connection_uri()

conn = psycopg.connect(conn_string) 
print("Connection established")
cursor = conn.cursor()

# Drop previous table of same name if one exists
cursor.execute("DROP TABLE IF EXISTS inventory;")
print("Finished dropping table (if existed)")

# Create a table
cursor.execute("CREATE TABLE inventory (id serial PRIMARY KEY, name VARCHAR(50), quantity INTEGER);")
print("Finished creating table")

# Insert some data into the table
cursor.execute("INSERT INTO inventory (name, quantity) VALUES (%s, %s);", ("banana", 150))
cursor.execute("INSERT INTO inventory (name, quantity) VALUES (%s, %s);", ("orange", 154))
cursor.execute("INSERT INTO inventory (name, quantity) VALUES (%s, %s);", ("apple", 100))
print("Inserted 3 rows of data")

# Clean up
conn.commit()
cursor.close()
conn.close()

При успешном выполнении кода возвращаются следующие данные:

Connection established
Finished dropping table (if existed)
Finished creating table
Inserted 3 rows of data

Чтение данных

Следующий пример кода подключается к вашему гибкому серверу базы данных Azure для PostgreSQL и использует cursor.execute с инструкцией SQL SELECT для чтения данных. Эта функция принимает запрос и возвращает набор результатов, который можно перебрать с помощью cursor.fetchall().

import psycopg
from get_conn import get_connection_uri

conn_string = get_connection_uri()

conn = psycopg.connect(conn_string) 
print("Connection established")
cursor = conn.cursor()

# Fetch all rows from table
cursor.execute("SELECT * FROM inventory;")
rows = cursor.fetchall()

# Print all rows
for row in rows:
    print("Data row = (%s, %s, %s)" %(str(row[0]), str(row[1]), str(row[2])))

# Cleanup
conn.commit()
cursor.close()
conn.close()

При успешном выполнении кода возвращаются следующие данные:

Connection established
Data row = (1, banana, 150)
Data row = (2, orange, 154)
Data row = (3, apple, 100)

Обновление данных

Следующий пример кода подключается к вашей гибкой базе данных Azure для сервера PostgreSQL и использует cursor.execute с инструкцией SQL UPDATE для обновления данных.

import psycopg
from get_conn import get_connection_uri

conn_string = get_connection_uri()

conn = psycopg.connect(conn_string) 
print("Connection established")
cursor = conn.cursor()

# Update a data row in the table
cursor.execute("UPDATE inventory SET quantity = %s WHERE name = %s;", (200, "banana"))
print("Updated 1 row of data")

# Cleanup
conn.commit()
cursor.close()
conn.close()

Удаление данных

Следующий пример кода подключается к гибкому серверу базы данных Azure для PostgreSQL и использует cursor.execute с инструкцией SQL DELETE для удаления элемента инвентаря, который вы ранее вставили.

import psycopg
from get_conn import get_connection_uri

conn_string = get_connection_uri()

conn = psycopg.connect(conn_string) 
print("Connection established")
cursor = conn.cursor()

# Delete data row from table
cursor.execute("DELETE FROM inventory WHERE name = %s;", ("orange",))
print("Deleted 1 row of data")

# Cleanup
conn.commit()
cursor.close()
conn.close()