Python+MySQL数据库编程

使用简单的纯文本文件可实现的功能有限。诚然，使用它们可做很多事情，但有时可能还需要额外的功能。你可能希望能够自动完成序列化，此时可求助于shelve和pickle（类似于shelve）。不过你可能需要比这更强大的功能。例如，你可能想自动支持数据的并发访问，及允许多位用户读写磁盘数据，而不会导致文件受损之类的问题。还有可能希望同时根据多个数据字段或属性进行复杂的搜索，而不是采用shelve提供的简单的单键查找。尽管可供选择的解决方案有很多，但如果要处理大量的数据，并希望解决方案易于其他程序员理解，选择较标准的数据库可能是个不错的主意。

下面讨论Python数据库API（一种连接到SQL数据库的标准化方式），并演示如何使用这个API来执行一些基本的SQL。最后，将讨论其他一些数据库技术。

这里不会提供关系型数据库和SQL语言教程。通过阅读有关数据库（如PostgreSQL，SQLite，或马上使用的MySQL）的文档，应该能够学到你需要的知识。如果你以前没有使用过关系型数据库，可参阅www.sqlcourse.com或在网上搜索相关的主题，也可参阅Clare Churcher的著作Beginning SQL Queries, 2nd ed(Apress,2016)。

即将使用的是使用广泛而且可靠的开源数据库MySQL，但显然绝非只能使用它。有多种流行的商用数据库，如Orancle和Microsoft SQL Server，还有一些使用广泛而且可靠的开源数据库，如PostgreSQL和Firebird。有关Python支持的数据库清单，请参阅https://wiki.python.org/moin/Databaseinterfaces。数据库也并非只有关系型（SQL）这一种，还有对象数据库【如Zope Object Database（ZODB，http://www.zodb.org）】、基于表格的紧凑数据库【如Metakit（http://equi4.com/metakit）】、更简单的键-值数据库【如UNIX DBM（https://docs.python.org/3/library/dbm.html）】。另外，还有日益流行的各种NoSQL数据库，如MongoDB（http://mongodb.com）、Cassandra（http://cassandra.apache.org）和Redis（http://redis.io），这些数据库都可以使用Python来访问。

文章的重点是低级的数据库交互，但有一些高级库能够让你轻松地完成复杂的工作，如SQLAlchemy和SQLObject，要获悉这方面的信息，可参阅http://sqlalchemy.org和http://sqlobject.org，也可在网上搜索Python对象关系映射器。

Python数据库API

前面说过，有各种SQL数据库可供选择，其中很多都有相应的Python客户端模块（有些数据库甚至有多个）。所有数据库的大多数基本功能相同，因此从理论上来说，对于使用其中一种数据库的程序，很容易对其进行修改以使用另一种数据库。问题是即便不同模块提供的功能大致相同，它们的接口（API）也是不同的。为解决Python数据库模块存在的这种问题，人们一致同意开发一个标准数据库API（DB API）。这个API的最新版本（2.0）是在PEP249（Python Database API Specification v2.0）中定义的，网址为http://python.org/peps/pep-0249.html。如果你对这个API的细节不感兴趣，可以跳过本节。

全局变量

所有与DB API2.0兼容的数据库模块都必须包含三个全局变量，它们描述了模块的特征。这样做的原因是，这个API设计得很灵活，无需进行太多包装就能配合多种不同的底层机制使用。如果要让程序能够使用多种不同的数据库，可能会比较麻烦，因为需要考虑众多不同的可能性。在很多情况下，一种更现实的做法是检查这些变量，看看给定的模块是否是程序能够接受的。如果不是，就显示错误信息并退出或者引发异常。下表总结了这些全局变量。

API级别（apilevel）是一个字符串常量，指出了使用的API版本。DB API2.0指出，这个变量的值为'1.0'或'2.0'。如果没有这个变量，就说明模块不与DB API2.0兼容，应假定是用的是DB API1.0。编写代码时，允许这个变量为其他值也没有害处，因为说不定什么时候DB API3.0就出来了。

线程安全程度（threadsafety）是一个0~3（含）的整数。0表示线程不能共享模块，而3表示模块是绝对线程安全的。1表示线程可共享模块本身，但不能共享连接，而2表示线程可共享模块和连接，但不能共享游标。如果你不使用线程（在大多数情况下可能不会是这样的），就根本不用关心这个变量。

参数风格（paramstyle）表示当你执行多个类似的数据库查询时，如何在SQL查询中加入参数。'format'表示字符串格式设置方式（使用基本的格式编码），如在插入参数的地方插入%s。'pyformat'表示扩展的格式编码，即旧式字典插入使用的格式编码，如%(foo)s。除这些Python风格外，还有三种指定待插入字段的方式：'qmark'表示使用问号，'numeric'表示使用:1和:2这样的形式表示字段（其中的数字是参数的编号），而'named'表示使用:foobar这样的形式表示字段（其中foobar为参数名）。如果你觉得参数样式令人迷惑，也不用担心。编写简单程序时，不会用到它们。如果需要明白特定的数据库是如何处理参数的，可参阅相关的文档。

异常

DB API定义了多种异常，让你能够细致的处理错误。然而，这些异常构成了一个层次结构，因此使用一个except块就可捕获多种异常。当然，如果你觉得一切都正常运行，且不介意出现不太可能的错误时关闭程序，可以根本不考虑这些异常。下表说明了这个异常的层次结构。异常应该在整个数据库模块都可用。有关这些异常的深入描述，请参阅DB API规范（前面提到的PEP）。

连接和游标

要使用底层的数据库系统，必须先连接到它，为此可使用名称贴切的函数connect。这个函数接收多个参数，具体是哪些取决于要使用的数据库。作为指南，DB API定义了下表所示的参数。推荐将这些参数定义为关键字参数，并按下表所示的顺序排列。这些参数都应该是字符串。

后面提供了函数connect的具体使用实例。

函数connect返回一个连接对象，表示当前到数据库的会话。连接对象支持下表所示的方法。

方法rollback可能不可用，因为并非所有数据库都支持事务（事务其实就是一系列操作）。可用时，这个方法撤销所有未提交的事务。

方法commit总是可用的，但如果数据库不支持事务，这个方法就什么都不做。关闭连接时，如果还有未提交的事务，将隐式的回滚它们——但仅当数据库支持回滚时才如此！如果你不想依赖于这一点，应在关闭连接时提交。只要提交了所有的事务，就无需操心关闭连接的事情，因为作为垃圾被收集时，连接会自动关闭。然而，为了安全起见，还是调用close吧，因为这样做不需要长时间敲击键盘。

说到方法cursor，就必须说说另一个主题：游标对象。你使用游标来执行SQL查询和查看结果。游标支持的方法比连接多，在程序中的地位也可能重要得多。下面两张表分别概述了游标的方法和属性。

有些方法将在后面详细讨论，还有一些（如setinputsizes和sizeoutputsizes）则不会讨论。有关这方面的的详细信息，请参阅前面提到的PEP。

类型

对于插入到某些类型的列中的值，底层SQL数据库可能要求他们满足一定的条件。为了能够与底层的SQL数据库正确的相互操作，DB API定义了一些构造函数和常量（单例），用于提供特殊的类型和值。例如，要在数据库中添加日期，应使用相应数据库连接模块中的构造函数Date来创建它，这让连接模块能够在幕后执行必要的转换。每个模块都必须实现下表所示的构造函数和特殊值。有些模块可能完全没有遵守这一点。例如，Python操作SQLite数据库的sqlite3模块就没有导出表中的特殊值（从STRING到ROWID）。

MySQL和pymysql

前面说过，可用的数据库引擎有很多，它们都有相应的Python模块。这些数据库引擎大都作为服务器运行，连安装都需要有管理员权限。为降低Python DB API和pymysql的使用门槛，我选择将MySQL和Python安装在一台机器上。

MySQL安装比较简单，网上一搜一堆教程，这里就不讲了。pymysql安装也是非常简单，执行命令pip install pymysql即可。如果使用的是Python2，就需要把pymysql换成mysqldb！

起步

要使用Python库中的pymysql，如果安装成功，可通过导入模块pymysql。然后，就可以创建到数据库的连接。为此，只需提供数据库地址，用户名，密码，要使用的数据库（一定要确保数据库存在）即可。

>>> import pymysql

>>> conn = pymysql.connect(host="localhost", user="root", password="123456789", db="test")

接下来可从连接获得游标。

>>> curs = conn.cursor()

这个游标可用来执行SQL查询。执行完查询后，如果修改了数据，务必提交所做的修改，这样才会将其保存到磁盘中。

>>> conn.commit()

你可以（也应该）在每次修改数据库后都进行提交，而不是仅在要关闭连接时才这样做。要关闭连接，只需调用方法close。

>>> conn.close()

数据库应用程序示例

作为示例，我将演示如何创建一个小型的营养成分数据库，这个数据库基于美国农业部（USDA）农业研究服务（https://www.ars.usda.gov）提供的数据。美国农业部的链接常常会有细微的变化。但只要按照下面介绍的做，就应该能够找到相关的数据集。在网页https://www.ars.usda.gov中，单击下拉列表Research中的链接Databases and Datasets进入相应的页面，再单击其中的链接Human Nutrition。在打开的页面中，应该能够找到链接Composition of Foods Raw, Processed, Prepared USDA National Nutrient Database for Standard Reference, Release 28。在单击这个链接打开的页面中有大量的数据文件，它们使用的是我们需要的纯文本（ASCII）格式。找到并单击链接ASCII (Abbreviated; 1.1Mb; ISO/IEC 8859-1)zip，在单击这个链接打开的页面中单击链接Download。你将获得一个zip文件，其中包含一个ABBREV.txt的文本文件，还有一个描述该文件内容的PDF文件。如果你找不到这个文件，也可使用其他的旧数据，只是需要相应的修改源代码。

在文件ABBREV.txt中，每一行都是一条数据记录，字段之间用脱字符（^）分隔。数字字段直接包含数字，而文本字段用两个波浪字符（~）将其字符串值括起。下面是一个示例行：

~01001~^~BUTTER,WITH SALT~^15.87^717^0.85^81.11^2.11^0.06^0.0^0.06^24^0.02^2^24^24^643^0.09^0.000^0.000^1.0^0.0^0.005^0.034^0.042^0.110^0.003^3^0^3^3^18.8^0.17^2499^684^671^0^158^0^0^0^2.32^0.0^0^7.0^51.368^21.021^3.043^215^5.0^~1 pat, (1" sq, 1/3" high)~^14.2^~1 tbsp~^0

要将这样的行分解成字段，只需使用line.split('^')即可。如果一个字段以波浪字符打头，你就知道他是一个字符串，因此可使用field.strip('~')来获取其内容。对于其他字段（即数字字段），使用float(field)就能获取其内容，但字段为空时不能这样做。本节接下来将开发一个程序，将这个ASCII文件中的数据转换为SQL数据库，并让你能够执行一些有趣的查询。

创建并填充数据表

要创建并填充数据表，最简单的解决方案是单独编写一个一次性程序。这样秩序运行这个程序一次，就可将它及原始数据源（文件ABBREV.txt）抛在脑后了，不过保留它们可能是个不错的主意。

下图所示的程序在food数据库中创建一个名为food的表（其中包含一些合适的字段）；读取文件ABBREV.txt并对其进行分析（使用工具函数convert对各行进行分割并对各个字段进行转换）；通过调用curs.execute来执行一条SQL INSERT语句，从而将字段中的值插入数据库中。

注意：也可使用curs.executemany，并向它提供一个列表（其中包含从数据文件中提取的所有行）。就这里而言，这样做速度稍有提高，但如果使用的是通过网络连接的客户/服务器SQL系统，速度将有极大的提高。

当你运行这个程序时（文件ABBREV.txt和它位于同一个目录），它将在food数据库中新建一个food的表，表中包含几乎所有数据。

建议你多多尝试这个程序：使用不同的输入，添加print语句等。

搜索并处理结果

数据库使用起来非常简单：创建一条连接并从它获取一个游标；使用方法execute执行SQL查询并使用诸如fetchall等方法提取结果。下面的代码是一个微型程序，它通过命令行参数接受一个SQL SELECT条件，并以记录格式将返回的行打印出来。你可在命令行中像下面这样运行它：

$ python food_query.py "kcal <= 100 AND fiber >= 10 ORDER BY sugar"

运行这个程序时，你可能发现了一个问题：第一行指出，生橘子皮（raw orange peel）好像不含任何糖分。这是因为在数据文件中缺少这个字段。你可对导入脚本进行改进，以检测这种情况，并插入NULL而不是0来指出缺失数据。这样，你就可使用类似下面的条件：

"kcal <= 100 AND fiber >= 10 AND sugar ORDER BY sugar"

这要求仅当sugar包含实际数据时才返回相应的行。这种策略恰好也适用于当前的数据库——上述条件将丢弃糖分为0的行。

警告 这个程序从用户那里获取输入，并将其插入到SQL查询中。在你是用户且不会输入太不可思议的内容时，这没有问题。然而，利用这种输入偷偷地插入恶意的SQL代码以破坏数据库是一种常见的计算机攻击方式，称为SQL注入攻击。请不要让你的数据库（以及其他任何东西）暴露在原始用户输入的“火力范围”内，除非你对这样做的后果心知肚明。