23　Python操作数据库

使用简单的纯文本方式只能实现有限的功能，不能进行快速查询，只有把数据全部读到内存中才能自己遍历。不过在实际应用中，我们操作的数据大小经常远远超过内存，根本无法全部读入内存。
为了便于程序保存和读取数据，并直接通过条件快速查询指定的数据，于是出现了数据库（Database）这种专门用于集中存储和查询的软件。
这里将介绍在Python 3.5中使用PyMySQL连接数据库，并实现简单的增、删、改、查。

数据库介绍

数据库历史非常久远，早在1950年就诞生了。经历了网状数据库、层次数据库，我们现在广泛使用的关系数据库是20世纪70年代在关系模型的基础上诞生的。
目前，广泛使用的关系数据库分为付费型和免费型。付费型数据库主要有以下几种：
（1）Oracle，典型的“高富帅”，收费昂贵，产品确实好，当前很多大型公司仍然使用它。
（2）SQL Server，微软自家产品，Windows定制专款。
（3）DB2，IBM的产品。
（4）Sybase，曾经跟微软关系非常亲密，后来关系破裂，使用的人比较少了，已逐渐淡出大家的视野。
这些数据库都是不开源而且付费的，最大的好处是出了问题可以找厂家解决。不过在Web的世界里，通常需要部署成千上万数据库服务器，如果使用付费型数据库，赚的钱都会被拿去买服务器了。所以，无论是Google、Facebook，还是国内的BAT，无一例外都选择免费的开源数据库。当前流行的免费数据库有以下几种：
（1）MySQL，当前使用最为广泛的开源数据库。
（2）PostgreSQL，学术气息有点重，其实挺不错，不过知名度没有MySQL高。
（3）SQLite，嵌入式数据库，适合桌面和移动应用。
作为Python开发工程师，选择哪款免费数据库呢？当然是MySQL。因为MySQL普及率最高，出了错可以很容易找到解决方法，而且围绕MySQL有一大堆监控和运维工具，安装和使用很方便。
为了继续后面的学习，你需要从MySQL官方网站（http://www.mysql.com）下载并安装MySQL Community Server。
你也许还听说过NoSQL数据库，很多NoSQL宣传速度和规模远远超过关系数据库，是否有很多同学觉得有了NoSQL就不需要SQL了呢？这样的想法是错误的，在搞明白NoSQL之前，还需要先明白SQL，在SQL的基础上学习NoSQL很容易，反过来就不行了。
本章主要介绍Python如何操作数据库，并不是单纯介绍数据库，如果你想从零学习关系数据库和基本的SQL语句，还需查看相关资料。

Python数据库API

Python数据库API是为方便统一操作数据库而提出的一个标准接口（API），也称为DB-API。
在没有Python DB-API之前，各数据库之间的应用接口非常混乱，实现各不相同。如果项目需要更换数据库，就需要进行大量修改，非常不便。Python DB-API的出现就是为了解决这些问题。
Python所有数据库接口程序都在一定程度上遵守Python DB-API规范。DB-API定义了一系列必需的对象和数据库存取方式，以便为各种各样的底层数据库系统和数据库接口程序提供一致的访问接口。由于DB-API为不同数据库提供了一致的访问接口，因此在不同的数据库之间移植代码成为一件轻松的事情。
DB-API规范包括全局变量、异常、连接、游标和类型等基本概念，下面我们逐一进行介绍。

1　全局变量

DB-API规范规定数据库接口模块必须实现一些全局属性以保证兼容性。Python提供了3个描述数据库模块特性的全局变量，如表1所示。

1　Python DB-API模块特性全局变量

apilevel指的是API级别，是一个字符串常量，表示这个DB-API模块所兼容的DB-API最高版本号。例如，版本号是1.0、2.0，如果未定义，就默认是1.0。

线程安全等级Threadsafety是一个整数,取值范围如下：

0表示不支持线程安全，多个线程不能共享此模块。

1表示初级线程安全支持，线程可以共享模块，但不能共享连接。

2表示中级线程安全支持，线程可以共享模块和连接，但不能共享游标。

3表示完全线程安全支持，线程可以共享模块、连接及游标。

参数风格（paramstyle）表示执行多次类似查询时，参数如何被拼接到SQL查询中。值format表示标准字符串格式化（使用基本的格式代码），可以在参数中进行拼接的地方插入%s。值pyformt表示扩展的格式代码，用于字典拼接，如%（foo）。除了Python风格之外，还有3种接合方式：qmark的意思是使用问号，numeric表示使用:1或:2格式的字段（数字表示参数的序号），而named表示:foobar这样的字段。其中，foobar为参数名。

16.2.2　异常

为了能尽可能准确地处理错误，DB-API中定义了一些异常。这些异常被定义在层次结构中，可以通过一个except块捕捉多种异常。

异常的层次如表2所示。

2　DB-API常见异常

3　连接和游标

为了使用基础数据库系统，首先必须连接它。连接数据库需要使用具有恰当名称的connect函数。该函数有多个参数，具体使用哪个参数需要根据数据库类型进行选择。DB-API定义了表3所示的参数作为准则（建议将这些参数按表中给定的顺序传递）。参数类型为字符串类型。

3　connect函数常用参数

connect函数返回连接对象，这个连接对象表示目前和数据库的会话。连接对象支持的方法如表4所示。

4　连接对象方法

rollback方法可能不可用，因为不是所有数据库都支持事务。

commit方法总是可用的，不过如果数据库不支持事务，它就没有任何作用。

cursor方法指游标对象。通过游标执行SQL查询并检查结果。游标比连接支持更多方法，而且在程序中更好用。表5是游标方法的概述，表6是游标特性的概述。

5　游标对象方法 6　游标对象特性
游标对象最重要的属性是execute*()和fetch*()方法。所有对数据库服务器的请求都由这两个方法完成。对fetchmany()方法来说，设置一个合理的arraysize属性很有用。当然，在不需要时最好关掉游标对象。

4　类型

每一个插入数据库中的数据都对应一个数据类型，每一列数据对应同一个数据类型，不同列对应不同的数据类型。在数据库操作的过程中，为了能够正确与基础SQL数据库进行数据交互操作，DB-API定义了用于特殊类型和值的构造函数及常量，所有模块都要求实现表7所示的构造函数和特殊值。

7　DB-API构造函数和特殊值

注：新纪元指1970-01-01 00:00:01 utc时间

数据库操作

前面我们介绍了数据库的基本概念，本节具体介绍数据库的连接及增、删、改、查、操作。
下面的示例以数据库为TEST，表名为employee，employee表字段为FIRST_NAME、LAST_NAME、AGE、SEX、INCOME和CREATE_TIME。
连接数据库TEST使用的用户名为root，密码为root。
在系统上已经安装了Python PyMySQ模块。若不知道怎么安装，则可查阅相关资料。
如果对SQL语句不熟悉，就要先了解数据库的一些基本操作，以方便更好理解接下来的内容。

1　数据库连接

下面是连接MySQL TEST数据库的实例。

#! /usr/bin/python
# -*-coding:UTF-8-*-

import pymysql

def db_connect():
    # 打开数据库连接
    db = pymysql.connect("localhost", "root", "root", "test")

    # 使用cursor()方法创建一个游标对象cursor
    cursor = db.cursor()

    # 使用execute()方法执行SQL查询
    cursor.execute("SELECT VERSION()")

    # 使用 fetchone() 方法获取单条数据
    data = cursor.fetchone()
    print ("Database version : %s " % data)

    # 关闭数据库连接
    db.close()

def main():
    db_connect()

if __name__ == "__main__":
    main()

执行结果如下：

Database version : 5.5.28

2　创建数据库表

如果数据库连接存在，我们就可以使用execute()方法为数据库创建表。创建表EMPLOYEE的代码如下：

#! /usr/bin/python
# -*-coding:UTF-8-*-

import pymysql

def create_table():
    db = pymysql.connect("localhost", "root", "root", "test")
    # 使用 cursor() 方法创建一个游标对象cursor
    cursor = db.cursor()

    # 使用 execute() 方法执行 SQL，如果表存在就删除
    cursor.execute("DROP TABLE IF EXISTS EMPLOYEE")

    # 使用预处理语句创建表
    sql = """CREATE TABLE EMPLOYEE (
          FIRST_NAME      CHAR(20) NOT NULL,
          LAST_NAME      CHAR(20),
          AGE INT,
          SEX CHAR(1),
          INCOME FLOAT,
          CREATE_TIME DATETIME)"""
    try:
         cursor.execute(sql)
         print("CREATE TABLE SUCCESS.")
    except Exception as e:
         print("CREATE TABLE FAILED,CASE:%s" % e)
    finally:
         # 关闭数据库连接
         db.close()

def main():
    create_table()

if __name__ == "__main__":
    main()

执行结果如下：

CREATE TABLE SUCCESS.

从MySQL客户端查看表结构，如图1所示。 1　employee表结构

3　数据库插入

下面使用SQL INSERT语句向表EMPLOYEE插入记录（注意使用了datetime模块）。

#! /usr/bin/python
# -*-coding:UTF-8-*-

import pymysql
import datetime

def insert_record():
    db = pymysql.connect("localhost", "root", "root", "test")

    # 使用cursor()方法获取操作游标
    cursor = db.cursor()

    # SQL 插入语句
    sql = "INSERT INTO EMPLOYEE(FIRST_NAME,LAST_NAME, AGE, SEX, INCOME," \
           " CREATE_TIME) VALUES('%s', '%s', %d, '%c', %d, '%s')" \
           % ('xiao', 'zhi', 22, 'M', 30000, datetime.datetime.now())

    try:
        # 执行sql语句
        cursor.execute(sql)
        # 提交到数据库执行
        db.commit()
        print("INSERT SUCCESS.")
    except Exception as e:
        print('INSERT INTO MySQL table failed.Case:%s' % e)
        # 如果发生错误就回滚
        db.rollback()
    finally:
         # 关闭数据库连接
         db.close()

def main():
    insert_record()

if __name__ == "__main__":
    main()

执行结果如下：

INSERT SUCCESS.

从MySQL客户端查看表插入结果，如图16-2所示。

2　插入数据结果

4　数据库查询

Python查询MySQL使用fetchone()方法获取单条数据，使用fetchall()方法获取多条数据。

• fetchone()：该方法获取下一个查询结果集。结果集是一个对象。
• fetchall()：接收全部返回结果行。
• Rowcount：这是一个只读属性，返回执行execute()方法后影响的行数。

下面的示例用于查询EMPLOYEE表中salary（工资）字段大于10000的所有数据。

#! /usr/bin/python
# -*-coding:UTF-8-*-

import pymysql

def query_data():
    # 打开数据库连接
    db = pymysql.connect("localhost", "root", "root", "test")
    # 使用cursor()方法获取操作游标
    cursor = db.cursor()

    # SQL 查询语句
    sql = "SELECT * FROM EMPLOYEE WHERE INCOME > %d" % 10000
    try:
        # 执行SQL语句
        cursor.execute(sql)
        # 获取所有记录列表
        results = cursor.fetchall()
        for row in results:
           fname = row[0]
           lname = row[1]
           age = row[2]
           sex = row[3]
           income = row[4]
           create_time = row[5]
           # 输出结果
           print("first_name=%s,last_name=%s,age=%d,\n"
                  "sex=%s,income=%d,create_time=%s" %
               (first_name, last_name, age, sex, income, create_time))
    except Exception as e:
        print("Error: unable to fecth data.Error info:%s" % e)
    finally:
         # 关闭数据库连接
         db.close()

def main():
    query_data()

if __name__ == "__main__":
    main()

执行结果如下：

first_name=xiao,last_name=zhi,age=22,
sex=M,income=30000,create_time=2016-10-05 22:29:37

5　数据库更新

下面的示例将EMPLOYEE表中SEX字段值为'M'的记录的AGE字段值增加1：

#! /usr/bin/python
# -*-coding:UTF-8-*-

import pymysql

def update_table():
    # 打开数据库连接
    db = pymysql.connect("localhost", "root", "root", "test")

    # 使用cursor()方法获取操作游标
    cursor = db.cursor()

    # SQL 更新语句
    sql = "UPDATE EMPLOYEE SET AGE = AGE + 1 WHERE SEX = '%s'" % 'M'
    try:
        # 执行SQL语句
        cursor.execute(sql)
        # 提交到数据库执行
        db.commit()
print("UPDATE SUCCESS.")
    except Exception as e:
        print('UPDATE MySQL table failed.Case:%s' % e)
        # 发生错误时回滚
        db.rollback()
    finally:
         # 关闭数据库连接
         db.close()

def main():
    update_table()

if __name__ == "__main__":
    main()

执行结果如下：

UPDATE SUCCESS.

从MySQL客户端查看更新结果，如图3所示。可以发现，AGE变为23了。

3　表更新结果

6　数据库删除

删除操作用于删除数据表中对应的数据。
下面演示删除数据表EMPLOYEE中AGE大于22的所有数据。

#! /usr/bin/python
# -*-coding:UTF-8-*-

import pymysql

def delete_record():
    # 打开数据库连接
    db = pymysql.connect("localhost", "root", "root", "test")

    # 使用cursor()方法获取操作游标
    cursor = db.cursor()

    # SQL 删除语句
    sql = "DELETE FROM EMPLOYEE WHERE AGE > %d" % 22
    try:
        # 执行SQL语句
        cursor.execute(sql)
        # 提交修改
        db.commit()
        print("DELETE SUCCESS.")
    except Exception as e:
         print("DELETE RECORD FAILED.Case:%s" % e)
         # 发生错误时回滚
         db.rollback()
    finally:
         # 关闭连接
         db.close()

def main():
    delete_record()

if __name__ == "__main__":
    main()

行结果如下：

DELETE SUCCESS.

从MySQL客户端查看删除结果，如图4所示。可以看到，之前插入的一条数据被删除了。

4　记录删除

事务

事务机制可以确保数据的一致性。

事务具有4个属性：原子性、一致性、隔离性、持久性，这4个属性通常称为ACID特性。

• 原子性（Atomicity）：一个事务是一个不可分割的工作单位，事务中的所有操作要么都做，要么都不做。
• 一致性（Consistency）：事务必须使数据库从一个一致性状态变为另一个一致性状态。一致性与原子性是密切相关的。
• 隔离性（Isolation）：一个事务的执行不能被其他事务干扰。也就是一个事务内部的操作及使用的数据对并发的其他事务是隔离的，并发执行的各个事务之间不能互相干扰。
• 持久性（Durability）：持续性也称永久性（Permanence），指一个事务一旦提交，它对数据库中数据的改变就应该是永久性的。接下来其他操作或故障不应该对其有任何影响。

Python DB-API 2.0的事务提供了两个方法，即commit和rollback。前面删除方法中的一段代码就使用了事务（前面的插入、更新都使用了事务）：

# SQL删除记录语句
ql = "DELETE FROM EMPLOYEE WHERE AGE > %d" % 22
    try:
        # 执行SQL语句
        cursor.execute(sql)
        # 提交修改
        db.commit()
        print("DELETE SUCCESS.")
    except Exception as e:
         print("DELETE RECORD FAILED.Case:%s" % e)
         # 发生错误时回滚
         db.rollback()
    finally:
         # 关闭连接
         db.close()

在Python数据库编程中，支持事务的数据库在游标建立时会自动开始一个隐形数据库事务。
commit()方法提交所有更新操作，rollback()方法回滚当前游标的所有操作。每一个方法都开启一个新事务。

调试

初学者跟数据库打交道时很容易碰到形形色色的问题，可能一个非常简单的问题也会导致你无法找到问题所在。此时我们需要处理以下几个问题：
（1）程序中有没有我们期望去做却没有实现的功能？找到运行该功能的代码，并确保这段代码如你所期望地运行了。
（2）程序中有没有运行某种不该出现的功能的代码？
（3）有没有一段代码产生的效果和你所期望的不一致？确保你完全明白这段代码，特别是牵涉对其他Python模块的函数或方法调用时。阅读调用到的函数的文档。使用简单的测试用例测试它们并检查结果。
为了能够编程，我们需要对程序如何工作有一个思维模型。如果编写了一段和你预料不同的代码，常常问题在于你的思维模型。
修正思维模型的最佳方法是将程序划分成不同部分（通常是函数和方法），并独立测试每一个部分。一旦找到模型和真实世界的偏差，就能够解决问题。
在开发过程中应当分组件进行构建和测试。当发现一个问题时，只需要检查一小部分不确认是否正确的代码即可。