import sqlite3 # 这点不用说了

connect 连接与新建数据库

conn = sqlite3.connect("database.db")
# 返回数据库的连接对象 = sqlite.connect("数据库名 或 路径")
# 指定路径 (database/face_recognition.db) 这里是相对路径

当路径内存在该数据库时连接数据库，相反新建数据库

注：如果不指定路径会默认保存在python文件同目录下

connect 也会同时打开连接，所以说不需要手动连接

conn.close() #断开连接

当然，既然有连接那么 with 也能用

with sqlite3.connect("database.db")  as conn:

连接后的 conn 可用于控制数据库，也可管理数据库数据

游标对象

游标可以用于管理数据库数据

with sqlite3.connect("database.db") as conn:
    with conn.cursor() as query: # 新建一个游标名为 query
    cursor = conn.curson() #同上，名为 cursor，建议使用这一条

为何建议使用 cursor = conn.curson() 是因为游标会随着 数据库的断开连接自动消散

但注意，游标不是必须存在，例如cursor.execute 效果和 conn.execute 效果一样 但 conn 的"游标" 调用完后会立马摧毁，而反复创建和摧毁游标会消耗性能，所以单次调用使用 conn，多次调用选择 cursor

创建表 与 添加列

cursor.execute('''CREATE TABLE IF NOT EXISTS database
               (id INTEGER PRIMARY KEY AUTOINCREMENT,
                name TEXT NOT NULL,
                age INTEGER,
                email TEXT UNIQUE)''')
conn.commit()

CREATE TABLE IF NOT EXISTS database

关键字	描述
CREATE TABLE	创建表格
IF	如果
NOT EXISTS	不存在

新建一个表名datebase，如果datebase已存在则跳过。准确来说是 "创建一个表格(datebase)，如果这个表格(datebase)不存在"

id INTEGER PRIMARY KEY AUTOINCREMENT

关键字	描述
id	这只是一个 列名
INTEGER	代表数据类型为整数
PRIMARY KEY	代表是主键
AUTOINCREMENT	自动递增

name TEXT NOT NULL

关键字	描述
name	列名
TEXT	数据类型为文本
NOT NULL	禁止空值

age INTEGER

关键字	描述
age	列名
INTEGER	数据类型为整数

这里没有声明不能为空，所以里面可以啥都不填

email TEXT UNIQUE

关键字	描述
emali	列名
TEXT	数据类型为文本
UNIQUE	禁止重复

最终效果是 如果"database"不存在则创建，向"database"添加列
"ID"，数据类型为"INTEGER"(整数)，是主键，值自动递增(第二个值为max+1)
"name"，数据类型为"TEXT"(文本)，禁止空值
"age"，数据类型为"INTEGER"(整数)，没有约束
"email"，"TEXT"(文本)，不能重复

总结下来：

游标.execute("CREATE TABLE IF NOT EXISTS 表明  (列名，数据类型，约束)")

conn.commit()

提交事项，即保存

追加列

当你在为你成功创建一个数据库而沾沾自喜时忽然发现忘加 "User ID" 了，此时要么选择删除表，重建或者选择追加列

cursor.execute("ALTER TABLE users ADD COLUMN ID INTEGER NOT NULL DEFAULT 0")

追加列名

CREATE TABLE IF NOT EXISTS database (id INTEGER PRIMARY KEY, {columns})

关键字	描述
ALTER TABLE	修改结构
users	修改的表名
ADD COLUMN	添加新列
ID	新增的列名为 "ID"
INTEGER	"ID" 的类型为整数
NOT NULL	约束，不能为空
DEFAULT 0	默认值为0

这个方法是官方唯一的追加方法，但同时还有一个 伪追加(代码就不写了) 原理是创建一个自定义列，里面想填啥填啥，缺一列时往里面填，缺两列时每人占两行，但局限也挺大，唯一的优点是不用改数据结构

约束

看到前面的约束了吧？不知道约束有啥用？ 当你的网站需要用户名不重复且不能为空时就知道有啥用了。下面是sqlite所有的约束：

列级约束

也就是针对这个列的约束

约束	描述
NOT NULL	禁止空值，里面的值不能是NULL
UNIQUE	禁止重复值，也就是用户名
PRIMARY KEY	主键(所有表中，只能有一个列是主键，主键值不可重复，不能为空)
CHECK	自定义约束:CHECK(条件判断，如age>0)
DEFAULT	后面跟默认值
COLLATE	指定排序规则。后面细讲

表级约束

针对这个表的约束 部分表级约束和列级约束无区别

约束	描述
PRIMARY KEY	主键，但表记主键能有多个。后面细说
UNIQUE	同列级
CHECK	条件判断可以选择两个列进行判断(a + b > 10)
FOREIGN KEY	外键约束。后面细说

特殊约束

约束	描述
AUTOINCREMENT	自动递增？主键修改？。后面细讲
ON CONFLICT	避免报错。后面细说

COLLATE 排序规则？

列级主键？表级主键？

列级主键：

CREATE TABLE 用户 (
    用户ID INTEGER PRIMARY KEY,  -- 这是列级主键
    用户名 TEXT
);

表记主键：

CREATE TABLE 订单 (
    订单ID INTEGER,
    用户ID INTEGER,
    订单日期 TEXT,
    PRIMARY KEY (订单ID, 用户ID)  -- 这是表级主键
);

它们的主要区别就是： 一个只能定义一个主键，另一个可以定义多个主键(表级主键无法选择自动递增)

主键？外键？

sqlite默认外键禁用，需要手动启用

conn.execute("PRAGMA foreign_keys = ON")

外键是绑定在主键上的。 通俗易懂些：外键拥有的值，主键一定有；主键拥有的值，外键不一定有

AUTOINCREMENT 自动递增？

想要使用 AUTOINCREMENT 有两个前提：

• 这个列必须是 整数

• 这个列必须是主键

而它的效果则相当于一个 MOD ，重新定义了主键

特性	未调用	调用 AUTOINCREMENT
重用以删除ID	是	否
最大ID存储	否	是
性能	高	低
溢出行为	循环	报错(SQLITE_FULL)

所以插入新值时，值默认是 max(已有值) + 1

ON CONFLICT 避免报错？

ONCONFLICT 的作用就是用来避免报错的，有点类似与 try 但 ONCONFLICT 针对sqlite的约束报错问题：

当你创建了一个列，约束为这个列不能重复，当出现重复数据时会根据你的要求选择不同的方式处理，或理解为根据要求，做出相应的应对措施

ON CONFLICT 的所有应对措施：

措施	描述
IGNORE	不报错，也不写入
REPLACE	覆盖旧数据
ABORT (默认)	报错
FAIL	停止，但保留已执行的
ROLLBACK	全部回滚。后面细讲

cursor.execute('''
    INSERT INTO users (username, age) 
    VALUES ('Alice', 30)
    ON CONFLICT(username) IGNORE  # 冲突时忽略
''')

ROLLBACK

ROLLBACK效果是：当出现一个问题时，回到上一次保存的状态 那有什么用： A下楼买烟，转了100块，好A -100 就在老板B要收到钱时...忽然出现了个错误！导致后面代码都不执行 但如果没有ROLLBACK，则会导致A 转了100，而B没收到 有 ROLLBACK 会因为出现问题从而将 A付的100反回去

try:
    conn.execute("BEGIN")  # 开始事务
    conn.execute("UPDATE accounts SET balance = balance - 100 WHERE user_id = 1")
    conn.execute("UPDATE accounts SET balance = balance + 100 WHERE user_id = 2")
    conn.commit()  # 全部成功才提交
except:
    conn.rollback()  # 有任何失败就全部撤销
    print("转账失败，已回滚")

数据写入

# 插入单条数据
cursor.execute("INSERT INTO users (name, age, email) VALUES (?, ?, ?)",
               ('A', 25, 'a@example.com'))
conn.commit()
​
# 插入多条数据
users = [
    ('B', 30, 'b@example.com'),
    ('C', 35, 'c@example.com')
]
cursor.executemany("INSERT INTO users (name, age, email) VALUES (?, ?, ?)", users)
conn.commit()

INSER INTO users(name,age,email) VALUES(?,?,?)

INSER INTO user 往表 "user" 填写数据 (name,age,email) 填写的列为 "name"、"age"、"email" VALUES(?,?,?) 表示格式，每个 "?" 都是一个占位符，可以避免 SQL 注入攻击 后面括号内的就就是数据，最后接上 commit() 写入

数据查看

查询列名

cursor.execute("SELECT * FROM users")   # 查询 users 中的所有列(* 代表全部)

查询特定列：

cursor.execute("SELECT name, email FROM users") #查询 users 中"name""email"这两个列的值
for name, email in cursor:
    print(f"Name: {name}, Email: {email}")

条件查询

cursor.execute("SELECT * FROM users WHERE age > ?", (18,))
for row in cursor.fetchall():
    print(row)

欸外补充一句，我们也可以通过"tabulate"来将数据以图表的方式显示
但和这篇文章关系不大，也就不讲了

fetch

方法	描述
all	读取所有行
one	读取下一行
many(size)	读取指定行

注意需要 fetch+方法(例：fetchall)

欸！这个时候就有人要问了：既然 cursor 能直接遍历，为啥还要用 fetch? 因为 cursor 实际上就是个换了马甲的 fetchone，效果是完全一样的

翻页

所谓翻页实际就是限制显示，类似于迭代器

cursor.execute("SELECT * FROM users LIMIT ? OFFSET ?", (per_page, offset))

关键字	描述
LIMIT ?	限制，"?"为占位。值为多少就返回几条数据
OFFSET ?	跳过多少行(OFFSET 20 代表跳过20行，从21开始返回数据)
(per_oage,offset)	对应那两个 "?"

返回字典

通过获取列名和内容就可以实现返回字典的效果

cursor.execute("SELECT * FROM users")
columns = [col[0] for col in cursor.description]
result = [dict(zip(columns, row)) for row in cursor.fetchall()]
print(result[0])

这就不用解释了

数据更改

UPDATE users SET name = ?,email = ? WHERE id = ?,("a","a@email.com",1)

更改users表中id为1的部分，名改为"a"，邮箱改为"a@email.com"

插入多行

a = [
    ("a", "a@email.com", 1),
    ("b", "b@email.com", 2),
]
cursor.executemany(
    "UPDATE users SET age = ?, email = ? WHERE id = ?",
    updates
)

数据删除

cursor.execute("DELETE FROM users WHERE ID=0")

删除users表中ID为0的用户