跳转至

CS50 Week 7 SQL

关系型数据库的增删查改以及在 Python 中的使用。

我们之前写的程序都是存在内存里面，当程序退出后，变量的值就消失了。SQL 可以让我们储存到硬盘中。

先练习一些 Python 数据处理的技巧：

数据处理

为什么.csv文件比 Excel 的.xlsx文件更好？

  • .csv文件由逗号分隔，格式简单，读写速度快。
  • .xlsx文件需要安装 Microsoft Office。同理，.numbers文件需要苹果系统，在不同用户之间的传输存在障碍。

排序

sorted(list)返回一个升序排列的新list。

例如，下面的代码可以按字母表打印titles：

Python
for title in sorted(titles):
    print(title, titles[title])

按照字典的 value 排序（而不是默认的 key）

使用参数key：

Python
...


def get_value(title):
    return titles[title]


for title in sorted(titles, key=get_value, reverse=True):
    print(title, titles[title])

也可以用匿名函数：

Python
for title in sorted(titles, key=lambda title: titles[title], reverse=True):
    print(title, titles[title])

删除字符串前后的空格

.strip()

将字符串全部转换为大写

.upper()

删除重复值

有 2 种思路：

  1. 新建空列表。如果列表中已经出现了某元素，则不将该元素添加到列表中；如果列表中没有出现过某元素，则将该元素添加到列表中。
Python
import csv

titles = []

with open("favorites.csv", "r") as file:
    reader = csv.DictReader(file)

    for row in reader:
        title = row["title"].strip().upper()
        if not title in titles:
            titles.append(title)

for title in titles:
    print(title)
  1. 利用set()本身不包含空值的特性，即可直接.add()，而不用检查是否有重复项。
Python
import csv

titles = set()

with open("favorites.csv", "r") as file:
    reader = csv.DictReader(file)

    for row in reader:
        title = row["title"].strip().upper()
        titles.add(title)

for title in titles:
    print(title)

计数 Counting

使用字典进行计数，key存放名称，value存放数量。

正则表达式

邮箱的正则表达式：

Text Only
.*@.*\..*

含义：

  • .：任意字符
  • .*：任意字符，出现 0 次及以上
  • @：字符@
  • \.：字符.
Text Only
.+@.+\..+

含义：

  • .+：任意字符，出现 1 次及以上

其他表达式：

  • .?：任意字符，出现 0 次或 1 次
  • ^：输入的开头
  • $：输入的结尾

关系型数据库

四种基本操作：CRUD（增删查改）

  • CREATE（增）
  • READ（查）
  • UPDATE（改）
  • DELETE（删）

展示某列

Text Only
SELECT title FROM favorites;

筛选数据

Text Only
SELECT genres FROM favorites WHERE title = "Game of Thrones";

更新数据

Text Only
UPDATE favorites SET genres = "Action, Adventure, Drama, Fantasy, Thriller, War" WHERE title = "Game of Thrones";

上面的代码会将所有的title = "Game of Thrones"的genres变成"Action, Adventure, Drama, Fantasy, Thriller, War"。

注意，如果不小心忘记加上WHERE条件，则会将所有的genres全部变成"Action, Adventure, Drama, Fantasy, Thriller, War"，这是不可撤销的！定时备份！小心操作！

多张表

SQL可以将一张表映射成多个关系型表。让整个数据库更加规范、易读。

插入数据

Text Only
sqlite> INSERT INTO shows (title) VALUES("Seinfeld");

在 Python 中使用 SQL

需要使用cs50这个库

Python
import csv

from cs50 import SQL

db = SQL("sqlite:///favorites.db")

title = input("Title: ").strip()

rows = db.execute("SELECT COUNT(*) AS counter FROM favorites WHERE title LIKE ?", title)

row = rows[0]

print(row["counter"])

打印运行时间

Text Only
sqlite> .timer on

创建 INDEX 以加速查询过程

有的表需要经常在搜索中用到，可以建立一个 INDEX，加速后续的查询过程。（占用空间，节约时间）

Text Only
sqlite> CREATE INDEX "title_index" ON "shows" ("title");

破坏 SQL 语句

通常用 SQL 语句查询用户的数据库，检查用户名和密码是否匹配，以判断是否登录成功。

但如果用户输入了一些特殊字符，可能会让 SQL 的判断语句被破坏。比如，用户输入的文本包含--，而--在 SQL 中是注释的意思，因此--后面的 SQL 代码就不会被执行，可以破坏登录验证的过程。

同时访问 SQL 的问题

如果两个用户同时用 SQL 语句对数据库进行更新，那么可能会产生冲突。某个变量可能额会少记一次更新。一个可能的解决方案是：在执行一条 SQL 语句时，锁定某个变量，在执行完成前不允许该变量发生变化。

评论