Python数据处理
¶

08. SQL数据库
¶

主讲人：丁平尖

上一讲：HTML、XML和JSON¶

  • 每种都提供了一种不同的存储数据的方式
  • JSON的关键动机：自我描述
  • 但我们发现JSON很快就会变得相当笨重……

一个更复杂的JSON对象的例子¶

  • 如果我有数百种不同类型的蛋糕或甜甜圈怎么办？
    • JSON对象的嵌套性使它们变得有点复杂。
    • 通常，JSON适用于传递（少量的）数据，
    • 但对于存储和操作大量、复杂的数据集合，有更好的工具，即数据库。
  • 注意：还有安全和软件工程方面的原因，更倾向于使用数据库而不是JSON来存储数据。

为什么要使用数据库？¶

  • 数据库软件处理数据的能力
    • 索引：允许数据库快速定位到数据行，减少查询时间
    • 日志记录：数据库的日志记录用于跟踪所有的事务和更改，以便进行恢复和审计
    • 归档自动处理：将不再频繁访问的数据移动到更低成本的存储中，以释放主数据库的空间。
  • 允许快速、并发（即多个用户）访问数据
  • 通过Web访问
    • 数据库可以运行在服务器上
  • 同样，JSON/XML/HTML等适用于传递数据，数据库适用于存储

数据库（DB）¶

  • 示例：人口普查信息、产品库存、图书馆目录
  • 关系数据库
    • https://en.wikipedia.org/wiki/Relational_database
    • 之所以这样命名，是因为它们捕获了实体之间的关系,自20世纪70年代以来一直存在，至今仍是主导模型
  • 图数据库
    • https://en.wikipedia.org/wiki/Graph_database
    • 适用于复杂的关系数据模型，例如社交网络、电子商务、物流网络等
  • 本课程范围之外：其他模型（例如，面向对象）
    • https://en.wikipedia.org/wiki/Database_model。

关系数据库的优缺点¶

  • 优点：
    • 自然适用于绝大多数应用
    • 有许多管理查询工具
  • 缺点：
    • 不适合某些数据（例如，网络、非结构化文本）
    • 一般采用固定的表结构（即，很难添加列）

ACID：原子性、一致性、隔离性、持久性¶

  • 原子性：
    • 确保一个事务要么完全执行，要么完全不执行。如果事务中的任何部分失败，该事务将被回滚，数据库将恢复到事务开始之前的状态。
  • 一致性：
    • 一致性确保在事务开始之前和结束之后，数据库都必须处于一致的状态。即如果某个事务完成，所有数据库应满足所有规则（如约束、外键等）。
  • 隔离性：
    • 隔离性确保并发事务的执行不会相互干扰。也就是说，一个事务的执行不应受其他事务的影响。所有并发事务都应表现得像是在独立执行。
  • 持久性：
    • 持久性确保一旦事务被提交，它所做的更改将被永久保存到数据库中，即使系统崩溃或出现故障，这些更改也不会丢失。
  • 注意：一些RDBMS为了更快的性能，牺牲了上述一个或多个特性

关系数据库的基本单位：记录¶

  • 数据库中的每个实体都有一个相应的记录
    • 记录的特征存储在字段中
    • 具有相同“类型”字段的记录收集到表中
    • 每条记录是一行，每个字段是一列
  • 字段可以包含不同的数据类型
    • 整数，浮点数，字符串，布尔型，日期和时间
    • 一些数据库软件允许类型有所差别。

SQL（最初由IBM称为SEQUEL）¶

  • 结构化查询语言（Structured English QUEry Language）
  • 用于与关系数据库交互的语言
  • 不是唯一的方法，但绝对是最受欢迎的
  • 各个平台之间有轻微的变化（“SQL方言”）
  • 好的教程：https://www.w3schools.com/sql/sql_intro.asp

关系数据库管理系统（RDBMS）¶

  • 促进与数据库交互的程序称为RDBMS
  • 公共/开源选项：
    • MySQL、PostgreSQL、SQLite
  • 专有：
    • IBM Db2、Oracle、SAP、SQL Server（Microsoft）
  • 我们将展示如何使用SQLite，因为它内置于Python中。

SQLite数据库¶

  • 管理工具
    • VS Code: SQLTools
      • https://marketplace.visualstudio.com/items?itemName=mtxr.sqltools
    • PyCharm: Database Tools and SQL for WebStorm
      • https://plugins.jetbrains.com/plugin/10925-database-tools-and-sql-for-webstorm
  • 管理工具安装相应数据库驱动
  • 新建数据库，并建立连接

Python sqlite3包实现了SQLlite¶

  • Connection对象代表数据库
    • Connection对象可以用来创建Cursor对象 游标促进与数据库的交互
  • conn = sqlite3.connect(‘example.db’)
    • 建立与给定数据库文件的连接（如果需要，则创建它）
    • 并返回一个Connection对象
  • cursor = conn.cursor()
    • 为与数据库交互创建并返回Cursor对象
  • cursor.execute ([SQL命令])
    • 运行给定的命令；游标现在包含查询结果
  • conn.commit()
    • 将对数据库的更改提交到数据库
In [1]:
import sqlite3
conn = sqlite3.connect('example.db')
cursor = conn.cursor()

添加/删除表：创建新表或删除现有表¶

  • 创建新表：CREATE TABLE
    • CREATE TABLE table_name [col1 datatype, col2 datatype, …]
  • 删除表：DROP TABLE
    • DROP TABLE table_name;
    • 删除表时要格外小心！
In [14]:
cursor.execute('''CREATE TABLE IF NOT EXISTS student
             (id, name, major, score);''')
conn.commit()
In [13]:
cursor.execute("DROP TABLE IF EXISTS student;")
conn.commit()

添加/删除记录：在表中插入新行或删除现有行¶

  • 向表中插入一行：INSERT INTO
    • INSERT INTO table_name [col1, col2, col3, …] VALUES value1, value2, value3, …
    • 注意：如果为所有列添加值，只需要指定值。
  • 从表中删除行：DELETE
    • DELETE FROM table_name WHERE condition
In [15]:
import pandas as pd
def print_student_details():
    sql_select = "SELECT * FROM student;"
    cursor.execute(sql_select)
    # 在执行数据库查询后，游标对象将访问查询返回的数据。
    # fetchall() 方法会返回查询结果的所有行，通常是以列表的形式
    result = cursor.fetchall()
    result = pd.DataFrame(result, columns=['id', 'name', 'major', 'score'])
    return(result)
In [16]:
sql = "INSERT INTO student (id, name, major, score) VALUES (5, 'PJ', 'CS', 85);"
cursor.execute(sql)
conn.commit()
print_student_details()
Out[16]:
id name major score
0 5 PJ CS 85
In [17]:
sql_delete = "DELETE FROM student WHERE name='PJ';"
cursor.execute(sql_delete)
conn.commit()
print_student_details()
Out[17]:
id name major score
  • 插入多行
    • cursor.executemany(sql, parameters): 对于参数中的每个项，重复执行参数化的SQL 语句 sql。
In [19]:
students = [
    (0, 'PJ', 'CS', 85),
    (1, 'John', 'Math', 90),
    (2, 'Jane', 'Math', 85),
    (3, 'Bob', 'English', 95),
    (4, 'Alice', 'History', 80),
    (5, 'Tom', 'Art', None),
    (6, 'Mary', 'Music', 98)
]
# Insert data into table
cursor.executemany('INSERT INTO student VALUES (?,?,?,?)', students)
conn.commit()
print_student_details()
Out[19]:
id name major score
0 0 PJ CS 85.0
1 1 John Math 90.0
2 2 Jane Math 85.0
3 3 Bob English 95.0
4 4 Alice History 80.0
5 5 Tom Art NaN
6 6 Mary Music 98.0

修改行¶

  • 修改表中的一行：UPDATE
    • UPDATE table_name SET col1=value1,col2=value2, WHERE condition
In [20]:
cursor.execute("UPDATE student SET score = 90 WHERE name = 'PJ'")
conn.commit()
print_student_details()
Out[20]:
id name major score
0 0 PJ CS 90.0
1 1 John Math 90.0
2 2 Jane Math 85.0
3 3 Bob English 95.0
4 4 Alice History 80.0
5 5 Tom Art NaN
6 6 Mary Music 98.0

检索记录：在表中查找所有行¶

  • SQL SELECT语句的基本形式：
    • SELECT [列名] FROM [表]
  • 示例：
    • 检索所有学生名称，出生年份：SELECT name, score FROM steudent;
  • cursor.fetchall()：
    • 返回查询结果的所有（剩余）行，作为一个列表。如果没有可用的行，则返回一个空列表。
In [21]:
cursor.execute("SELECT * FROM student;")
result = cursor.fetchall()
pd.DataFrame(result, columns=['id', 'name', 'major', 'score'])
Out[21]:
id name major score
0 0 PJ CS 90.0
1 1 John Math 90.0
2 2 Jane Math 85.0
3 3 Bob English 95.0
4 4 Alice History 80.0
5 5 Tom Art NaN
6 6 Mary Music 98.0
In [22]:
cursor.execute("SELECT name, score FROM student;")
result = cursor.fetchall()
pd.DataFrame(result, columns=['name', 'score'])
Out[22]:
name score
0 PJ 90.0
1 John 90.0
2 Jane 85.0
3 Bob 95.0
4 Alice 80.0
5 Tom NaN
6 Mary 98.0

过滤记录：SQL WHERE语句¶

  • 通过WHERE语句对返回的记录进一步过滤：
    • SELECT [列名] FROM [表] WHERE [过滤器]
  • 示例：
    • 检索分数大于等于90的学生信息：
      • SELECT * FROM student WHERE score >= 90
In [23]:
cursor.execute("SELECT * FROM student WHERE score > 90;")
result = cursor.fetchall()
pd.DataFrame(result, columns=['id', 'name', 'major', 'score'])
Out[23]:
id name major score
0 3 Bob English 95
1 6 Mary Music 98

关于WHERE语句的更多信息¶

  • WHERE关键字支持所有自然比较操作
  • （数值）操作符/关键字
    • 等于 = 不等于 <>
    • 小于 < 小于等于 <=
    • 大于 > 大于等于 >=
    • 在范围内 BETWEEN ... AND ...
In [24]:
cursor.execute("SELECT * FROM student WHERE score <> 80;")
result = cursor.fetchall()
pd.DataFrame(result, columns=['id', 'name', 'major', 'score'])
Out[24]:
id name major score
0 0 PJ CS 90
1 1 John Math 90
2 2 Jane Math 85
3 3 Bob English 95
4 6 Mary Music 98
In [25]:
cursor.execute("SELECT * FROM student WHERE score BETWEEN 80 AND 95;")
result = cursor.fetchall()
pd.DataFrame(result, columns=['id', 'name', 'major', 'score'])
Out[25]:
id name major score
0 0 PJ CS 90
1 1 John Math 90
2 2 Jane Math 85
3 3 Bob English 95
4 4 Alice History 80

更多关于WHERE语句的信息¶

  • WHERE关键字还允许（有限的）正则表达式支持和集合成员
    • 集合成员运算符IN和NOT IN
    • 正则表达式运算符LIKE
      • 使用LIKE关键字进行正则匹配，通配符_和%
        • _匹配单个字符
        • %匹配任意字符（包括0个字符）
In [26]:
cursor.execute("SELECT * FROM student WHERE major in ('English', 'Math')")
result = cursor.fetchall()
pd.DataFrame(result, columns=['id', 'name', 'age','major'])
Out[26]:
id name age major
0 1 John Math 90
1 2 Jane Math 85
2 3 Bob English 95
In [27]:
cursor.execute("SELECT * FROM student WHERE name LIKE 'J%';")
result = cursor.fetchall()
pd.DataFrame(result, columns=['id', 'name', 'age', 'gender'])
Out[27]:
id name age gender
0 1 John Math 90
1 2 Jane Math 85
In [28]:
cursor.execute("SELECT * FROM student WHERE name LIKE 'J_hn';")
result = cursor.fetchall()
pd.DataFrame(result, columns=['id', 'name', 'age', 'gender'])
Out[28]:
id name age gender
0 1 John Math 90

NULL匹配空字符串，¶

  • 匹配字段NULL的情况。
  • 注意，如果字段包含，比如说，‘ ’，那么NULL将不匹配那一行！
In [29]:
cursor.execute("SELECT * FROM student WHERE score is NULL;")
result = cursor.fetchall()
pd.DataFrame(result, columns=['id', 'name', 'major', 'score'])
Out[29]:
id name major score
0 5 Tom Art None

排序记录：SQL ORDER BY语句¶

  • 对SELECT语句返回的记录进行排序：
    • SELECT [列] FROM [表] ORDER BY [列] [ASC|DESC]
  • 示例
    • 按分数检索ID和姓名：
    • SELECT id, name FROM student ORDER BY score DESC
  • 注意：大多数实现默认按升序排序，但最好总是指定！
In [30]:
cursor.execute("SELECT id, name, score FROM student ORDER BY score DESC;")
result = cursor.fetchall()
pd.DataFrame(result, columns=['id', 'name', 'score'])
Out[30]:
id name score
0 6 Mary 98.0
1 3 Bob 95.0
2 0 PJ 90.0
3 1 John 90.0
4 2 Jane 85.0
5 4 Alice 80.0
6 5 Tom NaN

更多过滤：DISTINCT¶

  • 从返回的结果集中删除重复项：
    • SELECT DISTINCT [列] FROM [表]
  • 示例：检索所有专业：
    • SELECT DISTINCT major FROM student
In [31]:
cursor.execute("SELECT DISTINCT major FROM student;")
result = cursor.fetchall()
pd.DataFrame(result, columns=['Major'])
Out[31]:
Major
0 CS
1 Math
2 English
3 History
4 Art
5 Music

聚合结果：GROUP BY¶

  • 我想知道每个专业学生的平均成绩是多少？
In [32]:
cursor.execute("SELECT major, AVG(score) FROM student GROUP BY major;")
result = cursor.fetchall()
pd.DataFrame(result, columns=['Major', 'Mean Score'])
Out[32]:
Major Mean Score
0 Art NaN
1 CS 90.0
2 English 95.0
3 History 80.0
4 Math 87.5
5 Music 98.0

更多关于GROUP BY的信息¶

  • GROUP BY支持除AVG之外的其他操作：
  • COUNT, SUM, MIN, MAX 称为聚合函数
  • 可以使用HAVING子语句在GROUP BY之后过滤结果
In [33]:
cursor.execute("SELECT major, AVG(score) AS avg_score FROM student \
                Group by major HAVING COUNT(*) > 1")
result = cursor.fetchall()
pd.DataFrame(result, columns=['major', 'AVG(score)'])
Out[33]:
major AVG(score)
0 Math 87.5
  • 注意：HAVING关键字和WHERE关键字的区别在于HAVING在应用GROUP BY之后操作。
  • AS关键字只是让我们给聚合字段一个更好的名字。

合并表：JOIN¶

  • (INNER) JOIN：返回两个表中都有匹配值的记录
    • SELECT * FROM student INNER JOIN st_info ON student.name=st_info.name;
In [34]:
cursor.execute("SELECT * FROM st_info;")
result = cursor.fetchall()
pd.DataFrame(result, columns=['name', 'age', 'gender'])
Out[34]:
name age gender
0 Alice 20 Female
1 Bob 22 Male
2 Charlie 21 Male
3 David 23 Male
4 Eve 22 Female
5 Frank 21 Male
In [35]:
cursor.execute("SELECT * FROM student INNER JOIN st_info ON student.name=st_info.name;")
result = cursor.fetchall()
pd.DataFrame(result, columns=['id', 'name', 'major', 'score', 'name', 'age', 'gender'])
Out[35]:
id name major score name age gender
0 3 Bob English 95 Bob 22 Male
1 4 Alice History 80 Alice 20 Female

其他合并表的方式：OUTER JOIN¶

  • LEFT (OUTER) JOIN：返回左表的所有记录和右表中匹配的记录
  • RIGHT (OUTER) JOIN：返回右表的所有记录和左表中匹配的记录
  • FULL (OUTER) JOIN：当左表或右表中有匹配时返回所有记录
  • https://www.w3schools.com/sql/sql_join.asp
In [36]:
cursor.execute("SELECT * FROM student LEFT OUTER JOIN st_info ON student.name=st_info.name;")
result = cursor.fetchall()
pd.DataFrame(result, columns=['id', 'name', 'major', 'score', 'name', 'age', 'gender'])
Out[36]:
id name major score name age gender
0 0 PJ CS 90.0 None NaN None
1 1 John Math 90.0 None NaN None
2 2 Jane Math 85.0 None NaN None
3 3 Bob English 95.0 Bob 22.0 Male
4 4 Alice History 80.0 Alice 20.0 Female
5 5 Tom Art NaN None NaN None
6 6 Mary Music 98.0 None NaN None
In [37]:
cursor.execute("SELECT * FROM student FULL OUTER JOIN st_info ON student.name=st_info.name;")
result = cursor.fetchall()
pd.DataFrame(result, columns=['id', 'name', 'major', 'score', 'name', 'age', 'gender'])
Out[37]:
id name major score name age gender
0 0.0 PJ CS 90.0 None NaN None
1 1.0 John Math 90.0 None NaN None
2 2.0 Jane Math 85.0 None NaN None
3 3.0 Bob English 95.0 Bob 22.0 Male
4 4.0 Alice History 80.0 Alice 20.0 Female
5 5.0 Tom Art NaN None NaN None
6 6.0 Mary Music 98.0 None NaN None
7 NaN None None NaN Charlie 21.0 Male
8 NaN None None NaN David 23.0 Male
9 NaN None None NaN Eve 22.0 Female
10 NaN None None NaN Frank 21.0 Male
In [38]:
cursor.close()
conn.close()

SQLite和PostgreSQL对比¶

  • SQLite
    • SQLite 是一个自包含的、无服务器的、零配置的嵌入式数据库引擎，
    • 可以直接集成到应用程序中，不需要单独的数据库服务器。
  • PostgreSQL
    • PostgreSQL 是基于客户端-服务器架构的数据库系统，
    • 这意味着数据库服务器和客户端应用程序可以运行在不同的机器上，支持多用户同时访问。
  • 相较于 SQLite，PostgreSQL 能够处理更多的并发连接和事务，因此在要求高并发性能的应用场景中更为常见。
  • PostgreSQL
    • https://www.postgresql.org/download/

psycopg2¶

  • psycopg2 是一个用于连接和操作 PostgreSQL 数据库的 Python 库。
  • 提供了与数据库进行交互的高效和灵活的方法
  • 安装
    • pip install psycopg2
    • conda install anaconda::psycopg2
In [39]:
# sign in
import psycopg2
db_params = {
    'dbname': 'data_science',
    'user': 'postgres',
    'password': '12345678',
    'host': 'localhost',  # 数据库主机地址
    'port': '5432'        # PostgreSQL默认端口
}
conn = psycopg2.connect(**db_params)
cursor = conn.cursor()
In [47]:
cursor.execute("DROP TABLE IF EXISTS scores;")
conn.commit()
In [48]:
cursor.execute("CREATE TABLE IF NOT EXISTS scores (id INT, name VARCHAR(50), score INT)")
conn.commit()
In [49]:
cursor.execute("INSERT INTO scores (id, name, score) VALUES (1, 'John', 89);")
conn.commit()
In [50]:
import pandas as pd
cursor.execute("SELECT * FROM scores;")
results = cursor.fetchall()
pd.DataFrame(results, columns=['id', 'name', 'score'])
Out[50]:
id name score
0 1 John 89
In [51]:
data = [(2, 'PJ', 78),
        (3, 'AJ', 85)]
cursor.executemany("INSERT INTO scores (id, name, score) VALUES (%s, %s, %s)", data)
conn.commit()
In [52]:
cursor.execute("SELECT * FROM scores;")
results = cursor.fetchall()
pd.DataFrame(results, columns=['id', 'name', 'score'])
Out[52]:
id name score
0 1 John 89
1 2 PJ 78
2 3 AJ 85
In [53]:
cursor.close()
conn.close()