用Pandas库实现MySQL数据库的读写
用Pandas库实现MySQL数据库的读写
ORM技术
对象关系映射技术，即ORM（Object-Relational Mapping）技术，指的是把关系数据库的表结构映射到对象上，通过使用描述对象和数据库之间映射的元数据，将程序中的对象自动持久化到关系数据库中。
在Python中，最有名的ORM框架是SQLAlchemy。Java中典型的ORM中间件有: Hibernate, ibatis, speedframework。
SQLAlchemy
SQLAlchemy是Python编程语言下的一款开源软件。提供了SQL工具包及对象关系映射（ORM）工具，使用MIT许可证发行。
可以使用pip命令安装SQLAlchemy模块：
pip install sqlalchemy
The following command must be run outside of the IPython shell:
$ pip install sqlalchemy
The Python package manager (pip) can only be used from outside of IPython.
Please reissue the `pip` command in a separate terminal or command prompt.
See the Python documentation for more information on how to install packages:
https://docs.python.org/3/installing/
SQLAlchemy模块提供了create_engine()函数用来初始化数据库连接，SQLAlchemy用一个字符串表示连接信息：
数据库类型+数据库驱动名称://用户名:口令@机器地址:端口号/数据库名'
Pandas读写MySQL数据库
我们需要以下三个库来实现Pandas读写MySQL数据库：
- pandas
- sqlalchemy
- pymysql
其中，pandas模块提供了read_sql_query（）函数实现了对数据库的查询，to_sql（）函数实现了对数据库的写入，并不需要实现新建MySQL数据表。sqlalchemy模块实现了与不同数据库的连接，而pymysql模块则使得Python能够操作MySQL数据库。
我们将使用MySQL数据库中的mydb数据库以及myadmin_cityse表，内容如下：
mysql> select * from myadmin_citys limit 10;
+----+--------------------+-------+------+
| id | name | level | upid |
+----+--------------------+-------+------+
| 1 | 北京市 | 1 | 0 |
| 2 | 天津市 | 1 | 0 |
| 3 | 河北省 | 1 | 0 |
| 4 | 山西省 | 1 | 0 |
| 5 | 内蒙古自治区 | 1 | 0 |
| 6 | 辽宁省 | 1 | 0 |
| 7 | 吉林省 | 1 | 0 |
| 8 | 黑龙江省 | 1 | 0 |
| 9 | 上海市 | 1 | 0 |
| 10 | 江苏省 | 1 | 0 |
+----+--------------------+-------+------+
10 rows in set (0.00 sec)
下面将介绍一个简单的例子来展示如何在pandas中实现对MySQL数据库的读写：
# -*- coding: utf-8 -*-
# 导入必要模块
import pandas as pd
from sqlalchemy import create_engine
# 初始化数据库连接，使用pymysql模块
# MySQL的用户：root, 密码:123456, 端口：3306,数据库：mydb
engine = create_engine('mysql+pymysql://root:123456@192.168.81.134:3306/shopdb')
# 查询语句，选出employee表中的所有数据
sql = '''
select * from myadmin_citys limit 10;
'''
# read_sql_query的两个参数: sql语句， 数据库连接
df = pd.read_sql_query(sql, engine)
# 输出employee表的查询结果
print(df)
# 新建pandas中的DataFrame, 只有id,num两列
df = pd.DataFrame({'id':[1,2,3,4],'num':[12,34,56,89]})
# 将新建的DataFrame储存为MySQL中的数据表，不储存index列
df.to_sql('mydf', engine, index= False)
print('Read from and write to Mysql table successfully!')
id name level upid
0 1 北京市 1 0
1 2 天津市 1 0
2 3 河北省 1 0
3 4 山西省 1 0
4 5 内蒙古自治区 1 0
5 6 辽宁省 1 0
6 7 吉林省 1 0
7 8 黑龙江省 1 0
8 9 上海市 1 0
9 10 江苏省 1 0
Read from and write to Mysql table successfully!
在MySQL中查看mydf表格：
mysql> select * from mydf;
+------+------+
| id | num |
+------+------+
| 1 | 12 |
| 2 | 34 |
| 3 | 56 |
| 4 | 89 |
+------+------+
4 rows in set (0.00 sec)
这说明我们确实将pandas中新建的DataFrame写入到了MySQL中
将CSV文件写入到MySQL中
以上的例子实现了使用Pandas库实现MySQL数据库的读写，我们将再介绍一个实例：将CSV文件写入到MySQL中，示例的california_housing_test.csv文件前10行如下：
longitude latitude housing_median_age total_rooms total_bedrooms population households median_income median_house_value
-122.05 37.37 27 3885 661 1537 606 6.6085 344700
-118.3 34.26 43 1510 310 809 277 3.599 176500
-117.81 33.78 27 3589 507 1484 495 5.7934 270500
-118.36 33.82 28 67 15 49 11 6.1359 330000
-119.67 36.33 19 1241 244 850 237 2.9375 81700
-119.56 36.51 37 1018 213 663 204 1.6635 67000
-121.43 38.63 43 1009 225 604 218 1.6641 67000
-120.65 35.48 19 2310 471 1341 441 3.225 166900
-122.84 38.4 15 3080 617 1446 599 3.6696 194400
-118.02 34.08 31 2402 632 2830 603 2.3333 164200
# -*- coding: utf-8 -*-
# 导入必要模块
import pandas as pd
from sqlalchemy import create_engine
# 初始化数据库连接，使用pymysql模块
engine = create_engine('mysql+pymysql://root:123456@192.168.81.134:3306/shopdb')
# 读取本地CSV文件
df = pd.read_csv("D:\Desktop\california_housing_test.csv", sep=',')
# 将新建的DataFrame储存为MySQL中的数据表，不储存index列
df.to_sql('mpg', engine, index= False)
print("Write to MySQL successfully!")
Write to MySQL successfully!
在MySQL中查看mpg表格：
mysql> select * from mpg limit 10;
+-----------+----------+--------------------+-------------+----------------+------------+------------+---------------+--------------------+
| longitude | latitude | housing_median_age | total_rooms | total_bedrooms | population | households | median_income | median_house_value |
+-----------+----------+--------------------+-------------+----------------+------------+------------+---------------+--------------------+
| -122.05 | 37.37 | 27 | 3885 | 661 | 1537 | 606 | 6.6085 | 344700 |
| -118.3 | 34.26 | 43 | 1510 | 310 | 809 | 277 | 3.599 | 176500 |
| -117.81 | 33.78 | 27 | 3589 | 507 | 1484 | 495 | 5.7934 | 270500 |
| -118.36 | 33.82 | 28 | 67 | 15 | 49 | 11 | 6.1359 | 330000 |
| -119.67 | 36.33 | 19 | 1241 | 244 | 850 | 237 | 2.9375 | 81700 |
| -119.56 | 36.51 | 37 | 1018 | 213 | 663 | 204 | 1.6635 | 67000 |
| -121.43 | 38.63 | 43 | 1009 | 225 | 604 | 218 | 1.6641 | 67000 |
| -120.65 | 35.48 | 19 | 2310 | 471 | 1341 | 441 | 3.225 | 166900 |
| -122.84 | 38.4 | 15 | 3080 | 617 | 1446 | 599 | 3.6696 | 194400 |
| -118.02 | 34.08 | 31 | 2402 | 632 | 2830 | 603 | 2.3333 | 164200 |
+-----------+----------+--------------------+-------------+----------------+------------+------------+---------------+--------------------+
10 rows in set (0.00 sec)
仅仅5句Python代码就实现了将CSV文件写入到MySQL中，这无疑是简单、方便、迅速、高效的！
DataFrame.to_sql 的相关参数
DataFrame.to_sql（name，con，schema = None，if_exists ='fail'，index = True，index_label = None，chunksize = None，dtype = None，method = None
参数：
name ： stringSQL表的名称。
con ： sqlalchemy.engine.Engine或sqlite3.Connection使用SQLAlchemy可以使用该库支持的任何数据库。为sqlite3.Connection对象提供了旧版支持。
schema ： string，optional指定模式（如果数据库flavor支持此模式）。如果为None，请使用默认架构。
if_exists ： {'fail'，'replace'，'append'}，默认'fail'
如果表已存在，如何表现。
fail：引发ValueError。
replace：在插入新值之前删除表。
append：将新值插入现有表。
index ： bool，默认为True将DataFrame索引写为列。使用index_label作为表中的列名。
index_label ： 字符串或序列，默认为None索引列的列标签。如果给出None（默认值）且 index为True，则使用索引名称。如果DataFrame使用MultiIndex，则应该给出一个序列。
chunksize ： int，可选行将一次批量写入此大小。默认情况下，所有行都将立即写入。
dtype ： dict，可选指定列的数据类型。键应该是列名，值应该是SQLAlchemy类型或sqlite3传统模式的字符串。
