锐客网

  1. 首页 > it技术 > >

用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 sqlalchemyThe 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!')

idnamelevelupid 01北京市10 12天津市10 23河北省10 34山西省10 45内蒙古自治区10 56辽宁省10 67吉林省10 78黑龙江省10 89上海市10 910江苏省10 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行如下：
longitudelatitudehousing_median_agetotal_rooms total_bedroomspopulationhouseholdsmedian_incomemedian_house_value -122.05 37.37273885661 1537606 6.6085344700 -118.334.26431510310 809 277 3.599176500 -117.81 33.78273589507 1484495 5.7934270500 -118.36 33.8228671549116.1359330000 -119.67 36.33191241244 850 237 2.937581700 -119.56 36.51371018213 663 204 1.663567000 -121.43 38.63431009225 604 218 1.664167000 -120.65 35.48192310471 1341441 3.225166900 -122.84 38.4153080617 1446599 3.6696194400 -118.02 34.08312402632 2830603 2.3333164200

# -*- 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
【用Pandas库实现MySQL数据库的读写】参数：
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传统模式的字符串。

    推荐阅读


    上一篇：东凤|东凤| 往生 章一·16

    下一篇：就这样了