2

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?

tim-oh
  • 688
  • 8
  • 13
  • 1
    Please check this [answer](https://stackoverflow.com/a/60896206/4999991). Only change `pip3` package manager with `conda`. Let me know if it helps. – Foad S. Farimani Mar 28 '20 at 01:23

1 Answers1

5

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

  1. 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)

tim-oh
  • 688
  • 8
  • 13