通过带有Python3的Jupyter Notebook访问MySQL数据库 [英] Access to a MySQL database via Jupyter Notebook w/ Python3
问题描述
我需要通过Jupyter Notebook访问一个MySQL数据库，在该数据库上运行Python 3.6(Anaconda安装).这是一个线性工作流，从数据库中提取数据并在Python/Pandas中进行操作.无需ORM，只需一个简单的连接器即可.但是，广泛引用了 MySQLdb程序包不适用于Python 3.x.
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.
有哪些替代方案?
推荐答案
在Ubuntu上Jupyter的推荐安装方式是Anaconda，因此合适的软件包管理器是conda.笔记本无法通过pip/pip3或apt安装.使用conda可以轻松获得至少两个良好的连接器:
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 效果很好，易于安装:
- pymysql works well and is easy to install:
sudo conda install pymysql
- 官方"连接器:
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.
如果您的目标是将数据导入Pandas数据框，则使用内置的pd.sql_read_table或pd.sql_read_query会很方便，因为它会标记列等.如上所述，它仍然需要安装连接器.
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.
MySQL-connector-python的示例，您需要在其中输入数据库详细信息:
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)
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)
这篇关于通过带有Python3的Jupyter Notebook访问MySQL数据库的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
