欢迎大家来到IT世界,在知识的湖畔探索吧!

• Welcome!
• Flat-File Database
• Relational Databases
• IMDb
• JOINs
• Indexes
• Using SQL in Python
• Race Conditions
• SQL Injection Attacks
• Summing Up

Welcome!

• 在前几周，我们向你介绍了 Python，这是一种高级编程语言，它利用了我们在 C 语言中学到的相同构建块。
• 本周，我们将继续更多与 Python 相关的语法。
• 此外，我们将把这些知识与数据相结合。
• 最后，我们将讨论 SQL 或 结构化查询语言。
• 总的来说，本课程的目标之一是学习一般编程——而不仅仅是如何用本课程中描述的语言编程。

Flat-File Database

• 正如你之前可能看到的那样，数据通常可以用列和表的模式来描述。
• 像在Microsoft Excel和Google Sheets中创建的电子表格可以输出到csv或者 逗号分隔值 文件。
• 如果你查看一个csv文件，你会注意到该文件是平面的，因为我们所有的数据都存储在由文本文件表示的单个表中。我们将这种形式的数据称为 平面文件数据库。
• Python附带对csv文件的本机支持。
• 在终端窗口中，键入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])

欢迎大家来到IT世界,在知识的湖畔探索吧!

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

欢迎大家来到IT世界,在知识的湖畔探索吧!  # 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函数跳到阅读器的下一行。
• 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:
          print(row["language"])

• 请注意，此示例直接使用 print 语句中的language键。
• 要计算csv文件中表达的最喜欢的语言的数量，我们可以执行以下操作：

欢迎大家来到IT世界,在知识的湖畔探索吧!  # 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语句进行计数。
• 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]}")

• 请注意，当键favorite中的counts值已存在时，该值将递增。如果它不存在，我们定义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

  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

  def get_value(language):
      return counts[language]

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

• 请注意，将创建一个get_value函数，并且函数本身作为参数传递给sorted函数。key参数允许你告诉 Python 你希望用于对项目进行排序的方法。
• Python具有我们迄今为止尚未看到的独特功能：它允许使用 匿名 或lambda函数。当你不想费心创建完全不同的函数时，可以使用这些函数。请注意以下修改：

  # Sorts favorites by value using lambda function

  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=lambda language: counts[language], reverse=True):
      print(f"{favorite}: {counts[favorite]}")

• 请注意，get_value函数已被删除。相反，lambda language: counts[language]在一行中执行我们以前的两行函数所做的工作。
• 我们可以更改我们正在检查的列，专注于我们最喜欢的问题：

  # Favorite problem instead of favorite language

  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["problem"]
          if favorite in counts:
              counts[favorite] += 1
          else:
              counts[favorite] = 1

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

• 请注意，problem替换了language。
• 如果我们想允许用户直接在终端中提供输入怎么办？我们可以修改我们的代码，利用我们之前关于用户输入的知识：

  # Favorite problem instead of favorite language

  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["problem"]
          if favorite in counts:
              counts[favorite] += 1
          else:
              counts[favorite] = 1

  # Print count
  favorite = input("Favorite: ")
  if favorite in counts:
      print(f"{favorite}: {counts[favorite]}")

• 请注意，与我们在 C 语言中的经验相比，我们的代码是多么紧凑。

Relational Databases

• Google，Twitter和Meta都使用关系数据库来大规模存储其信息。
• 关系数据库将数据存储在称为表的结构中的行和列中。
• SQL 允许四种类型的命令：

  Create
  Read
  Update
  Delete

• 这四种操作被亲切地称为CRUD。
• 我们可以通过键入sqlite3 favorites.db在终端上创建一个SQL数据库。出现提示后，我们将同意创建favorites.db要按y。
• 你会注意到一个不同的提示，因为我们现在在一个名为sqlite3的程序中。
• 我们可以通过输入.mode csv将sqlite3进入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(language) FROM favorites; 。此外，你可以键入SELECT DISTINCT(language) FROM favorites;以获取数据库中各个语言的列表。你甚至可以键入SELECT COUNT(DISTINCT(language)) FROM favorites;以获取这些计数。

  # Searches database popularity of a problem

  import csv

  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(*) FROM favorites WHERE problem LIKE ?", "%" + favorite + "%")

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

  # Print popularity
  print(row["COUNT(*)"])

• 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 = ‘Mario’;。请注意如何利用AND来缩小我们的结果范围。
• 同样，我们可以执行SELECT language, COUNT(*) FROM favorites GROUP BY language;。这将提供一个临时表，显示语言和计数。
• 我们可以通过键入SELECT language, COUNT(*) FROM favorites GROUP BY language ORDER BY COUNT(*);来改善这一点。这将按count对表进行排序。
• 我们也可以使用INSERT INTO table (column…) VALUES(value, …);往SQL数据库INSERT数据 .
• 我们可以执行INSERT INTO favorites (language, problem) VALUES (‘SQL’, ‘Fiftyville’);。
• 我们还可以利用UPDATE命令来更新你的数据。
• 例如，你可以执行UPDATE favorites SET language = ‘C++’ WHERE language = ‘C’;。这将导致覆盖所有以前的语句，其中 C 是最喜欢的编程语言。
• 请注意，这些查询具有巨大的功能。因此，在实际设置中，应考虑谁有权执行某些命令。
• DELETE允许你删除部分数据。例如，你可以DELETE FROM favorites WHERE problem = ‘Tideman’;。

IMDb

• IMDb 提供了一个包含人物、节目、作家、开始、流派和评级的数据库。其中每个表彼此相关，如下所示：

• 下载 show.db 后，你可以在终端窗口中执行sqlite3 shows.db。
• 执行时，.schema你不仅会找到每个表，还会找到每个字段中的各个字段。
• 如上图所示，shows有一个id字段。该genres表有一个show_id字段，其中包含它和shows表之间的通用数据。
• 正如你在上图中也看到的那样，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 people LIMIT 10;。检查输出，我们可以执行SELECT * FROM shows LIMIT 10;。此外，我们可以执行SELECT * FROM stars LIMIT 10;。show_id是此最终查询中的外键，因为show_id对应于shows中的唯一id字段。 person_id对应于people列中的唯一字段id。
• 我们可以进一步利用这些数据来理解这些关系。执行SELECT * FROM genres;。有很多流派！
• 我们可以通过执行SELECT * FROM genres WHERE genre = ‘Comedy’ LIMIT 10;来进一步限制这些数据。从此查询中，你可以看到呈现了 10 个节目。
• 你可以通过执行SELECT * FROM shows WHERE id = 626124;来发现这些显示的内容。
• 我们可以通过执行以下命令进一步提高查询效率

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

• 请注意，此查询将两个查询嵌套在一起。内部查询由外部查询使用。
• 我们可以通过执行进一步完善

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

• 如果你想找到史蒂夫·卡瑞尔主演的所有节目怎么办？你可以执行SELECT * FROM people WHERE name = ‘Steve Carell’;。你会找到他的id。你可以利用这一id来定位他出现的许多shows。但是，一一尝试会很乏味。我们接下来如何进行查询以使其更加简化？请考虑以下事项：

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

• 请注意，这个冗长的查询将产生一个最终结果，该结果对于发现问题的答案很有用。

JOINs

• 请考虑以下两个表：

• 我们如何暂时合并表格？可以使用JOIN命令将表连接在一起。
• 执行以下命令：

  SELECT * FROM shows    JOIN ratings on shows.id = ratings.show_id    WHERE title = 'The Office';

• 现在你可以看到所有被称为 The Office 节目。
• 你可以通过执行以下JOIN命令来类似地应用于上面的史蒂夫·卡瑞尔查询：

  SELECT title FROM people    JOIN stars ON people.id = stars.person_id    JOIN shows ON stars.show_id = shows.id    WHERE name = `Steve Carell`;

• 请注意每个JOIN命令如何告诉我们哪些列与哪些其他列对齐。
• 这可以类似地实现如下：

  SELECT title FROM people, stars, shows  WHERE people.id = stars.person_id  AND stars.show_id = shows.id  AND name = 'Steve Carell';

• 请注意，这将获得相同的结果。
• 通配符%运算符可用于查找所有名字以Steve C开头的人都可以使用该语法SELECT * FROM people WHERE name LIKE ‘Steve C%’;。

Indexes

• 虽然关系数据库能够比使用CSV文件更快、更健壮，但可以使用 索引 在表中优化数据。
• 索引可用于加快我们的查询速度。
• 我们可以通过在sqlite3中执行.timer on来跟踪查询速度。
• 若要了解索引如何加快查询速度，请运行以下命令：SELECT * FROM shows WHERE title = ‘The Office’; 请注意查询执行后显示的时间。
• 然后，我们可以创建一个语法CREATE INDEX title_index on shows (title);。这告诉sqlite3创建一个索引并执行与title列相关的一些特殊的底层优化。
• 这将创建一个名为 B树 的数据结构，该数据结构看起来类似于二叉树。但是，与二叉树不同，可以有两个以上的子节点。

• 运行查询SELECT * FROM shows WHERE title = ‘The Office’;，你会注意到查询运行得更快！
• 遗憾的是，为所有列编制索引会导致占用更多存储空间。因此，需要权衡提高速度。

Using SQL in Python

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

from cs50 import SQL

• 与以前使用 CS50 库类似，此库将帮助完成在 Python 代码中使用 SQL 的复杂步骤。
• 你可以在文档中阅读有关 CS50 库的 SQL 功能的更多信息。
• 回想一下我们上次在favorites.py中离开的地方。你的代码应如下所示：

  # Favorite problem instead of favorite language  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["problem"]          if favorite in counts:              counts[favorite] += 1          else:              counts[favorite] = 1  # Print count  favorite = input("Favorite: ")  if favorite in counts:      print(f"{favorite}: {counts[favorite]}")

• 按如下方式修改代码：

  # Searches database popularity of a problem  import csv  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(*) FROM favorites WHERE problem LIKE ?", "%" + favorite + "%")  # Get first (and only) row  row = rows[0]  # Print popularity  print(row["COUNT(*)"])         

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

Race Conditions

• 使用 SQL 有时会导致一些问题。
• 你可以想象这样一种情况：多个用户可能同时访问同一数据库并执行命令。
• 这可能会导致代码被其他人的操作中断的故障。这可能会导致数据丢失。
• 内置的 SQL 功能（如BEGIN TRANSACTION 、COMMIT和ROLLBACK）有助于避免其中一些争用条件问题。

SQL Injection Attacks

• 现在，仍在考虑上面的代码，你可能想知道上面的?有什么作用。在SQL的实际应用中可能出现的问题之一是所谓的 注入攻击。注入攻击是恶意参与者可以输入恶意 SQL 代码的地方。
• 例如，考虑登录屏幕，如下所示：

• 如果没有我们自己的代码中的适当保护，不良行为者可能会运行恶意代码。请考虑以下事项：

   rows = db.execute("SELECT COUNT(*) FROM favorites WHERE problem LIKE ?", "%" + favorite + "%")

• 请注意，由于?已就位，因此可以在查询盲目接受favorite验证之前运行验证。
• 你永远不希望在上述查询中使用格式化字符串或盲目信任用户的输入。
• 利用 CS50 库，该库将清理并删除任何潜在的恶意字符。

Summing Up

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

• 平面文件数据库
• 关系数据库
• SQL
• JOINs
• 索引
• 在 Python 中使用 SQL
• 竞争条件
• SQL 注入攻击

下次见！

原文出处：https://cs50.harvard.edu/college/2022/fall/notes/7/