读写数据库

大多数情况下，海量的数据是使用数据库进行存储的，这主要是依赖于数据库的数据结构化、数据共享性、独立性等特点。因此，在实际生产环境中，绝大数的数据都是存储在数据库中。Pandas 支持Mysql、Oracle、SQLite等主流数据库的读写操作。

为了高效地对数据库中的数据进行读取，这里需要引入SQLAlchemy。SQLAlchemy是使用Python编写的一款开源软件，它提供的SQL工具包和对象映射工具能够高效地访问数据库。在使用SQLAlchemy时需要使用相应的连接工具包，比如Mysql需要安装mysqlconnector，Oracle则需要安装cx_oracle。

Pandas的io.sql模块中提供了常用的读写数据库函数，具体如表1所示。

表1 pandas.io.sql模块常用的函数

在表1中列举了各个函数的具体功能。其中，read_sql_table()函数与read_sql_query()函数都可以将读取的数据转换为DataFrame对象，前者表示将整张表的数据转换成DataFrame，后者则表示将执行SQL语句的结果转换为DataFrame对象。

注意：

在连接mysql数据库时，这里使用的是mysqlconnector驱动，如果当前的Python环境中没有改模块，则需要使用pip install mysqlconnector命令安装该模块。

下面以read_sql()函数和to_sql()方法为例，分别给大家介绍如何读写数据库中的数据，具体内容如下。

1. 使用read_sql()函数读取数据

read_sql()函数既可以读取整张数据表，又可以执行SQL语句，其语法格式如下：

pandas.read_sql(sql,con,index_col=None,coerce_float=True,params=None,parse_dates=None,
                columns=None, chunksize=None)

上述函数中常用参数表示的含义如下：

（1） sql：表示被执行的SQL语句。

（2） con：接收数据库连接，表示数据库的连接信息。

（3） index_col：默认为None，如果传入一个列表，则表示为层次化索引。

（4） coerce_float：将非字符串、非数字对象的值转换为浮点数类型。

（5） params：传递给执行方法的参数列表，如params = {‘name’:’value’}。

（6） columns**：**从SQL表中选择列名列表（仅在读取时使用）。

如果发现数据中存在空值，则会使用NaN进行补全，

假设在MySql数据库有一张数据表，该表中的内容如图1所示

图1 person_info表

接下来，通过一个示例来演示如何使用read_sql()函数读取数据库中的数据表，示例代码如下。

 In [86]: import pandas as pd
          from sqlalchemy import create_engine
          # mysql账号为root 密码为123456 数据库名：info 
          # 数据表名称：person_info
          engine = create_engine('mysql+ mysqlconnector://root:123456'
                                 '@127.0.0.1:3306/info')
          pd.read_sql('person_info',engine)
 Out[86]:
​    id name age height gender
  0  1  小铭  18   180   女
  1  2 小月月  18  180    女
  2  3  彭明  29   185    男
  3  4  刘华  59   175   男
  4  5  王贤  18   172   女
  5  6  周平  36  None   男
  6  7  程坤  27   181   男
  7  8  李平  38   160   女

上述示例中，首先导入了sqlalchemy模块，通过create_engine()函数创建连接数据库的信息，然后调用read_sql()函数读取数据库中的person_info数据表，并转换成DataFrame对象。

注意：

在使用create_engine()函数创建连接时，其格式如下：'数据库类型+数据库驱动名称://用户名:密码@机器地址:端口号/数据库名'。

read_sql()函数还可以执行一个SQL语句，例如，从person_info数据表中筛选出id值大于3的全部数据，具体的SQL语句如下：

select * from person_info where id >3;

根据上述SQL语句来读取数据库里面的数据，并将执行后的结果转换成DataFrame对象展示，示例代码如下：

 In [87]: import pandas as pd
          from sqlalchemy import create_engine
          # mysql账号为root 密码为123456 数据名：info
          # 数据表名称：person_info
          # 创建数据库引擎
          # mysql+mysqlconnector表示使用Mysql数据库的mysqlconnector驱动
          engine = create_engine('mysql+mysqlconnector://root:123456@127.0.0.1/info')
          sql = 'select * from person_info where id >3;'
          pd.read_sql(sql,engine)
 Out[87]:
​      id  name  age  height  gender
0  4  刘华   59    175    男
​    1   5   王贤   18   172    女
​    2  6  周平   36   None    男
​    3  7   成坤   27   181    男
​    4  8  李平   38    160    女

需要强调的是，这里的SQL语句不仅是用于筛选的SQL语句，其它用于增删改查的SQL语句都是可以执行的。

2. 使用to_sql()方法将数据写入到数据库中

to_sql()方法的功能是将Series或DataFrame对象以数据表的形式写入到数据库中，其语法格式如下：

to_sql(name,con,schema = None,if_exists ='fail',index = True,index_label = None,
       chunksize = None,dtype = None)

上述方法中，部分参数所表示的含义如下所示：

(1) name：表示数据库表的名称。

(2) con: 接收数据库连接，表示数据库的连接信息。

(3) if_exists：可以取值为fail、replace或append，默认为’fail’。每个取值代表的含义如下：

• fail：如果表存在，则不执行写入操作。

• replace：如果表存在，则将源数据库表删除，再重新创建。

• append：如果表存在，那么在原数据库表的基础上追加数据。

(4) index：表示是否将DataFrame行索引作为数据传入数据库，默认为True。

(5) index_label：表示是否引用索引名称。如果index设为True，此参数为None，则使用默认名称；如果index为层次化索引，则必须使用序列类型。

接下来，通过一个示例程序来演示如何使用Pandas向数据库中写入数据。 首先，创建一个名称为students_info的数据库，具体的SQL语句如下。

create database students_info charset=utf8;

然后，创建一个与图2中的表格结构相同的DataFrame对象，它统计了每个年级中男生和女生的人数。

图2 年级男生与女生的信息

接着，调用to_sql()函数将DataFrame对象写入到名称为studnets数据表中，具体代码如下。

In [88]: from pandas import DataFrame,Series
         import pandas as pd
         from sqlalchemy import create_engine
         from sqlalchemy.types import *
         df = DataFrame({"班级":["一年级","二年级","三年级","四年级"],
                         "男生人数":[25,23,27,30],"女生人数":[19,17,20,20]})
         # 创建数据库引擎
         # mysql+mysqlconnector 表示使用Mysql数据库的mysqlconnector驱动
         # 账号：root 密码：123456 数据库名：studnets_info
         # 数据表的名称： students
         engine=create_engine('mysql+ mysqlconnector://root:123456'
                              '@127.0.0.1/'students_info')
         df.to_sql('students',engine)

当程序执行结束后，可以在数据库中查看是否成功创建了数据表，以及数据是否保存成功，这里使用命令行的方式进行验证。

打开命令提示符窗口，在光标位置输入“mysql –u数据库账号 –p密码”进行登陆。登陆成功后，使用“use”命令选择studnets_info数据库，然后使用如下命令语句查询students表中的全部数据，具体命令如下。

select * from students

查询到的结果具体如图3所示。

图3 保存到数据库的数据

注意：

在使用to_sql()方法写入数据库时，如果写入的数据表名与数据库中其他的数据表名相同时，则会返回该数据表已存在的错误。