Edit

Share via

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 basic 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 specified, all examples and commands 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 that you can use to connect to MySQL databases. You can also use other libraries, such as PyMySQL or SQLAlchemy, to connect to MySQL databases.

  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_Passwrod> # Change to your preferred admin password
export DB_NAME=<DB_Name> # Change to your preferred database name
export DB_PORT=3306
export version=ServerVersion.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_blob.py with the following code. This Python 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://docs.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 using the Azure CLI to execute the sample code. If your account has sufficient permissions to create resource groups and storage resources in your Azure subscription, the script should run successfully without additional configuration.

For use in production environments, we recommend that you authenticate with a service principal by setting 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, refer to Role-based access control (RBAC) in Azure.

For PostreSQL database server, see:

5: Run the script

  1. If you haven't already, sign in to Azure 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. You can 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='./BaltimoreCyberTrustRoot.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 certificate needed to communicate over TSL/SSL with your Azure Database for MySQL server. For more information, see Obtain an SSL Certificate in the Azure Database for MySQL documentation.

    #!/bin/bash
# Download Baltimore CyberTrust Root certificate required for Azure MySQL SSL connections
CERT_URL="https://www.digicert.com/CACerts/BaltimoreCyberTrustRoot.crt.pem"
CERT_FILE="BaltimoreCyberTrustRoot.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 Connection security. 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 storage resources created in this example.

Resource groups don't incur any ongoing charges in your subscription, but resources, like storage accounts, 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
#!/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="5.7"

# 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