Visualization of SQL Data in Jupyter Notebook & Embedding in WordPress Post

Abhishek Ghosh

By Abhishek Ghosh June 1, 2018 2:23 pm Updated on June 1, 2018

Visualization of SQL Data in Jupyter Notebook & Embedding in WordPress Post

In our previous few guides, we discussed around Data Visualization in WordPress Posts From SQL and embedding Jupyter Notebook in WordPress Post. In This Short Guide, We Will Show Visualization of SQL Data in Jupyter Notebook & Embedding in WordPress Post in Easy Language, With All Steps. Needed concepts are basic – how SQL file created for a table, how MySQL server installed, installing Jupyter Notebook, MySQL database connectors like MySQLdb (for Python). Like MySQLdb, we have another tool named PyMySQL :

https://github.com/PyMySQL/PyMySQL

1	https://github.com/PyMySQL/PyMySQL

MySQLdb written in C++, while PyMySQL is written in Python. There are many other tools like :

https://github.com/catherinedevlin/ipython-sql

1	https://github.com/catherinedevlin/ipython-sql

Visualization of SQL Data in Jupyter Notebook

Installing PyMySQL is easy :

pip3 install PyMySQL

1	pip3 install PyMySQL

We need to have some common tools around data sciences installed like pandas, matplotlib. Now open a new Notebook. We can connect to a particular MySQL database in this way :

import os
import pymysql
import pandas as pd

host = os.getenv('MYSQL_HOST')
port = os.getenv('MYSQL_PORT')
user = os.getenv('MYSQL_USER')
password = os.getenv('MYSQL_PASSWORD')
database = os.getenv('MYSQL_DATABASE')

conn = pymysql.connect(
    host=host,
    port=int(port),
    user=user,
    passwd=password,
    db=database,
    charset='utf8mb4')

1

2

3

4

5

6

7

8

9

10

11

12

13

14

15

16

17

import os

import pymysql

import pandas as pd

host = os.getenv('MYSQL_HOST')

port = os.getenv('MYSQL_PORT')

user = os.getenv('MYSQL_USER')

password = os.getenv('MYSQL_PASSWORD')

database = os.getenv('MYSQL_DATABASE')

conn = pymysql.connect(

host=host,

port=int(port),

user=user,

passwd=password,

db=database,

charset='utf8mb4')

Now, if we run meaningful commands to select data from that database (example is from a Gist on Github for easiness to show ready to use real example) :

df = pd.read_sql_query(
    "SELECT DATE(created_at) AS date, COUNT(*) AS count FROM user GROUP BY date HAVING date >= '2017-04-01' ",
    conn)
df.tail(10)

1

2

3

4

df = pd.read_sql_query(

"SELECT DATE(created_at) AS date, COUNT(*) AS count FROM user GROUP BY date HAVING date >= '2017-04-01' ",

conn)

df.tail(10)

Then we will get an output as chart, like :

No    date       count
...
35    2017-05-22 3
36    2017-05-24 1
37    2017-05-25 1
38    2017-05-26 1
39    2017-05-27 1
...

1

2

3

4

5

6

7

8

No date count

...

35 2017-05-22 3

36 2017-05-24 1

37 2017-05-25 1

38 2017-05-26 1

39 2017-05-27 1

...

Now, we can run command like this to construct a chart :

%matplotlib inline

df.index = df['date']
p = df.tail(10).plot.bar()

1

2

3

4

5

%matplotlib inline

df.index = df['date']

p = df.tail(10).plot.bar()

Visualization of SQL Data in Jupyter Notebook & Embedding in WordPress Post

Then, we can close the connection :

conn.close()

1	conn.close()

Embedding in WordPress Post

Easy. If we want to embed the tutorial then we have to convert it to JSON data using nbconvert like tool and copy pasting that on Github, Gitlab will look like this :

https://gist.github.com/AbhishekGhosh/e4c7d1026d9850eae22f2bfd53caf576

1	https://gist.github.com/AbhishekGhosh/e4c7d1026d9850eae22f2bfd53caf576

We can include that full as Js, which is like this :

<script src="https://gist.github.com/AbhishekGhosh/e4c7d1026d9850eae22f2bfd53caf576.js"></script>

1	<script src="https://gist.github.com/AbhishekGhosh/e4c7d1026d9850eae22f2bfd53caf576.js"></script>

Embedding that looks like this :;

Tagged With data visualization jupyter sql , how to run pymysql in jupyter notebook , sql in jupyter , visualisation in jupyter using mysqldb , where do i install pymysql to use on jupyter notebook on windows

About Abhishek Ghosh

Here’s what we’ve got for you which might like :

Take The Conversation Further ...

Get new posts by email: