Python 连接 SQLite 数据库

SQLite 是什么？

SQLite 是一种关系型数据库。与其他关系型数据库（如 MySQL、SQL Server 等）不一样，SQLite 不是 “B/S结构” 的数据库，而是一种 “嵌入式” 的数据库。在 SQLite 中，它的数据库就是一个文件。SQLite 将整个数据库，包括定义、表、索引以及数据本身，作为一个单独的文件保存起来。

SQLite

有些小伙伴认为 SQLite 是一个不适合生产环境使用的 “玩具数据库”。事实上，它被大家严重低估了。作为一款非常可靠的数据库，SQLite 经常被集成到各种应用程序中。比如我们最常用的微信、QQ 等，就是使用 SQLite 来保存聊天记录的。

Python 本身就内置了 SQLite 的相关模块（即 sqlite3），所以我们不需要另外安装就可以直接使用。

Python 创建 SQLite 数据库

在 Python 中，如果想要使用 SQLite 创建一个数据库文件，我们需要进行以下 5 步操作。

创建连接（Connection）。
创建游标（Cursor）。
执行 SQL 语句。
关闭游标（Cursor）。
关闭连接（Connection）。

后面介绍的对表的 “增删查改” 这 4 种操作，同样需要执行这 5 步。接下来在当前目录下创建一个名为 data 的文件夹，整个项目结构如下图所示。

Python SQLite 项目结构

示例 1：使用 SQLite 的基本步骤

import sqlite3

# 第 1 步，创建连接
conn = sqlite3.connect(r'data\test.db')
# 第 2 步，创建游标
cursor = conn.cursor()
# 第 3 步，执行 SQL 语句
cursor.execute('''create table product (id int primary key, 
                               name varchar(10), 
                               type varchar(10),
                               price decimal(5, 1))''')
# 第 4 步，关闭游标
cursor.close()
# 第 5 步，关闭连接
conn.close()

运行代码之后，会发现 test.db 这个数据库文件已经创建好了，如下图所示。

Python 创建 SQLite 数据库

分析：

# 第 1 步，创建连接
conn = sqlite3.connect(r'data\test.db')

首先使用 sqlite3 模块的 connect() 函数创建一个 Connection 对象。如果 test.db 文件不存在，那么 Python 就会自动创建该文件；如果 test.db 文件已经存在，那么 Python 就会自动连接上 test.db。

# 第 2 步，创建游标
cursor = conn.cursor()

创建好连接之后，我们再使用 Connection 对象的 cursor() 方法来创建一个 Cursor 对象。Cursor 对象就是通常所说的 “游标”。游标用于逐行处理数据。不管是创建表，还是对表执行增删查改，都必须借助游标才可以操作。

# 第 3 步，执行 SQL 语句
cursor.execute('''create table product (id int primary key, 
                               name varchar(10), 
                               type varchar(10),
                               price decimal(5, 1))''')

获取到 Cursor 对象之后，我们就可以使用 Cursor 对象的 execute() 方法来创建表。上面这段代码表示创建一个名为 product 的表，该表有以下 4 个字段（主键是 id）。

id：表示商品编号，其数据类型是整型（int）。
name：表示商品名称，其数据类型是字符串（varchar），最大长度为 10。
type：表示商品类型，其数据类型是字符串（varchar），最大长度为 10。
price：表示商品售价，其数据类型是浮点型（decimal），最大长度为 5，小数位为 1 位。

# 第 4 步，关闭游标
cursor.close()
# 第 5 步，关闭连接
conn.close()

最后还需要把连接和游标都关闭了。首先关闭游标，然后再关闭连接。再次运行这个例子，如果报出下面的错误，就表示 product 表创建成功了。这是因为在同一个数据库中，不允许创建相同名字的表，否则就会报错。

（报错）sqlite3.OperationalError: table product already exists

Python 对 SQLite 的增删查改

在 Python 中，如果想要对一个 SQLite 表进行 “增删查改” 操作，我们都是使用 Cursor 对象的 execute() 方法以及 Connection 对象的 commit() 方法来实现的。首先使用 cursor.execute() 执行 SQL 语句，然后使用 connection.commit() 提交这个事务。

1. 增

在 Python SQLite 中，我们可以使用 insert into 语句来为某个表增加数据（或插入数据）。

语法：

insert into 表名 (字段1, 字段2, ..., 字段n) values (值1, 值2, ..., 值n)

说明：

从前文可知，product 表有 4 个字段：id、name、type、price。在给每一个字段赋值时，需要根据其数据类型来赋值。比如 id 字段的值必须是一个整型，name 字段的值必须是一个字符串（最大长度为 10），以此类推。如果插入值的数据类型不一致，就会导致各种预想不到的问题。

示例 2：使用 SQLite 增加数据

import sqlite3

# 创建连接（连接数据库）
conn = sqlite3.connect(r'data\test.db')
# 创建游标
cursor = conn.cursor()

# 执行 SQL 语句，增加数据
cursor.execute('insert into product (id, name, type, price) values (1, "橡皮", "文具", 2.5)')
cursor.execute('insert into product (id, name, type, price) values (2, "尺子", "文具", 1.2)')
cursor.execute('insert into product (id, name, type, price) values (3, "铅笔", "文具", 4.6)')
cursor.execute('insert into product (id, name, type, price) values (4, "筷子", "餐具", 39.9)')
cursor.execute('insert into product (id, name, type, price) values (5, "汤勺", "餐具", 12.5)')

# 关闭游标
cursor.close()
# 提交事务
conn.commit()
# 关闭连接
conn.close()

分析：

上面示例表示往 product 表中增加 5 条记录。一条记录，也就是表的一行数据。增加 5 条记录，也就是增加 5 行数据。“记录” 这个概念很常见，小伙伴们应该清楚知道它指的是什么。在插入记录时，如果是往所有字段都插入数据，那么字段名可以省略。下面两种方式是等价的。

# 方式 1
cursor.execute('insert into product (id, name, type, price) values (1, "橡皮", "文具", 2.5)')

# 方式 2
cursor.execute('insert into product values (1, "橡皮", "文具", 2.5)')

由于 test.db 这个文件已经存在，所以 conn=sqlite3.connect(r'data\test.db') 这句代码不再表示创建数据库文件，而是表示连接 test.db 这个文件。

请记住一点：在对表的 “增删查改” 操作中，关闭连接之前一定要使用 Connection 对象的 commit() 方法来提交事务，否则就无法操作成功。

运行上面代码会往 product 表增加 5 条记录。为了验证是否增加成功，我们可以再次运行整个例子代码，如果报出下面的错误，就说明已经增加成功。这是因为在同一个表中，不允许出现相同主键的记录。

sqlite3.IntegrityError: UNIQUE constraint failed: product.id

2. 查

在 Python SQLite 中，我们可以使用 select 语句来查询表中符合条件的数据。select 语句是 SQL 所有语句中用得最多的一种语句，如果你能把 select 语句认真掌握好，那说明离掌握 SQL 已经不远了。

语法：

select 字段1, 字段2, ... , 字段n from 表名 where 查询条件

示例 3：使用 SQLite 查询数据

import sqlite3

# 创建连接、创建游标
conn = sqlite3.connect(r'data\test.db')
cursor = conn.cursor()

# 执行 SQL 语句
cursor.execute('select * from product')
# 获取查询结果
result = cursor.fetchall()
print(result)

# 关闭游标、提交事务、关闭连接
cursor.close()
conn.commit()
conn.close()

运行结果如下。

[(1, '橡皮', '文具', 2.5), (2, '尺子', '文具', 1.2), (3, '铅笔', '文具', 4.6), (4, '筷子', '餐具', 39.9), (5, '汤勺', '餐具', 12.5)]

分析：

cursor.execute('select * from product') 表示从 product 表中查询所有的记录。cursor.fetchall() 表示获取所有 “符合条件” 的记录，它返回的是一个列表，列表的每一个元素是一个元组。

如果把 cursor.execute('select * from product') 改为 cursor.execute('select name, price from product')，此时表示从 product 表中获取 name 和 price 这两列。再次运行后结果如下：

[('橡皮', 2.5), ('尺子', 1.2), ('铅笔', 4.6), ('筷子', 39.9), ('汤勺', 12.5)]

如果把 cursor.execute('select * from product') 改为 cursor.execute('select * from product where price > 10')，此时表示从 product 表中获取 price 大于 10 的记录。再次运行后结果如下：

[(4, '筷子', '餐具', 39.9), (5, '汤勺', '餐具', 12.5)]

提示: SQL 语句变化多样，它本身就可以作为一门独立的技术，你可以查阅 SQL 官方文档来了解更多内容。如果想要更好地掌握 SQL，也可以学习咱们绿叶网出版的：《从 0 到 1：SQL 即学即用》。

3. 改

在 Python SQLite 中，我们可以使用 update 语句来修改表中符合条件的数据。

语法：

update 表名 set 字段 = 值 where 查询条件

示例 4：使用 SQLite 修改数据

import sqlite3

# 创建连接、创建游标
conn = sqlite3.connect(r'data\test.db')
cursor = conn.cursor()

# 执行 SQL 语句
cursor.execute('update product set price=10.0 where name="橡皮"')
cursor.execute('select * from product where name="橡皮"')
result = cursor.fetchall()
print(result)

# 关闭游标、提交事务、关闭连接
cursor.close()
conn.commit()
conn.close()

运行结果如下。

[(1, '橡皮', '文具', 10.0)]

分析：

cursor.execute('update product set price=10.0 where name="橡皮"') 表示找到 name="橡皮" 这条记录，然后将 price 的值改为 10.0。实际上，这句代码等价于：

cursor.execute('update product set price=? where name=?', (10.0, '橡皮'))

此时的 execute() 方法接收两个参数：第 1 个参数是 SQL 语句，它是一个字符串；第 2 个参数是替换值，它是一个元组。元组中的值会依次替换 SQL 语句中的问号（?），最后拼接成一个字符串。

4. 删

在 Python SQLite 中，我们可以使用 delete 语句来删除表中符合条件的数据。

语法：

delete from 表名 where 查询条件

示例 5：使用 SQLite 删除数据

import sqlite3

# 创建连接、创建游标
conn = sqlite3.connect(r'data\test.db')
cursor = conn.cursor()

# 执行 SQL 语句
cursor.execute('delete from product where id=5')
cursor.execute('select * from product')
result = cursor.fetchall()
print(result)

# 关闭游标、提交事务、关闭连接
cursor.close()
conn.commit()
conn.close()

运行结果如下。

[(1, '橡皮', '文具', 10.0), (2, '尺子', '文具', 1.2), (3, '铅笔', '文具', 4.6), (4, '筷子', '餐具', 39.9)]

分析：

cursor.execute('delete from product where id=5') 表示从 product 表中删除 id=5 这条记录。实际上，这句代码等价于：

cursor.execute('delete from product where id=?', (5,))