Skip to content

Latest commit

 

History

History
150 lines (92 loc) · 8.32 KB

azure-sdk-example-database.md

File metadata and controls

150 lines (92 loc) · 8.32 KB
title description ms.date ms.topic ms.custom
Create an Azure Database for MySQL - Flexible Server instance and database using the Azure SDK libraries
Use the management libraries in the Azure SDK libraries for Python to create an Azure Database for MySQL or Azure Database for PostgreSQL database.
02/02/2024
conceptual
devx-track-python, py-fresh-zinc

Example: Use the Azure libraries to create a database

This example demonstrates how to use the Azure SDK management libraries in a Python script to create an Azure Database for MySQL flexible server instance and 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 an Azure Database for PostgreSQL flexible server instance and database.

Equivalent Azure CLI commands are at later in this article. If you prefer to use the Azure portal, see Create a MySQL server or Create a PostgreSQL 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, set up an environment where you can run the code. Here are some options:

[!INCLUDE create_environment_options]

2: Install the needed Azure library packages

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

:::code language="txt" source="~/../python-sdk-docs-examples/db/requirements.txt":::

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.

:::code language="python" source="~/../python-sdk-docs-examples/db/provision_db.py":::

Authentication in the code

Later in this article, you sign in to Azure with the Azure CLI to run the sample code. If your account has permissions to create resource groups and storage resources in your Azure subscription, the code will run successfully.

To use such code in a production script, you can set environment variables to use a service principal-based method for authentication. To learn more, see How to authenticate Python apps with Azure services. You need to ensure that the service principal has sufficient permissions to create resource groups and storage resources in your subscription by assigning it an appropriate role in Azure; for example, the Contributor role on your subscription.

Reference links for classes used in the code

For PostreSQL database server, see:

4: Run the script

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

    az login

  2. Set the AZURE_SUBSCRIPTION_ID and PUBLIC_IP_ADDRESS environment variables. You can run the az account show command to get your subscription ID from the id property in the output. You can use WhatsIsMyIP to find your IP address.

    cmd

    set AZURE_SUBSCRIPTION_ID=00000000-0000-0000-0000-000000000000
set PUBLIC_IP_ADDRESS=<Your public IP address>

    bash

    AZURE_SUBSCRIPTION_ID=00000000-0000-0000-0000-000000000000
PUBLIC_IP_ADDRESS=<Your public IP address>

  3. Optionally, set the DB_SERVER_NAME, DB_ADMIN_NAME, and DB_ADMIN_PASSWORD environment variables; otherwise, code defaults are used.

  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.

:::code language="python" source="~/../python-sdk-docs-examples/db/use_db.py":::

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

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.

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

[!INCLUDE resource_group_begin_delete]

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.

cmd

:::code language="azurecli" source="~/../python-sdk-docs-examples/db/provision.cmd":::

bash

:::code language="azurecli" source="~/../python-sdk-docs-examples/db/provision.sh":::

See also