Python实战（3）-数据库操作

前面说过，可用的SQL数据库引擎有很多，它们都有相应的Python模块。这些数据库引擎大都作为服务器程序运行，连安装都需要有管理员权限。为降低Python DB API的使用门槛，我选择了一个名为SQLite的小型数据库引擎。它不需要作为独立的服务器运行，且可直接使用本地文件，而不需要集中式数据库存储机制。在较新的Python版本（从2.5开始）中，SQLite更具优势，因为标准库包含一个SQLite包装器：使用模块sqlite3实现的PySQLite。除非从源代码编译Python，否则Python很可能包含这个数据库。

如果你使用的是较旧的Python版本，必须安装PySQLite才能使用SQLite数据库，可从https://github.com/ghaering/pysqlite下载。在带包管理器系统的Linux系统中，很可能可直接从包管理器获取PySQLite和SQLite。你也可使用Python自己的包管理器pip。另外，你还可获取PySQLite和SQLite的源代码包，再自己编译它们。如果你使用的是较新的Python版本，几乎可以肯定已经有PySQLite。如果有什么缺失了，那就是数据库本身，即SQLite（但这也很可能已经有了）​。在这种情况下，可从SQLite官网（http://sqlite.org）获取源代码（务必获取执行了自动代码生成的源代码包）​。要编译SQLite，只需按README文件中的说明做即可。接着编译PySQLite时，必须确保编译进程能够访问SQLite库和include文件。如果SQLite被安装在标准位置，PySQLite发布版中的安装脚本很可能能够找到它。在这种情况下，只需执行如下命令即可：

python setup.py build
python setup.py install

你也可以只执行第二个命令，因为它将自动执行构建过程。如果这样做时出现了大量的错误消息，很可能是因为安装脚本没有找到所需的文件。请确保你知道include文件和库安装在什么地方，并显式地将这个位置提供给安装脚本。假设我在目录/home/mlh/sqlite/current中就地编译了SQLite，那么头文件可能位于/home/mlh/sqlite/current/src，而库位于/home/mlh/sqlite/current/build/lib。为了让安装进程使用这些路径，可编辑安装脚本setup.py，在其中像下面这样设置变量include_dirs和library_dirs。

            include_dirs = ['/home/mlh/sqlite/current/src']library_dirs = ['/home/mlh/sqlite/current/build/lib']

起步

要使用Python标准库中的SQLite，可通过导入模块sqlite3来导入它。然后，就可创建直接到数据库文件的连接。为此，只需提供一个文件名（可以是文件的相对路径或绝对路径）​；如果指定的文件不存在，将自动创建它。

import sqlite3
conn = sqlite3.connect('somedatabase.db')

我的源码目录原来没有somedatabase.db文件，执行后结果如下：

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

        curs = conn.cursor()

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

          >>> conn.commit()

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

          >>> conn.close()

数据库应用程序示例

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

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

          ～07276～^～HORMEL SPAM ... PORK W/ HAM MINCED CND～^ ... ^～1 serving～^^～～^0

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

创建并填充数据库表

要创建并填充数据库表，最简单的解决方案是单独编写一个一次性程序。这样只需运行这个程序一次，就可将它及原始数据源（文件ABBREV.txt）抛在脑后了，不过保留它们可能是个不错的主意。代码清单13-1所示的程序创建一个名为food的表（其中包含一些合适的字段）​；读取文件ABBREV.txt并对其进行分析（使用工具函数convert对各行进行分割并对各个字段进行转换）​；通过调用curs.execute来执行一条SQL INSERT语句，从而将字段中的值插入数据库中。注意：也可使用curs.executemany，并向它提供一个列表（其中包含从数据文件中提取的所有行）​。就这里而言，这样做速度稍有提高，但如果使用的是通过网络连接的客户/服务器SQL系统，速度将有极大的提高。

代码清单13-1 将数据导入数据库（importdata.py）

        import sqlite3def convert(value):if value.startswith('～'):return value.strip('～')if not value:value = '0'return float(value)conn = sqlite3.connect('food.db')curs = conn.cursor()curs.execute('''CREATE TABLE food (id TEXT PRIMARY KEY,desc    TEXT,water   FLOAT,kcal    FLOAT,protein FLOAT,fat      FLOAT,ash      FLOAT,carbs   FLOAT,fiber   FLOAT,sugar   FLOAT)''')query = 'INSERT INTO food VALUES (?,?,?,?,?,?,?,?,?,?)'field_count = 10for line in open('ABBREV.txt'):fields = line.split('^')vals = [convert(f) for f in fields[:field_count]]curs.execute(query, vals)conn.commit()conn.close()

当你运行这个程序时（文件ABBREV.txt和它位于同一个目录）​，它将新建一个名为food.db的文件，其中包含数据库中的所有数据。建议你多多尝试这个程序：使用不同的输入、添加print语句等

搜索并处理结果

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

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

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

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

这要求仅当sugar字段包含实际数据时才返回相应的行。这种策略恰好也适用于当前的数据库——上述条件将丢弃糖分为0的行。你可能想尝试使用ID搜索特定食品的条件，如使用ID 08323搜索Cocoa Pebbles。问题是SQLite处理其值的方式不那么标准，事实上，它在内部将所有的值都表示为字符串，因此在数据库和Python API之间将执行一些转换和检查。通常，这没有问题，但使用ID搜索可能会遇到麻烦。如果你提供值08323，它将被解读为数字8323，进而被转换为字符串"8323"，即一个不存在的ID。在这种情况下，可能应该显示错误消息，而不是采取这种意外且毫无帮助的行为；但如果你很小心，在数据库中就将ID设置为字符串"08323"，就不会出现这种问题。

代码清单13-2 食品数据库查询程序（food_query.py）

        import sqlite3, sysconn = sqlite3.connect('food.db')curs = conn.cursor()query = 'SELECT * FROM food WHERE ' + sys.argv[1]print(query)curs.execute(query)names = [f[0] for f in curs.description]for row in curs.fetchall():for pair in zip(names, row):print('{}: {}'.format(*pair))print()

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