>>> from helper import info; info()
页面更新时间： 2020-07-05 19:24:31
操作系统/OS: Linux-4.19.0-9-amd64-x86_64-with-debian-10.4 ；Python: 3.7.3

SQLite 和 PySQLite¶

前面说过，可用的SQL数据库引擎有很多，它们都有相应的Python模块。这些数据库引擎大都作为服务器程序运行，连安装都需要有管理员权限。为降低Python DB API的使用门槛，我选择了一个名为SQLite的小型数据库引擎。它不需要作为独立的服务器运行，且可直接使用本地文件，而不需要集中式数据库存储机制。

在较新的Python版本（从2.5开始）中，SQLite更具优势，因为标准库包含一个SQLite包装器：使用模块sqlite3实现的PySQLite。除非从源代码编译Python，否则Python很可能包含这个数据库。你可能应尝试运行13.2.1节中的程序片段，如果它能够运行，就无需专门安装PySQLite 和SQLite了。

如果你使用的不是标准库中的PySQLite版本，可能需要修改前述程序片段中的import语句。有关这方面的详细信息，请参阅相关的文档。

获取PySQLite¶

如果你使用的是较旧的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来导入它。然后，就可创建直接到 13 数据库文件的连接。为此，只需提供一个文件名（可以是文件的相对路径或绝对路径）；如果指定的文件不存在，将自动创建它。

>>> import sqlite3
>>>
>>> conn = sqlite3.connect('xx_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中，每行都是一条数据记录，字段之间用脱字符（^）分隔。数字字段直接包含数字，而文本字段用两个波浪字符（~）将其字符串值括起。下面是一个示例行（为简洁起见删除了部分内容）：

₀₇₂₇₆^~HORMEL SPAM … PORK W/ HAM MINCED CND~^ … ^~1 serving~^^~~0

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

注意这个示例程序很简单，我是有意为之的。有关在Python中使用数据库的复杂示例，请参阅第26章。

创建并填充数据库表

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

代码清单13-1所示的程序创建一个名为food的表（其中包含一些合适的字段）；读取文件

ABBREV.txt并对其进行分析（使用工具函数convert对各行进行分割并对各个字段进行转换）；通过调用curs.execute来执行一条SQL INSERT语句，从而将字段中的值插入数据库中。

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

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

>>> import sqlite3
>>> def convert(value):
>>>     if value.startswith('~'):
>>>         return value.strip('~')
>>>     if not value:
>>>         value = '0'
>>>     return float(value)
>>>
>>> conn = sqlite3.connect('/tmp/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
>>> ) ''')
>>>
>>> conn.commit()

>>> query = 'INSERT INTO food VALUES (?,?,?,?,?,?,?,?,?,?)'
>>>
>>> field_count = 10
>>>
>>> for 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()
  File "<ipython-input-5-6b1c878c0d01>", line 9
    for f in fields: # [:field_count]]
                   ^
SyntaxError: invalid syntax

注意¶

在代码清单13-1中，使用的参数风格为qmark，即使用问号来标记字段。如果你使用的是较旧的PySQLite版本，可能需要使用字符%来标记字段。

当你运行这个程序时（文件ABBREV.txt和它位于同一个目录），它将新建一个名为food.db的

文件，其中包含数据库中的所有数据。建议你多多尝试这个程序：使用不同的输入、添加print语句等。 2. 搜索并处理结果

数据库使用起来非常简单：创建一条连接并从它获取一个游标；使用方法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, sys
>>> conn = 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()
SELECT * FROM food WHERE -f
---------------------------------------------------------------------------

OperationalError                          Traceback (most recent call last)

<ipython-input-9-1d53fcbf6cd6> in <module>()
      6 print(query)
      7
----> 8 curs.execute(query)
      9
     10 names = [f[0] for f in curs.description]


OperationalError: no such column: f

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