Visualizations From Database Data

Visualizations From Database DataDiogo RibeiroBlockedUnblockFollowFollowingApr 29Database systems are used for storing data.

They are the most used data storage location for important data in the organization.

Databases offer many benefits when it comes to data storage and management which makes them hard to replace in this task.

In this post, we are going to create visualizations from database data and display them in the web page using Flask.

This post assumes that you have knowledge of databases especially MySQL database.

Working with databases for data analysis task is pretty easy and enjoyable.

First I assume that you have MySQL database installed otherwise you can download it here and install it.

Visualizations From Database DataInstalling PyMySQLBefore we access data stored in the database from Python we need to connect to the database first.

There are various ways of doing this, one of the approaches we are going to use is by installing a database adapter.

Since we are going to use MySQL database we will install PyMySQL database adapter.

PyMySQL is a Python-based database adapter that enables the connection to the MySQL database easy.

Runpip install PyMySQLto install it.

Connecting to The DatabaseNow that you have PyMySQL installed let’s create a database and a table to store famous titanic data set.

Create a database from the MySQL database system mine I named it as flask_demo but you can name it with any name.

Create a table called train with fields from the titanic CSV file from this location titanic train data set.

Import the CSV to the table you have created.

Let’s now test our connection to MySQL database using PyMySQL .

In your Jupyter notebook or Spyder IDE copy the following code and execute it.

Take note of the database name, user and password to match yours.

import pandas as pdimport pymysqlc = pymysql.

connect(host=’localhost’, db=’flask_demo’, user=’root’, passwd=’’)res = pd.

read_sql(“SELECT * FROM train”, con=c)c.

close()print(res.

head(5))If you see the output then the connection works otherwise there was a problem.

What we have done is to query all records but display only 5 records from the table.

DataFrame AnalysisLet’s now do some analysis on the DataFrame from the database.

Let’s go into our Flask working folder and create a file called demo.

py.

Add the following code.

from io import BytesIOfrom flask import Flask, render_template, send_file, make_response,jsonfrom matplotlib.

backends.

backend_agg import FigureCanvasAgg as FigureCanvasimport numpy as npimport pymysqlimport matplotlib.

pyplot as pltimport pandas as pdplt.

style.

use(‘ggplot’)app = Flask(__name__)c = pymysql.

connect(host=’localhost’, port=3306, user=’root’, passwd=’’, db=’flask_demo’)@app.

route(‘/’)def index(): df = pd.

read_sql(“SELECT * FROM train”, con=c) return render_template(“index.

html”, data=df.

head(5).

to_html())if __name__ == ‘__main__’: app.

run(debug=True)The above code connects to the database using PyMySQL adapter, retrieves data using Pandas command and sends the output of the DataFrame to the HTML web page.

Let’s create a templates folder and create a file called index.

html.

Add this code to the index.

html file.

<!DOCTYPE html><html><head> <title>Database With Flask</title></head><body><h1>Titanic Data Set</h1>{{ data | safe }}</body></html>Run the demo.

py file using python demo.

py command.

When you browse to 127.

0.

0.

1:5000/ in your browser you should be able to see this web page content.

Now let’s do some analysis on the titanic data set and display the results on the web page.

Add this function in your demo.

py file:@app.

route('/analysis')def passenger_class_dist(): df = pd.

read_sql("SELECT * FROM train", con=c) #gender gender_results=df.

groupby(['Sex'])['Sex'].

count() gender_new_df=pd.

DataFrame(gender_results) #Passenger class distribution class_results=df.

groupby(['Pclass'])['Pclass'].

count() class_new_df=pd.

DataFrame(class_results) #Passenger class and gender distribution pass_class_gender_group_df=df.

groupby(['Pclass','Sex'])['Sex'].

count() unstacked_df=pass_class_gender_group_df.

unstack('Pclass').

T # Unstack the results then transpouse pass_class_gender_new_df=pd.

DataFrame(unstacked_df,columns=['female','male']) #create new dataframe return render_template("analysis.

html", gender_data=gender_new_df.

to_html(), passengers_data=cThis function analyses the titanic data with respect to gender.

In the templates folder let’s create another file called analysis.

html and add the following code:<!DOCTYPE html><html><head> <title>Database With Flask</title></head><body><h1>Titanic Data Analysis</h1><div> <div style="float: left; "><h2>Gender Distribution</h2>{{ gender_data | safe }} </div> <div style="float: right; margin-right: 120px; "> <div style="float: left; "><h2>Passenger Class Distribution</h2>{{ passengers_data | safe }} </div> <div style="float: right; margin-left: 90px; "><h2>Passenger Class and Gender Distribution</h2>{{ passengers_gender_data | safe }} </div> </div> </div> </body></html>Run the demo.

py file using python demo.

py command.

When you browse to 127.

0.

0.

1:5000/analysis in your browser you should be able to see this web page content.

Displaying VisualizationLet’s display some visualizations from our analysis to the web page.

Add this code in your demo.

py file.

@app.

route('/gender_pie_chart/')def gender_pie_chart(): df = pd.

read_sql("SELECT * FROM train", con=c) data=df.

groupby(['Sex'])['Sex'].

count() gender_labels=['Female','Male'] fig,ax=plt.

subplots() gender_color = ['r','g'] ax.

pie(data, labels=gender_labels, colors=gender_color,autopct='%1.

1f%%', startangle=90, shadow= False) plt.

axis('equal') canvas = FigureCanvas(fig) img = BytesIO() fig.

savefig(img) img.

seek(0) return send_file(img, mimetype='image/png') @app.

route('/class_bar_graph/')def class_bar_graph(): df = pd.

read_sql("SELECT * FROM train", con=c) new_df=df.

groupby(['Pclass'])['Pclass'].

count() pclass=new_df[0:] new_df=df.

groupby(['Pclass'])['Pclass'].

count() fig, ax = plt.

subplots() ax = pclass.

plot(kind='bar', color = ['r','g','y'], fontsize=12) ax.

set_xlabel("Passenger Class (Pclass)", fontsize=12) ax.

set_ylabel("Population", fontsize=12) canvas = FigureCanvas(fig) img = BytesIO() fig.

savefig(img) img.

seek(0) return send_file(img, mimetype='image/png') @app.

route('/class_gender_bar_graph/')def class_gender_bar_graph(): df = pd.

read_sql("SELECT * FROM train", con=c) group_df=df.

groupby(['Pclass','Sex'])['Sex'].

count() unstacked_df=group_df.

unstack('Pclass').

T new_df=pd.

DataFrame(unstacked_df,columns=['female','male']) fig, ax = plt.

subplots() ax = new_df[['female','male']].

plot(kind='bar',color = ['r','g'], legend=True, fontsize=12) ax.

set_xlabel("Passenger Class (Pclass)", fontsize=12) ax.

set_ylabel("Population", fontsize=12) canvas = FigureCanvas(fig) img = BytesIO() fig.

savefig(img) img.

seek(0) return send_file(img, mimetype='image/png') @app.

route('/visualization')def visualization(): return render_template('visualization.

html')Now add create a file called visualization.

html in the templates folder and add the following code<!DOCTYPE html><html><head> <title>Database With Flask</title></head><body><h1>Titanic Data Visualization</h1><div> <div style="float: left; "><h2>Gender Distribution</h2><img src="/gender_pie_chart/" alt="Titanic Gender Pie Chart"> </div> <div style="float: right; "> <div style="float: left; "><h2>Passenger Class Distribution</h2><img src="/class_bar_graph/" alt="Titanic Class Bar Graph"> </div> </div> <div style="float: left; "><h2>Passenger Class and Gender Distribution</h2><img src="/class_gender_bar_graph/" alt="Titanic Class Gender Bar Graph"> </div></div> </body></html>Run the demo.

py file using python demo.

py command.

When you browse to 127.

0.

0.

1:5000/visualization in your browser you should be able to see this web page content.

Complete Codedemo.

pyfrom io import BytesIOfrom flask import Flask, render_template, send_file, make_response,jsonfrom matplotlib.

backends.

backend_agg import FigureCanvasAgg as FigureCanvasimport numpy as npimport pymysqlimport matplotlib.

pyplot as pltimport pandas as pdplt.

style.

use('ggplot') app = Flask(__name__) c = pymysql.

connect(host='localhost', port=3306, user='root', passwd='', db='flask_demo') @app.

route('/')def index(): df = pd.

read_sql("SELECT * FROM train", con=c) return render_template("index.

html", data=df.

head(5).

to_html()) @app.

route('/analysis')def passenger_class_dist(): df = pd.

read_sql("SELECT * FROM train", con=c) #gender gender_results=df.

groupby(['Sex'])['Sex'].

count() gender_new_df=pd.

DataFrame(gender_results) #Passenger class distribution class_results=df.

groupby(['Pclass'])['Pclass'].

count() class_new_df=pd.

DataFrame(class_results) #Passenger class and gender distribution pass_class_gender_group_df=df.

groupby(['Pclass','Sex'])['Sex'].

count() unstacked_df=pass_class_gender_group_df.

unstack('Pclass').

T # Unstack the results then transpouse pass_class_gender_new_df=pd.

DataFrame(unstacked_df,columns=['female','male']) #create new dataframe return render_template("analysis.

html", gender_data=gender_new_df.

to_html(), passengers_data=class_new_df.

to_html(), passengers_gender_data=pass_class_gender_new_df.

to_html()) @app.

route('/gender_pie_chart/')def gender_pie_chart(): df = pd.

read_sql("SELECT * FROM train", con=c) data=df.

groupby(['Sex'])['Sex'].

count() gender_labels=['Female','Male'] fig,ax=plt.

subplots() gender_color = ['r','g'] ax.

pie(data, labels=gender_labels, colors=gender_color,autopct='%1.

1f%%', startangle=90, shadow= False) plt.

axis('equal') canvas = FigureCanvas(fig) img = BytesIO() fig.

savefig(img) img.

seek(0) return send_file(img, mimetype='image/png') @app.

route('/class_bar_graph/')def class_bar_graph(): df = pd.

read_sql("SELECT * FROM train", con=c) new_df=df.

groupby(['Pclass'])['Pclass'].

count() pclass=new_df[0:] new_df=df.

groupby(['Pclass'])['Pclass'].

count() fig, ax = plt.

subplots() ax = pclass.

plot(kind='bar', color = ['r','g','y'], fontsize=12) ax.

set_xlabel("Passenger Class (Pclass)", fontsize=12) ax.

set_ylabel("Population", fontsize=12) canvas = FigureCanvas(fig) img = BytesIO() fig.

savefig(img) img.

seek(0) return send_file(img, mimetype='image/png') @app.

route('/class_gender_bar_graph/')def class_gender_bar_graph(): df = pd.

read_sql("SELECT * FROM train", con=c) group_df=df.

groupby(['Pclass','Sex'])['Sex'].

count() unstacked_df=group_df.

unstack('Pclass').

T new_df=pd.

DataFrame(unstacked_df,columns=['female','male']) fig, ax = plt.

subplots() ax = new_df[['female','male']].

plot(kind='bar',color = ['r','g'], legend=True, fontsize=12) ax.

set_xlabel("Passenger Class (Pclass)", fontsize=12) ax.

set_ylabel("Population", fontsize=12) canvas = FigureCanvas(fig) img = BytesIO() fig.

savefig(img) img.

seek(0) return send_file(img, mimetype='image/png') @app.

route('/visualization')def visualization(): return render_template('visualization.

html') if __name__ == '__main__': app.

run(debug=True)index.

html<!DOCTYPE html><html><head> <title>Database With Flask</title></head><body><h1>Titanic Data Set</h1>{{ data | safe }}</body></html>analysis.

html<!DOCTYPE html><html><head> <title>Database With Flask</title></head><body><h1>Titanic Data Analysis</h1><div> <div style="float: left; "><h2>Gender Distribution</h2>{{ gender_data | safe }} </div> <div style="float: right; margin-right: 120px; "> <div style="float: left; "><h2>Passenger Class Distribution</h2>{{ passengers_data | safe }} </div> <div style="float: right; margin-left: 90px; "><h2>Passenger Class and Gender Distribution</h2>{{ passengers_gender_data | safe }} </div> </div> </div> </body></html>visualization.

html<!DOCTYPE html><html><head> <title>Database With Flask</title></head><body><h1>Titanic Data Visualization</h1><div> <div style="float: left; "><h2>Gender Distribution</h2><img src="/gender_pie_chart/" alt="Titanic Gender Pie Chart"> </div> <div style="float: right; "> <div style="float: left; "><h2>Passenger Class Distribution</h2><img src="/class_bar_graph/" alt="Titanic Class Bar Graph"> </div> </div> <div style="float: left; "><h2>Passenger Class and Gender Distribution</h2><img src="/class_gender_bar_graph/" alt="Titanic Class Gender Bar Graph"> </div></div> </body></html>ConclusionIn this post, we have learned about how to create visualizations from data stored in the database and display the graphs on a web page using Flask.

Before we access MySQL database we install PyMySQL Python database adapter.

We also need both Pandas and matplotlib libraries installed.

With knowledge from this posts series, you can now easily create your custom data analytics dashboard using Flask and MySQL database with ease.

However, the code used in this posts series can be further improved and optimized to scale to any degree.

Deploying Visualizations with Flask is another way apart from the use of Apache Superset platform.

.

. More details

Leave a Reply