1 数据库处理

1.1 数据库框架

整体框架
python # 导入 sqlite 驱动 import sqlite3 # 连接到数据库 conn = sqlite3.connect('test.db') # 创建游标 cursor = conn.cursor() # 使用 sql sql_add = 'insert into user (id,name) values(?, ?);' cursor.executemany(sql_add, (11, 'Bill2')) # 关闭游标 cursor.close() # 提交事务 conn.commit() # 关闭连接 conn.close() python # python自带模块，不用单独安装 # 不存在则创建该数据库 # 对sqlite的操作都使用 游标的方法 # 编写sql # 执行sql

1.2 游标对象（cursor）

1.2.1 游标方法（fetch开头）

游标方法（可在3.4.1之后看）
python # 准备工作 db_prepare() conn = sqlite3.connect('test.db') cursor = conn.cursor() # 只处理sql的一行 cursor.execute('select * from staffs') print(cursor.fetchone()) # 只处理sql的指定行数 cursor.execute('select * from staffs') print(cursor.fetchmany(3)) # 处理sql所有行 cursor.execute('select * from staffs') print(cursor.fetchall()) # fetch开头的这些方法具有迭代性 cursor.execute('select * from staffs') print(cursor.fetchone()) print(cursor.fetchone()) print(cursor.fetchall()) # 断开连接 cursor.close() conn.commit() conn.close() python # 使用的是 3.4 中的函数 db_prepare # 只有fetchone返回的是元组，其他返回的都是有列表包装好的元组 (1, 'Peter', 'male', 'bom') # fethch(n) 中的 n 就是索要处理的 sql行数 [(1, 'Peter', 'male', 'bom'), (2, 'Joey', 'female', 'bom'), (3, 'Bill', 'male', 'te')] # 注意：fetchall 和 fetchone 在使用时不要忘记他们都是方法，需要加 () [(1, 'Peter', 'male', 'bom'), (2, 'Joey', 'female', 'bom'), (3, 'Bill', 'male', 'te'), (4, 'Janet', 'female', 'bom'), (5, 'Susan', 'female', 'fvt')] # 如果只使用一次 select from 语句，并在其后使用多个 fetch 开头的方法，则每次处理都是从下一行数据开始 (1, 'Peter', 'male', 'bom') (2, 'Joey', 'female', 'bom') [(3, 'Bill', 'male', 'te'), (4, 'Janet', 'female', 'bom'), (5, 'Susan', 'female', 'fvt')]

1.2.2 游标方法/属性（已归纳）

类型 语法 位置
方法 cursor.execute() 见：3.1.1 和 3.1.2
cursor.executemany() 见：3.1.2
属性 cursor.description
cursor.rowcount 见：3.4.5

2 数据表处理

2.1 创建数据表（create table）

创建数据表
python import sqlite3 conn = sqlite3.connect('test.db') cursor = conn.cursor() # 创建数据表 sql = '''create table staffs( ID int primary key not null, name text not null, age int, hobby char(30) )''' cursor.execute(sql) cursor.close() conn.commit() conn.close()
【sqlite】create table 语句
python sql = '''create table 表名( 列名1 数据类型 primary key not null, 列名2 数据类型 not null, 列名3 数据类型, 列名4 数据类型)''' # 注意 sql 中 # primary key 设置主键 # not null 约束：该列数据唯一，不可重复
【sqlite】数据类型
int 整数
real 浮点数
char(n) 字符串，()里的数字为字符数量限制
text 文本字符串，编码（UTF-8、UTF-16BE 或 UTF-16LE）
创建数据表 · 加强版（覆盖式）
python import sqlite3 import os def create_tab(db_name, tab_name, info): conn = sqlite3.connect(db_name) cursor = conn.cursor() try: cursor.execute(f'create table {tab_name} ({info})') except: cursor.execute(f'drop table {tab_name}') cursor.execute(f'create table {tab_name} ({info})') cursor.close() conn.commit() conn.close() info = ''' ID int primary key not null, name text not null, age int, hobby char(30) ''' create_tab('test.db', 'leaders', info)
  • sqlite是单线程工作的，如果有其他线程终止没有完成会出现报错：OperationalError: database is locked

2.2 删除数据表（drop table）

删除数据表
python import sqlite3 conn = sqlite3.connect('test.db') cursor = conn.cursor() # 删除数据表 sql = 'drop table staffs' cursor.execute(sql) cursor.close() conn.commit() conn.close() python # sql = 'drop table 表名'
python import sqlite3 conn = sqlite3.connect('test.db') cursor = conn.cursor() # 删除数据表 if table is not None and table != '': sql = 'DROP TABLE IF EXISTS ' + table cursor.execute(sql) cursor.close() conn.commit() conn.close() python

2.3 改动数据表

2.3.1 改动表名（alter table）

改变数据表名
python import sqlite3 conn = sqlite3.connect('test.db') cursor = conn.cursor() # 重命名数据表名 cursor.execute('alter table leaders rename to leader') cursor.close() conn.commit() conn.close() python # cursor.execute('alter table 旧表名 rename to 新表名')

2.3.2 添加字段 （add column）

增加字段
python import sqlite3 conn = sqlite3.connect('test.db') cursor = conn.cursor() # 指定表添加列名 cursor.execute('alter table staffs add column sex') cursor.close() conn.commit() conn.close() python cursor.execute('alter table 表名 add column 列名')

3.4 查询数据表

3.4.1 查询所有表名（sqlite_master）

查询所有表名（可在3.4.1之后看）
python # 准备工作 db_prepare() conn = sqlite3.connect('test.db') cursor = conn.cursor() # 返回表名 tables = cursor.execute("select name from sqlite_master where type='table' order by name").fetchall() table_list = [table[0] for table in tables] print(table_list) # 断开连接 cursor.close() conn.commit() conn.close() python # 使用的是 3.4 中的函数 db_prepare ['staffs']

3.4.2 查询所有字段（pragma table_info）

查询字段
python # 准备工作 db_prepare() conn = sqlite3.connect('test.db') cursor = conn.cursor() # 返回字段信息 columns = cursor.execute('pragma table_info(staffs)').fetchall() print(names) # 返回字段数量 print(len(columns)) # 返回字段列表 column_list = [column[1] for column in columns] print(column_list) # 断开连接 cursor.close() conn.commit() conn.close() python # 使用的是 3.4 中的函数 db_prepare # 返回列表包装的元组，元组第一参数为字段索引；第二参数为字段名；第三参数为字段的数据类型；第四参数暂未知；第五参数我猜为字段是否可空 [(0, 'ID', 'INT', 1, None, 1), (1, 'name', 'TEXT', 1, None, 0), (2, 'sex', 'char(10)', 0, None, 0), (3, 'job', 'char(10)', 0, None, 0)] 4 ['ID', 'name', 'sex', 'job']

3 数据处理

3.1 增加数据（insert into）

数据库中每行的数据都是由其各自的字段的。字段在创建时可以做一些约束，比如能不能有重复值，或者是否设置为主键。字段在数据库中，每一列都是一个字段，在本章中为方便说明将字段名叫做列名。

3.1.1 两写法（execute单双参）

单参式 和 双参式
python import sqlite3 conn = sqlite3.connect('test.db') cursor = conn.cursor() # 一体式 增加一行数据 sql = 'insert into leaders(ID, name, age, hobby) values(1, \'Peter\', 26, \'games, girls\')' cursor.execute(sql) cursor.close() conn.commit() conn.close() python import sqlite3 conn = sqlite3.connect('test.db') cursor = conn.cursor() # 分离式 增加一行数据 sql = 'insert into leaders(ID, name, age, hobby) values(?, ?, ?, ?)' cursor.execute(sql, (1, 'Peter', 26, 'games, girls')) cursor.close() conn.commit() conn.close()
python # 单参式 sql = 'insert into 表名(列名(s)) values(数据1, 数据2, 数据3...)' cursor.execute(sql) # 单参使用字符串的时候需要用 \' 替代 ' 如：... values(1, \'Peter\')' # 或者 "" 和 '' 混合使用 如："insert into staffs values(1, 'Peter')" # 双参式 sql = 'insert into 表名(列名1, 列名2, 列名3...) values(?, ?, ?)' data = [数据1, 数据2, 数据3...] / data = (数据1, 数据2, 数据3...) cursor.execute(sql, data) # ? 和数据的数量要一致 # execute 不光是用来添加数据，所有 sql 上的操作都是通过这个方法

3.1.2 两方法（executemany）

增加数据 — execute/executemany
python import sqlite3 conn = sqlite3.connect('test.db') cursor = conn.cursor() sql = 'insert into staffs(ID, name, age, hobby) values(?, ?, ?, ?)' # 增加一行数据 cursor.execute(sql, (1, 'Peter', 26, 'games, girls')) cursor.execute(sql, [2, 'Joey', 24, 'breakfast, lunch, dinner']) # 同时增加多行数据 cursor.executemany(sql, [(3, 'Bill', 28, 'games, girls'), (4, 'Janet', 29, 'singing, watching')]) cursor.close() conn.commit() conn.close() python # sql 语句中可以使用 ? 占位，接着在 execute 中补充数据 # cursor.execute(sql, (数据1, 数据2, 数据3, 数据4)) # cursor.execute 的第二参数 可以是元组，也可以是列表 # cursor.executemany(sql, [(数据1, 数据2, 数据3, 数据4), (数据1, 数据2, 数据3, 数据4)...]) # 列表包元祖
python # 该总结是以双参为前提 sql = 'insert into leaders(列名1, 列名2, 列名3...) values(?, ?, ?)' # .execute 像 3.1.1 中写的一样 data = [数据1, 数据2, 数据3...] / data = (数据1, 数据2, 数据3...) cursor.execute(sql, data) # .executemany 是用列表包装，里面包括多行数据 data = [(数据1, 数据2, 数据3...), (数据1, 数据2, 数据3...), ...] cursor.executemany(sql, data) # execute/executemany 不光是用来添加数据，所有 sql 上的操作都是通过这个方法

3.1.3 他表提取（insert+select）
python import sqlite3 conn = sqlite3.connect('test.db') cursor = conn.cursor() sql = 'insert into staffs select * from leaders' cursor.execute(sql) cursor.close() conn.commit() python # leaders 和 staffs 格式相同、且 unique的列 数据不冲突 为前提
【sqlite】insert into + select from [+ where]
python # 从其他表中填充（选择性） sql = 'insert into 目标表名(列名1, 列名2, 列名4) select (列名1, 列名2, 列名4) from 数据源表名 [where 条件表达式]' # 全部 sql = 'insert into 目标表名 select * from 数据源表名 [where 条件表达式]'

3.2 删除数据（delete from）

3.2.1 删除全部/部分数据

全部/部分数据
python import sqlite3 conn = sqlite3.connect('test.db') cursor = conn.cursor() # 删除全部数据 cursor.execute('delete from staffs') cursor.close() conn.commit() conn.close() python import sqlite3 conn = sqlite3.connect('test.db') cursor = conn.cursor() # 删除满足条件的数据 cursor.execute('delete from staffs where ID = 2') cursor.close() conn.commit() conn.close()

3.3 改动数据（update set）

3.3.1 改动符合条件的数据

改动符合条件的数据
python import sqlite3 conn = sqlite3.connect('test.db') cursor = conn.cursor() # 删除全部数据 cursor.execute("update staffs set name='Peter Wang', hobby='games' where ID=1") cursor.close() conn.commit() conn.close() python cursor.execute("update 表名 set 列名1=数据1, 列名2=数据2 where 条件表达式")

3.4 查询数据

本章节实例都以下面代码为基础
python import sqlite3 import os def db_prepare(): conn = sqlite3.connect('test.db') cursor = conn.cursor() # 覆盖式创建工作表 sql_create = '''create table staffs( ID int primary key not null, name text not null, sex char(10), job char(10) )''' try: cursor.execute(sql_create) except: cursor.execute('drop table staffs') cursor.execute(sql_create) # 增加数据 sql_add = 'insert into staffs(ID, name, sex, job) values(?, ?, ?, ?)' cursor.executemany(sql_add, [(1, 'Peter', 'male', 'bom'), \ (2, 'Joey', 'female', 'bom'), \ (3, 'Bill', 'male', 'te'), \ (4, 'Janet', 'female', 'bom'), \ (5, 'Susan', 'female', 'fvt')]) # 断开连接 cursor.close() conn.commit() conn.close()

3.4.1 显示全部/前n行/某列信息

显示全部/前n行/某列信息
python # 准备工作 db_prepare() conn = sqlite3.connect('test.db') cursor = conn.cursor() # 显示全部数据 cursor.execute('select * from staffs') print(cursor.fetchall()) # 显示数据某（些）列 cursor.execute('select name,job from staffs') print(cursor.fetchall()) # 显示数据符合条件的前 n 行 cursor.execute('select name,job from staffs limit 2') print(cursor.fetchall()) # 断开连接 cursor.close() conn.commit() conn.close() python [(1, 'Peter', 'male', 'bom'), (2, 'Joey', 'female', 'bom'), (3, 'Bill', 'male', 'te'), (4, 'Janet', 'female', 'bom'), (5, 'Susan', 'female', 'fvt')] [('Peter', 'bom'), ('Joey', 'bom'), ('Bill', 'te'), ('Janet', 'bom'), ('Susan', 'fvt')] # 常与排序连用，见 [('Peter', 'bom'), ('Joey', 'bom')]

3.4.2 按条件/选项/范围/通配符查询

显示全部/前n行/某列信息
python # 准备工作 db_prepare() conn = sqlite3.connect('test.db') cursor = conn.cursor() # 按条件查询 cursor.execute("select name, job from staffs where job='bom' and sex='male'") print(cursor.fetchall()) # 按选项查询 cursor.execute("select name, job from staffs where job in ('bom', 'te')") print(cursor.fetchall()) # 按参数范围查询 cursor.execute("select name, job from staffs where ID between 1 and 2") print(cursor.fetchall()) # 按通配符查询 cursor.execute("select name, job from staffs where name like 'P%'") print(cursor.fetchall()) # 断开连接 cursor.close() conn.commit() conn.close() python # where 列名 < <= = >= > 参数，可用 and or 连接多个条件表达式 [('Peter', 'bom')] # where 列名 in (参数1,参数2...) [('Peter', 'bom'), ('Joey', 'bom'), ('Bill', 'te'), ('Janet', 'bom')] # where 列名 between 参数1 and 参数2 [('Peter', 'bom'), ('Joey', 'bom')] # where 列名 like '通配符表达式' # 通配符 % 可替代 0-n 个字符 # 通配符 _ 可替代 1 个字符 # 通配符 [...] 多个字符中的任一个字符 # 通配符 ^....]/[!...] 多个字符以外的任一个字符 [('Peter', 'bom')]

3.4.3 查询+去重（destinct）

单参数 — 查询数据的同时去重
python # 准备工作 db_prepare() conn = sqlite3.connect('test.db') cursor = conn.cursor() # 查询的同时去重 cursor.execute('select distinct job from staffs') print(jobs_list) jobs_list = cursor.fetchall() job_list = [job[0] for job in jobs_list] print(job_list) # 断开连接 cursor.close() conn.commit() conn.close() python [('bom',), ('te',), ('fvt',)] # 该返回值无法直接使用，需要进一步处理 ['bom', 'te', 'fvt']
多参数 — 查询数据的同时去重
python # 准备工作 db_prepare() conn = sqlite3.connect('test.db') cursor = conn.cursor() # 查询的同时去重 cursor.execute('select distinct job,sex from staffs') jobs_list = cursor.fetchall() print(jobs_list) # 断开连接 cursor.close() conn.commit() conn.close() python [('bom', 'male'), ('bom', 'female'), ('te', 'male'), ('fvt', 'female')]

3.4.4 查询+排序（order by）

查询数据的同时排序
python # 准备工作 db_prepare() conn = sqlite3.connect('test.db') cursor = conn.cursor() # 查询的同时正序排序 cursor.execute('select name,job from staffs order by job asc') print(cursor.fetchall()) # 查询的同时倒序排序 cursor.execute('select name,job from staffs order by job desc') print(cursor.fetchall()) # 断开连接 cursor.close() conn.commit() conn.close() python [('Peter', 'bom'), ('Joey', 'bom'), ('Janet', 'bom'), ('Susan', 'fvt'), ('Bill', 'te')][('bom',), ('te',), ('fvt',)] [('Bill', 'te'), ('Susan', 'fvt'), ('Peter', 'bom'), ('Joey', 'bom'), ('Janet', 'bom')]

3.4.5 增删行数（rowcount）

返回添加的行数
python import sqlite3 import os conn = sqlite3.connect('test.db') cursor = conn.cursor() sql_create = '''create table staffs( ID int primary key not null, name text not null, sex char(10), job char(10) )''' # 添加数据后立刻使用，可返回刚刚插入的数量 cursor.execute(sql_create) print(cursor.rowcount) # 删除数据后立刻使用，可返回刚刚删除的数量 cursor.execute('delete from staffs where ID = 2') print(cursor.rowcount) # 查询数据后立刻使用，只返回 -1 cursor.execute('select * from staffs') print(cursor.rowcount) cursor.close() conn.commit() conn.close() python 5 1 -1

3.4.6 数据行数（count(*))

查询数据行数
python import sqlite3 conn = sqlite3.connect('test.db') cursor = conn.cursor() # 查询数据行数 cursor.execute('select count(*) from staffs') print(cursor.fetchall()[0][0]) cursor.close() conn.commit() conn.close() python # cursor.fetchall 正常情况下返回 [(数量)] 2

3.4.7 符合结果的数量

查询数据
python # 准备工作 db_prepare() conn = sqlite3.connect('test.db') cursor = conn.cursor() # 返回符合结果的数量 names = cursor.execute("select name from staffs where job='bom'").fetchall() print(names) print(len(names)) # 断开连接 cursor.close() conn.commit() conn.close() python [('Peter',), ('Joey',), ('Janet',)] 3

4 DLC（待学习）

sqlite3.Row 对象
Python sqlite3.Row方法代码示例 - 纯净天空
sqlite3 封装
Python开发SQLite3数据库相关操作详解【连接,查询,插入,更新,删除,关闭等】python脚本之家
sqlite3 在时间上的应用
Python 操作SQLite数据库详情python脚本之家
其他sql语句
as；join；into；约束相关；sql 函数