Building a customized dashboard using SQL Server and Dash. (Part 1: SQL Server)

Photo by Stephen Dawson on Unsplash

“Visualization gives you answers to questions you didn’t know you had.” — Ben Schneiderman

While working on an automation project the biggest challenge is to showcase the efficiency you brought in. This is easier said than done as most of the time clients you deal with will have no interest in seeing the RAW Data, and to be frank, no one does!

So I decided to build a platform that would allow everyone not only to visually present the data but also act as a control room for the bot (Automated Process) and below was the result.

Dashboard

In this tutorial, we are going to learn how to use various python tools to create a similar Dashboard. This will be a 3 part series with-

Part 1- Learn how to interact with SQL Server and what kind of data to store.

Part 2- Discussing DASH in detail.

Part3- Creation of a simple dashboard.

Prerequisites

For this project, you just require 3 things-

1. Basic knowledge of python.
2. Basic knowledge of databases such as SQL Server or MySQL.
3. Ability to determine the requirements of your client.

Step1: Downloading tools.

Database: For the tutorial purpose, we are going to use SQL Express which can be found here.

Tools to access Database: We are going to use Azure Data Studio. I found it to be a relatively simpler tool to use (especially if you are a beginner).

Python3: You can download Python 3.7.6 here.

Now before diving into interaction here are a few tips to set up your database-

1. Determine the purpose of your Database- You must be aware of the information you are going to record and try to keep the number of tables to a minimum.
2. Always provide primary keys.
3. Avoid Nulls and Redundant data.

Step2: Downloading the pyodbc module.

With the database properly set up its time to install the library through which we are going to communicate with it. Now there are a lot of libraries for various Databases but for SQL Server the most popular is the pyodbc library.

Open the command line (CMD) and type the following command to install the pyodbc library on your system.

Command to install pyodbc

Step3. Creating a connection string.

Connection strings tell pyodbc to which Database you want to connect to and if there are any authentication parameters required for it.

Syntax —

The syntax for a connection string

Now as we are connecting to SQL Server, we won’t change the Driver. We can get the details of Server and Database from Azure data studio as shown below.

Getting details of Database

This changes our connection string to -

Actual connection string.

As you can probably tell we have removed both ‘UID’ (user ID) and ‘PWD’ (password) fields as SQL Express doesn’t require those fields to connect. Also, we added a new field of autocommit. This tells the python code that every change we make to the Database should be committed. If we remove this then we have to send a connection.commit() command every time we make a change.

Step4. Creating cursors and executing a query.

A database cursor can be thought of as a pointer to a specific row within a query result. The pointer can be moved from one row to the next.

Defining Cursor and executing a query.

After creating a connection and defining a cursor we can easily execute any query we like. In the above example, we have used a fetchall() method which allowed us to retrieve all of the rows.

Then with the simple a for-loop, we are able to iterate over each and every row.

Please note that the ‘row’ of datatype ‘pyodbc.Row’, basically acts like a normal list so the individual values can be accessed by indexing.

Also, whenever we are done with a particular Database we must close the connection via command at line #6.

And that’s it. With just 4 steps you are easily able to set up and access your database with python.

You can find the entire code below-

Code for accessing SQL Server Database.

Bonus Material:

1. Apart from the fetchall() method, you can also explore —

• fetchone() — Allows us to fetch the top 1 row only.
• fetchval()- If we are fetching only one column then we can use this to return just the value.
• commit()- Use this if you want to commit an executed query.
• rollback()- Use this if you want to roll back an executed query.
• close()- It closes the cursor.

2. Handling Quotes-

Applying where condition might be a bit tricky as in python both ‘ ’ and “ ” define a string. Now when we consider a query such as

Query with incorrect syntax.

In the above case, the quotes don’t match and hence we will get a compiler error. To solve this we can use “ ” and ‘ ’ interchangeably.

Query with correct syntax.

3. Getting data as Pandas DataFrame

Sometimes you may require data in the form of a Pandas DataFrame (a particular scenario explained in the next part). For that, we can use read_sql_query() method of pandas as shown below.

Getting data as a Pandas Dataframe

So that’s the end of this tutorial. See you in the next part!