1. 主要对象

sqlite3 模块主要对象:

数据库连接对象: sqlite3.Connection
游标对象: sqlite3.Cursor

使用sqlite3模块，先创建Connection数据库连接对象，再创建Cursor游标对象，然后调用cursor.execute()方法执行 SQL 语句。

1.1 Connection

连接数据库方法: sqlite3.connect(database, …)

# 连接 SQLite 数据, 
# database 是准备打开的数据库文件路径, 
# database 也可以传 ":memory:" 在内存中打开一个数据库。
# 默认返回 Connection 对象
sqlite3.connect(database[, timeout, detect_types, 
                isolation_level, check_same_thread,  
                factory, cached_statements, uri])

SQLite 原生只支持 5 种类型：TEXT、INTEGER、REAL、BLOB、NULL。
如果想用其它类型，必须自己添加相应的支持。

sqlite3.Connection类常用属性和方法:

# 获取/设置 当前默认的隔离级别
Connection.isolation_level
# 获取游标, 返回 Cursor 实例
Connection.cursor(factory=Cursor)
# 提交当前事务。如果没有调用该方法，则从上一次 commit() 以来的所有变化在 其他数据库连接 上都不可见。
Connection.commit()
# 回滚到上一次 commit() 以来的所有数据库的改变
Connection.rollback()
# 关闭数据库连接。注意，该方法不会自动 commint() 
Connection.close()
# 备份数据库，target 为 Connection 实例
Connection.backup(target, *, pages=-1, progress=None, 
                  name="main", sleep=0.250)
# 执行 SQL 语句的快捷方法，Connection 上直接执行 SQL 语句是 非标准的，
# 它会先调用 cursor() 方法创建一个游标对象后再执行 SQL 语句。
Connection.execute(sql[, parameters])
Connection.executemany(sql[, parameters])
Connection.executescript(sql_script)

Python3 SQLite3 的「事务控制」说明

1.2 Cursor

sqlite3.Cursor类常用属性和方法:

# 执行单条 SQL 语句，可参数化语句（参数值使用占位符），支持两种占位符: 问号 和 命名占位符。
# 例如: cursor.execute("insert into people values (?, ?)", (who, age))
Cursor.execute(sql[, parameters])
# 一条 SQL 语句，使用多个参数序列，多次执行。
# 例如: params = [(who0, age0), (who1, age1), ...]
# cursor.executemany("insert into people values (?, ?)", params)
Cursor.executemany(sql, seq_of_parameters)
# 执行 SQL 脚本，非标准的便捷方法。可用于一次执行多条 SQL 语句，
# 它会首先执行一条 COMMIT 语句，再执行 SQL 脚本。
# 例如: cursor.executescript("create ...; insert ...; select ...;")
Cursor.executescript(sql_script)
# 执行完一条查询的 SQL 语句后，获取该条 SQL 语句查询的结果集的下一行，
# 返回一个单独序列，例如: (who1, age1)，没有更多可用数据时返回 None。
Cursor.fetchone()
# 执行完一条查询的 SQL 语句后，获取该条 SQL 语句查询结果集的 size 行，
# 返回一个列表，例如: [(who1, age1), (who2, age2)]，没有更多可用行时将返回一个空列表。
# 此方法是基于 size 参数尝试获取指定行，如果获取不到指定行数，实际结果可能返回较少的行数。
Cursor.fetchmany(size=cursor.arraysize)
# 执行完一条查询的 SQL 语句后，获取该条 SQL 语句查询的所有结果集（剩余所有行），
# 返回一个列表，例如: [(who1, age1), (who2, age2), ...]，没有可用行时将返回一个空列表。
Cursor.fetchall()
# 该只读属性将提供上一次 SQL 查询的列名称。为了与 Python DB API 保持兼容，
# 它会为每个列返回一个 7 元组，每个元组的最后 6 个条目均为 None。
# 例如: (
# ('name', None, None, None, None, None, None), 
# ('age', None, None, None, None, None, None)
# )
# 对于没有任何匹配行的 SELECT 语句同样会设置该属性。
Cursor.description
# 立即关闭 cursor
Cursor.close()

2. SQL 语句操作: CREATE / INSERT / SELECT / UPDATE / DELETE

import sqlite3
# 连接数据库
conn = sqlite3.connect(":memory:")
# 获取游标
cursor = conn.cursor()
# 创建表
cursor.execute(""" create table if not exists Person( id integer primary key not null, name text not null, age integer not null ) """)
# 插入数据
cursor.execute("insert into Person(name, age) values(?, ?)", ("Tom1", 25))
cursor.executemany("insert into Person(name, age) values(?, ?)",
                   [("Tom2", 26), ("Tom3", 27), ("Tom4", 28)])
# 查询数据
cursor.execute("select * from Person")
print(cursor.description)       # (('id', None, ...), ('name', ...), ('age', ...))
print(cursor.fetchall())        # [(1, 'Tom1', 25), (2, 'Tom2', 26), ...]
# 更新数据
cursor.execute("update Person set name=?, age=? where name=?", ("TOM111", 18, "Tom1"))
cursor.execute("select * from Person where name=?", ("TOM111",))
print(cursor.fetchone())        # (1, 'TOM111', 18)
# 删除数据
cursor.execute("delete from Person where id=?", (3,))
cursor.execute("select * from Person where age>=?", (26,))
print(cursor.fetchall())        # [(2, 'Tom2', 26), (4, 'Tom4', 28)]
# 关闭游标
cursor.close()
# 提交事务
conn.commit()
# 关闭数据库连接
conn.close()

执行结果:

(('id', None, None, None, None, None, None), ('name', None, None, None, None, None, None), ('age', None, None, None, None, None, None))
[(1, 'Tom1', 25), (2, 'Tom2', 26), (3, 'Tom3', 27), (4, 'Tom4', 28)]
(1, 'TOM111', 18)
[(2, 'Tom2', 26), (4, 'Tom4', 28)]

3. 通过「列名称」访问查询结果数据

import sqlite3
conn = sqlite3.connect(":memory:")
cursor = conn.cursor()
cursor.execute(""" create table if not exists Person( id integer primary key not null, name text not null, age integer not null ) """)
cursor.execute("insert into Person(name, age) values(?, ?)", ("Tom1", 25))
# 使用 sqlite3.Row 作为行工厂,
# SELECT 查询结果的行数据将以 sqlite3.Row 实例的方式返回
cursor.row_factory = sqlite3.Row
# 执行 select 查询语句
cursor.execute("select * from Person")
# 遍历结果
while True:
    row = cursor.fetchone()         # 获取下一行
    if row is None:
        break
    print(row)                      # <sqlite3.Row object>
    print(row.keys())               # 所有列名称: ['id', 'name', 'age']
    print(row[0])                   # 通过「索引」访问结果值
    print(row["id"])                # 通过「列名称」访问结果值（名称不区分大小写）
    assert row[1] == row["name"]
    assert row[2] == row["age"]
# 也可以直接迭代 cursor, 迭代返回的元素为 Row 实例
cursor.execute("select * from Person")
for row in cursor:
    print(row)                      # <sqlite3.Row object>
cursor.close()
conn.commit()
conn.close()

4. SQLite 和 Python 数据类型转换

SQLite 原生支持如下的 5 种类型: NULL, INTEGER, REAL, TEXT, BLOB

因此可以将以下 Python 类型发送到 SQLite 而不会出现任何问题:

SAVE DATA
-------------------------------------------
Python          -->          SQLite
-------------------------------------------
None             |           NULL
int              |           INTEGER
float            |           REAL
str              |           TEXT
bytes            |           BLOB
-------------------------------------------

SQLite 类型默认转换为 Python 类型的方式:

READ DATA
-------------------------------------------
SQLite          -->          Python
-------------------------------------------
NULL             |           None
INTEGER          |           int
REAL             |           float
TEXT             |           str
BLOB             |           bytes
-------------------------------------------

5. 数据库备份

将现有数据库复制到另一个数据库中：

import sqlite3
conn1 = sqlite3.connect("test1.db")
conn2 = sqlite3.connect("test2.db")
# 将数据库 test1.db 复制到 test2.db 中（如果 test2.db 已存在, 将被覆盖）
conn1.backup(conn2)
conn2.commit()
conn1.close()
conn2.close()

将现有数据库复制到临时副本中：

import sqlite3
conn1 = sqlite3.connect("test1.db")
conn2 = sqlite3.connect(":memory:")
conn1.backup(conn2)
conn2.commit()
conn1.close()
conn2.close()

6. 使用 Connection 作为上下文管理器

连接对象Connection支持使用with用来作为上下文管理器，它可以自动提交或者回滚事务。如果出现异常，事务会被回滚conn.rollback()；否则，事务会被提交conn.commit()。

import sqlite3
conn = sqlite3.connect(":memory:")
conn.execute("create table Person(id integer primary key, name text unique)")
# 成功后, conn.commit() 会自动调用
with conn:
    conn.execute("insert into Person(name) values (?)", ("Tom",))
# 当 with 块以异常结束后会自动调用 conn.rollback(), 并且异常仍然会被抛出, 必须手动捕获异常。
try:
    with conn:
        conn.execute("insert into Person(name) values (?)", ("Tom",))
except sqlite3.IntegrityError as e:
    print("Error: " + str(e))       # Error: UNIQUE constraint failed: Person.name
# 作为上下文管理器的连接对象只提交或回滚事务, 还需要手动关闭连接对象
conn.close()