Accessing MySQL Database from JupyterHub

Owned by Chris Green
Last updated: Oct 03, 2023
11 min read

 

This document will show how to connect to a MySQL database which is on the same server as JupyterHub.

In MySQL, a new user ‘megan’ has been created with a ‘test’ password. This user has been given access to the database ‘demodb’.

Python Packages

Python Packages

The following python packages are used in this notebook: - pymysql - sqlalchemy - pandas and scikitlearn - these will be used in one example

Connecting to a MySQL database

Either pymysql or sqlalchemy can be used to connect to a database, however it is better to use sqlalchemy if you want to import a pandas dataframe as a table into your MySQL database.

# pymysql import pymysql.cursors #connect to database connection = pymysql.connect(host='localhost', user='megan', password='test', database='demodb')
#sqlalchemy from sqlalchemy import create_engine eng = create_engine('mysql+pymysql://megan:test@localhost/demodb') #connects to the database demodb as user=megan and password=test eng.connect() #this will show an error if it fails to connect to the database <sqlalchemy.engine.base.Connection at 0x7f4c5c739f60>

After connecting to the database, you can check the list of tables using:

#if using sqlalchemy eng.table_names() ['example', 'iris_table', 'tutorials_tbl']

Import dataset into MySQL

The following example will show how to store an Iris dataset as a new table in our demo database.

The command above also has an option for the case when the table name matches a table which already exists in the database. This means that you can either replace and overwrite the table in the database, or append the current table in MySQL and add this dataset to the data which is already there.

This shows that we have a new table in the database demodb called ‘iris_table_demo’.

Let’s look at the table iris_table and print the records from that table.

However, it is better to first import the table into a pandas data frame:

References