This browser is no longer supported.
Upgrade to Microsoft Edge to take advantage of the latest features, security updates, and technical support.
Note
Access to this page requires authorization. You can trysigning in orchanging directories.
Access to this page requires authorization. You can trychanging directories.
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.
If you haven't already, set up an environment where you can run the code. Here are some options:
Configure a Python virtual environment usingvenv or your tool of choice. To start using the virtual environment, be sure to activate it. To install python, seeInstall Python.
#!/bin/bash# Create a virtual environmentpython -m venv .venv# Activate the virtual environmentsource .venv/Scripts/activate # only required for Windows (Git Bash)Use aconda environment. To install Conda, seeInstall Miniconda.
Use aDev Container inVisual Studio Code orGitHub Codespaces.
In this step, you install the Azure SDK libraries needed to create the database.
In your console, create arequirements.txt file that lists the management libraries used in this example:
azure-mgmt-resourceazure-mgmt-rdbmsazure-identitymysql-connector-pythonNote
Themysql-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 asPyMySQL orSQLAlchemy, to connect to MySQL databases.
In your console with the virtual environment activated, install the requirements:
pip install -r requirements.txtNote
On Windows, attempting to install the mysql library into a 32-bit Python library produces an error about themysql.h file. In this case, install a 64-bit version of Python and try again.
In this step, you set environment variables for use in the code in this article. The code uses theos.environ method to retrieve the values.
#!/bin/bashexport AZURE_RESOURCE_GROUP_NAME=<ResourceGroupName> # Change to your preferred resource group nameexport LOCATION=<Location> # Change to your preferred regionexport 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 nameexport DB_ADMIN_NAME=<DB_Admin_Name> # Change to your preferred admin nameexport DB_ADMIN_PASSWORD=<DB_Admin_Passwrod> # Change to your preferred admin passwordexport DB_NAME=<DB_Name> # Change to your preferred database nameexport DB_PORT=3306export version=ServerVersion.EIGHT0_21In this step, you create a Python file namedprovision_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, osfrom azure.identity import DefaultAzureCredentialfrom azure.mgmt.resource import ResourceManagementClientfrom azure.mgmt.rdbms.mysql_flexibleservers import MySQLManagementClientfrom azure.mgmt.rdbms.mysql_flexibleservers.models import Server, ServerVersion# Acquire a credential object using CLI-based authentication.credential = DefaultAzureCredential()# Retrieve subscription ID from environment variablesubscription_id = os.environ["AZURE_SUBSCRIPTION_ID"]# Retrieve resource group name and location from environment variablesRESOURCE_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 variablesdb_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 objectmysql_client = MySQLManagementClient(credential, subscription_id)# Provision the server and wait for the resultserver_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 connectRULE_NAME = "allow_ip"ip_address = os.environ["PUBLIC_IP_ADDRESS"]# Provision the rule and wait for completionpoller = 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 serverdb_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}")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, seeHow 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 toRole-based access control (RBAC) in Azure.
For PostreSQL database server, see:
If you haven't already, sign in to Azure using the Azure CLI:
az loginRun the script:
python provision_db.pyThe script takes a minute or two to complete.
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.
Create a file nameduse_db.py with the following code.
This code works only for MySQL; you use different libraries for PostgreSQL.
import osimport mysql.connectordb_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.txtimport pyodbcdriver = "{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. For more information, seeObtain an SSL Certificate in the Azure Database for MySQL documentation.
#!/bin/bash# Download Baltimore CyberTrust Root certificate required for Azure MySQL SSL connectionsCERT_URL="https://www.digicert.com/CACerts/BaltimoreCyberTrustRoot.crt.pem"CERT_FILE="BaltimoreCyberTrustRoot.crt.pem"echo "Downloading SSL certificate..."curl -o "$CERT_FILE" "$CERT_URL"Finally, run the code:
python use_db.pyIf you see an error that your client IP address isn't allowed, check that you defined the environment variablePUBLIC_IP_ADDRESS correctly. If you already created the MySQL server with the wrong IP address, you can add another in theAzure portal. In the portal, select the MySQL server, and then selectConnection security. Add the IP address of your workstation to the list of allowed IP addresses.
Run theaz 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/bashaz group delete -n $AZURE_RESOURCE_GROUP_NAME --no-waitYou can also use theResourceManagementClient.resource_groups.begin_delete method to delete a resource group from code. The code inExample: Create a resource group demonstrates usage.
The following Azure CLI commands complete the same provisioning steps as the Python script. For a PostgreSQL database, useaz postgres flexible-server commands.
#!/bin/bash#!/bin/bash# Set variablesexport LOCATION=<Location> # Change to your preferred regionexport AZURE_RESOURCE_GROUP_NAME=<ResourceGroupName> # Change to your preferred resource group nameexport DB_SERVER_NAME=<DB_Server_Name> # Change to your preferred DB server nameexport DB_ADMIN_NAME=<DB_Admin_Name> # Change to your preferred admin nameexport DB_ADMIN_PASSWORD=<DB_Admin_Password> # Change to your preferred admin passwordexport DB_NAME=<DB_Name> # Change to your preferred database nameexport DB_SERVER_VERSION="5.7"# Get public IP addressexport PUBLIC_IP_ADDRESS=$(curl -s https://api.ipify.org)# Provision the resource groupecho "Creating resource group: $AZURE_RESOURCE_GROUP_NAME"az group create \ --location "$LOCATION" \ --name "$AZURE_RESOURCE_GROUP_NAME"# Provision the MySQL Flexible Serverecho "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 addressecho "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 databaseecho "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."Was this page helpful?