Creating a database with Python

Another way to work with data stored in Azure SQL Database is from the Python programming language. Creating a database with Python requires the use of the Azure Management API:

pip install azure-common
pip install azure-mgmt-sql
pip install azure-mgmt-resource

Now, let’s reproduce with Python the same steps we followed to create the database from the Microsoft Azure Portal.

First, we import the libraries required to manage Azure SQL databases:

from azure.common.client_factory import get_client_from_cli_profile
from azure.mgmt.resource import ResourceManagementClient
from azure.mgmt.sql import SqlManagementClient

In the next lines, we create the resource group. As we can see, the information provided is the same we informed during the creation process in the Microsoft Azure portal:

RESOURCE_GROUP = 'myResourceGroup'
LOCATION = 'eastus'  # example Azure availability zone, should match resource group
SQL_SERVER = 'ccsqlserver0546'
SQL_DB = 'ccDatabase'
USERNAME = 'ccazureuser'
PASSWORD = 'YOUR_PASSWORD'

# create resource client
resource_client = get_client_from_cli_profile(ResourceManagementClient)
# create resource group
resource_client.resource_groups.create_or_update(RESOURCE_GROUP, {'location': LOCATION})

sql_client = get_client_from_cli_profile(SqlManagementClient)

The following lines create the SQL Server with the information stored in the previous step:

# Create a SQL server
server = sql_client.servers.create_or_update(
    RESOURCE_GROUP,
    SQL_SERVER,
    {
        'location': LOCATION,
        'version': '12.0', # Required for create
        'administrator_login': USERNAME, # Required for create
        'administrator_login_password': PASSWORD # Required for create
    }
)

Finally, we are ready to create the SQL Database:

# Create a SQL database in the Basic tier
database = sql_client.databases.create_or_update(
    RESOURCE_GROUP,
    SQL_SERVER,
    SQL_DB,
    {
        'location': LOCATION,
        'collation': 'SQL_Latin1_General_CP1_CI_AS',
        'create_mode': 'default',
        'requested_service_objective_name': 'Basic'
    }
)