首先连接到数据库，获得 connection 对象，然后再获得 cursor，使用 cursor 来执行 sql 语句并 获取结果。

连接

import sqlite3

db = sqlite3.connect("database.db")  # connections
db.execute("CREATE TABLE books (id int primary key, name text)")
db.execute("INSERT INTO books (name) VALUES ('war and peace')")
db.execute("INSERT INTO books (name) VALUES ('the bible')")
db.commit()  # always remember to commit
db.execute("SELECT * FROM books")

db.close()

# 或者使用 with 语句
with sqlite3.connect("db") as db:
    # cursor executes

虽然 Python 的 DB API 2.0 规范是要求使用 db.cursor() 获得一个 cursor 来执行 execute, 但是 对 sqlite 来说完全没有必要，直接用 db.execute 这种快捷方式就好了。

构建语句

不要用 Python 自带的字符串格式化，可能有 sql 注入的风险，要使用问号或者冒号格式化。

# fabricating statement
db.execute("select from books where name = ?", ["the bible"]) 
# NOTE the param must be a sequence

# You could also use named placeholders
db.execute("insert into books (name) values (:name)", {name: "the bible"})

获取数据

有两种方式：

使用 fetchone, fetchmany(n), fetchall

r = db.execute("SELECT id FROM stocks WHERE name = 'MSFT'")
id = r.fetchone()[0]

直接迭代返回结果

for row in db.execute("SELECT * FROM stocks ORDER BY price"):
    print row[0], row[1]

注意结果每行是一个 tuple，即使 select 了一个元素，结果也是 tuple。

如果我们想要返回一个字典，需要更改一下 row_factory, 推荐使用 sqlite3.Row

db.row_factory = sqlite3.Row

lastrowid 属性。This read-only attribute provides the rowid of the last modified row. It is only set if you issued a INSERT statement using the execute()method. For operations other than INSERT or when executemany() is called, lastrowid is set to None.

加载拓展

Python 的 sqlite 可能没有加载任何拓展（咋想的？), 想要加载拓展，需要手动加载：

db.enable_load_extension(True)
db.load_extension("fts5")
db.enable_load_extension(False)

自定义类型

sqlite 中只包含了有限的几种数据类型，甚至连 datetime 都没有。在 Python 中可以自定义数据 类型，而且已经内置了 date 和 timestamp 两个类型的转换。

只需要在创建连接的时候指定对应的 flag, 然后在创建表的时候指定好数据类型是 date 和 timestamp 就好了，sqlite 内部使用数字存储，但是在 Python 中会自动转换。

db = sqlite3.connect(":memory:", detect_types=sqlite3.PARSE_DECLTYPES|sqlite3.PARSE_COLNAMES)
db.execute("create table test(d date, ts timestamp)")
today = datetime.date.today()
now = datetime.datetime.now()

db.execute("insert into test(d, ts) values (?, ?)", (today, now))
cur = db.execute("select d, ts from test")
row = cur.fetchone()
print(today, "=>", row[0], type(row[0]))
print(now, "=>", row[1], type(row[1]))

事务

默认情况下，sqlite 本身是在 autocommit 模式执行的，也就是说语句提交了就自动 commit 了。 但是在 Python 中，默认不是在 autocommit 模式下的，而且会隐式插入一条 begin 语句 开启一个事务，这样的好处是可以使用 with 语句，当抛出异常的时候直接回滚。也可以关闭这种 模式，直接使用 sqlite 自己的 autocommit 模式，这样也不用手工 commit 了或者使用 with 语句了。

# 使用事务
with db:
  for _ in range(10):
    db.execute("inset into books (name) values (1)")

# 直接 autocommit
db.isolation_level=None

备份

相当于 sqlite3 的 .backup 命令

target = sqlite3.connect("backup.db")
db.backup(target)

异常

分为两类：

sqlite3.Warning
sqlite3.Error
  - sqlite3.DatabaseError
  - sqlite3.IntegrityError
  - sqlite3.ProgrammingError
  - sqlite3.OperationalError
  - sqlite3.NotSupportedError

多线程

默认情况下，sqlite3 禁止多线程使用同一个链接，可以使用 check_same_thread 参数来控制该行为

db = sqlite3.connect("sample.db", check_same_thread=False)

不过这时候就需要自己来保证序列顺序了。

使用最新版本的 sqlite3

要想使用最新版本的 sqlite3, 要么在编译的时候已经安装了，要么就需要指定一下链接的路径。

export CFLAGS="-DSQLITE_ENABLE_FTS3 \
  -DSQLITE_ENABLE_FTS3_PARENTHESIS \
  -DSQLITE_ENABLE_FTS4 \
  -DSQLITE_ENABLE_FTS5 \
  -DSQLITE_ENABLE_JSON1 \
  -DSQLITE_ENABLE_LOAD_EXTENSION \
  -DSQLITE_ENABLE_RTREE \
  -DSQLITE_ENABLE_STAT4 \
  -DSQLITE_ENABLE_UPDATE_DELETE_LIMIT \
  -DSQLITE_SOUNDEX \
  -DSQLITE_TEMP_STORE=3 \
  -DSQLITE_USE_URI \
  -O2 \
  -fPIC"
export PREFIX="/usr/local"
LIBS="-lm" ./configure --disable-tcl --enable-shared --enable-tempstore=always --prefix="$PREFIX"
make
sudo make install  # Install system-wide.

这时候还要在指定一下链接路径：

export LD_LIBRARY_PATH=/usr/local/lib

不过如果 prefix 直接使用了 /usr 就不需要了。

参考

1. https://docs.python.org/3/library/sqlite3.html