Visualising data using Python with SQL Data

When you think of creating a webform for getting user input from and into a locally hosted SQL database, the last thing you might think of is using Python. Recently, I was asked to do just that and found it more difficult than I expected. In this post I will explain how to visually display the retrieved data. Data visualisation tools such as Plot.ly Dash or Plot.ly are one way of using the data gathered within your database and to display it in an appropriately styled chart or table. 

Plot.ly is a powerful tool that is free to use and offers hours of learning opportunities, in particular when it comes to Python HTML language, and is compatible with CSS and JavaScript for external scripts to be imported into an application.

How to connect to the Database

The first thing I did was obtain a suitable dataset. In this case I used the World.sql  example data which can be downloaded from the MySQL website. I imported the data into MySQL Workbench and created a connection. To create a connection you need to specify the following in your Python script:

All of the above are configured in MySQL before you include them in your script. So next thing you need to do is establish a way to retrieve the data so you can verify the content. You do this by using the cursor. A SQL Server cursor is a logical instruction within the script to loop over a predetermined number of rows one at a time.

Then you declare the connection and pass in the information to allow it to find the correct database locally and the necessary credentials. Then tell the cursor what you need it to do. The below code will fetch it from the database and find the Name, Continent, Population, LifeExpectancy, and GNP from the Country Table. You can then use Plot.ly to format the data in a suitable way to your needs. A link to a tutorial on how to do this can be found here.  

cursor = connection.cursor()

cursor.execute('select Name, Continent, Population, LifeExpectancy, GNP from Country');

rows = cursor.fetchall()

To visualise your data from the database, you should decide on what you want to display and how you want it to look. Do you want a table with filtering or a bar chart, scatter plot or something more specific and tailored to your needs? If so, it can be customised to give a professional look and feel in a short amount of time. These can be hosted on Azure for portability and presenting away from your machine.

It’s easy to do this once you’ve decided on the content and layout. Ideally the best way to do this is by using the trace option where you define the type of chart, the x value, the y value and indicator type if the chart is a scatter plot. For example are your plot markers going to be dots or squares etc.

Hopefully this short guide will give you some tips on how to look at data in Python and has given you an insight into how Plotly can augment your visualisation.