DEV Community: Abhiman Tiwari

Create REST API in Python with Django, using the Django REST Framework and Azure SQL

Abhiman Tiwari — Thu, 03 Mar 2022 12:44:20 +0000

Django is a Python-based open-source web framework. It is a popular and well-liked web framework among developers all around the world. But wouldn’t it be amazing to build a website with Django while also taking advantage of Azure SQL database’s security, performance, high availability, and other great capabilities?

In this article, we will create a REST API in Python with Django, using the Django REST Framework and Azure SQL database that allows you to perform CRUD operations. Along the way, I will also show you how you can deploy your Django-based app on Azure app service.

mssql-django is a fork of django-mssql-backend. This driver provides an enterprise database connectivity option for the Django Web Framework, with support for Microsoft SQL Server and Azure SQL Database.
mssql-django supports Django 2.2, 3.0, 3.1, 3.2 and 4.0.

Download the sample code

Clone this repository:

git clone https://github.com/azure-samples/azure-sql-db-django

Alternatively, you can clone the code using Visual Studio Code as well.

Open the folder location where you want to clone the code
In Visual Studio Code, select Source Control > ... > Clone (or select View, Command Palette and enter Git:Clone), paste the Git repository URL, and then select Enter</>.

Once you have the code downloaded to your local computer. You should see folder structure as below:

azure-sql-db-django
 ┣ customerapi
 ┃ ┣ migrations
 ┃ ┣ admin.py
 ┃ ┣ apps.py
 ┃ ┣ models.py
 ┃ ┣ serializers.py
 ┃ ┣ tests.py
 ┃ ┣ urls.py
 ┃ ┣ views.py
 ┃ ┗ __init__.py
 ┣ django-sql-project
 ┃ ┣ asgi.py
 ┃ ┣ settings.py
 ┃ ┣ urls.py
 ┃ ┣ wsgi.py
 ┃ ┗ __init__.py
 ┣ LICENSE
 ┣ manage.py
 ┣ README.md
 ┗ requirements.txt

Create Azure SQL Database

If you don't have an Azure SQL server already, you can create one (no additional costs for a server) by running the following AZ CLI command (via WSL, or Linux or Azure Cloud Shell):

Create a resource group if you don't have one already created:

az group create -l <location> -n <MyResourceGroup>

Create the Database Server:

az sql server create -n <server-name> -l <location> --admin-user <admin-user> --admin-password <admin-password> -g <resource-group>

Make sure to note the database name, username and password somewhere safe.

Create a new Azure SQL database:

az sql db create -g <resource-group> -s <server-name> -n my-db --service-objective GP_Gen5_2

Make sure you have the firewall configured to allow your machine to access Azure SQL:

az sql server firewall-rule create --resource-group <resource-group> --server <server-name> --name AllowMyClientIP_1 --start-ip-address <your_public_ip> --end-ip-address <your_public_ip>

You can get your public IP here or through other ways, for example: https://ifconfig.me/

Setup the local environment

Make sure you have Python => 3.8.10 installed on your machine.

To confirm you can run python or python3 on terminal.

python --version

All the commands shown here are for Windows. If you are working on any other OS/ environment e.g. Linux, MAC etc. change these commands accordingly.

Make sure you have venv installed and create a new virtual environment in the folder where you have cloned the repository:

python3 -m venv env

In the above command the second parameter env is the location to create virtual environment.\
venv will create a virtual Python installation in the env folder.\
You should exclude your virtual environment directory from your version control system using .gitignore or similar.

Before you start installing or using Django packages in your virtual environment, you'll need to activate it, for example on Windows:

.\env\Scripts\activate

You can confirm, you’re in the virtual environment by checking the location of your Python interpreter. It should be in the env directory.
where python
As long as your virtual environment is activated, pip will install packages into that specific environment and you’ll be able to import and use packages in your Python application.

Install the dependencies

Make sure virtual environment is active and you are into your <working_folder>\azure-sql-db-django.

You can install all the required packages in one go by running the below command and directly move to Database ConnectionString Configuration section or may follow the instructions to execute them one by one:
pip install -r .\requirements.txt

Install Django:

pip install django

Also, install Django REST framework for REST API:

pip install djangorestframework

You should also install django-cors-headers. It's a Django application for handling the server headers required for Cross-Origin Resource Sharing (CORS).

pip install django-cors-headers

Adding CORS headers allows your resources to be accessed on other domains. It’s important you understand the implications before adding the headers since you could be unintentionally opening up your site’s private data to others.

Configure Azure SQL connectivity with Django App

Dependencies

pyodbc 3.0 or newer
Microsoft SQL Server ODBC driver

Installation

Install ODBC driver: Instructions
Install pyodbc:
```
pip install pyodbc
```
Install mssql-django:
```
pip install mssql-django
```

Configuration

Configure the Database ConnectionString in the settings.py file used by your Django project to use mssql and the related ODBC driver.

DATABASES = {
    'default': {
        'ENGINE': 'mssql',
        'PORT': '1433',
        'OPTIONS': {
                'driver': 'ODBC Driver 17 for SQL Server',
            },
    }
}

To connect Azure SQL DB using MSI (Managed Service Identity), you can have settings as below:

DATABASES = {
    'default': {
         'ENGINE': 'mssql',
         'Trusted_Connection': 'no', 
         'OPTIONS': { 
             'driver': 'ODBC Driver 17 for SQL Server', 
             'extra_params': "Authentication=ActiveDirectoryMsi;Encrypt=yes;TrustServerCertificate=no" }
     }
}

Please note that for this sample we decided to avoid having secrets in the settings.py file. All sensitive details will be loaded from environment variables. For development purposes you can create an .env file, using the provided .env.sample, to provide database connection info.

mssql-django doesn't support using time zones yet, so the recommendation is to ensure the USE_TZ option is set to False.
DATABASES = {
...
}
# set this to False if the backend does not support using time zones
USE_TZ = False

Run the migrations command to propagate changes you made to your models (creating a class, adding a field, deleting a model, etc.) into your database schema.

python manage.py makemigrations customerapi

python manage.py migrate customerapi

Once migration is done successfully, you'll see that database objects are created in your database. You can connect to your database and verify. Quickstart available here: Quickstart: Use Azure Data Studio to connect and query Azure SQL database

Run sample locally

Execute the below command, to start the development web server on the local machine. By default, the server runs on port 8000 on the IP address 127.0.0.1. You can pass in an IP address and port number explicitly.

Initialize Django - this is needed only the first time

python manage.py migrate

python manage.py createsuperuser

and then run the server

python manage.py runserver

Once the Django application is running, you'll see something like:

...
System check identified no issues (0 silenced).
February 04, 2022 - 14:32:15
Django version 4.0.2, using settings 'django-sql-project.settings'
Starting development server at http://127.0.0.1:8000/
Quit the server with CTRL-BREAK.

Using a REST Client (like Insomnia, Postman, or curl), you can now call your API, for example:

curl -X GET http://127.0.0.1:8000/customerapi/customer/

And you’ll get a response something like (based on available data in tables):

[
    {"CustomerId": 1, "CustomerName": "Keith"},{"CustomerId": 2, "CustomerName": "Janet"},{"CustomerId": 4, "CustomerName": "Cortana"},{"CustomerId": 5, "CustomerName": "Michael"},{"CustomerId": 7, "CustomerName": "David"},{"CustomerId": 8, "CustomerName": "Mike"}
]

Check out the sample to test all the CRUD operations.

Deploy your application code to Azure App Service

Azure App service supports multiple methods to deploy your application code to Azure including support for GitHub Actions and all major CI/CD tools. This article focuses on how to deploy your code from your local workstation to Azure.

Prerequisites

If you don't have an Azure subscription, create a free account before you begin.

This article requires that you're running the Azure CLI version 2.0 or later locally. To see the version installed, run the az --version command. If you need to install or upgrade, see Install Azure CLI.

You'll need to login to your account using the az login command.

az login

If you have multiple subscriptions, choose the appropriate subscription in which the resource should be created. Select the specific subscription ID under your account using az account set command. Substitute the subscription ID property from the az login output for your subscription into the subscription ID placeholder.

az account set --subscription <subscription id>

Refer this article, to know more about configuring a Linux Python app for Azure app Service.

Configure static files

In your settings file, define STATIC_URL and STATIC_ROOT, for example:

# Static files (CSS, JavaScript, Images)
# https://docs.djangoproject.com/en/4.0/howto/static-files/

STATIC_URL = '/static/'
STATIC_ROOT = 'static'

Run the python manage.py collectstatic to gather static files into a directory at STATIC_ROOT path for the admin site:

python manage.py collectstatic

Create the App Service webapp and deploy code from a local workspace

In the terminal, make sure you're in the repository root (<working_folder>\azure-sql-db-django) that contains the app code.

Run the below az webapp commands:

az webapp up create a webapp and deploy code from a local workspace to the app. Python apps are created as Linux apps by default.

# Create a web app and deploy the code
az webapp up -g <MyResourceGroup> -l <location> -p <azure-sql-db-django-plan> --sku B1 -n <azure-sql-db-django-api> -r 'PYTHON:3.9'

# Configure database information as environment variables
az webapp config appsettings set --settings DB_SERVER="<azure-sql-server-name>.database.windows.net" DB_NAME="<db-name>" DB_USER="<db-user-id>" DB_PASSWORD="<db-password>"

For the --resource-group -g, you can use the same resource group you created for the Database in the previous section.
For the --location -l argument, use the same location as you did for the database in the previous section.
Create the App Service plan azure-sql-db-django-plan in the Basic pricing tier (B1), if it doesn't exist. --plan and --sku are optional.
For the --runtime -r, canonicalize runtime in the format of Framework|Version, e.g. "PYTHON|3.9". Allowed delimiters: "|" or ":". Use az webapp list-runtimes --linux --output table for available list.
The app code expects to find database information in a number of environment variables. To set environment variables in App Service, you create "app settings" with the az webapp config appsettings set command.

App Service detects a Django project by looking for a wsgi.py file in each subfolder, which manage.py startproject creates by default. When App Service finds that file, it loads the Django web app. For more information, see Configure built-in Python image.

Browse to the app running on Azure App Service

The Python Django sample code is running a Linux container in App Service using a built-in image.

Browse to the deployed application in your web browser at the URL https://<app-name>.azurewebsites.net/admin or make a call to the API https://<app-name>.azurewebsites.net/customerapi/customer/ using any other REST clients (like Insomnia, Postman, or curl).

Congratulations! You're running a Python Django app in Azure App Service for Linux, with Azure SQL database.

You can use mssql-django as a backend for your existing Django 4.0 project with no major change if that's already configured for MS SQL Server.

If you encounter any issues or have any feedback about mssql-django, head over to our mssql-django project repository and submit an issue.

Saving Bot Activities in Azure SQL Database

Abhiman Tiwari — Thu, 10 Feb 2022 10:23:06 +0000

In this blog post, we’ll walkthrough of how to integrate Bot Framework v4 SDK with Azure SQL Database and save Bot activities/ conversations into the database.

We’ll leverage Bot.Builder.Community.Storage.EntityFramework package to save conversations using EntityFrameworkTranscriptStore with TranscriptLoggerMiddleware into Azure SQL Database.

Let’s start –

Create Azure Sql Database:

If you are new to Azure SQL and looking to understand more about different offerings of Azure SQL, you can start with this article.

Once you have decided the type of Azure SQL Database that suits your requirement, you can navigate to the Select SQL Deployment options page and create your Database.

For this demo, I have created a single database in the serverless compute tier.

You can follow this article which explains the steps of creating an Azure SQL Database.

Connect to the database:

Once your Azure sql database is created, you can use the Query editor (preview) in the Azure portal to connect to the database and execute queries. Alternatively, you can also connect your database using SSMS and Azure Data Studio, etc.

In the Azure portal, search for and select SQL databases, and then select your database from the list.
On the page for your database, select Query editor (preview) in the left menu.
Enter your server admin login information and select OK

Note: Click on the Connection strings link in the above blade and copy the connection string somewhere which we’ll use later in this demo.

Create tables in your Azure SQL database:

Now, you need to create tables in your Azure SQL database to store the Bot Activities.

Execute the CreateTableScript.sql script in your Azure SQL Database.
Once you execute the CreateTableScript.sql script in your Azure SQL Database, you should be able to see these two tables ( BotDataEntity & TranscriptEntity) created.

We have just created the table, it doesn’t contain any data yet.

Getting Bot project ready:

For this Demo, we will be using Bot Framework v4 Echo bot sample which accepts input from the user and echoes it back.

You may download the working code sample from GitHub or make the following changes to your existing Bot project.

Import these packages into your Bot project.

Add the following code to your bot project’s Startup.cs.

var loggerConnectionString = Configuration["StoreConnectionString"];
var logger = new EntityFrameworkTranscriptStore(loggerConnectionString);
     services.AddSingleton<ITranscriptStore>(logger);

Add ITranscriptStore as one of the parameters to AdapterWithErrorHandler class, and Use TranscriptLoggerMiddleware:

public AdapterWithErrorHandler(ITranscriptStore transcriptLogger, IConfiguration configuration, ILogger<BotFrameworkHttpAdapter> logger, ConversationState conversationState = null)
              : base(configuration, logger)
          {
              Use(new TranscriptLoggerMiddleware(transcriptLogger));

The ConnectionString you have copied from Azure sql database, put that into your Bot project against StoreConnectionString key in appsettings.json file, something like shown below –

      {
        "MicrosoftAppId": "",
        "MicrosoftAppPassword": "",
        "StoreConnectionString": "Server=tcp:YourSQLServerName.database.windows.net,1433;Initial Catalog=YourDatabaseName;Persist Security Info=False;User ID=YourSqlUserId;Password=YourSqlPassword;MultipleActiveResultSets=False;Encrypt=True;TrustServerCertificate=False;Connection Timeout=30;"
      }

Note:
I have left MicrosoftAppId and MicrosoftAppPassword blank as I’ll be running my Bot locally in Emulator but if your Bot is deployed on some remote server e.g., Azure app service or Bot needs to communicate to other external services e.g., Cognitive services, then you will need to put MicrosoftAppId and MicrosoftAppPassword as well to authenticate your Bot.

We are almost done. Now you should be able to run your Bot project.

Let’s see it in Action:

Run your bot code/ project (in case you have already hosted it to some web server e.g., Azure app service, you may skip this part) –

If you are running from the terminal, navigate to your Bot project folder and run dotnet run command.

    #run the bot
    dotnet run

If you are running from Visual Studio

- Launch Visual Studio
- File -> Open -> Project/Solution
- Navigate to your Bot project folder e.g., EntityFrameworkTranscriptStoreExample
- Select your Bot project file e.g., EntityFrameworkTranscriptStoreExample.csproj file
- Press F5 to run the project

Note: As a prerequisite to run this project, make sure you have .NET Core (>=3.1) version installed on your machine.

Test the Bot using Bot Framework Emulator:

Bot Framework Emulator is a desktop application that allows Bot developers to Test and debug their bots on localhost or run remotely through a tunnel. You may install the Bot Framework Emulator from here.

Connect to the bot using Bot Framework Emulator:

Launch Bot Framework Emulator
File -> Open Bot
Enter a Bot URL e.g., http://localhost:3978/api/messages. You may also enter the URL of the remote web server hosting the Bot code but, in that case, you will also need to provide MicrosoftAppId and MicrosoftAppPassword for Bot to successfully authenticate.

Once connected, send some messages to your Bot, and it should echo them back.

Logs:

Let’s Connect the database to see if Bot Activities are being recorded. You should see the messages saved into the Azure SQL tables we have created.

BotDataEntity should store one entry for each unique connection.
TranscriptEntity should contain all the conversations.

If you query the tables, you should be able to fetch all the messages exchanged between the user and Bot.

Caution:
Please note that depending on your Bot, you may need to specify in your Bot’s terms of use directly to notify your users whenever storing user data is involved.