What is Jupyter Notebook?

The Jupyter Notebook is an open-source web application that allows you to write code in a web browser. It’s a mix of code, instructions, and output, and all of this information is displayed "in-line" on one web page, which makes it very easy to write code that tells a story.

How to Setup Jupyter Notebook using the pip command

Before using the Jupyter Notebook, you need to install a version of Python on your PC. You can go here python.org/downloads to download and install Python on your computer before proceeding with this tutorial.

Assuming you already have Python installed on your computer, open your terminal window and run the code below:

Pip install jupyter

Wait for all the packages to download (make sure you’re connected to the internet). Run the code below to launch Jupyter Notebook in your browser if pip was successful in installing Jupyter.

jupyter notebook

in your terminal A page like this would be automatically opened in your web browser:

Click on the "New" icon on the right and select "Python 3(ipykernel)" to start a new project.

From the newly opened tab, you can start entering your codes.

What is MySQL Database?

MySQL is the most popular open-source relational database management system, or in short, an RDBMS, with a client-server model. MySQL, like other relational databases, stores data in tables made of rows and columns.

SQL, or structured query language, allows users to create, manage, control, and query data. As part of the widely used LAMP technology, it stores and retrieves data in a wide range of popular apps, websites, and services.

MySQL runs on almost every platform, including Linux, Unix, and Windows. Although it may be used for a variety of purposes, MySQL is most commonly linked with web applications and online publishing.

Why do you need to integrate Jupyter Notebook and MySQL?

Even though MySQL is a well-known and powerful solution for data management, it might be constrained if you don't have the necessary tools, especially if you're a data expert. With libraries like Numpy, Pandas, Seaborn, and Matlotlib, Python allows us the flexibility to manipulate data to meet business demands. Python offers us this freedom and more, allowing us to alter and clean our data as well as create visual representations of it.

Jupyter Notebook MySQL integration

By running the following code, Pip will install the required libraries:

! pip install PyMySQL
! pip install ipython-sql
! pip install mysqlclient

Make sure to use an exclamation point before the pip command.

Then, import the libraries listed below.

import pymysql
import pandas as pd

Enter the following code to establish a connection to the MySQL database:

db_name = "testdb"
db_host = "localhost"
db_username = "root"
db_password = "type_your_password"

try:
    conn = pymysql.connect(host = db_host,
                           port = int(3306),
                           user = "root",
                           password = db_password,
                           db = db_name)
except e:
    print (e)
if conn:
    print ("connection successful")
else:
        print ("error")

How to query data from MySQL database using SQL and Python.

Enter the following code to read data from the table and store it in a pandas dataframe:

df = pd.read_sql_query("select * from airbnb_open_data", conn)
df

You can also read the pandas.read_sql_query official documentation here.