notebook python 内嵌 数据库,通过Jupyter Notebook w / Python3访问MySQL数据库

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?

解决方案

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:

pymysql works well and is easy to install:

sudo conda install pymysql

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)