第77天：Python 操作 SQLite / 开普饭

1 简介

SQLite 是一种轻型嵌入式关系型数据库，它包含在一个相对小的 C 库中。SQLite 占用资源低，处理速度快，它支持 Windows、Linux、Unix 等多种主流操作系统，支持 Python、Java、C# 等多种语言，目前的版本已经发展到了 SQLite3。

SQLite 是一个进程内的库，它实现了自给自足、无服务器、无需配置、支持事务。Python 可以通过 sqlite3 模块与 SQLite3 集成，Python 2.5.x 以上版本内置了 sqlite3 模块，因此，我们在 Python 中可以直接使用 SQLite。

2 SQLite 数据类型

在介绍使用之前，我们先了解下 SQLite 数据类型。SQLite 采用动态数据类型，也就是说数据的类型取决于数据本身。

2.1 存储类型

存储类型就是数据保存成文件后的表现形式，存储类型有 5 种，如下所示：

类型	描述
NULL	空值
INTEGER	有符号的整数类型
REAL	浮点数类型
TEXT	字符串，使用数据库编码（UTF-8、UTF-16BE 或 UTF-16LE）存储
BLOB	二进制表示

2.2 亲和类型

亲和类型简单来说就是数据表列的数据对应存储类型的倾向性，当数据插入时，字段的数据将会优先采用亲缘类型作为值的存储方式，同样有 5 种，如下所示：

类型	描述
NONE	不做任何转换，直接以该数据所属的数据类型进行存储
NUMERIC	该列可以包含使用所有五个存储类型的值
INTEGER	类似于 NUMERIC，区别是在执行 CAST 表达式时
TEXT	该列使用存储类型 NULL、TEXT 或 BLOB 存储数据
REAL	类似于 NUMERIC，区别是它会强制把整数值转换为浮点类型

2.3 声明类型

声明类型也就是我们写 SQL 时字段定义的类型，我们看一下常用的声明类型与亲和类型的对应关系。

声明类型	亲和类型
INT/INTEGER/TINYINT/BIGINT	INTEGER
VARCHAR/TEXT/CLOB	TEXT
BLOB	NONE
DOUBLE/FLOAT	REAL
DECIMAL/BOOLEAN/DATE/DATETIME	NUMERIC

3 SQLite 常用函数

SQLite 提供了一些内置函数，也就是我们可以直接使用的函数，下面来看一下。

函数	描述
COUNT	计算一个数据库表中的行数
MAX	某列的最大值
MIN	某列的最小值
AVG	某列的平均值
SUM	某列的和
RANDOM	返回一个介于 -9223372036854775808 和 +9223372036854775807 之间的随机整数
ABS	返回数值参数的绝对值
UPPER	把字符串转换为大写字母
LOWER	把字符串转换为小写字母
LENGTH	返回字符串的长度
sqlite_version	返回 SQLite 库的版本

使用示例如下所示：

SELECT COUNT(*) FROM table;SELECT MAX/MIN/AVG/SUM/ABS/UPPER/LOWER/LENGTH(col) FROM table;SELECT random() AS Random;SELECT sqlite_version() AS 'SQLite Version';

4 基本使用

4.1 连接数据库

# 导入模块import sqlite3# 连接数据库conn = sqlite3.connect('test.db')

如果数据库不存在，则会自动被创建。

4.2 游标

连接数据库后，我们需要使用游标进行相应 SQL 操作，游标创建如下所示：

# 创建游标cs = conn.cursor()

4.3 创建表

我们在 test.db 库中新建一张表 student，如下所示：

# 创建表cs.execute('''CREATE TABLE student (id varchar(20) PRIMARY KEY, name varchar(20));''')# 关闭 Cursorcs.close()# 提交当前事务conn.commit()# 关闭连接conn.close()

表创建好后，我们可以使用图形化工具 SQLiteStudio 直观的查看一下，官方下载地址：https://sqlitestudio.pl/index.rvt?act=download，打开如图所示：

以 Windows 系统为例，选择免安装版 portable 进行下载，下载好后解压文件，直接运行文件夹中的 SQLiteStudio.exe 即可，打开后如图所示：

我们先点击上方工具栏上的 Database 按钮，然后选 Add a database，如图所示：

接着点击文件下方右侧的绿色加号按钮或文件夹按钮，选择数据库文件，比如我们选择 test.db 文件，选好了后点击测试连接，如果能够正常连接，我们就点击 OK 按钮添加数据库。

添加完数据库后，再点击 SQLiteStudio 主界面上方工具栏中 View 按钮，接着选数据库，结果如图所示：

接着双击 test 库，结果如图所示：

此时已经看到 student 表了，双击 student 表，我们还可以查看表的更多信息，如图所示：

4.4 新增

我们向 student 表中插入两条数据，如下所示：

cs.execute("INSERT INTO student (id, name) VALUES ('1', 'Jhon')")cs.execute("INSERT INTO student (id, name) VALUES ('2', 'Alan')")cs.execute("INSERT INTO student (id, name) VALUES ('3', 'Bob')")cs.close()conn.commit()conn.close()

执行完后，到 SQLiteStudio 中看一下，如图所示：

我们看到数据已经进来了。

4.5 查询

前面我们是通过 SQLiteStudio 查看数据的，现在我们通过 SQL 查看一下，如下所示：

# 导入模块import sqlite3# 连接数据库conn = sqlite3.connect('test.db')# 创建游标cs = conn.cursor()# 查询数据cs.execute("SELECT id, name FROM student")# 获取查询结果集中的下一行print('fetchone-->', cs.fetchone())# 获取查询结果集中的下一行组print('fetchmany-->', cs.fetchmany())# 获取查询结果集中所有（剩余）的行print('fetchall-->', cs.fetchall())cs.close()conn.close()

输出结果：

fetchone--> ('1', 'Jhon')fetchmany--> [('2', 'Alan')]fetchall--> [('3', 'Bob')]

4.6 更新

我们修改 id 为 1 这条数据的 name 值，如下所示：

# 导入模块import sqlite3# 连接数据库conn = sqlite3.connect('test.db')# 创建游标cs = conn.cursor()# 修改数据cs.execute("SELECT id, name FROM student WHERE id = '1'")print('修改前-->', cs.fetchall())cs.execute("UPDATE student set name = 'Nicolas' WHERE id = '1'")cs.execute("SELECT id, name FROM student WHERE id = '1'")print('修改后-->', cs.fetchall())conn.commit()cs.close()conn.close()

输出结果：

修改前--> [('1', 'Jhon')]修改后--> [('1', 'Nicolas')]

4.7 删除

我们删除 id 为 1 这条数据，如下所示：

# 导入模块import sqlite3# 连接数据库conn = sqlite3.connect('test.db')# 创建游标cs = conn.cursor()# 删除cs.execute("SELECT id, name FROM student")print('删除前-->', cs.fetchall())cs.execute("DELETE FROM student WHERE id = '1'")cs.execute("SELECT id, name FROM student")print('删除后-->', cs.fetchall())conn.commit()cs.close()conn.close()

输出结果：

删除前--> [('2', 'Alan'), ('1', 'Jhon')]删除后--> [('2', 'Alan')]

这里我们只介绍了增删改查基本操作，SQLite 的 SQL 操作与我们常用的 MySQL 等数据库基本类似。