跳转至

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 语句时,锁定某个变量,在执行完成前不允许该变量发生变化。

评论