Access To A Mysql Database Via Jupyter Notebook W/ Python3

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 Pyth

Solution 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)