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 :
|
1 |
https://github.com/PyMySQL/PyMySQL |
MySQLdb written in C++, while PyMySQL is written in Python. There are many other tools like :
|
1 |
https://github.com/catherinedevlin/ipython-sql |
Visualization of SQL Data in Jupyter Notebook
Installing PyMySQL is easy :
---
|
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 :
|
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) :
|
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 :
|
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 :
|
1 2 3 4 5 |
%matplotlib inline df.index = df['date'] p = df.tail(10).plot.bar() |
Then, we can close the connection :
|
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 :
|
1 |
https://gist.github.com/AbhishekGhosh/e4c7d1026d9850eae22f2bfd53caf576 |
We can include that full as Js, which is like this :
|
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