编辑

通过

你当前正在访问 Microsoft Azure Global Edition 技术文档网站。 如果需要访问由世纪互联运营的 Microsoft Azure 中国技术文档网站，请访问 https://docs.azure.cn。

用于 Python 的 Azure MySQL/PostgreSQL 库Azure MySQL/PostgreSQL libraries for Python

  • 项目

MySQLMySQL

使用 MySQL 管理器和 pyodbc 通过 Python 处理 Azure MySQL 数据库中存储的资源和数据。Work with resources and data stored in Azure MySQL Database from python with the MySQL manager and pyodbc.

客户端 ODBC 驱动程序和 pyodbcClient ODBC driver and pyodbc

建议用于访问 Azure Database for MySQL 的客户端库是 Microsoft ODBC 驱动程序。The recommended client library for accessing Azure Database for MySQL is the Microsoft ODBC driver. 使用 ODBC 驱动程序可直接连接到数据库并执行 SQL 语句。Use the ODBC driver to connect to the database and execute SQL statements directly.

示例Example

连接到 Azure Database for MySQL，并选择销售表中的所有记录。Connect to a Azure Database for MySQL and select all records in the sales table. 可以从 Azure 门户获取数据库的 ODBC 连接字符串。You can get the ODBC connection string for the database from the Azure Portal.

SERVER = 'YOUR_SEVER_NAME' + '.mysql.database.azure.com'
DATABASE = 'YOUR_DATABASE_NAME'
USERNAME = 'YOUR_MYSQL_USERNAME'
PASSWORD = 'YOUR_MYSQL_PASSWORD'

DRIVER = '{MySQL ODBC 5.3 UNICODE Driver}'
cnxn = pyodbc.connect(
    'DRIVER=' + DRIVER + ';PORT=3306;SERVER=' + SERVER + ';PORT=3306;DATABASE=' + DATABASE + ';UID=' + USERNAME + ';PWD=' + PASSWORD)
cursor = cnxn.cursor()
selectsql = "SELECT * FROM SALES"  # SALES is an example table name
cursor.execute(selectsql)

管理 APIManagement API

使用管理 API 在订阅中创建和管理 MySQL 资源。Create and manage MySQL resources in your subscription with the management API.

要求Requirements

必须安装用于 Python 的 MySQL 管理库。You must install the MySQL management libraries for Python.

pip install azure-mgmt-rdbms

请参阅 Python SDK 身份验证页，了解有关使用管理客户端获取身份验证凭据的详细信息。Please see the Python SDK authentication page for details on obtaining credentials to authenticate with the management client.

示例Example

创建 MySQL 5.7 数据库资源，并使用防火墙规则限制对 IP 地址范围的访问。Create a MySQL 5.7 Database resource and restrict access to a range of IP addresses using a firewall rule.


from azure.mgmt.rdbms.mysql import MySQLManagementClient
from azure.mgmt.rdbms.mysql.models import ServerForCreate, ServerPropertiesForDefaultCreate, ServerVersion

SUBSCRIPTION_ID = "YOUR_AZURE_SUBSCRIPTION_ID"
RESOURCE_GROUP = "YOUR_AZURE_RESOURCE-GROUP_WITH_POSTGRES"
MYSQL_SERVER = "YOUR_DESIRED_MYSQL_SERVER_NAME"
MYSQL_ADMIN_USER = "YOUR_MYSQL_ADMIN_USERNAME"
MYSQL_ADMIN_PASSWORD = "YOUR_MYSQL_ADMIN_PASSOWRD"
LOCATION = "westus"  # example Azure availability zone, should match resource group


client = MySQLManagementClient(credentials=creds,
    subscription_id=SUBSCRIPTION_ID)

server_creation_poller = client.servers.create_or_update(
    resource_group_name=RESOURCE_GROUP,
    server_name=MYSQL_SERVER,
    ServerForCreate(
        ServerPropertiesForDefaultCreate(
            administrator_login=MYSQL_ADMIN_USER,
            administrator_login_password=MYSQL_ADMIN_PASSWORD,
            version=ServerVersion.five_full_stop_seven
        ),
        location=LOCATION
    )
)

server = server_creation_poller.result()

# Open access to this server for IPs
rule_creation_poller = client.firewall_rules.create_or_update(
    RESOURCE_GROUP
    MYSQL_SERVER,
    "some_custom_ip_range_whitelist_rule_name",  # Custom firewall rule name
    "123.123.123.123",  # Start ip range
    "167.220.0.235"  # End ip range
)

firewall_rule = rule_creation_poller.result()

了解管理 APIExplore the Management APIs

PostgreSQLPostgreSQL

使用 ODBC 驱动程序和 pyodbc 可直接连接到数据库并执行 SQL 语句。Use the ODBC driver and pyodbc to connect to the database and execute SQL statements directly.

详细了解 Azure Database for PostgreSQL。Learn more about Azure Database for PostgreSQL.

客户端 ODBC 驱动程序和 pyodbcClient ODBC driver and pyodbc

建议用于访问 Azure Database for PostgreSQL 的客户端库是 Microsoft ODBC 驱动程序和 pyodbc。The recommended client library for accessing Azure Database for PostgreSQL is the Microsoft ODBC driver and pyodbc.

示例Example

连接到 Azure Database for PostgreSQL，并选择 SALES 表中的所有记录。Connect to a Azure Database for PostgreSQL and select all records in the SALES table. 可以从 Azure 门户获取数据库的 ODBC 连接字符串。You can get the ODBC connection string for the database from the Azure Portal.

import pyodbc

SERVER = 'YOUR_SERVER_NAME.postgres.database.azure.com'
DATABASE = 'YOUR_DB_NAME'
USERNAME = 'YOUR_USERNAME'
PASSWORD = 'YOUR_PASSWORD'

DRIVER = '{PostgreSQL ODBC Driver}'
cnxn = pyodbc.connect(
    'DRIVER=' + DRIVER + ';PORT=5432;SERVER=' + SERVER +
    ';PORT=5432;DATABASE=' + DATABASE + ';UID=' + USERNAME +
    ';PWD=' + PASSWORD)
cursor = cnxn.cursor()
selectsql = "SELECT * FROM SALES" # SALES is an example table name
cursor.execute(selectsql)

管理 APIManagement API

要求Requirements

必须安装用于 Python 的 PostgreSQL 管理库。You must install the PostgreSQL management libraries for Python.

pip install azure-mgmt-rdbms

请参阅 Python SDK 身份验证页，了解有关使用管理客户端获取身份验证凭据的详细信息。Please see the Python SDK authentication page for details on obtaining credentials to authenticate with the management client.

示例Example

此示例在现有的 Postgres 服务器上创建新的 Postgres 数据库。In this example we will create a new Postgres database on our existing Postgres server.

from azure.mgmt.rdbms.postgresql import PostgreSQLManagementClient

SUBSCRIPTION_ID = "YOUR_AZURE_SUBSCRIPTION_ID"
RESOURCE_GROUP = "YOUR_AZURE_RESOURCE_GROUP_WITH_POSTGRES"
POSTGRES_SERVER = "YOUR_POSTGRES_SERVER_NAME"
DB_NAME = "YOUR_DESIRED_DATABASE_NAME"

client = PostgreSQLManagementClient(credentials, SUBSCRIPTION_ID)

db_creation_poller = client.databases.create_or_update(
    resource_group_name=RESOURCE_GROUP,
    server_name=POSTGRES_SERVER, database_name=DB_NAME)
db = db_creation_poller.result()

了解管理 APIExplore the Management APIs