Access to a MySQL database via Jupyter Notebook w/ Python3

Question

I needed access a MySQL database via Jupyter Notebook, on which I run Python 3.6 (Anaconda install). It's a linear workflow, extracting data from the DB and manipulating it in Python/Pandas. No need for an ORM, a simple connector should do. However, the widely referenced MySQLdb package doesn't work with Python 3.x.

What are the alternatives?

Answer 1

The recommended installation modality for Jupyter on Ubuntu is Anaconda, so the appropriate package manager is conda. Installation via pip/pip3 or apt won't be accessible to the Notebook. conda makes it simple to get at least two good connectors:

1. pymysql works well and is easy to install:

sudo conda install pymysql

2. The 'official' connector:

sudo conda install mysql-connector-python

I tried pymysql first and it was fine but then switched to the second option due to the availability of extensive documentation.

If your objective is to import the data into a Pandas dataframe then use of the built-in pd.sql_read_table or pd.sql_read_query is convenient, as it labels the columns etc. It still requires installation of a connector, as discussed above.

An example with MySQL-connector-python, where you need to enter the database DETAILS:

import pandas as pd import sqlalchemy engine = sqlalchemy.create_engine('mysql+mysqlconnector://USER:PASSWORD@HOST/DB_NAME') example_df = pd.read_sql_table("YOUR_TABLE_NAME", engine)