Week 7 SQL

欢迎！

  • 前几周，我们向您介绍了 Python，这是一种高级编程语言，它使用了我们在 C 中学到的相同构建块。然而，我们引入这种新语言并不是为了学习“只是另一种语言”。相反，我们这样做是因为有些工具更适合某些工作，但不适合其他工作！

  • 本周，我们将继续更多与 Python 相关的语法。

  • 此外，我们将把这些知识与数据相结合。

  • 最后，我们将讨论 SQL 或 Structured Query Language，这是我们可以与数据交互和修改数据的特定于域的方式。

  • 总的来说，本课程的目标之一是学习一般编程，而不仅仅是如何使用本课程描述的语言进行编程。

平面文件数据库

  • 正如您之前可能已经看到的，数据通常可以用列和行的模式来描述。

  • 在 Microsoft Excel 和 Google Sheets 中创建的电子表格可以输出到 csv 或逗号分隔值文件。

  • 如果你查看一个 csv 文件，会注意到它是扁平的：所有数据都存储在一个由文本文件表示的单一表格中。我们将这种数据形式称为平面文件数据库。

  • 所有数据都是逐行存储的。每列由逗号或其他值分隔。

  • Python 原生支持 csv 文件。

  • 首先，下载 favorites.csv 并将其上传到 cs50.dev 内的文件资源管理器。其次，检查此数据，请注意第一行的特殊之处在于它定义了每一列。然后，每条记录被逐行存储。

在终端窗口中，输入 code favorites.py 并编写代码，如下所示：

# Prints all favorites in CSV using csv.reader

import csv

# Open CSV file
with open("favorites.csv", "r") as file:

    # Create reader
    reader = csv.reader(file)

    # Skip header row
    next(reader)

    # Iterate over CSV file, printing each favorite
    for row in reader:
        print(row[1])

请注意，csv 库已导入。此外，我们创建了一个 reader 来保存 csv.reader(file) 的结果。 csv.reader 函数从文件中读取每一行，在我们的代码中，我们将结果存储在 reader 中。因此，print(row[1]) 将打印 favorites.csv 文件中的语言。

您可以按如下方式改进代码：

# Stores favorite in a variable

import csv

# Open CSV file
with open("favorites.csv", "r") as file:

    # Create reader
    reader = csv.reader(file)

    # Skip header row
    next(reader)

    # Iterate over CSV file, printing each favorite
    for row in reader:
        favorite = row[1]
        print(favorite)

请注意，favorite 被存储然后打印。另外，请注意，我们使用 next 函数跳到阅读器的下一行。

  • 上述方法的缺点之一是，我们假设 row[1] 永远都是“最喜欢的语言”这一列。但是，如果列的顺序被调整了，会发生什么？

我们可以解决这个潜在的问题。 Python 还允许您按列表的键进行索引。修改您的代码如下：

# Prints all favorites in CSV using csv.DictReader

import csv

# Open CSV file
with open("favorites.csv", "r") as file:

    # Create DictReader
    reader = csv.DictReader(file)

    # Iterate over CSV file, printing each favorite
    for row in reader:
        favorite = row["language"]
        print(favorite)

请注意，此示例直接在打印语句中使用 language 键。 favorite 索引到 row["language"] 的 reader 字典中。

这可以进一步简化为：

# Prints all favorites in CSV using csv.DictReader

import csv

# Open CSV file
with open("favorites.csv", "r") as file:

    # Create DictReader
    reader = csv.DictReader(file)

    # Iterate over CSV file, printing each favorite
    for row in reader:
        print(row["language"])

要统计 csv 文件中表达的最喜欢语言的数量，我们可以执行以下操作：

# Counts favorites using variables

import csv

# Open CSV file
with open("favorites.csv", "r") as file:

    # Create DictReader
    reader = csv.DictReader(file)

    # Counts
    scratch, c, python = 0, 0, 0

    # Iterate over CSV file, counting favorites
    for row in reader:
        favorite = row["language"]
        if favorite == "Scratch":
            scratch += 1
        elif favorite == "C":
            c += 1
        elif favorite == "Python":
            python += 1

# Print counts
print(f"Scratch: {scratch}")
print(f"C: {c}")
print(f"Python: {python}")

请注意，每种语言均使用 if 语句进行计数。此外，请注意这些 if 语句中的双等号 ==。

Python 允许我们用字典来统计每种语言的 counts。考虑对代码做如下改进：

# Counts favorites using dictionary

import csv

# Open CSV file
with open("favorites.csv", "r") as file:

    # Create DictReader
    reader = csv.DictReader(file)

    # Counts
    counts = {}

    # Iterate over CSV file, counting favorites
    for row in reader:
        favorite = row["language"]
        if favorite in counts:
            counts[favorite] += 1
        else:
            counts[favorite] = 1

# Print counts
for favorite in counts:
    print(f"{favorite}: {counts[favorite]}")

请注意，当 counts 中具有键 favorite 的值已经存在时，该值会递增。如果不存在，我们定义 counts[favorite] 并将其设置为 1。此外，格式化字符串已得到改进，以呈现 counts[favorite]。

Python 还允许对 counts 进行排序。按如下方式改进您的代码：

# Sorts favorites by key

import csv

# Open CSV file
with open("favorites.csv", "r") as file:

    # Create DictReader
    reader = csv.DictReader(file)

    # Counts
    counts = {}

    # Iterate over CSV file, counting favorites
    for row in reader:
        favorite = row["language"]
        if favorite in counts:
            counts[favorite] += 1
        else:
            counts[favorite] = 1

# Print counts
for favorite in sorted(counts):
    print(f"{favorite}: {counts[favorite]}")

请注意代码底部的 sorted(counts)。

如果你查看Python文档中sorted函数的参数，你会发现它有很多内置参数。您可以利用其中一些内置参数，如下所示：

# Sorts favorites by value using .get

import csv

# Open CSV file
with open("favorites.csv", "r") as file:

    # Create DictReader
    reader = csv.DictReader(file)

    # Counts
    counts = {}

    # Iterate over CSV file, counting favorites
    for row in reader:
        favorite = row["language"]
        if favorite in counts:
            counts[favorite] += 1
        else:
            counts[favorite] = 1

# Print counts
for favorite in sorted(counts, key=counts.get, reverse=True):
    print(f"{favorite}: {counts[favorite]}")

请注意传递给 sorted 的参数。 key 参数允许您告诉 Python 您希望用于对项目进行排序的方法。在本例中，counts.get 用于按值排序。 reverse=True 告诉 sorted 从最大到最小排序。

Python 有许多可以在代码中使用的库。其中一个是 collections，我们可以从中导入 Counter。Counter 让你能够统计每种语言出现的次数，而不必像之前那样写一连串 if 语句。可以这样实现：

# Sorts favorites by value using .get

import csv

from collections import Counter

# Open CSV file
with open("favorites.csv", "r") as file:

    # Create DictReader
    reader = csv.DictReader(file)

    # Counts
    counts = Counter()

    # Iterate over CSV file, counting favorites
    for row in reader:
        favorite = row["language"]
        counts[favorite] += 1

# Print counts
for favorite, count in counts.most_common():
    print(f"{favorite}: {count}")

请注意 counts = Counter() 如何启用从 collections 导入的 Counter 类。

关系数据库

  • Google、X 和 Meta 都使用关系数据库来大规模存储信息。

  • 关系数据库将数据存储在称为“表”的结构中的行和列中。

SQL 允许四种类型的命令：

  Create
  Read
  Update
  Delete

  • 这四种操作通常合称为 CRUD。

  • 我们可以使用 SQL 语法 CREATE TABLE table (column type, ...); 创建一个数据库。但是你在哪里运行这个命令呢？

sqlite3 是 SQL 数据库的一种类型，具有本课程所需的核心功能。

  • 我们可以通过在终端输入 sqlite3 favorites.db 来创建 SQL 数据库。出现提示后，我们将同意通过按 y 创建 favorites.db。

  • 您会注意到不同的提示，因为我们现在使用名为 sqlite 的程序。

  • 我们可以通过输入 .mode csv 将 sqlite 置于 csv 模式。然后，我们可以通过键入 .import favorites.csv favorites 从 csv 文件导入数据。看起来似乎什么都没有发生！

  • 我们可以输入 .schema 来查看数据库的结构。

  • 您可以使用语法 SELECT columns FROM table 从表中读取项目。

  • 例如，您可以键入 SELECT * FROM favorites;，这将打印 favorites 中的每一行。

  • 您可以使用命令 SELECT language FROM favorites; 获取数据的子集。

SQL支持多种访问数据的命令，包括：

  AVG
  COUNT
  DISTINCT
  LOWER
  MAX
  MIN
  UPPER
  • 例如，您可以键入 SELECT COUNT(*) FROM favorites;。此外，您可以键入 SELECT DISTINCT language FROM favorites; 来获取数据库中各种语言的列表。您甚至可以输入 SELECT COUNT(DISTINCT language) FROM favorites; 来获取这些数量。

SQL 提供了我们可以在查询中使用的附加命令：

  WHERE       -- adding a Boolean expression to filter our data
  LIKE        -- filtering responses more loosely
  ORDER BY    -- ordering responses
  LIMIT       -- limiting the number of responses
  GROUP BY    -- grouping responses together

请注意，我们使用 -- 在 SQL 中编写注释。

选择

  • 例如，我们可以执行 SELECT COUNT(*) FROM favorites WHERE language = 'C';。这会给出计数结果。

  • 此外，我们可以输入 SELECT COUNT(*) FROM favorites WHERE language = 'C' AND problem = 'Hello, World';。请注意如何利用 AND 来缩小我们的结果范围。

  • 同样，我们可以执行 SELECT language, COUNT(*) FROM favorites GROUP BY language;。这将提供一个显示语言和计数的临时表。

  • 我们可以通过输入 SELECT language, COUNT(*) FROM favorites GROUP BY language ORDER BY COUNT(*); 来改进这一点。这将按 count 对结果表进行排序。

  • 同样，我们可以执行 SELECT COUNT(*) FROM favorites WHERE language = 'C' AND (problem = 'Hello, World' OR problem = 'Hello, It''s Me');。请注意，有两个 '' 标记，以便允许以不会混淆 SQL 的方式使用单引号。

  • 此外，我们可以执行 SELECT COUNT(*) FROM favorites WHERE language = 'C' AND problem LIKE 'Hello, %'; 来查找以 Hello, 开头（包括空格）的任何问题。

  • 我们还可以通过执行 SELECT language, COUNT(*) FROM favorites GROUP BY language; 对每种语言的值进行分组。

  • 我们可以按如下方式对输出进行排序：SELECT language, COUNT(*) FROM favorites GROUP BY language ORDER BY COUNT(*) DESC;。

  • 我们甚至可以创建别名，就像查询中的变量一样：SELECT language, COUNT(*) AS n FROM favorites GROUP BY language ORDER BY n DESC;。

  • 最后，我们可以将输出限制为 1 个或多个值：SELECT language, COUNT(*) AS n FROM favorites GROUP BY language ORDER BY n DESC LIMIT 1;。

插入

  • 我们还可以使用 INSERT INTO table (column...) VALUES(value, ...); 这种形式向 SQL 数据库中 INSERT 数据。

  • 我们可以执行 INSERT INTO favorites (language, problem) VALUES ('SQL', 'Fiftyville');。

  • 您可以通过执行 SELECT * FROM favorites; 来验证此收藏夹的添加。

删除

DELETE 允许您删除部分数据。例如，您可以 DELETE FROM favorites WHERE Timestamp IS NULL;。这将删除 Timestamp 为 NULL 的任何记录。

更新

  • 我们还可以利用 UPDATE 命令来更新您的数据。

  • 例如，您可以执行 UPDATE favorites SET language = 'SQL', problem = 'Fiftyville';。这会覆盖此前所有把 C 和 Scratch 作为最喜欢编程语言的记录。

  • 请注意，这些查询具有巨大的力量。因此，在现实环境中，您应该考虑谁有权执行某些命令以及您是否有可用的备份！

IMDb

  • 可以想象，我们可能想创建一个数据库来分类各种电视节目。我们可以创建一个包含 title、star、star、star、star 等列的电子表格。这种方法的一个问题是会浪费大量空间。有些节目可能只有一位主演，另一些节目则可能有几十位。

  • 我们可以将数据库拆分成多个表。可以有一个 shows 表、一个 stars 表和一个 people 表。在 people 表中，每个人都可以有一个唯一的 id。在 shows 表中，每个节目也可以有一个唯一的 id。在名为 stars 的第三张表中，我们可以通过 show_id 和 person_id 将节目与人员关联起来。虽然这是一种改进，但仍然不是理想的数据库设计。

IMDb 提供人物、节目、作家、明星、流派和评级的数据库。这些表中的每一个都相互关联，如下所示：

  • 下载 shows.db 后，您可以在终端窗口中执行 sqlite3 shows.db。

让我们聚焦于数据库中名为 shows 和 ratings 的两个表之间的关系。这两个表之间的关系可以用下图来说明：

  • 为了说明这些表之间的关系，我们可以执行以下命令：SELECT * FROM ratings LIMIT 10;。检查输出，我们可以执行 SELECT * FROM shows LIMIT 10;。

  • 检查 shows 和 ratings 后，我们可以看到它们具有一对一的关系：一个节目有一个评级。

  • 为了理解数据库结构，执行 .schema 时，你不仅会看到每个表，还会看到每个表中的字段。

  • 更具体地说，您可以执行 .schema shows 来了解 shows 内部的字段。您还可以执行 .schema ratings 来查看 ratings 内部的字段。

  • 正如您所看到的，show_id 存在于所有表中。在 shows 表中，它简称为 id。表之间共有的字段称为键。主键用于标识表中的唯一记录。外键用于通过指向另一个表中的主键来建立表之间的关系。您可以在 ratings 的架构中看到，show_id 是引用 shows 中 id 的外键。

  • 如上所述，通过将数据存储在关系数据库中，可以更有效地存储数据。

在 sqlite 中，我们有五种数据类型，包括：

  BLOB       -- binary large objects that are groups of ones and zeros
  INTEGER    -- an integer
  NUMERIC    -- for numbers that are formatted specially like dates
  REAL       -- like a float
  TEXT       -- for strings and the like

此外，可以设置列来添加特殊约束：

  NOT NULL
  UNIQUE

  • 我们可以进一步利用这些数据来理解这些关系。执行 SELECT * FROM ratings;。评分记录有很多！

  • 我们可以通过执行 SELECT show_id FROM ratings WHERE rating >= 6.0 LIMIT 10; 进一步限制该数据。从这个查询中，您可以看到有 10 个节目呈现。然而，我们不知道每个show_id代表什么节目。

  • 您可以通过执行 SELECT * FROM shows WHERE id = 626124; 来了解这些显示的内容

我们可以通过执行以下命令进一步提高查询效率：

SELECT title
FROM shows
WHERE id IN (
    SELECT show_id
    FROM ratings
    WHERE rating >= 6.0
    LIMIT 10
)

请注意，此查询将两个查询嵌套在一起。内部查询由外部查询使用。

JOINs

  • 我们正在从 shows 和 ratings 中提取数据。请注意 shows 和 ratings 都有一个共同点 id。

  • 我们怎样才能临时合并表格呢？可以使用 JOIN 命令将表连接在一起。

执行以下命令：

SELECT * FROM shows
  JOIN ratings on shows.id = ratings.show_id
  WHERE rating >= 6.0
  LIMIT 10;

请注意，这会产生比我们之前看到的更宽的表。

前面的查询已经说明了这些键之间的“一对一”关系，让我们检查一些“一对多”关系。关注genres表，执行以下命令：

SELECT * FROM genres
LIMIT 10;

请注意这如何让我们了解原始数据。您可能会注意到，一场演出具有三个价值。这是一对多的关系。

  • 我们可以通过输入 .schema genres 来了解有关 genres 表的更多信息。

执行以下命令可以了解数据库中各种喜剧的更多信息：

SELECT title FROM shows
WHERE id IN (
  SELECT show_id FROM genres
  WHERE genre = 'Comedy'
  LIMIT 10
);

请注意这如何生成喜剧列表，包括 Catweazle。

要了解有关 Catweazle 的更多信息，请通过连接来连接各个表：

SELECT * FROM shows
JOIN genres
ON shows.id = genres.show_id
WHERE id = 63881;

请注意，这会产生一个临时表。有一个重复的表很好。

  • 与一对一和一对多关系相反，可能存在“多对多”关系。

我们可以通过执行以下命令来了解有关“办公室”节目以及该节目中的演员的更多信息：

SELECT name FROM people WHERE id IN
    (SELECT person_id FROM stars WHERE show_id =
        (SELECT id FROM shows WHERE title = 'The Office' AND year = 2005));

请注意，这会生成一个表，其中通过嵌套查询包含各种星星的名称。

我们找到史蒂夫·卡瑞尔主演的所有节目：

SELECT title FROM shows WHERE id IN
    (SELECT show_id FROM stars WHERE person_id =
        (SELECT id FROM people WHERE name = 'Steve Carell'));

这会产生史蒂夫·卡瑞尔主演的节目标题列表。

这也可以这样表达：

SELECT title FROM shows, stars, people
WHERE shows.id = stars.show_id
AND people.id = stars.person_id
AND name = 'Steve Carell';
  • 通配符 % 运算符可用于查找姓名以 Steve C 开头的所有人员，可以使用语法 SELECT * FROM people WHERE name LIKE 'Steve C%';。

索引

  • 虽然关系数据库比使用 CSV 文件更快、更稳健，但可以使用索引在表中优化数据。

  • 索引可以用来加速我们的查询。

  • 我们可以通过在 sqlite3 中执行 .timer on 来跟踪查询的速度。

  • 要了解索引如何加速查询，请运行以下命令： SELECT * FROM shows WHERE title = 'The Office'; 请注意查询执行后显示的时间。

  • 然后，我们可以使用语法 CREATE INDEX title_index ON shows (title); 创建索引。这告诉 sqlite3 创建索引并执行与此列 title 相关的一些特殊的底层优化。

这将创建一个称为 B Tree 的数据结构，这是一种看起来类似于二叉树的数据结构。然而，与二叉树不同的是，可以有两个以上的子节点。

此外，我们还可以创建索引，如下所示：

CREATE INDEX name_index ON people (name);
CREATE INDEX person_index ON stars (person_id);

运行查询，您会发现查询运行得更快了！

SELECT title FROM shows WHERE id IN
    (SELECT show_id FROM stars WHERE person_id =
        (SELECT id FROM people WHERE name = 'Steve Carell'));
  • 不幸的是，对所有列建立索引会导致使用更多的存储空间。因此，需要权衡提高速度。

在 Python 中使用 SQL

为了帮助在本课程中使用 SQL，可以在代码中按如下方式使用 CS50 库：

from cs50 import SQL

  • 与之前使用 CS50 库类似，该库将协助您完成在 Python 代码中使用 SQL 的复杂步骤。

  • 您可以在文档中阅读有关 CS50 库的 SQL 功能的更多信息。

  • 利用我们对 SQL 的新知识，我们现在可以同时利用 Python。

修改 favorites.py 的代码如下：

# Searches database popularity of a problem

from cs50 import SQL

# Open database
db = SQL("sqlite:///favorites.db")

# Prompt user for favorite
favorite = input("Favorite: ")

# Search for title
rows = db.execute("SELECT COUNT(*) AS n FROM favorites WHERE language = ?", favorite)

# Get first (and only) row
row = rows[0]

# Print popularity
print(row["n"])

请注意，db = SQL("sqlite:///favorites.db") 提供了 Python 数据库文件的位置。然后，以 rows 开头的行利用 db.execute 执行 SQL 命令。事实上，该命令将引号内的语法传递给 db.execute 函数。我们可以使用此语法发出任何 SQL 命令。此外，请注意 rows 作为字典列表返回。在本例中，只有一个结果（一行）作为字典返回到行列表。

竞赛条件

  • 使用 SQL 有时会导致一些问题。

  • 您可以想象这样的情况：多个用户可以访问同一数据库并同时执行命令。

  • 这可能会导致代码被其他人的操作中断的故障。这可能会导致数据丢失。

  • 内置 SQL 功能（例如 BEGIN TRANSACTION、COMMIT 和 ROLLBACK）有助于避免其中一些竞争条件问题。

SQL 注入攻击

  • 现在，仍然考虑上面的代码，您可能想知道上面的 ? 问号是做什么的。 SQL 的实际应用中可能出现的问题之一是所谓的“注入攻击”。注入攻击是指恶意行为者可以输入恶意 SQL 代码。

例如，考虑如下登录屏幕：

如果我们自己的代码没有适当的保护，坏人可能会运行恶意代码。考虑以下几点：

rows = db.execute("SELECT COUNT(*) FROM users WHERE username = ? AND password = ?", username, password)

请注意，由于 ? 已就位，因此可以在查询盲目接受 favorite 之前对其运行验证。

  • 您永远不想在上述查询中使用格式化字符串或盲目信任用户的输入。

  • 利用 CS50 库，该库将“清理”并删除任何潜在的恶意字符。

总结

在本课程中，您学习了与 Python 相关的更多语法。此外，您还学习了如何将这些知识与平面文件和关系数据库形式的数据集成。最后，您了解了SQL。具体来说，我们讨论了……

  • 平面文件数据库

  • 关系数据库

  • SQL 命令，例如 SELECT、CREATE、INSERT、DELETE 和 UPDATE。

  • 主键和外键

JOINs

  • 索引

  • 在 Python 中使用 SQL

  • 比赛条件

  • SQL 注入攻击

下次见！