SQLite初体验

Linux

SQLite初体验

为什么需要数据库

电商或者高铁订票这类大规模应用，需要处理大量数据和并发请求，支持复杂查询，确保数据一致性和完整性，文件系统是处理不了这种需求的。即使对于一些小型的个人应用，相比于把所需要的数据直接放到一些项目目录里的模式，数据库也能帮助更好地组织和检索数据，避免数据混乱。在整理半导体声子数据的时候，我学习到了一些数据库的知识。

image-20240930114758936

数据库分类

数据库可以分为多种类型，比如关系型数据库，非关系型数据库，时序数据库.. ，每一种类型下又有很多种数据库实现。平时接触比较多的就是关系型数据库，这类数据库储存的数据表跟Excel差不多，就是那种可以排列成方格型的。常见的关系型数据库实现有MySQL、PostgreSQL、Oracle Database、SQLite等等。大体上，这些数据库可以分为两大类，一类是客户端-服务器模式，一类是嵌入式模式。

客户端-服务器数据库由两部分组成：客户端通过网络连接到服务端发送操作请求，服务端实际处理数据库操作。MySQL就是这种数据库，大体上运行的方式像下面所介绍的：

以我们平时使用的微信为例，微信其实是由客户端程序（可以简称为客户端）和服务器程序（可以简称为服务器）这两部分组成的。微信客户端可能有很多种形式，比如移动端的App、桌面端的软件或者是网页版的微信。微信的每个客户端都有一个唯一的用户名，也就是你的微信号。另一方面，腾讯公司在他们的机房里运行着一个服务器程序，我们平时在微信上的各种操作，其实就是使用微信客户端与微信服务器打交道。

比如狗哥使用微信给猫爷发一条微信消息的过程大致如下所示。

  • 狗哥发出的微信消息被客户端进行包装，添加诸如发送者与接收者等信息，然后从客户端发送到微信服务器。
  • 微信服务器从收到的消息中获取发送者和接收者信息，并据此将消息发送到猫爷的微信客户端。然后，猫爷的微信客户端就显示狗哥发给他的消息。

MySQL的运行过程与之类似，它的服务器程序直接和我们存储的数据打交道，多个客户端程序可以连接到这个服务器程序。客户端向服务器发送增删改查等请求，然后服务器程序根据这些请求，对存储的数据进行相应的处理。与微信一样，MySQL的每一个客户端都需要使用用户名和密码才能登陆服务器，并且只有在登录之后才能向服务器发送某些请求来操作数据。MySQL的日常使用场景是下面这样的：

  • 启动MySQL服务器程序。

  • 启动MySQL客户端程序，并连接到服务器程序。

  • 在客户端程序中输入一些语句，并将其作为请求发送给服务器程序。服务器程序收到这些请求后，会根据请求的内容来操作具体的数据并向客户端返回操作结果。

    ​ --- MySQL是怎样使用的，小孩子4919，人民邮电出版社

这类数据库支持多用户并发访问，适合大型应用。而且数据存储和管理集中化，可以更好地进行备份和恢复。客户端-服务器数据库功能强大，但也因此比较“重”。而对于嵌入式数据库，数据库引擎直接集成在应用程序中，数据存储在应用程序本地。它不需要单独的服务器进程，所有的操作都在本地完成。而且数据存储在单一文件中，便于管理和移动，比较适合但用户和小规模应用，非常轻量级易于部署。和服务器模式的数据库相比，就像 Vscode 和 Visual Studio 的感觉一样。

SQLite 就是一个嵌入式关系型数据库，整个数据库存储在一个单一的.db文件中，每个应用程序都直接与 SQLite 数据库文件进行交互，不需要额外的服务器。客户端应用程序通过 SQLite API 直接执行 SQL 查询和数据操作，不需要发送请求到远程服务器。虽然对并发和写操作等的支持比不上重型强大的数据库，但是对一些小型应用来说非常轻快，安装配置上也比客户端-服务器模式的数据库简单了很多。安装如 MySQL 或 PostgreSQL 这种数据库需要下载服务器软件、安装服务、配置网络访问和权限、设置用户账户连接参数.. 等等，而安装 SQLite 只需将 SQLite 的库文件包含在项目中即可，甚至对于解释型语言比如 Python 来说直接使用 sqlite3 模块就可以了，它已经自带了 SQLite 引擎。

思维差异：长格式与宽格式

在接触数据库之前，熟悉和常见的表格形式，就是 Excel 这种。这类表格通常是宽格式的（wide format），而数据库中的数据则主要是长格式（long format）的。这种数据格式差异在刚接触数据库的时候需要进行一些思维调整。对于宽格式来说，某个属性的不同数值排成了不同的列，每个记录占一行。这种数据构成了一张二维表，在展示数据时比较直观。

1
2
3
4
Name       Math    Physics  Chemistry
--------------------------------------
Alice       85        88        92
Bob         90        94        89

而在长格式中，数据的每个记录占一行，多个记录通过“键值对”的形式记录在不同的行中，数据库中的表都是这种形式。

1
2
3
4
5
6
7
8
Name       Subject       Score
------------------------------
Alice       Math           85
Alice       Physics        88
Alice       Chemistry      92
Bob         Math           90
Bob         Physics        94
Bob         Chemistry      89

长格式和宽格式相比，看起来没有那么直观，而且行数多了许多。但好处是可以以一种统一、规范的表结构，来描述各式各样的数据。例如，如果需要增加新的学科（例如 Biology ），在长格式中只需添加一行，而不需要修改表的结构。甚至再加一列新的属性，长格式也不需要对原有的表本身做出什么调整，而宽格式原有的表则几乎不能用了。这种差异会对数据库表的设计产生影响，习惯了宽格式思维的话，会倾向于将不同类别的数据存储在不同的表中。比如有一个包含多种类型的产品数据，在宽格式思维下，可能会把每种产品类型的数据存储在不同的表中，类似这样：product_type_a 表；product_type_b 表；product_type_c 表... 每个表都有一组相似的字段（比如名称、价格、数量等）但是这样会导致数据分散在多个表中，增加了维护复杂性。当需要获取所有产品类型的汇总数据时，必须跨多个表进行查询，进也降低了代码的可维护性。而且如果以后有新的产品类型，可能需要创建新的表，调整现有的查询逻辑。

而在长格式思维下，有类型的产品数据可以存储在同一个表中，区别只在于某一字段（如 product_type）表示产品的类别。这样处理，所有数据都集中在一个表中，无需为不同类别的数据创建多个表。查询和管理变得更加一致和简单。而且如果有新的产品类型，只需添加一个新的 product_type 值，无需修改表结构或创建新的表。在查询、聚合和分析时，任意列的操作过程都是是一致和统一的，使得存在一种标准的规范来处理数据库。而如果是宽格式的话，有行、列等等的区分，通用性较差。

这里容易产生一些困惑，将大量数据存储到一张表中，会不会造成性能上的一些问题呢？实际上哪怕是二维数组，本质上也是一维地存储起来的。数据库也是这样，它查询的性能依赖于索引，而不是将数据分散到不同的表中。将所有数据存储在同一个表中，并对关键字段（如 product_type 或 id）建立索引，能够极大地提升查询性能。索引可以帮助数据库快速查找到匹配的记录，而无需扫描整个表，而将数据分散到多个表并不会自动提升性能。相反，这种设计可能会带来额外的复杂性。在进行跨表查询时，数据库可能会做额外的工作来进行 JOIN 操作。尤其当不同表的结构相似或一致时，使用多个表反而不如一个表加索引来得高效。其次，数千万行的数据对现代的数据库来说也只算是小规模的数据，许多数据库系统（如 MySQL、PostgreSQL 等）能够轻松处理几亿甚至几十亿行的数据，只要设计得当（比如合理的索引、分区等）。

透视表

当然，有时候我们也希望把从数据库中查询出来的长格式的数据表转换为宽格式，创建一个二维的数据表。这个需求可以用 Pandas 的 pivot 透视表方法来实现，其他语言应该也有类似的东西。

1
DataFrame.pivot(index=None, columns=None, values=None)

index：用于指定行的唯一标识（即行索引），通常是某个特定的字段。

columns：用于指定列的类别，通常是某个特定的字段。它的值将成为列的列标。

values：指定要填入表格中的值字段，这些值将分布在指定的 index 和 columns 组合中。

假设我们有如下数据，表示不同学生在不同学科上的成绩：

1
2
3
4
5
6
7
8
9
10
import pandas as pd

data = {
    'Name': ['Alice', 'Bob', 'Alice', 'Bob', 'Alice', 'Bob'],
    'Subject': ['Math', 'Math', 'Physics', 'Physics', 'Chemistry', 'Chemistry'],
    'Score': [85, 90, 88, 94, 92, 89]
}

df = pd.DataFrame(data)
print(df)

输出的 DataFrame 是一个“长格式”的表格：

1
2
3
4
5
6
7
    Name   Subject  Score
0  Alice      Math     85
1    Bob      Math     90
2  Alice   Physics     88
3    Bob   Physics     94
4  Alice  Chemistry    92
5    Bob  Chemistry    89

我们可以使用 pivot 方法，将数据按照“姓名”和“学科”来重塑，将“分数”作为值。

1
2
df_pivot = df.pivot(index='Name', columns='Subject', values='Score')
print(df_pivot)

输出的结果是一个“宽格式”的表：

1
2
3
4
5
Subject  Chemistry  Math  Physics
Name                             
Alice      92.0     85.0   88.0
Bob        89.0     90.0   94.0

数据表基础

一个应用可能有多个数据库，不同的模块可以使用单独的数据库。每个数据库都包含多个数据表，用于存储特定类型的数据。一个数据库其实也就是一些数据表的集合。之所以要把这些数据表放到一起，是因为这些数据表之间可能存在一些关联。

数据表由多个行和列组成，每列存储特定类型的数据（如整数、字符串、日期），被称为字段（field）。每一行存储一条具体的数据，被称为记录（record）。每张表有一列作为主键（primary key），主键的值不能够重复，通过主键可以找到表中唯一的一条记录。一般情况下，我们们用整型来作为主键，同时把主键列设置为 AUTO_INCREMENT ，即自动增长。在插入数据记录时，不需要显式指定这一列的值。我们还希望通过某一列把两张表关联起来，比如学生信息表里的某一列是学生的专业，而专业的具体信息则储存成了另一张专业信息表。这时，可以把学生信息表中专业这一列设置为外键（foreign key），并指定外键所对应的其他表格。另外，除主键外，有时我们也希望其他某个列或者列组合种存储的值是唯一的。当数据表插入的新的值和表中原有的值重复时，数据库就会报错。为了达到这种效果，需要对有必要保持唯一性的列或者列组合添加 UNIQUE 约束。此外，我们还可能把几千万到上一条数据放到同一个表中，靠不同的字段值来区分他们。这时候，为了提高检索速度，我们可以在经常用于搜索的列上创建索引（index），类似于书籍的目录，可以让数据库快速地定位到特定的数据行，而无需扫描整个表。

学生ID 学生姓名 专业名称
1 牛 流体力学
2 羊 固体力学
专业ID 专业名称 专业分数
1 流体力学 100
2 固体力学 200

表格范式

在 Excel 中，有时候会用到合并表格的功能，把某几个单元合并为一个大单元，用来处理具有某些行具有相同列参数的情况，如下图所示。这种未分割的表格，一行中有两个以上的数值，被称为非范式表格。关系型数据库中不能使用这种表格来进行数据管理，因此需要进行表格分割，这个过程叫作规范化。

image-20240927122750034

把数据表拆分成如下所示的一栏中只有一个项目的表格，就称这样的表格满足第一范式（first normal form, 1NF）。第一范式仍然不能作为关系型数据库的表格来使用。对于销售明细表，目前是由报表编码和商品编码这两列共同确定后面的内容，但是，可以发现商品名称、单价等仅依赖于商品编码，而不依赖于报表编码。

image-20240927125233425

把数据进一步拆分，使得所有非主键的列必须完全依赖于表的主键，这样的表格就满足了第二范式（second normal form, 2NF）。只有在组合主键的情况下，才有可能违反第二范式，这是因为单一主键的表的非主键列一定由主键完全确定了。因此销售表已经满足了第二范式。

image-20240927125325174

进一步观察上面的销售表可以发现，出口国名称这一列并不是直接依赖于报表编码的，而是间接依赖于出口国编码。实际上，报表编码确定了，出口国编码就确定了，因此出口国名称也就间接确定了。这种通过某一列的值而间接确定其他列的值的情况，称之为传递依赖（transitively dependent）。去除掉间接依赖的部分，使得表格的非主键字段直接完全依赖于主键，而不是通过其他非主键字段简介依赖，称满足这样条件的表格为满足第三范式（third normal form, 3NF）。在关系型数据库中，通常使用满足第三范式的表格。

image-20240927131250756

总结：

第一范式 (1NF)：表中的所有列必须是原子的，每个列只能存储单一值，不能包含重复或嵌套的数据。

第二范式 (2NF)：在符合第一范式的基础上，表中所有非主键字段必须完全依赖于整个主键，不能依赖组合键的一部分（无部分依赖）。

第三范式 (3NF)：在符合第二范式的基础上，非主键字段必须直接依赖于主键，不能通过其他非主键字段间接依赖（无传递依赖）。

数据格式：强类型与弱类型

MySQL 和 SQLite ，就像 C++ 和 Python 一样。 MySQL 是一个丰富的强类型系统，数据类型支持比较多，支持像 UNSIGNED 这样的限制。而且数据类型会被严格验证，比如将字符串插入到整数类型列中会产生错误。而 SQLite 的数据类型系统则较为宽松，支持的类型种类比较少。这是因为 SQLite 采用动态类型机制，字段的数据类型更多的是“建议”。即使将列定义为整型，它仍然可以接受其他类型的数据，除非为该列显式添加约束。

MySQL 支持多种数据类型，比如对于字符串类型，就分成了很多类别：

  • CHAR：固定长度字符串。
  • VARCHAR：可变长度字符串。
  • TEXT：大文本字符串，最多 65,535 字节。
  • MEDIUMTEXT：中等大小的文本字符串，最多 16,777,215 字节。
  • LONGTEXT：最大大小的文本字符串，最多 4,294,967,295 字节。
  • BLOB：二进制大对象，存储二进制数据，大小与 TEXT 类似。

SQLite 则仅有5种数据类型：

  1. NULL：表示没有值。
  2. INTEGER：用于存储整数，可以是 1、2、3、4、6 或 8 字节的整数。
  3. REAL：用于存储浮点数，采用 8 字节的 IEEE 754 格式。
  4. TEXT：用于存储文本字符串，可以是任意长度的字符。
  5. BLOB：用于存储二进制数据（如图像、音频等），在存储时不会进行任何转换。

但是 SQLite 也允许声明其他类型，比如使用 VARCHAR 或者CHAR类型来存储字符串，但是最终实际上他们都会用 TEXT 类型来存储，没有任何区别。

SQL

SQL（Structured Query Language，结构化查询语言）就是用于操作关系型数据库的标准化语言。它提供了一组指令，用于与数据库进行交互，执行各种操作，比如查询、插入、更新和删除数据，创建和管理数据库结构等。所有关系型数据库管理系统（如 MySQL、PostgreSQL、Oracle、SQL Server、SQLite）都支持 SQL，但不同系统可能有一些特定的扩展和实现差异。SQL 的具体语法用到现查就行了，查几次就熟悉了。

在数据库中，大体上有两种方式去运行 SQL 语句。最简单的方式，是直接运行 SQL 语句（自动提交模式）。每条 SQL 语句执行后自动提交，立即生效。如果某个 SQL 语句失败，之前执行的语句仍然保持提交状态，不会回滚。

1
2
3
4
-- 每条 SQL 语句执行后，数据库会自动提交
INSERT INTO users (name, age) VALUES ('Alice', 30);
UPDATE users SET age = 31 WHERE name = 'Alice';
DELETE FROM users WHERE name = 'Alice';

另一种方式，是在事务中运行 SQL 语句（手动管理事务）。这种方式是把多个 SQL 语句当作一个整体进行管理。多个 SQL 操作可以被封装在一个事务中，确保这些操作要么全部成功，要么全部回滚，就像 Git 版本管理中的一次 commit 一样。可以通过显式的 BEGIN TRANSACTION（或等效语句）来开启事务。在事务结束时，使用 COMMIT 提交所有操作，或者使用 ROLLBACK 撤销所有操作。

1
2
3
4
5
6
7
8
9
10
11
12
-- 开始事务
BEGIN TRANSACTION;

-- 一组相关的操作
INSERT INTO accounts (name, balance) VALUES ('Alice', 1000);
UPDATE accounts SET balance = balance + 100 WHERE name = 'Bob';

-- 如果所有操作成功，提交事务
COMMIT;

-- 如果有任何操作失败，回滚事务
ROLLBACK;

在 Python 中， SQLAlchemy 是一个方便我们链接和使用关系型数据库的包，它把数据库对象都可以一一映射为 Python 对象，方便我们在 Python 中操作数据库。我们称它实现了 ORM（对象关系映射，Object-Relational Mapping） 。SQLAlchemy 的 ORM 允许我们用 Python 代码操作数据库，而不需要手动编写 SQL 查询。

SQLAlchemy 的工作流程如下所示：

  1. 定义 Python 类：首先，使用 declarative_base() 创建一个基类，所有模型类都将继承这个基类。然后，定义自己的类，类中的属性映射到数据库表中的列。

  2. 建立数据库连接：通过 create_engine() 函数建立与数据库的连接。这个引擎是 SQLAlchemy 与数据库通信的基础。

  3. 创建会话：会话（session）用于管理与数据库的交互。通过 sessionmaker() 创建一个会话类，然后实例化这个类来与数据库进行实际的操作。

  4. 执行查询或操作：在会话中执行查询或进行数据库操作（插入、更新、删除）。SQLAlchemy 会自动将 Python 对象转换为 SQL 语句并执行。

  5. 提交或回滚事务：执行完所有的操作后，调用 session.commit() 提交事务，或者在出错时调用 session.rollback() 回滚事务。

关于4、5，直到调用 session.commit() 之前，所有操作都会被视为属于同一个事务。SQLAlchemy 默认情况下不会在每次操作后自动提交。必须手动调用 session.commit()，在此之前的所有操作都包含在一个隐式事务中。如果不调用 commit() 而直接关闭 session，事务会自动回滚。一旦调用 commit()，这些更改才会被永久保存到数据库中。