lecture7

lecture7 SQL

Flat-File Database

• 数据常常可以视作表格，每一行是一个记录，每一列是一个属性

• 电子表格例如Excel，Google Spreadsheet可以导出为csv格式文件。csv文件是纯文本文件，每一行是一个记录，每一列用逗号分隔，可以看作是最简单的数据库

• csv文件被称为flat-file database，因为它没有任何结构，只是一堆数据，所有的数据被存在一个表中

• python 原生支持csv文件的读写，可以用csv模块读写csv文件

• # 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模块，然后用open函数打开csv文件，然后用csv.reader函数创建一个reader对象，csv.reader函数从文件中读取每一行。

• 但是这样的方式有一个问题，在数据中我们会发现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)
  

• • 使用next函数跳过第一行
• • 还有一种改进

• • # 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.DictReader函数，它会将每一行转换为一个字典，字典的键是列标题，字典的值是每一行的值

• 当对字典的值排排序时，若不想按值排序时单独创立函数，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]}")
    

• • Notice that the get_value function has been removed. Instead, lambda language: counts[language] does in one line what our previous two-line function did.

Realational Database

关系型数据库是一种流行的存储大量数据的方式
Relational databases借助表的数据结构以行和列存储数据

SQL支持四种操作，被简记为CRUD

• Create：创建表
• Read：读取表中的数据
• Update：更新表中的数据
• Delete：删除表中的数据

创建一个新的数据库 sqlite3 favorites.db

可以使用.mode csv命令将sqlite3的模式设置为csv模式，这样就可以使用csv文件来创建表了 .import favorites.csv favorites 第一个参数是导入文件名字，第二个则为创建表的名称
.schema可以查看数据库的结构，但是并不是一种标准的SQL语句，使用使用其他的一些商用型数据库是不可用

SELECT columns FROM table从表中读取一个元素

SQL支持一些基本函数

• COUNT：计算表中的行数

• SUM：计算表中某一列的和

• AVG：计算表中某一列的平均值

• MIN：计算表中某一列的最小值

  \(\cdots\)

使用方法为SELECT function(column) FROM table

SQL的额外功能函数

• WHERE：过滤表中的数据
• GROUP BY：将表中的数据分组
• ORDER BY：将表中的数
• LIMIT：限制表中的数据
• LIKE：模糊匹配表中的数据
• $\cdots $
• 例如SELECT language，COUNT(*) FROM favorites GROUP BY language

IMDB

IMDB是一个电影数据库，可以从这里下载数据集

执行.schema不仅可以看到每一张表，还可以看到每一张表的结构

Indexes

索引是一种数据结构，可以加快数据库的查询速度，但是会降低数据库的写入速度

我们可以跟踪查询的时间，使用.timer on命令，然后使用.timer off命令关闭

之后我们可以创建表的索引，使用CREATE INDEX index_name ON table(column)命令，这将会创建一个B-tree索引

Using SQL in Python

# 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")创建一个数据库对象，这个对象有一个execute方法，可以执行SQL语句
• 在execute方法中，我们可以使用问号来代替变量，然后在execute方法的第二个参数中传入变量的值
• 注意execute使用?作为占位符

Race Conditions

当两个进程同时访问数据库时，可能会发生race conditions，例如两个进程同时读取数据库，然后同时写入数据库，这样就会导致数据的不一致性

SQL锁可以解决这个问题，当一个进程正在读取数据库时，其他进程无法读取数据库，当一个进程正在写入数据库时，其他进程无法读取和写入数据库

SQL Injection Attacks

在python中执行SQL语句时，我们可以使用问号来代替变量，然后在execute方法的第二个参数中传入变量的值，这样可以防止SQL注入攻击。

但是为什么呢？

假设我们有一个表，其中有两列，一列是用户名，一列是密码，我们想要查询某个用户的密码，我们可以使用SELECT password FROM users WHERE username = 'username'，但是如果我们的username是' OR 1=1 --，那么这个SQL语句就会变成SELECT password FROM users WHERE username = '' OR 1=1 --'，这样就会返回所有的密码，因为OR 1=1永远为真，--是注释，所以后面的语句都会被注释掉

但是使用?占位符就可以防止这种攻击，因为' OR 1=1 --会被当作一个字符串，而不是SQL语句的一部分

homeWork

Favorite

SELECT * FROM shows ORDER BY title;数据表杂乱无章使用以上命令排序

UPDATE shows SET title="How I Met Your Mother" WHERE title LIKE "How i met your mother"; UPDATE修改表中数据使用LIKE模糊匹配提高效率

prophecy

要求建三张表将原来只有一张表的数据库分开使用SQL in python

三步总共：

• 从已给出的csv文件将student，house，relationships三张表的数据提取出来放到分别放到三个字典中
• 在schema.sql加入重新建立三张表的命令
• 在students.py中加入将三个字典中的数据导入到三张表中的命令

Lab songs

• In 1.sql, write a SQL query to list the names of all songs in the database.
  Your query should output a table with a single column for the name of each song.
  SELECT name FROM songs;

• In 2.sql, write a SQL query to list the names of all songs in increasing order of tempo.
  Your query should output a table with a single column for the name of each song.
  SELECT name from songs ORDER BY tempo;

• In 3.sql, write a SQL query to list the names of the top 5 longest songs, in descending order of length.
  Your query should output a table with a single column for the name of each song.
  SELECT name from songs ORDER BY duration_ms DESC LIMIT 5;

• • DESC降序排列,LIMIT限制输出数量

• In 4.sql, write a SQL query that lists the names of any songs that have danceability, energy, and valence greater than 0.75.
  Your query should output a table with a single column for the name of each song.
  SELECT name FROM songs WHERE (danceability>0.75 and energy>0.75 and valence>0.75);

• In 5.sql, write a SQL query that returns the average energy of all the songs.
  Your query should output a table with a single column and a single row containing the average energy.
  SELECT AVG(energy) FROM songs;

• In 6.sql, write a SQL query that lists the names of songs that are by Post Malone.
  Your query should output a table with a single column for the name of each song.
  You should not make any assumptions about what Post Malone’s artist_id is.
  SELECT name FROM songs WHERE artist_id=(SELECT id FROM artists WHERE name='Post Malone');

• • 在一个表中查询可以使用子查询，子查询的结果作为外层查询的条件就像这里的artist_id，这里的artist_id是一个外键，它指向artists表中的id，所以我们可以使用artists表中的id来查询songs表中的数据

• In 7.sql, write a SQL query that returns the average energy of songs that are by Drake.
  Your query should output a table with a single column and a single row containing the average energy.
  You should not make any assumptions about what Drake’s artist_id is.
  SELECT AVG(energy) FROM songs WHERE artist_id=(SELECT id FROM artists WHERE name='Drake');

• In 8.sql, write a SQL query that lists the names of the songs that feature other artists.
  Songs that feature other artists will include “feat.” in the name of the song.
  Your query should output a table with a single column for the name of each song.
  SELECT name FROM songs WHERE name LIKE '%feat.%';

• • 注意占位符即可