Create a Python Django Website with a SQL Server Database


By:   |   Updated: 2021-02-04   |   Comments (4)   |   Related: More > Python


Problem

Data are valuable assets and play an essential role in the business decision-making process. Many solutions, such as Power BI reports and SharePoint lists, can offer end-users timely access to the requested information. Under certain circumstances, the use of websites is also an ideal candidate for data access. There are many programming languages and frameworks for web development. Django, a Python-based web framework, is now the web development framework of many organizations' choice (Rubio, 2017). Some organizations may not have a dedicated web development team, still, they have some IT professionals to support their data and database management systems. These professionals expect a straightforward and step-by-step tutorial that quickly creates a proof-of-concept website using Microsoft SQL Server as a database backend. They can then decide if their companies would pursue building a full-featured website based on the Django framework.

Solution

Django, which is pronounced "JAN-go" with the "D" silent (Dayley, 2008), is an ideal framework for any IT professionals looking to develop websites and web applications. Django has a built-in web server that is used for development purposes. The framework supports several database management systems including Microsoft SQL Server. This article walks through developing and testing a website using Visual Studio 2019, an integrated development environment (IDE). At the end of this tutorial, we will create a proof-of-concept website as shown in Figure 1. The website, which is database-driven, retrieves data in a SQL Server database table and presents the contents on a web page.

django

Figure 1 The Proof-of-concept Website Produced from this Tutorial

This article does not explain how to design, develop, and deploy a website. It barely scratches the surface. Use this tutorial as a starting point for creating a database-driven website. When the proof-of-concept website gets approval, we can start to learn more about web development. For IT professionals who already know Python and HTML, the learning curve is small. Visual Studio Docs (Petersen et al., 2018) teach how to build a website from an empty Django project in Visual Studio. The Django website (Django, 2020) provides everything you need to know about Django. A free online version of "The Django Book" (Behrens, 2012) is useful for further learning.

The author organizes this Python Django tutorial into six sections that help navigate the steps to create a website that uses Microsoft SQL Server as a database backend. Section 1 covers the basics of creating a Django web development with Python project using Microsoft Visual Studio 2019 (Community Edition). In Section 2, the article goes through a four-step process of creating and configuring access to a SQL Server database from the Django framework. To demonstrate work with data and databases, Section 3 creates a Contact data model. We create a view function in Section 4. The function embeds data retrieved from the database into a web response object. Then, in Section 5, we write display logic in a template to format data. To add data into the database, we register the Contact data model in the Django admin application and use the admin interface to add two contacts in Section 6. Finally, the author wraps up this tutorial in the Summary section.

The author tested code in this article with Visual Studio 2019 (Community Edition), Django 2.2.17, and Python 3.9 on Windows 10 Home 10.0 <X64>. The DBMS is Microsoft SQL Server 2019 Developer Edition (64-bit).

1 – Creating a Django Python Web Project

There are several ways to start a Django project. This article creates a Django project using Visual Studio 2019 (Community Edition) with the installed Python development workload. This tip (Zhou, 2020) on MSSQLTips introduces installing Python development workload for Visual Studio 2019. After launching Visual Studio 2019, select the "Create a new project" button on the start screen, as shown in Figure 2.

visual studio 2019

Figure 2 The Visual Studio 2019 Start Window

The "Create a new project" window appears. To shorten the template list in the window, select the option "Python" as a programming language and the option "Web" as a project type. We should then be able to find the "Django Web Project" template on the list, as illustrated in Figure 3. This window also shows us other web frameworks. According to Herman, Django is a full-featured web framework that simplifies web development (Herman, 2020).

recent project templates

Figure 3 The Create a New Project Window

Select the "Django Web Project" from the template list and click on the "Next" button. The "Configure your new project" window appears. Enter a project name and select the location of this project. We use default settings for other configurations. The window should look like Figure 4.

django web project

Figure 4 The Configure Your New Project Window

Click on the button "Create" to create the project. The Visual Studio user interface should look like Figure 5. To avoid version conflicts, get into a habit of running each Python project in isolation. Thus, we create a virtual environment for each Python project and install the required packages. Check the default Python environment. Clicking on the arrow beside the project item "Python Environments," we observe that the default environment uses Python 3.9 in the author's computer. This tutorial also works on other versions, for instance, Python 3.7 and 3.8.

synchronize the database

Figure 5 The Visual Studio 2019 User Interface

In the Solution Explorer pane, when we right-click on the "Python Environment," a context menu appears as shown in Figure 6. We select the menu item "Add Environment" from the list.

solution explorer

Figure 6 The Python Environment Context Menu

The "Add environment" dialog appears. We enter a name for the virtual environment and use the default setting for other configurations. The dialog should look like Figure 7. Click on the "Create" button to accept all configurations and close the dialog.

django web project

Figure 7 The Add Environment Dialog

Expanding the environment node in the Solution Explorer pane, we can see the virtual environment with Django (2.2.17) installed, as shown in Figure 8. With the virtual environment, the project can run in an isolated environment. We can find a list of installed packages in the file "requirements.txt." When we want to recreate this virtual environment on other machines, we can use this text file to install the same packages.

solution explorer

Figure 8 The Installed Packages in the Virtual Environment

Switch to the Python Environment pane, as shown in Figure 9. If any package has a new version, an arrow indicator beside the version number appears. We can click on the arrows to upgrade corresponding packages; however, we use the default versions to avoid incompatibilities. Since this project runs in a specific virtual environment, other Python applications on the same computer can install packages with different versions.

python environments

Figure 9 The Python Environments Pane

By default, Django adds a SQLite database for the project. We can see the database file "db.sqlite3" in Figure 8. Companies that adopt Microsoft technologies may want to use SQL Server databases for their websites. We should make the Django framework use a SQL Server database as a database back-end before running the project. The project folder also contains other subfolders and files. Petersen and her colleagues examine the boilerplate code (Petersen et al., 2018). It is worth noting that, by default, the startup file is "manage.py."

2 – Using Microsoft SQL Server as a Database Backend

Django officially supports five database management systems: PostgreSQL, MariaDB, MySQL, Oracle, and SQLite (Django, 2020). Some third parties provide backends for other DBMSs, such as CockroachDB, Firebird, and Microsoft SQL Server. To use a Microsoft SQL Server database with Django, we need to install a third party package, "django-mssql-backend" (ES Solutions AB, 2020). We can follow a four-step process to set up the database backend for Django.

2.1 Installing Django backend for Microsoft SQL Server

Right-click on the virtual environment name "django_env" in the Solution Explorer pane. The context menu should look like Figure 10. We select the menu item "Manage Python Package…" from the context menu.

solution explorer

Figure 10 The Virtual Environment Context Menu

The Python Environments pane appears, as shown in Figure 11. We enter "django-mssql-backend" in the "search PyPI and installed packages" box and then click on "Run command: pip install django-mssql-backend". The package installation process starts.

python environments

Figure 11 Install Django backend for Microsoft SQL Server

After the installation process completes, we switch to the "Solution Explorer" pane. The installed package, "django-mssql-backend," should appear in the package list as shown in Figure 12.

solution expolorer

Figure 12 The List of Installed Packages in the Current Virtual Environment

2.2 Creating a SQL Server Database

Launch Microsoft SQL Server Management Studio. Create a new database, "django_db," and a database user, "django." The user "django" should be a member of the db_owner role. We connect to the empty database using the new user account to confirm the database creation, as shown in Figure 13. The database can reside in the local server or a remote server.

object explorer

Figure 13 Create a Database and a Database User

2.3 Configuring Database Access in Django Settings

After creating a SQL Server database, we can delete the SQLite database file "db.sqlite3." We then configure the SQL Server database access. When we create a new Django web project, Django automatically adds the settings.py file to the project’s root folder. The settings.py file holds the project’s settings. There is a database section in the file, and, by default, the section should look like Figure 14.

django web configuration

Figure 14 The Database Section in the settings.py

Open the file "settings.py," and substitute the highlighted code block in Figure 14 with the following code snippet. The package website (ES Solutions AB, 2020) explains these settings.

DATABASES = {
    'default': {
        'ENGINE': 'sql_server.pyodbc',
        'NAME': 'django_db',
        'USER': 'django',
        'PASSWORD': 'password',
        'HOST': '192.168.2.17',
        'PORT': '',
        'OPTIONS': {
            'driver': 'ODBC Driver 13 for SQL Server',
        },
    },
}  

2.4 Synchronizing the SQL Server Database

After configuring the access to the SQL Server database in the setting.py file, we run the synchronization process to create database objects in the SQL Server database. Right-click on the project name and select the "Python->Django Migrate…" menu item from the context menu, as shown in Figure 15.

solution explorer

Figure 15 Navigate to the Django Migrate Menu Item

The command menu item, "Django Migrate…," creates database objects in the database. The output in the Django Management Console, seen in Figure 16, shows that the synchronization process prepared necessary tables for these four applications: django.contrib.admin, django.contrib.auth, django.contrib.contenttypes, and django.contrib.sessions.

django web projects

Figure 16 Synchronize the Project to the Database

As mentioned in the readme file, shown in Figure 5, we need to create a superuser before running the project. Right-click on the project name and select the menu item "Python->Django Create Superuser" from the context menu, as shown in Figure 17.

django create superuser

Figure 17 Navigate to Django Create Superuser Menu Item

Follow instructions in the Django Management Console to create a superuser as shown in Figure 19. Ignore the warning messages.

django web project

Figure 18 Django Create the Superuser

Switch to the Microsoft SQL Server Management Studio and connect to the Django_db database. The "Django Migrate" command created several tables as shown in Figure 19. The superuser we created is in table "dbo.auth_user."

object explorer

Figure 19 Database Tables

Once the setup is complete, we can use Django's built-in web server to verify that the website is working as expected. The filename "manage.py" in the project folder should be bold to indicate that it is the startup file. Click on the "Web Server (Microsoft Edge)" button in the toolbar, as shown in Figure 20. The browser name inside the parentheses can be different from the screenshot. As of writing this tip, Visual Studio 2019 indicates an error in the startup file "manage.py" when running the project in Python 3.9. We can ignore this error. After launching the website, we see two windows: a web browser and a console window.

django web project

Figure 20 The Button in the Toolbar to Start the Project

The web browser window should look like Figure 21. This simple website has as many functions as a small-scale website. We can navigate pages on the website and log in to the site using the superuser account. If we can log in to the website, that means the Django framework has communicated with the SQL Server successfully.

django

Figure 21 The Website Home Page

The console window shown in Figure 22 displays server logs. When we navigate to other pages or sign into the website, the window shows all these activities. The window also can show the output of a variable when we use the Python print () function. Besides, we can observe error messages in this window if errors occur. Thus, we may use this window for debugging.

python print

Figure 22 The Console Window

We have had a taste of the Django Web Application Framework. After several steps, we developed a simple website using Visual Studio 2019. To make the website database-driven, we must be able to perform database operations through web interfaces. A fundamental process is retrieving data from a database and then presenting the formatted data to end-users through web pages. Components in various layers of the Django framework work together to complete the process.

Django, a loosely coupled framework, includes three layers: the model layer, the view layer, and the template layer. In the model layer, we use Django data models to represent data and data relationships in databases. Templates in the template layer, written in plain text, provide end-users with formatting data. The view layer, sitting between the model layer and template layer, retrieves data from databases via the models and then transports models to templates for displaying. We can manage modes, views, and templates independently.

3 – Creating the Contact Model

We can communicate with a SQL Server database via Django models. Django wants developers to define data models in one place, the model layer. In other places, database management systems should derive definitions from the model layer. Therefore, when using a SQL Server database with the framework, we need to synchronize changed data models to the database. Visual Studio 2019 provides command menu items to simplify this synchronization process.

For the sake of demonstration, we create the Contact model that defines the contact entity. We assume the entity has these attributes: name, city, state, phone number, email address, and the entity creation date. The synchronization process creates a database table corresponding to this model. Switch to the "Solution Explorer" pane. Open the file "app/models.py," and write a model class as follows:

# Create your models here. 
class Contact(models.Model): 
   name = models.CharField(max_length=50) 
   city = models.CharField(max_length=50) 
   state = models.CharField(max_length=2) 
   create_date = models.DateTimeField() 
   phone_number = models.CharField(max_length=20) 
   email = models.CharField(max_length=20) 

   def __str__(self): 
      return self.name 

After creating a Django model, we should synchronize the changes to the database. By running the "makemigrations" command, we store the changes as a migration. Navigate to the menu command item shown in Figure 23 and execute the command.

django make migrations

Figure 23 Navigate to the Django Make Migrations Command

Figure 24 shows the output of executing the "Make Migrations" command. This command creates a file "app\migrations\0001_initial.py" that contains the changes in the model.

django management console

Figure 24 The Make Migrations Output

We then execute the "Migrate" command to create the corresponding table in the database. We bring up a context menu by right-clicking on the project name. In the context menu, we drill down into the menu item: Python ->Django Migrate…, as shown in Figure 25, then execute the command.

django migrate

Figure 25 Navigate to the Django Migrate Command

Figure 26 shows the output of executing the "Migrate" command. This command applies the changes in the data model to the database. Connecting to the database, we find the new database table, "app_contact." The prefix "app" is the Django app name. The IDE created this app by default.

migrate command output

Figure 26 The Migrate Command Output

4 – Creating the View Function

We created the Contact model and the corresponding database table. We want to use a view to retrieve data from the database table and bundle data up in a web response object. End-users can get access to data embedded in the web response object through a web browser. Generally, the view, working similarly as a Python function, receives a web request, executes some business logic, and generates a web response (Behrens, 2012).

There are several ways to create views such as class-based views and view functions. For simplicity, we create a view function. Opening the file "app/views.py," we observe that the IDE created these view functions: home (), contact (), and about (). In this tutorial, we only need to update the function contact (). To make the data model available in the view, we use the "import" keyword. We add the following line to the file views.py:

from .models import Contact			 

We then modify the contact () function in the view layer. After retrieving data from the database table, we add the data into a context dictionary. Next, we call the render () function that returns a web response object. After this change, the function should look like the following:

def contact(request): 
   """Renders the contact page."""
   assert isinstance(request, HttpRequest) 

   # Retrieve all contacts in the database table
   contact_list = Contact.objects.order_by('name') 

   return render(
      request, 
      'app/contact.html', 
      {
         'title':'Contact', 
         'message':'Your contact page.',
         'year':datetime.now().year, 
         'contact_list': contact_list, # Embed data into the HttpResponse object
      }
   )

5 – Creating the Template

In the view function, we used the render () function that combines a Django template with a dictionary and returns a web response object. The Django template parses and formats data in the response object and provides end-users with styled contents. The template is composed of HTML elements, variables, and template tags. Besides the dictionary, the template also takes variables from URL options and context processors. Django offers a robust library of built-in filters to enhance the rendered output. With template tags and filters, we can write complex display logic. To put it simply, we design web pages with HTML elements and then use the variables, template tags, and filters to generate dynamic content.

The template uses the syntax – {{variable name}} – to output the content of a variable. The syntax of the template tags looks like {% tag %}. Create HTML files and add the variables and template tags to display dynamic contents. The project has some templates created already that can be viewed in the project folder "app/templates/app/." In this article, we modify the template "contact.html," to look like the following code snippet. It is worth noting that the variable "contact_list" is a key of the dictionary constructed in the view function. We use the "for" tag to navigate through the contact list and generate HTML code for each item.

{% extends "app/layout.html" %}
{% block content %}
<h2>{{ title }}.</h2> 
<h3>{{ message }}</h3> 
   {% if contact_list %}
      {% for contact in contact_list %}
      <address> 
         {{contact.name}}<br />
         {{contact.city}}, {{contact.state}}<br />
         <abbr title="Phone">P:</abbr>{{contact.phone_number}} <br />
         Email: <a href="mailto:{{contact.email}}">{{contact.email}}</a> 
      </address> 
      {% endfor %}
   {% else %}
      <p>No contact are available.</p> 
   {% endif %}
{% endblock %}

Let’s use Django's built-in web server to verify that the "Contact" page is working as expected. Click on the "Web Server" button in the toolbar. The project starts and the home page appears. When we navigate to the "Contact" page, it should look like Figure 27. Since there is no contact in the database and the contact list is empty, the "if" tag evaluates the variable as false. The template renders code inside the {% else %} block.

application name

Figure 27 The Contact Page without any Contact

6 – Manipulating Data in the Django Admin Interface

Visual Studio 2019 adds a Django admin application to a website when we create a Django web project. The application has a full-featured admin interface. Through the interface, we can manage data in the Django project’s database from an administrator perspective. By default, the URL to access the admin interface follows this pattern: "http://localhost:60655/admin/".

To access the database, we should register data models in an "admin.py" file in a Django app folder. Visual Studio 2019 does not automatically generate the "admin.py" file. We manually add this file and Figure 28 shows the folder structure.

django web project

Figure 28 The Django Application Folder Structure

Several methods can be used to register Django models for the Django admin in the "admin.py" file. Some of them can customize the admin behavior for a model. This article uses the most straightforward method to register the Contact model by adding the following code snippet to the "admin.py" file.

from django.contrib import admin
from .models import Contact

admin.site.register(Contact) 

Let’s start the project again and we will land on the home page. In a web browser, the URL in the address bar should look like "http://localhost:60655/". The port number may be different. We access the admin interface by changing the URL to http://localhost:60655/admin. The admin interface should look like Figure 29.

django administration

Figure 29 The Admin Application Login Interface

We use the superuser account, created in Section 2.4, to sign into the admin application. The interface should look like Figure 30. The "Contacts" item in the APP section links to the contact data in the database. Provided we click on the "Contacts" item, we should see a new page with a list of contacts. However, the list is empty at the moment.

django administration

Figure 30 The Django Administration Interface

We can add new contacts to the database table through the "Add" button shown in Figure 30. According to Figure 31 and Figure 32, we add two contacts. Navigating to the "Contact" page shown in Figure 1, we see two contacts we added through the admin interface. These two contacts are in the database table, "app_contact." The "Contact" page demonstrates that, through the Django framework, we retrieved data from the database and presented formatted data to end-users.

django framework

Figure 31 Add Tech to the app_contact Table

app contact

Figure 32 Add Python to the app_contact Table

By the end of this tutorial, we have created a website that with contents in a database. The techniques used in this article allow us to create database-driven websites or dynamic websites within which most contents are not in HTML files but are in database tables. With security settings, we can also allow a page to display contents according to user permissions. Users can see data that they are allowed to view. If we want to enhance the web page appearance, we can use front-end technologies such as CSS and JavaScript.

Summary

Data are valuable assets and people want to access data when and where they need it. Using websites to retrieve data from databases and deliver formatting contents to end-users is a candidate solution. Some companies may not have dedicated web developers. IT professionals in these companies can use the Django framework, a robust web development framework based on Python, to develop websites. According to Rubio, Django is one of the most productive frameworks for building the features needed by most medium to large scale web applications (Rubio, 2017). Additionally, the Django framework has healthy open-source communities; and, the framework website provides well-written documents (Django, 2020). With these resources, people can learn the Django framework quickly.

Many companies adopt Microsoft technologies and use SQL Server as a database management system. This article explored the process to quickly create a proof-of-concept website using Microsoft SQL Server as a database backend. We started by creating a Django web project. The project ran in an isolated virtual environment. We then installed Django backend for Microsoft SQL Server in the virtual environment. After we created a database and were granted rights, we configured database access in Django settings. Next, we synchronized the Django project to create initial database tables for the framework.

Django is a loosely coupled framework and the basic architecture includes three layers: the model layer, the view layer, and the template layer. We created a Contact data model in the model layer to structure and manipulate the website's data. In the view layer, we added a view function to process users' requests, retrieved data from the database via the data model, and returned web responses to end-users. We designed the web page through a template in which we used a designer-friendly syntax to format contents.

Finally, we briefly introduced the Django admin interface which we can use to manage entities in the Django project. We registered the Contact model in the application, then, added two contacts to the database through the admin interface. Putting all these together, we created a database-driven website that has contents not in HTML files but database tables. With the techniques introduced in this article, we can improve the website and make it more pertinent to real-world needs.

References

Behrens, M. (2012). The Django Book. https://django-book.readthedocs.io/en/latest/

Dayley, B. (2008). Sams Teach Yourself Django in 24 Hours. SAMS.

Django (2020). Django documentation. https://docs.djangoproject.com/en/3.1/

ES Solutions AB (2020). django-mssql-backend 2.8.1. https://pypi.org/project/django-mssql-backend/

Herman, M. (2020). Django vs. Flask in 2020: Which Framework to Choose. https://testdriven.io/blog/django-vs-flask/

Petersen, T., Brockschmidt, K., Warren, G., Rohm, W. A., Fosmark, T., Agiewich, R., Partlow, J., Coulter, D., & Caserio, C. (2018). Tutorial: Get started with the Django web framework in Visual Studio. https://docs.microsoft.com/en-us/visualstudio/python/learn-django-in-visual-studio-step-01-project-and-solution?view=vs-2019

Rubio, D. (2017). Beginning Django: Web Application Development and Deployment with Python. Manning Publications.

Zhou, N. (2020). Learning Python in Visual Studio 2019. MSSQLTips. https://www.mssqltips.com/sqlservertip/6442/learning-python-in-visual-studio-2019/

Next Steps
  • Some people may be reluctant to start a web project if they do not have any web development background. This article provided a straightforward, step-by-step tutorial to quickly create a proof-of-concept website using Microsoft SQL Server as a database backend. By the end of the tutorial, we created a simple database-driven website. This process demonstrated that web development is not complicated and we can quickly create a website with modern tools. Certainly, web development is not as simple as the sample in this article. The article also did not introduce the Django framework and Model-View-Template (MVT) design pattern in depth. After creating a website using this article, the author recommends a tutorial from Microsoft: Tutorial: Get started with the Django web framework in Visual Studio. This tutorial creates a website using the "Blank Django Web Project" template. Studying this tutorial helps us understand the website structure and the Django framework. After completing this tutorial, we can move to the "Getting started" tutorial provided by the website https://docs.djangoproject.com/. The application created in this tutorial has two parts: (1) end-users view polls and vote in them; (2) Administrators can add, update, and delete polls. The tutorial has not used Visual Studio, but it has explained the Django framework in depth.
  • Check out these related tips:





get scripts

next tip button



About the author
MSSQLTips author Nai Biao Zhou Nai Biao Zhou is a Senior Software Developer with 20+ years of experience in software development, specializing in Data Warehousing, Business Intelligence, Data Mining and solution architecture design.

View all my tips


Article Last Updated: 2021-02-04

Comments For This Article




Monday, March 15, 2021 - 4:20:03 PM - Nai Biao Zhou Back To Top (88401)
Hello Lakshmi,

Thank you for posting your solution here.

Regarding the ODBC Driver for SQL Server, my other tip: https://www.mssqltips.com/sqlservertip/6694/crud-operations-in-sql-server-using-python/ introduces how to verify whether a computer has the driver installed. Please see the subtitle, "1.2 ODBC Driver for SQL Server" in the tip.

Best Regards,
Nai Biao Zhou

Monday, March 15, 2021 - 1:00:42 AM - Lakshmi Narayanan Back To Top (88394)
Open the file "settings.py," and substitute the highlighted code block in Figure 14 with the following code snippet. The package website (ES Solutions AB, 2020) explains these settings.

not working

'driver': 'ODBC Driver 13 for SQL Server',


WORKING

'driver': 'SQL Server Native Client 10.0',


I am connecting to Remote server.. looking for such solutions for years.. thanks for the guide...

great work...


Thursday, March 4, 2021 - 3:55:13 PM - Nai Biao Zhou Back To Top (88338)
Hello Lakshmi,

For some reason, I can not see the errors in your comment.
By the way, you may check the database permission configuration. During my testing, the user is a member of the db_owner role (shown in Figure 13).

Best Regards,

Nai Biao Zhou

Thursday, March 4, 2021 - 2:23:19 PM - Lakshmi Narayanan Back To Top (88337)
HI,

2.3 Configuring Database Access in Django Settings

This step not working..

I need you help... The following error throws when Django Migrate action..

Pls help me...


download














get free sql tips
agree to terms