Question

正如上面所提到的，我们使用 Python 内置的 sqlite3 模块直接在 Python 代码中创建一个内存数据库以及充满了数据的表，从而快速开始本章的学习。与第 2 章和第 3 章一样，第一个示例的重点在于演示如何对 SQL 查询输出的行进行计数。在任何不确定你的查询会输出多少行的情况下，这个功能非常重要。通过这个功能，在开始工作以前，你可以知道有多少行数据需要处理。这个示例还很实用，因为我们会使用很多在 Python 中与数据库交互相关联的语法，用来创建数据库中的表、在表中插入数据和从输出中获取数据并对行进行计数。你将看到很多语法会在本章的示例中多次重复出现。

现在就开始吧。要创建数据库中的表、在表中插入数据，以及在输出中获取数据并对行进行计数，在文本编辑器中输入下列代码，然后将文件保存为 1db_count_rows.py：

 1 #!/usr/bin/env python3
 2 import sqlite3
 3
 4 # 创建SQLite3内存数据库
 5 # 创建带有4个属性的sales表
 6 con = sqlite3.connect(':memory:')
 7 query = """CREATE TABLE sales
 8               (customer VARCHAR(20),
 9               product VARCHAR(40),
10               amount FLOAT,
11               date DATE);"""
12 con.execute(query)
13 con.commit()
14
15 # 在表中插入几行数据
16 data = [('Richard Lucas', 'Notepad', 2.50, '2014-01-02'),
17         ('Jenny Kim', 'Binder', 4.15, '2014-01-15'),
18         ('Svetlana Crow', 'Printer', 155.75, '2014-02-03'),
19         ('Stephen Randolph', 'Computer', 679.40, '2014-02-20')]
20 statement = "INSERT INTO sales VALUES(?, ?, ?, ?)"
21 con.executemany(statement, data)
22 con.commit()
23
24 # 查询sales表
25 cursor = con.execute("SELECT * FROM sales")
26 rows = cursor.fetchall()
27
28 # 计算查询结果中行的数量
29 row_counter = 0
30 for row in rows:
31     print(row)
32     row_counter += 1
33 print('Number of rows: %d' % (row_counter))

图 4-1、图 4-2 和图 4-3 分别展示了在 Anaconda Spyder、Notepad++（Windows）和 TextWrangler（macOS）中编辑脚本的界面。

图 4-1：Anaconda Spyder 中的 Python 脚本 1db_count_rows.py

图 4-2：Notepad++（Windows）中的 Python 脚本 1db_count_rows.py

图 4-3：TextWrangler（macOS）中的 Python 脚本 1db_count_rows.py

在这些图中应该可以看出，与处理 CSV 文件和 Excel 工作簿不同，要与数据库进行交互，还需要学习一些新的语法。

第 2 行代码导入 sqlite3 模块，它提供了一个轻量级的基于磁盘的数据库，不需要独立的服务器进程，并且允许使用 SQL 查询语言的一个变种去访问数据库。SQL 命令在本章的示例代码中均使用大写字母表示。因为本章是关于在 Python 中与数据库进行交互的，所以这里主要介绍了通用的 CRUD（也就是 Create、Read、Update 和 Delete，即创建、读取、更新和删除）数据库操作 1。示例代码包括创建数据库和表（Create）、向表中插入数据（Create）、更新表中数据（Update）和从表中选择特定的行（Read）。这些 SQL 操作在各个关系数据库中是通用的。

1在 http://en.wikipedia.org/wiki/Create,_read,_update_and_delete 这个网址你可以学到更多关于 CRUD 操作的知识。

为了使用这个模块，首先必须创建一个代表数据库的连接对象。第 6 行代码创建了连接对象 con 来代表数据库。在这个示例中，我使用专用名称 ':memory:' 在内存中创建了一个数据库。如果你想要这个数据库持久化，就需要提供另外的字符串。例如，如果我使用字符串 'My_database.db' 或 'C:\Users\Clinton\Desktop\my_database.db'，而不是 ':memory:'，那么数据库对象就会持久保存在当前目录或你的桌面上。

第 7~11 行代码使用 3 个双引号创建了一个多行字符串，并将这个字符串赋给变量 query。这个字符串是一个 SQL 命令，可以在数据库中创建一个名为 sales 的表。sales 表有 4 个属性：customer、product、amount 和 date。customer 属性是一个变长字符型字段，最大字符数为 20。product 属性也是个变长字符型字段，最大字符数为 40。amount 属性是一个浮点数型字段。date 属性是一个日期型字段。

第 12 行代码使用连接对象的 execute() 方法执行包含在变量 query 中的 SQL 命令，在内存数据库中创建 sales 表。

第 13 行代码使用连接对象的 commit() 方法将修改提交（也就是保存）到数据库。当你对数据库做出修改时，必须使用 commit() 方法来保存你的修改，否则，这种修改就不会保存到数据库中。

第 16 行代码创建了一个元组列表，并将这个列表赋给变量 data。列表中的每个元素都是一个包含 4 个值的元组：3 个字符串和 1 个浮点数。这 4 个值按位置对应了表的 4 个属性（就是表中的 4 列）。还有，每个元组包含了表中的一行数据。因为列表包含 4 个元组，所以它包含了表中的 4 行数据。

第 20 行代码与第 7 行代码类似，创建了一个字符串并将其赋给变量 statement。因为这个字符串可以写在一行中，所以包含在一对双引号中，不像在第 7 行代码中，要使用一对 3 个双引号来表示多行字符串。这行代码中的字符串是另一个 SQL 命令，是一个 INSERT 语句，可以将 data 中的数据行插入 sales 表。当你第一次看到这行代码时，会很想知道问号（？）的作用。问号在这里用作占位符，表示你想在 SQL 命令中使用的值。然后，在连接对象的 execute() 或 executemany() 方法中，你需要提供一个包含 4 个值的元组，元组中的值会按位置替换到 SQL 命令中。相对于使用字符串操作组装 SQL 命令的方法，这种参数替换的方法可以使你的代码不易受到 SQL 注入攻击 2，这种攻击确实有害于我们的系统。

2SQL 注入攻击是一种恶意 SQL 语句，攻击者使用它来获取私人信息或损坏数据存储与应用程序。要获取更多关于 SQL 注入攻击的信息，请参考 http://en.wikipedia.org/wiki/SQL_injection。

第 21 行代码使用连接对象的 executemany() 方法为 data 中的每个数据元组执行变量 statement 中的 SQL 命令。因为 data 中有 4 个数据元组，所以这个 executemany() 方法执行 4 次 INSERT 语句，高效率地将 4 行数据插入 sales 表。

请记住，在介绍第 13 行代码时我们强调过，当你对数据库做出修改后，必须使用 commit() 方法，否则你的修改就不会保存到数据库中。将 4 行数据插入 sales 表肯定是对数据库进行了修改，所以在第 22 行代码中，又一次使用连接对象的 commit() 方法将修改保存到数据库。

现在我们的内存数据库中有一个 sales 表，表中有 4 行数据，下面学习一下如何从数据库的表中提取数据。第 25 行代码使用连接对象的 execute() 方法运行一条 SQL 命令，并将命令结果赋给一个光标对象 cursor。光标对象有若干方法，例如，execute、executemany、fetchone、fetchmany 和 fetchall。因为经常需要查看或处理在 execute() 方法中运行的 SQL 命令的全部结果，所以我们通常使用 fetchall() 方法取出（返回）结果集中的所有行。

第 26 行代码执行了 fetchall() 方法，使用光标对象的这个方法返回在第 25 行代码中执行的 SQL 命令的结果集中的所有行，并将这些行赋给列表变量 rows。这样，变量 rows 就是包含了所有来自于第 25 行代码中的 SQL 命令的数据行的列表。每行数据都是一个元组，所以 rows 是一个元组列表。在这种情况下，因为我们已知 sales 表中包含 4 行数据，并且 SQL 命令从 sales 表中选择所有的行，所以 rows 是一个含有 4 个元组的列表。

最后，在第 29~33 行代码中，又回到了熟悉的基础操作，创建了一个变量 row_counter 来计算 rows 中行的数量，创建了一个 for 循环在 rows 的行中迭代，对于 rows 中的每一行，使 row_counter 增加 1，结果，当 for 循环结束了在 rows 的所有行中的迭代之后，在命令行窗口（或终端窗口）中打印出字符串 Number of rows：和 row_counter 中的值。正如之前说过的，我们希望 rows 中有 4 行数据。

要看看这个 Python 脚本的实际运行情况，根据不同操作系统，在命令行中输入下面的一个命令，然后按回车键。

· Windows 操作系统

python 1db_count_rows.py

· macOS 操作系统

chmod +x 1db_count_rows.py
./1db_count_rows.py

你可以看到输出被打印到屏幕上，如图 4-4（Windows 系统）或图 4-5（macOS 系统）所示。

图 4-4：Windows 系统上 1db_count_rows.py 的输出，创建 SQLite3 数据库和表，向表中插入 4 行数据，在表中查询所有数据，并将结果打印在屏幕上

图 4-5：macOS 系统上 1db_count_rows.py 的输出

输出显示，sales 表中有 4 条记录。扩展一下，输出还可以显示我们创建了内存数据库、创建了表 sales、在表中添加了 4 条数据、从表中取出所有行，以及计算输出行的数量。

现在你已经掌握了一些基础操作，包括创建内存数据库、创建表、向表中加载数据，以及从表中读取数据。下面学习如何使用 CSV 文件向表中添加数据和更新表中数据，扩展一下你的数据库操作能力。

4.1.1　向表中插入新记录

前面的示例代码介绍了向表中加载数据的基本操作，但是这个示例有个严重的局限性，就是需要将要加载到表中的数据手写到代码中。如果我们要向表中加载 10 000 条记录，每条记录有 20~30 个字段，那将如何处理？无需多说，手动输入数据不具有扩展性。

在很多情况下，需要加载到数据表中的数据或者是一个数据库查询的结果，或者是保存在一个或多个 Excel 文件或 CSV 文件中的数据。因为对于多数数据库系统来说，将一个数据库查询结果导出为 CSV 文件非常容易，并且我们已经学习了如何处理 Excel 文件和 CSV 文件，所以现在再学习另外一种数据加载方式，也就是如何从 CSV 格式的输入文件将数据批量地加载到数据库的表中。

让我们创建一个新的 Python 脚本。这个脚本将创建一个数据表，向表中插入 CSV 文件中的数据，然后展示表中的数据。其中的第三个步骤，即在命令行窗口或终端窗口打印数据，不是必须的（而且如果你加载了几千条记录的话，我也不建议将记录打印在窗口中），我之所以在示例中包括了这个步骤，是想演示一种不指定单独的列索引，打印出每条记录中所有列的方法（也就是说，这种语法可以扩展到任意数目的列）。下面开始操作，在文本编辑器中输入下列代码，然后将文件保存为 2db_insert_row.py：

 1 #!/usr/bin/env python3
 2 import csv
 3 import sqlite3
 4 import sys
 5 # CSV输入文件的路径和文件名
 6 input_file = sys.argv[1]
 7 # 创建SQLite3内存数据库
 8 # 创建带有5个属性的Suppliers表
 9 con = sqlite3.connect('Suppliers.db')
10 c = con.cursor()
11 create_table = """CREATE TABLE IF NOT EXISTS Suppliers
12                    (Supplier_Name VARCHAR(20),
13                    Invoice_Number VARCHAR(20),
14                    Part_Number VARCHAR(20),
15                    Cost FLOAT,
16                    Purchase_Date DATE);"""
17 c.execute(create_table)
18 con.commit()
19 # 读取CSV文件
20 # 向Suppliers表中插入数据
21 file_reader = csv.reader(open(input_file, 'r'), delimiter=',')
22 header = next(file_reader, None)
23 for row in file_reader:
24     data = []
25     for column_index in range(len(header)):
26          data.append(row[column_index])
27     print(data)
28     c.execute("INSERT INTO Suppliers VALUES (?, ?, ?, ?, ?);", data)
29 con.commit()
30 print('')
31 # 查询Suppliers表
32 output = c.execute("SELECT * FROM Suppliers")
33 rows = output.fetchall()
34 for row in rows:
35     output = []
36     for column_index in range(len(row)):
37          output.append(str(row[column_index]))
38     print(output)

这个脚本和第 2 章中的脚本一样，依赖于 csv 模块和 sys 模块。第 2 行代码导入 csv 模块，以使我们使用其中的函数读取和分析 CSV 格式的输入文件。第 4 行代码导入 sys 模块，这样我们就可以在命令行中提供一个文件的路径和名称供脚本使用。第 3 行代码导入 sqlite3 模块，我们可以使用其中的方法创建简单的本地数据库和数据表，也可以执行 SQL 查询。

第 6 行代码使用 sys 模块在命令行中读取文件的路径和名称，并将这个值赋给变量 input_file。

第 9 行代码为一个简单的本地数据库 Suppliers.db 创建连接。我提供了一个数据库名称，而不使用专门的关键字 ':memory:'，来演示如何创建一个持久化数据库，当你重启计算机时，这个数据库不会被删除。因为你要把这个脚本保存在桌面上，所以 Suppliers.db 也要保存在桌面上。如果你想将数据库保存在其他位置，可以选择一个路径，比如 'C:\Users\<Your Name>\Documents\Supplier.db'，用来代替 'Suppliers.db'。

第 10~18 行代码创建了一个光标，以及一个多行 SQL 语句，用来创建一个具有 5 个列属性的数据表 Suppliers。执行这条 SQL 语句，并将修改提交到数据库。

第 21~29 行代码从 CSV 格式的输入文件中读取要加载到数据库中数据，并对输入文件中的每行数据执行一条 SQL 语句，将数据插入到数据库的表中。第 21 行代码使用 csv 模块创建 file_reader 对象。第 22 行代码使用 next() 方法从输入文件中读入第一行，也就是标题行，然后将其赋给变量 header。第 23 行代码创建了一个 for 循环，在输入文件的所有数据行之间循环。第 24 行代码创建了一个空列表变量 data。对于输入中的每一行，用行中的数据去填充 data，这些数据将在第 28 行代码中的 INSERT 语句中使用。第 25 行代码创建了一个 for 循环，在每行数据的各个列之间循环。第 26 行代码通过列表的 append() 方法使用输入文件这一行中所有的数据去填充 data。第 27 行代码在命令行窗口或终端窗口中打印出追加到 data 中的这行数据。请注意缩进。第 27 行代码的缩进是在外部 for 循环之下，不是在内部 for 循环之下的，所以它是对于输入文件中的每一行来执行，而不是对于输入文件中的每一行和每一列去执行。这一行有助于调试脚本，但是一旦你确定代码能够正确运行，就可以将这行代码删除或者注释掉，这样在屏幕上就不会打印出过多的输出。

第 28 行代码是实际将每行数据加载到数据表中的代码。这行代码使用光标对象的 execute() 方法执行一条 INSERT 语句，将一行数据插入到表 Suppliers 中。问号 ? 是要插入的每个实际值的占位符。问号的数量对应着输入文件中列的数量，它们都对应着数据表中列的数量。而且，输入文件中列的顺序也要对应数据表中列的顺序。要替换到问号位置的值来自于列表 data，这个列表要放在 execute() 语句中逗号的后面。因为 data 是使用输入文件中的每行数据填充的，而且 INSERT 语句也是对于输入文件中的每行数据执行的，所以这些代码可以高效地从输入文件中读取数据行，并把这些数据行加载到数据表中。最后，第 29 行代码是另一个 commit 语句，将修改提交到数据库。

第 32~38 行代码演示了如何从数据表 Suppliers 中选择所有数据，并将输出打印到命令行窗口或者终端窗口。第 32 和 33 行代码执行一条 SQL 语句，从 Suppliers 表中选择所有数据，并将“output”中的所有行读入变量“rows”。第 34 行代码创建了一个 for 循环，在“rows”的每一行中循环。第 36 行代码创建了一个 for 循环，在每行的各个列之间循环。第 37 行代码将每列中的值追加到一个名为“output”的列表中。最后，第 38 行代码中的 print 语句确保输出中的每一行都被打印到一个新行中（请注意缩进，是在行循环中，不是在列循环中）。

现在我们需要一个 CSV 格式的输入文件，其中包含着要加载到数据表中的所有数据。对于这个示例，可以使用在第 2 章中用过的 supplier_data.csv 文件。如果你跳过了第 2 章，或者没有这个文件，那么可以在图 4-6 中看到这个文件中的数据。

图 4-6：CSV 文件 supplier_data.csv 中的示例数据，显示在 Excel 工作表中

当你有了 Python 脚本和 CSV 输入文件之后，就可以使用脚本将 CSV 输入文件中的数据加载到 Suppliers 数据表中了。要完成这个操作，在命令行中输入以下命令，然后按回车键：

python 2db_insert_rows.py supplier_data.csv

图 4-7 展示了在命令行窗口中打印输出的界面。输出的第一部分是从 CSV 文件中解析出的数据行，输出的第二部分是同样的行，只不过是从 sqlite 数据表中提取出来的。

图 4-7：Windows 系统下 2db_insert_rows.py 的输出

这个输出展示了 12 个值列表，来自于 CSV 输入文件中除标题行之外的 12 行数据。在这些来自于输入文件的 12 行值列表之下，有一个空行，然后是从数据表中提取的 12 行数据的值列表。

这个示例通过从 CSV 输入文件中将所有要加载的数据读取和插入到表中，演示了如何规模化地将数据加载到数据库中。这个示例适用于向数据表中添加新行的情况，但是如果想更新表中已有的行，应该怎么做呢？下一个示例就可以解决这个问题。

4.1.2　更新表中记录

前一个示例介绍了使用 CSV 输入文件向数据表中添加新行的方法，因为可以使用循环和 glob，所以可以将这个方法扩展到任意数目的文件。但有些时候，不需要向数据表中加载新数据，而是需要更新表中已有的行。

幸运的是，我们可以重新使用从 CSV 输入文件中读取数据的技术来更新表中已有的行。实际上，为 SQL 语句组装一组值和为输入文件中的每一行执行 SQL 语句的技术与前一个示例是一样的。SQL 语句还是有所改变的，不同的是从 INSERT 语句变成了 UPDATE 语句。

我们已经熟悉了如何使用 CSV 输入文件将数据加载到数据表中，下面学习如何使用 CSV 输入文件更新数据表中已有的记录。要完成这个操作，在文本编辑器中输入下列代码，然后将文件保存为 3db_update_row.py：

 1 #!/usr/bin/env python3
 2 import csv
 3 import sqlite3
 4 import sys
 5 # CSV输入文件的路径和文件名
 6 input_file = sys.argv[1]
 7 # 创建SQLite3内存数据库
 8 # 创建带有4个属性的sales表
 9 con = sqlite3.connect(':memory:')
10 query = """CREATE TABLE IF NOT EXISTS sales
11          (customer VARCHAR(20),
12          product VARCHAR(40),
13          amount FLOAT,
14          date DATE);"""
15 con.execute(query)
16 con.commit()
17 # 向表中插入几行数据
18 data = [('Richard Lucas', 'Notepad', 2.50, '2014-01-02'),
19     ('Jenny Kim', 'Binder', 4.15, '2014-01-15'),
20     ('Svetlana Crow', 'Printer', 155.75, '2014-02-03'),
21     ('Stephen Randolph', 'Computer', 679.40, '2014-02-20')]
22 for tuple in data:
23     print(tuple)
24 statement = "INSERT INTO sales VALUES(?, ?, ?, ?)"
25 con.executemany(statement, data)
26 con.commit()
27 # 读取CSV文件并更新特定的行
28 file_reader = csv.reader(open(input_file, 'r'), delimiter=',')
29 header = next(file_reader, None)
30 for row in file_reader:
31     data = []
32     for column_index in range(len(header)):
33          data.append(row[column_index])
34     print(data)
35     con.execute("UPDATE sales SET amount=?, date=? WHERE customer=?;", data)
36 con.commit()
37 # 查询sales表
38 cursor = con.execute("SELECT * FROM sales")
39 rows = cursor.fetchall()
40 for row in rows:
41     output = []
42     for column_index in range(len(row)):
43          output.append(str(row[column_index]))
44     print(output)

所有代码看上去都很熟悉。第 2~4 行代码导入了 3 个 Python 内置模块，这样我们就可以使用其中的方法来读取命令行输入、读取 CSV 输入文件和与内存数据库和数据表进行交互。第 6 行代码将 CSV 输入文件的路径名赋给变量 input_file。

第 9~16 行代码创建了一个内存数据库，还创建了一个具有 4 个列属性的数据表 sales。

第 18~24 行代码为 sales 表创建了 4 条记录，并将这 4 条记录插入到了表中。请用一点时间仔细看一下 Richard Lucas 和 Jenny Kim 的记录。我们稍后要用脚本更新的就是这 2 条记录。现在，sales 表中有 4 条记录，看上去与你要更新记录的任何数据表没什么不同，即使比实际的数据表要小很多。

第 28~36 行代码与前面的示例几乎完全相同。唯一的明显区别是第 35 行，UPDATE 语句代替了原来的 INSERT 语句。在 UPDATE 语句中，你必须指定你想更新哪一条记录和哪一个列属性。在这个例子中，我们想为一组特定的 customer 更新 amount 值和 date 值。像前面的示例一样，UPDATE 语句也需要很多问号占位符表示出查询中的值的位置，CSV 输入文件中数据的顺序也要同查询中属性的顺序一样。在这个例子中，查询中的属性从左到右分别是 amount、date 和 customer；所以，CSV 输入文件中的列从左到右也应该是数量、日期和客户名称。

最后，第 39~44 行代码和前面示例中这部分的代码基本相同。这些代码从 sales 表中取出所有行，然后在命令行窗口或终端窗口中打印出每一行，并使用一个空格分隔每一列。

现在我们需要一个 CSV 输入文件，其中包含着要用来更新数据表中某些记录的数据。下面是创建这个 CSV 文件的步骤：

(1) 打开一个电子表格程序。

(2) 添加如图 4-8 中所示的数据。

(3) 将文件保存为 data_for_updating.csv。

图 4-8：文件 data_for_updating.csv 中的示例数据，显示在 Excel 工作表中

现在你已经完成了 Python 脚本和 CSV 输入文件，可以使用脚本和输入文件来更新 sales 数据表中的某些行了。要完成这个操作，在命令行中输入以下命令，然后按回车键：

python 3db_update_rows.py data_for_updating.csv

图 4-9 展示了将输出打印在命令行窗口中的界面。前 4 行输出（元组）是初始数据行，接下来 2 行（列表）是从 CSV 文件中读入的数据，最后 4 行（列表）是从数据库中取出的更新了行之后的数据。

图 4-9：Windows 系统下 3db_update_rows.py 的输出

这个输出首先展示了数据库中初始的 4 行数据，接下来是要更新到数据库中的 2 行数据。在要更新的 2 行数据中，Richard Lucas 的新的 amount 值为 4.25，新的 date 值为 5/11/2014。同样，Jenny Kim 的新的 amount 值为 6.75，新的 date 值为 5/12/2014。

在这 2 行下面，输出还展示了执行更新之后从数据表中取出的 4 行数据。每行数据打印在一个单独的行中，行中每个数据使用空格分隔。回忆一下，Richard Lucas 的初始 amount 值和 date 值分别是 2.5 和 2014-01-02。同样，Jenny Kimd 的初始 amount 值和 data 值分别是 4.15 和 2014-01-15。从图 4-9 中的输出可以看出，Richard Lucas 和 Jenny Kim 的这两个值已经被更新成了 CSV 输入文件中的新值。

这个示例演示了批量更新数据表中已有记录的方法，这种方法使用 CSV 输入文件来提供更新特定记录的数据。本章内容到现在为止，提供的示例都是依赖于 Python 内置的 sqlite3 模块。使用这个模块可以快速编写脚本，不用依赖某个独立的、需要下载安装的数据库系统，比如 MySQL、PostgreSQL 或 Oracle。在下节中，我们要在下载安装一个数据库系统（MySQL）的基础上重新实现这些示例，还要学习在数据库系统中向数据表中加载数据和更新记录，以及通过数据库查询将输出写入 CSV 文件的方法。下面开始这部分的学习。