Skip to content

SQLite

与许多其他数据库管理系统不同，SQLite 不是一个客户端/服务器结构的数据库引擎，而是一种嵌入式数据库，它的数据库就是一个文件。
SQLite 将整个数据库, 包括定义、表、索引以及数据本身，作为一个单独的、可跨平台使用的文件存储在主机中。
由于 SQLite 本身是用 C 语言编写的，而且体积很小，所以，经常被集成到各种应用程序中，甚至在手机 ios 和 Android 的 App 中都可以集成。

官方文档

SQLite经典教程

菜鸟教程

易百教程

Python 就内置了 SQLite3，所以，在 Python 中使用 SQLite，不需要安装任何模块，可以直接使用

创建一个名称为 test.db 的 SQLite 数据库文件，然后执行 SQL 语句创建出一个 user 表，user 表包含 id 和 name 两个字段

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
import sqlite3
# 连接到 SQLite 数据库
# 数据库文件是 test.db，如果文件不存在，会自动在当前目录创建
conn = sqlite3.connect('test.db')
# 创建一个 Cursor
cursor = conn.cursor()
# 执行一条 SQL 语句，创建 user 表
cursor.execute('create table user (id int(10) primary key, name varchar(20))')
# 关闭游标
cursor.close()
# 关闭 Connection
conn.close()

上面的代码只能运行一个，再次运行时，会提示sqlite3.OperationalError: table user already exists，这是因为 user 表已经存在

Python操作SQLite

新增用户数据信息

为了向数据表中新增数据，可以使用如下 SQL 语句
insert into 表名(字段名1, 字段名2, ..., 字段名n) values (字段值1, 字段值2, ..., 字段值n)

例如，在 user 表中，有 2 个字段，字段名分别为 id 和 name。而字段值需要根据字段的数据类型来赋值，如 id 是一个长度为 10 的整型，name 是长度为 20 的字符串型数据。向 user 表中插入 3 条用户记录，则 SQL 语句如下

sqlite3.Connection.commit()
这个 commit() 方法用于提交当前事务。如果没有调用这个方法，那么从上一次提交 commit() 以来所有的变化在其他数据库连接上都是不可见的。如果你往数据库里写了数据，但是又查询不到，请检查是否忘记了调用这个方法。

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
import sqlite3
conn = sqlite3.connect('user.db')
cursor = conn.cursor()
cursor.execute('create table user (id int(10) primary key, name varchar(20))')

cursor.execute('insert into user (id, name) values (1, "cai")')
cursor.execute('insert into user (id, name) values (2, "nocilantro")')
cursor.execute('insert into user (id, name) values (3, "不要香菜🙏")')

conn.commit()

cursor.close()
conn.close()

查看用户数据信息

查找 user 表中的数据可以使用如下 SQL 语句:

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

查询数据时通常使用如下 3 种方式:

  • fetchone(): 获取查询结果集种的下一条记录
  • fetchmany(size): 获取指定数量的记录
  • fetchall(): 获取结果集的所有记录
 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
import sqlite3
conn = sqlite3.connect('user.db')
cursor = conn.cursor()

cursor.execute('select * from user')

result1 = cursor.fetchone()
print(result1)

result2 = cursor.fetchmany(2)
print(result2)

result3 = cursor.fetchall()
print(result3)

conn.commit()

cursor.close()
conn.close()
"""
(1, 'cai')
[(2, 'nocilantro'), (3, '不要香菜🙏')]
[]
"""

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
cursor.execute('select * from user')
result1 = cursor.fetchone()
print(result1)

cursor.execute('select * from user')
result2 = cursor.fetchmany(2)
print(result2)

cursor.execute('select * from user')
result3 = cursor.fetchall()
print(result3)
"""
(1, 'cai')
[(1, 'cai'), (2, 'nocilantro')]
[(1, 'cai'), (2, 'nocilantro'), (3, '不要香菜🙏')]
"""

修改上面的代码，将查询结果的语句块代码修改为：

1
2
3
4
5
6
cursor.execute('select * from user where id > ?', (1, ))
result3 = cursor.fetchall()
print(result3)
"""
[(2, 'nocilantro'), (3, '不要香菜🙏')]
"""

在 select 查询语句中，使用问号作为占位符代替具体的数值，然后使用一个元组来替换问号(注意，不要忽略元组中最后的逗号)。上述查询语句等价于

1
cursor.execute('select * from user where id > 1')

使用占位符的方式可以避免 SQL 注入的风险，推荐使用这种方式

修改用户数据信息

修改 user 表中的数据可以使用如下 SQL 语句

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

例如，将 sqlite 数据库中的 user 表 ID 为 1 的数据 name 字段 "cai" 修改为 "xiangcai" 的代码如下

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
import sqlite3
conn = sqlite3.connect('user.db')
cursor = conn.cursor()

cursor.execute('update user set name = ? where id = ?', ('xiangcai', 1))

cursor.execute('select * from user where id = ?', (1, ))
result3 = cursor.fetchall()
print(result3)

conn.commit()
cursor.close()
conn.close()
"""
[(1, 'xiangcai')]
"""

删除用户数据信息

1
delete from 表名 where 查询条件

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
import sqlite3
conn = sqlite3.connect('user.db')
cursor = conn.cursor()

cursor.execute('delete from user where id = ?', (1,))

cursor.execute('select * from user')
result3 = cursor.fetchall()
print(result3)

conn.commit()
cursor.close()
conn.close()
"""
[(2, 'nocilantro'), (3, '不要香菜🙏')]
"""

命令行操作

.开头的命令

启动: 

1
2
3
4
5
6
➜  ~ sqlite3
SQLite version 3.29.0 2019-07-10 17:32:03
Enter ".help" for usage hints.
Connected to a transient in-memory database.
Use ".open FILENAME" to reopen on a persistent database.
sqlite>

.help 获取可用的命令清单

命令 描述
.databases 列出附加数据库的名称和文件
.tables 列出附加数据库中的所有表
.import FILE TABLE 导入来自 FILE 文件的数据到 TABLE 表中 
1
2
3
4
5
6
sqlite> .databases
main:
sqlite> .tables
sqlite> .import test.db test_table
sqlite> .tables
test_table

基础语句

查看所有表名: select name from sqlite_master where type='table';
查看表的索引列表: PRAGMA index_list(表名)
查看表的信息: PRAGMA table_info(表名)
创建索引: create index 索引名 on 表名 (字段名1, 字段名2,...)
查看数据库中所有表的索引: select name from sqlite_master where type='index'

DDL-数据定义语言

命令 描述
CREATE 创建一个新的表，一个表的视图，或者数据库中的其他对象
ALTER 修改数据库中的某个已有的数据库对象，比如一个表
DROP 删除整个表，或者表的视图，或者数据库中的其他对象

DML-数据操作语言

命令 描述
INSERT 创建一条记录
UPDATE 修改记录
DELETE 删除记录

DQL-数据查询语言

命令 描述
SELECT 从一个或多个表中检索某些记录