Photo by Tobias Fischer on Unsplash

How to connect Jupyter Notebook with a database using MySQL Connector

Jupyter Notebook is very handy and it’s fun to use when we’re working with data. In some cases we may want to connect to a database rather than loading a file every single time especially if the dataset is constantly growing.

In this post i will be showing you how to connect your Jupyter Notebook with a local / remote database using MySQL Connector. The only pre-requisites are having your Jupyter environment installed and a database to connect :)
So let’s get started!

First, you’ll need open terminal and install the mysql-connector package:

pip3 install mysql-connector

After installing the package open a Jupyter Notebook and follow the steps below.

import os
import mysql.connector

I would suggest you to declare your credentials and other connection informations as environment variables and then use them for a much more secure connection. Once you have your variables ready, you can enter the name of corresponding environment variables in brackets in the code below.

username = os.environ.get('')
password = os.environ.get('')
host = os.environ.get('')

And now create the database connection.

cnx = mysql.connector.connect(host = host,
port = #optional,
user = username,
passwd = password,
database = #database
)

Now you should have been connected to your database. If you are trying to connect to remote database and receiving an error about authentication plug-in add the line below into mysql.connector.connect function.

auth_plugin='mysql_clear_password'

VOILA! You are set to run SQL-Queries in your Jupyter Notebook and more importantly have the ability to process the data from your database without exporting it.

And now let’s create a dataframe using an SQL-Query.

df = pd.read_sql_query('Select * FROM Table', cnx)

After running this code above, a dataframe with the every rows & columns of selected table will be created.
This post covers a minor but highly crucial part of data analysis. Without the ingredients you can’t cook and without the data it’s extremely hard to do analysis.

It took me a while to create this connection between a database and Jupyter Notebook, so I hope this post will save some time for people out there facing with the same issue.

--

--

Get the Medium app

A button that says 'Download on the App Store', and if clicked it will lead you to the iOS App store
A button that says 'Get it on, Google Play', and if clicked it will lead you to the Google Play store
Alpar Gür

Alpar Gür

Data Analyst • Research Assisstant • Movement • Weightlifting • Music • Cooking