Editéieren

Example: Use the Azure libraries to create a database

This example demonstrates how to use the Azure SDK for Python management libraries to programmatically create an Azure Database for MySQL flexible server and a corresponding database. It also includes a script that uses the mysql-connector-python library (not part of the Azure SDK) to connect to and query the database.

You can adapt this example to create an Azure Database for PostgreSQL flexible server by modifying the relevant SDK imports and API calls.

If you prefer to use the Azure CLI, Equivalent Azure CLI commands are provided later in this article. For a graphical experience, refer to the Azure portal documentation:

Unless otherwise noted, all examples and commands in this article work consistently across Linux/macOS bash and Windows command shells.

1: Set up your local development environment

If you haven't already, set up an environment where you can run the code. Here are some options:

  • Configure a Python virtual environment using venv or your tool of choice. To start using the virtual environment, be sure to activate it. To install python, see Install Python.

    #!/bin/bash
# Create a virtual environment
python -m venv .venv
# Activate the virtual environment
source .venv/Scripts/activate # only required for Windows (Git Bash)

  • Use a conda environment. To install Conda, see Install Miniconda.

  • Use a Dev Container in Visual Studio Code or GitHub Codespaces.

2: Install the needed Azure library packages

In this step, you install the Azure SDK libraries needed to create the database.

  1. In your console, create a requirements.txt file that lists the management libraries used in this example:

    azure-mgmt-resource
azure-mgmt-rdbms
azure-identity
mysql-connector-python

    Note

    The mysql-connector-python library isn't part of the Azure SDK. It's a third-party library you can use to connect to MySQL databases. You can also use other libraries, such as PyMySQL or SQLAlchemy.

  2. In your console with the virtual environment activated, install the requirements:

    pip install -r requirements.txt

    Note

    On Windows, attempting to install the mysql library into a 32-bit Python library produces an error about the mysql.h file. In this case, install a 64-bit version of Python and try again.

3. Set environment variables

In this step, you set environment variables for use in the code in this article. The code uses the os.environ method to retrieve the values.

#!/bin/bash
export AZURE_RESOURCE_GROUP_NAME=<ResourceGroupName> # Change to your preferred resource group name
export LOCATION=<Location> # Change to your preferred region
export AZURE_SUBSCRIPTION_ID=$(az account show --query id --output tsv)
export PUBLIC_IP_ADDRESS=$(curl -s https://api.ipify.org)
export DB_SERVER_NAME=<DB_Server_Name> # Change to your preferred DB server name
export DB_ADMIN_NAME=<DB_Admin_Name> # Change to your preferred admin name
export DB_ADMIN_PASSWORD=<DB_Admin_Password> # Change to your preferred admin password
export DB_NAME=<DB_Name> # Change to your preferred database name
export DB_PORT=3306
export DB_SERVER_VERSION=EIGHT0_21

4: Write code to create and configure a MySQL Flexible Server with a database

In this step, you create a Python file named provision_db.py with the following code. This script uses the Azure SDK for Python management libraries to create a resource group, a MySQL flexible server, and a database on that server.

import random, os
from azure.identity import DefaultAzureCredential
from azure.mgmt.resource import ResourceManagementClient
from azure.mgmt.rdbms.mysql_flexibleservers import MySQLManagementClient
from azure.mgmt.rdbms.mysql_flexibleservers.models import Server, ServerVersion

# Acquire a credential object using CLI-based authentication.
credential = DefaultAzureCredential()

# Retrieve subscription ID from environment variable
subscription_id = os.environ["AZURE_SUBSCRIPTION_ID"]

# Retrieve resource group name and location from environment variables
RESOURCE_GROUP_NAME = os.environ["AZURE_RESOURCE_GROUP_NAME"]
LOCATION = os.environ["LOCATION"]

# Step 1: Provision the resource group.
resource_client = ResourceManagementClient(credential, subscription_id)

rg_result = resource_client.resource_groups.create_or_update(RESOURCE_GROUP_NAME,
    { "location": LOCATION })

print(f"Provisioned resource group {rg_result.name}")

# For details on the previous code, see Example: Provision a resource group
# at https://learn.microsoft.com/azure/developer/python/azure-sdk-example-resource-group


# Step 2: Provision the database server

# Retrieve server name, admin name, and admin password from environment variables

db_server_name = os.environ.get("DB_SERVER_NAME")
db_admin_name = os.environ.get("DB_ADMIN_NAME")
db_admin_password = os.environ.get("DB_ADMIN_PASSWORD")

# Obtain the management client object
mysql_client = MySQLManagementClient(credential, subscription_id)

# Provision the server and wait for the result
server_version = os.environ.get("DB_SERVER_VERSION") 

poller = mysql_client.servers.begin_create(RESOURCE_GROUP_NAME,
    db_server_name, 
    Server(
        location=LOCATION,
        administrator_login=db_admin_name,
        administrator_login_password=db_admin_password,
        version=ServerVersion[server_version]  # Note: dictionary-style enum access
    )
)

server = poller.result()

print(f"Provisioned MySQL server {server.name}")

# Step 3: Provision a firewall rule to allow the local workstation to connect

RULE_NAME = "allow_ip"
ip_address = os.environ["PUBLIC_IP_ADDRESS"]

# Provision the rule and wait for completion
poller = mysql_client.firewall_rules.begin_create_or_update(RESOURCE_GROUP_NAME,
    db_server_name, RULE_NAME, 
    { "start_ip_address": ip_address, "end_ip_address": ip_address }  
)

firewall_rule = poller.result()

print(f"Provisioned firewall rule {firewall_rule.name}")


# Step 4: Provision a database on the server

db_name = os.environ.get("DB_NAME", "example-db1")
 
poller = mysql_client.databases.begin_create_or_update(RESOURCE_GROUP_NAME,
    db_server_name, db_name, {})

db_result = poller.result()

print(f"Provisioned MySQL database {db_result.name} with ID {db_result.id}")

Authentication in the code

Later in this article, you sign in to Azure by using the Azure CLI to run the sample code. If your account has sufficient permissions to create resource groups and database resources in your Azure subscription, the script runs successfully without additional configuration.

For production environments, authenticate by using a service principal and set the appropriate environment variables. This approach enables secure, non-interactive access suitable for automation. For setup instructions, see How to authenticate Python apps with Azure services.

Ensure the service principal is assigned a role with adequate permissions, such as the Contributor role at the subscription or resource group level. For details on assigning roles, see Role-based access control (RBAC) in Azure.

For PostgreSQL database server, see:

5: Run the script

  1. If you didn't already, sign in to Azure by using the Azure CLI:

    az login

  2. Run the script:

    python provision_db.py

    The script takes a minute or two to complete.

6: Insert a record and query the database

In this step, you create a table in the database and insert a record. Use the mysql-connector library to connect to the database and run SQL commands.

  1. Create a file named use_db.py with the following code.

    This code works only for MySQL. You use different libraries for PostgreSQL.

    import os
import mysql.connector

db_server_name = os.environ["DB_SERVER_NAME"]
db_admin_name = os.getenv("DB_ADMIN_NAME")
db_admin_password = os.getenv("DB_ADMIN_PASSWORD")

db_name = os.getenv("DB_NAME")
db_port = os.getenv("DB_PORT")

connection = mysql.connector.connect(user=db_admin_name,
    password=db_admin_password, host=f"{db_server_name}.mysql.database.azure.com",
    port=db_port, database=db_name, ssl_ca='./DigiCertGlobalRootG2.crt.pem')

cursor = connection.cursor()

"""
# Alternate pyodbc connection; include pyodbc in requirements.txt
import pyodbc

driver = "{MySQL ODBC 5.3 UNICODE Driver}"

connect_string = f"DRIVER={driver};PORT=3306;SERVER={db_server_name}.mysql.database.azure.com;" \
                 f"DATABASE={DB_NAME};UID={db_admin_name};PWD={db_admin_password}"

connection = pyodbc.connect(connect_string)
"""

table_name = "ExampleTable1"

sql_create = f"CREATE TABLE {table_name} (name varchar(255), code int)"

cursor.execute(sql_create)
print(f"Successfully created table {table_name}")

sql_insert = f"INSERT INTO {table_name} (name, code) VALUES ('Azure', 1)"
insert_data = "('Azure', 1)"

cursor.execute(sql_insert)
print("Successfully inserted data into table")

sql_select_values= f"SELECT * FROM {table_name}"

cursor.execute(sql_select_values)
row = cursor.fetchone()

while row:
    print(str(row[0]) + " " + str(row[1]))
    row = cursor.fetchone()

connection.commit()

    All of this code uses the mysql.connector API. The only Azure-specific part is the full host domain for MySQL server (mysql.database.azure.com).

  2. Next, download the root certificate needed to communicate over TLS/SSL with your Azure Database for MySQL server. Azure MySQL Flexible Server uses the DigiCert Global Root G2 certificate. For more information, see Connect to Azure Database for MySQL - Flexible Server with encrypted connections.

    #!/bin/bash
# Download DigiCert Global Root G2 certificate required for Azure MySQL SSL connections
CERT_URL="https://cacerts.digicert.com/DigiCertGlobalRootG2.crt.pem"
CERT_FILE="DigiCertGlobalRootG2.crt.pem"
echo "Downloading SSL certificate..."
curl -o "$CERT_FILE" "$CERT_URL"

  3. Finally, run the code:

    python use_db.py

If you see an error that your client IP address isn't allowed, check that you defined the environment variable PUBLIC_IP_ADDRESS correctly. If you already created the MySQL server with the wrong IP address, you can add another in the Azure portal. In the portal, select the MySQL server, and then select Networking. Add the IP address of your workstation to the list of allowed IP addresses.

7: Clean up resources

Run the az group delete command if you don't need to keep the resource group and database resources created in this example.

Resource groups don't incur any ongoing charges in your subscription, but resources in the resource group might continue to incur charges. It's a good practice to clean up any group that you aren't actively using. The --no-wait argument allows the command to return immediately instead of waiting for the operation to finish.

#!/bin/bash
az group delete -n $AZURE_RESOURCE_GROUP_NAME --no-wait

You can also use the ResourceManagementClient.resource_groups.begin_delete method to delete a resource group from code. The code in Example: Create a resource group demonstrates usage.

For reference: equivalent Azure CLI commands

The following Azure CLI commands complete the same provisioning steps as the Python script. For a PostgreSQL database, use az postgres flexible-server commands.

#!/bin/bash

# Set variables
export LOCATION=<Location> # Change to your preferred region
export AZURE_RESOURCE_GROUP_NAME=<ResourceGroupName> # Change to your preferred resource group name
export DB_SERVER_NAME=<DB_Server_Name> # Change to your preferred DB server name
export DB_ADMIN_NAME=<DB_Admin_Name> # Change to your preferred admin name
export DB_ADMIN_PASSWORD=<DB_Admin_Password> # Change to your preferred admin password
export DB_NAME=<DB_Name> # Change to your preferred database name
export DB_SERVER_VERSION="8.4"

# Get public IP address
export PUBLIC_IP_ADDRESS=$(curl -s https://api.ipify.org)

# Provision the resource group
echo "Creating resource group: $AZURE_RESOURCE_GROUP_NAME"
az group create \
    --location "$LOCATION" \
    --name "$AZURE_RESOURCE_GROUP_NAME"

# Provision the MySQL Flexible Server
echo "Creating MySQL Flexible Server: $DB_SERVER_NAME"
az mysql flexible-server create \
    --location "$LOCATION" \
    --resource-group "$AZURE_RESOURCE_GROUP_NAME" \
    --name "$DB_SERVER_NAME" \
    --admin-user "$DB_ADMIN_NAME" \
    --admin-password "$DB_ADMIN_PASSWORD" \
    --sku-name Standard_B1ms \
    --version "$DB_SERVER_VERSION" \
    --yes

# Provision a firewall rule to allow access from the public IP address
echo "Creating firewall rule for public IP: $PUBLIC_IP_ADDRESS"
az mysql flexible-server firewall-rule create \
    --resource-group "$AZURE_RESOURCE_GROUP_NAME" \
    --name "$DB_SERVER_NAME" \
    --rule-name allow_ip \
    --start-ip-address "$PUBLIC_IP_ADDRESS" \
    --end-ip-address "$PUBLIC_IP_ADDRESS"

# Provision the database
echo "Creating database: $DB_NAME"
az mysql flexible-server db create \
    --resource-group "$AZURE_RESOURCE_GROUP_NAME" \
    --server-name "$DB_SERVER_NAME" \
    --database-name "$DB_NAME"

echo "MySQL Flexible Server and database created successfully."

See also

  • Last updated on