Edit

Example: Use the Azure libraries to create a database

  • Article
  • 6 minutes to read

This example demonstrates how to use the Azure SDK management libraries in a Python script to create an Azure MySQL database. It also provides a simple script to query the database using the mysql-connector library (not part of the Azure SDK). You can use similar code to create a PostgreSQL or MariaDB database.

Equivalent Azure CLI commands are at later in this article. If you prefer to use the Azure portal, see Create a PostgreSQL server or Create a MariaDB server.

All the commands in this article work the same in Linux/macOS bash and Windows command shells unless noted.

1: Set up your local development environment

If you haven't already, follow all the instructions on Configure your local Python dev environment for Azure.

Be sure to create and activate a virtual environment for this project.

2: Install the needed Azure library packages

Create a file named requirements.txt with the following contents:

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

In a terminal 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: Write code to create the database

Create a Python file named provision_db.py with the following code. The comments explain the details. In particular, specify environment variables for AZURE_SUBSCRIPTION_ID and PUBLIC_IP_ADDRESS. The latter variable is your workstation's IP address for this sample to run. You can use WhatsIsMyIP to find your IP address.

import random, os
from azure.identity import AzureCliCredential
from azure.mgmt.resource import ResourceManagementClient
from azure.mgmt.rdbms.mysql import MySQLManagementClient
from azure.mgmt.rdbms.mysql.models import ServerForCreate, ServerPropertiesForDefaultCreate, ServerVersion

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

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

# Constants we need in multiple places: the resource group name and the region
# in which we provision resources. You can change these values however you want.
RESOURCE_GROUP_NAME = 'PythonAzureExample-DB-rg'
LOCATION = "westus"

# 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

# We use a random number to create a reasonably unique database server name.
# If you've already provisioned a database and need to re-run the script, set
# the DB_SERVER_NAME environment variable to that name instead.
#
# Also set DB_USER_NAME and DB_USER_PASSWORD variables to avoid using the defaults.

db_server_name = os.environ.get("DB_SERVER_NAME", f"PythonAzureExample-MySQL-{random.randint(1,100000):05}")
db_admin_name = os.environ.get("DB_ADMIN_NAME", "azureuser")
db_admin_password = os.environ.get("DB_ADMIN_PASSWORD", "ChangePa$$w0rd24")

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

# Provision the server and wait for the result
poller = mysql_client.servers.begin_create(RESOURCE_GROUP_NAME,
    db_server_name, 
    ServerForCreate(
        location=LOCATION,
        properties=ServerPropertiesForDefaultCreate(
            administrator_login=db_admin_name,
            administrator_login_password=db_admin_password,
            version=ServerVersion.FIVE7
        )
    )
)

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"]

# For the above code, create an environment variable named PUBLIC_IP_ADDRESS that
# contains your workstation's public IP address as reported by a site like
# https://whatismyipaddress.com/.

# 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}")

This code uses CLI-based authentication (using AzureCliCredential) because it demonstrates actions that you might otherwise do with the Azure CLI directly. In both cases, you're using the same identity for authentication. Depending on your environment, you may need to run az login first to authenticate.

To use such code in a production script (for example, to automate VM management), use DefaultAzureCredential (recommended) or a service principal based method as described in How to authenticate Python apps with Azure services.

For other database types, see:

4: Run the script

python provision_db.py

5: Insert a record and query the database

Create a file named use_db.py with the following code. Note the dependencies on the DB_SERVER_NAME, DB_ADMIN_NAME, and DB_ADMIN_PASSWORD environment variables. You get these values from the output of running the previous code provision_db.py or in the code itself.

This code works only for MySQL; you use different libraries for PostgreSQL and MariaDB.

import os
import mysql.connector

db_server_name = os.environ["DB_SERVER_NAME"]
db_admin_name = os.getenv("DB_ADMIN_NAME", "azureuser")
db_admin_password = os.getenv("DB_ADMIN_PASSWORD", "ChangePa$$w0rd24")

db_name = os.getenv("DB_NAME", "example-db1")
db_port = os.getenv("DB_PORT", 3306)

connection = mysql.connector.connect(user=f"{db_admin_name}@{db_server_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).

Next, Download the certificate needed to communicate over TSL/SSL with your Azure Database for MySQL server from https://www.digicert.com/CACerts/BaltimoreCyberTrustRoot.crt.pem and save the certificate file to the same folder as the Python file. For more information, see Obtain an SSL Certificate in the Azure Database for MySQL documentation.

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.

6: Clean up resources

az group delete -n PythonAzureExample-DB-rg  --no-wait

Run the az group delete command if you don't need to keep the resource group (and resources in it) created in this example. The --no-wait argument allows the command to return immediately instead of waiting for the operation to finish.

Run this command if you don't need to keep the resources provisioned in this example and would like to avoid ongoing charges in your subscription.

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 commands; for MariaDB, use az mariadb commands.

az group create -l centralus -n PythonAzureExample-DB-rg

az mysql server create -l westus -g PythonAzureExample-DB-rg -n PythonAzureExample-MySQL-12345 ^
    -u azureuser -p ChangePa$$w0rd24 --sku-name B_Gen5_1

# Change the IP address to the public IP address of your workstation, that is, the address shown
# by a site like https://whatismyipaddress.com/. 

az mysql server firewall-rule create -g PythonAzureExample-DB-rg --server PythonAzureExample-MySQL-12345 ^
    -n allow_ip --start-ip-address 10.11.12.13 --end-ip-address 10.11.12.13

az mysql db create -g PythonAzureExample-DB-rg --server PythonAzureExample-MySQL-12345 -n example-db1

See also