sqlite3小型数据库详细全面介绍

sqlite3小型数据库详细全面介绍

1.概述

sqlite3 模块基于 Python DB-API 2.0 实现了与 SQLite（进程内关系型数据库） 兼容的接口。SQLite 被设计为主要应用在应用程序内部，不像 MySQL， PostgreSQL 或者 Oracle 那样需要一个单独的数据库服务器。它快速，灵活并且经过严格测试，适用于一些应用的原型和生产部署。

2.sqlite数据库和表操作

2.1.创建数据库

SQLite 数据库做为单个文件存储在文件系统中。它管理对文件的访问，包括锁定文件以防止多个写入时发生损坏。数据库在第一次访问文件时创建，如果这个数据库已经创建，不会重复创建。

创建数据库非常简单，只需要使用connect() 打开一个文件，如果这个文件不存在就会创建数据库。一个db结尾的文件就是一个数据库。

import osimport sqlite3db_filename = '../todo.db'db_is_new = not os.path.exists(db_filename)conn = sqlite3.connect(db_filename)if db_is_new:print('Need to create schema')else:print('Database exists, assume schema does, too.')conn.close()

第一次运行上面的代码，todo.db不存在则会创建该文件，也就是创建一个数据库。第二次运行发现已经存在了就不会创建只是打开数据库。

# 第一次运行提示没有这个数据库，他会自动创建数据库Need to create schema# 第二次运行提示数据库已存在，不会重复创建Database exists, assume schema does, too.

2.2.创建表

创建表使用SQL语句，sqlite提供了两个常用的执行SQL语句方法，execute() 执行单条SQL语句 和 executescript() 执行批量SQL语句。

1.批量执行SQL语句创建表executescript

下面创建一个todo_schema.sql 文件，写入创建项目和任务两个表格SQL语句

-- to-do 应用示例的数据库表结构-- 项目表是由任务组成的create table project (nametext primary key,description text,deadlinedate);-- 任务是完成项目的步骤create table task (id integer primary key autoincrement not null,priority integer default 1,detailstext,status text,deadline date,completed_on date,projecttext not null references project(name));

Connection 中的 executescript() 方法可以用来执行 DDL 指令去创建数据库表结构

import osimport sqlite3db_filename = 'todo.db'schema_filename = 'todo_schema.sql'db_is_new = not os.path.exists(db_filename)with sqlite3.connect(db_filename) as conn:if db_is_new:print('Creating schema')# 读取sql文件内容，创建表格with open(schema_filename, 'rt') as f:schema = f.read()conn.executescript(schema)print('Inserting initial data')# 执行sql语句，向表中插入数据conn.executescript("""insert into project (name, description, deadline)values ('pymotw', 'Python Module of the Week','2016-11-01');insert into task (details, status, deadline, project)values ('write about select', 'done', '2016-04-25','pymotw');insert into task (details, status, deadline, project)values ('write about random', 'waiting', '2016-08-22','pymotw');insert into task (details, status, deadline, project)values ('write about sqlite3', 'active', '2017-07-31','pymotw');""")else:print('Database exists, assume schema does, too.')

3.增删改查操作

3.1.增加数据

1.增加单条数据execute

import sqlite3db_filename = 'todo.db'insql = "insert into project (name, description, deadline) values ('py3', 'Python Module of the Week','2016-11-01');"with sqlite3.connect(db_filename) as conn:# 创建游标cour = conn.cursor()# 执行sql语句cour.execute(insql)# 提交数据conn.commit()# 打印受影响的行数print(cour.rowcount)

2.批量插入executemany

要将相同的 SQL 指令应用于大量数据，请使用 executemany() 方法。 它对于加载数据很有用，因为它避免了在 Python 中循环输入，并对底层的循环进行了优化.
executemany() 可以批量插入数据，List中数据类型必须为Tuple元组eg. [(0, 3132), (1, 1298), (2, 6543), (3, 4553) ……]

cur.executemany('INSERT INTO test VALUES (?,?,?)',[(3,'name3',19),(4,'name4',26)])

下面的示例程序使用 csv 模块从一个 csv 文件中读取一系列数值并将它们加载到数据库中。
tasks.csv 文件包含如下内容

deadline,project,priority,details2016-11-30,pymotw,2,"finish reviewing markup"2016-08-20,pymotw,2,"revise chapter intros"2016-11-01,pymotw,1,"subtitle"

将csv文件中的数据批量添加到数据库

import csvimport sqlite3import sysdb_filename = 'todo.db'data_filename = sys.argv[1]SQL = """insert into task (details, priority, status, deadline, project)values (:details, :priority, 'active', :deadline, :project)"""with open(data_filename, 'rt') as csv_file: csv_reader = csv.DictReader(csv_file) with sqlite3.connect(db_filename) as conn: cursor = conn.cursor() cursor.executemany(SQL, csv_reader)

3.占位符插入数据

在代码中操作sql时经常会将可变的内容替换为变量，例如插入sql语句中value后面的值就是可变部分。这些值会如果写成固定值不符合业务，所以需要用占位符占位，然后用变量替换它。

# sql语句，value后面的值用占位符代替sql = 'INSERT INTO student(sname, sage) VALUES (?, ?);'# 构建数据元组student_data = ('沐沐',18)# 执行sql语句cour.execute(sql, student_data)# 提交数据-同步到数据库文件-增删改查，除了查询以外有需要进行提交conn.commit()

3.2.删除数据

1.删除单条数据

sql = 'delete from student where id=?'# 构建数据元组student_data = (1,)# 执行sql语句cour.execute(sql, student_data)# 提交数据-同步到数据库文件-增删改查，除了查询以外有需要进行提交conn.commit()

2.删除多条数据

sql = 'delete from student where id=?'# 构建数据元组列表student_data = [(2,), (3,), (4,)]# 执行sql语句cour.executemany(sql, student_data)# 提交数据-同步到数据库文件-增删改查，除了查询以外有需要进行提交conn.commit()

3.3.修改数据

sql = 'update student set sname=?, sage=? where id=?'# 构建数据元组student_data = ('小小', 18, 1)# 执行sql语句cour.execute(sql, student_data)# 提交数据-同步到数据库文件-增删改查，除了查询以外有需要进行提交conn.commit()

3.4.查询数据

查询分为两个步骤，执行 execute() 方法向数据库提交sql语句。然后，使用 fetchall() 方法去取回结果。
fetchone() 一次取出一个结果
fetchall()取回所有结果，返回元组
fetchmany() 批量取出固定数量的结果。

1.查询所有数据

import sqlite3db_filename = 'todo.db'with sqlite3.connect(db_filename) as conn:cursor = conn.cursor()cursor.execute("""select id, priority, details, status, deadline from taskwhere project = 'pymotw'""")# fetchall()取回所有结果，返回元组for row in cursor.fetchall():task_id, priority, details, status, deadline = rowprint('{:2d} [{:d}] {:<25} [{:<8}] ({})'.format(task_id, priority, details, status, deadline))

2.查询固定数量数据fetchmany

import sqlite3db_filename = 'todo.db'with sqlite3.connect(db_filename) as conn:cursor = conn.cursor()cursor.execute("""select name, description, deadline from projectwhere name = 'pymotw'""")name, description, deadline = cursor.fetchone()print('Project details for {} ({})\ndue {}'.format(description, name, deadline))cursor.execute("""select id, priority, details, status, deadline from taskwhere project = 'pymotw' order by deadline""")# 取出固定数量print('\nNext 5 tasks:')for row in cursor.fetchmany(5):task_id, priority, details, status, deadline = rowprint('{:2d} [{:d}] {:<25} [{:<8}] ({})'.format(task_id, priority, details, status, deadline))

3.5.查询元信息

依据 DB-API 2.0 的规范，在调用 execute() 方法后， 游标 应该设置其 description 属性去保存 fetch 方法返回的数据信息。这些数据信息应该是包含字段名称，类型，显示大小，内部大小，精度，比例和是否接受空值的一系列元组。

import sqlite3db_filename = 'todo.db'with sqlite3.connect(db_filename) as conn:cursor = conn.cursor()cursor.execute("""select * from task where project = 'pymotw'""")print('Task table has these columns:')for colinfo in cursor.description:print(colinfo)

由于 sqlite3 不会对插入数据库的数据进行类型或大小的限制，因此这里只填入字段名称。

python3 sqlite3_cursor_description.pyTask table has these columns:('id', None, None, None, None, None, None)('priority', None, None, None, None, None, None)('details', None, None, None, None, None, None)('status', None, None, None, None, None, None)('deadline', None, None, None, None, None, None)('completed_on', None, None, None, None, None, None)('project', None, None, None, None, None, None)

3.6.行对象

默认情况下，fetch 方法从数据库中返回的值是以「行」的形式的元组。 调用方需要依据查询语句中的字段顺序并从元组中得到各个值。 当查询的数量增加或处理数据的代码在不同的地方时，使用对象结合字段名称得到查询结果通常会更方便。 这样，随着查询语句的变化，元组内容的数量和顺序也可以相应的变化，并且依赖查询结果去执行的代码不容易受到影响。

Connection 对象有一个 row_factory 属性，该属性允许调用的代码去控制查询结果集中的每行的对象类型。 sqlite3 还包含一个 Row 类，可以用作行工厂，即 row_factory 。 字段的值可以通过 Row 的实例对象的索引或名称访问。

总结：通过Row类对象指定返回结果集字段显示先后位置

import sqlite3db_filename = 'todo.db'with sqlite3.connect(db_filename) as conn:# 修改行工厂为 Rowconn.row_factory = sqlite3.Rowcursor = conn.cursor()cursor.execute("""select name, description, deadline from projectwhere name = 'pymotw'""")name, description, deadline = cursor.fetchone()print('Project details for {} ({})\ndue {}'.format(description, name, deadline))cursor.execute("""select id, priority, status, deadline, details from taskwhere project = 'pymotw' order by deadline""")# 通过Row对象指定返回结果集字段展示的位置print('\nNext 5 tasks:')for row in cursor.fetchmany(5):print('{:2d} [{:d}] {:<25} [{:<8}] ({})'.format(row['id'], row['priority'], row['details'],row['deadline'], row['status'],))

运行结果

Project details for Python Module of the Week (pymotw)due 2016-11-01Next 5 tasks: 1 [1] write about select[2016-04-25] (done) 2 [1] write about random[2016-08-22] (waiting) 3 [1] write about sqlite3 [2017-07-31] (active)

3.7.命名参数

当sql中某个变量需要重复使用时，为了避免重复创建变量，应使用命名参数，它相当于定义一个公共变量，重复使用。

import sqlite3import sysdb_filename = 'todo.db'id = int(sys.argv[1])status = sys.argv[2]with sqlite3.connect(db_filename) as conn:cursor = conn.cursor()query = "update task set status = :status where id = :id"cursor.execute(query, {'status': status, 'id': id})

这个例子中 update 语句使用了两个命名参数。 id 用于查找出要修改的正确的行，status 用于将值写入表中

1 [1] write about select[done] (2016-04-25)2 [1] write about random[done] (2016-08-22)3 [1] write about sqlite3 [active] (2017-07-31)

4.定义新的字段类型

SQLite 本身就支持整数，浮点数和文本字段。 这些类型的数据由 sqlite3 模块从 Python 中表示的值自动转换为可以存储在数据库中的值，并根据需要返回。 整数值在数据库中会加载到 int 或 long 变量中，具体取决于值的大小。 如果 Connection 的 text_factory 属性未被更改，文本值的保存和取出都是 str 。

尽管 SQLite 内部只支持很少的数据类型，但 sqlite3 模块包含了定义自定义类型的工具，以允许 Python 应用程序在字段中存储任何类型的数据。
在数据库连接时使用 detect_types 标志可以开启对默认支持字段类型以外的类型进行转换。如果在定义表时需要自定义的字段，请使用 PARSE_DECLTYPES 。

4.1.转换查询结果字段类型

detect_types=sqlite3.PARSE_DECLTYPES：PARSE_DECLTYPES属性 解析返回的每一列的声明类型，然后使用类型转换器字典执行为该类型注册的转换器函数

下面的例子将时间字段自动转为date类型

import sqlite3import sysdb_filename = 'todo.db'sql = "select id, details, deadline from task"def show_deadline(conn):conn.row_factory = sqlite3.Rowcursor = conn.cursor()cursor.execute(sql)row = cursor.fetchone()for col in ['id', 'details', 'deadline']:print('{:<8}{!r:<26} {}'.format(col, row[col], type(row[col])))returnprint('Without type detection:')with sqlite3.connect(db_filename) as conn:show_deadline(conn)print('\nWith type detection:')with sqlite3.connect(db_filename,# 转换字段类型 detect_types=sqlite3.PARSE_DECLTYPES, ) as conn:show_deadline(conn)

sqlite3 模块使用了 datetime模块中的 date 类和 datetime 类对日期和时间字段提供转换器。类型检测打开时，这些日期相关的转换器也自动是可用状态。

Without type detection:id1<class 'int'>details 'write about select' <class 'str'>deadline'2016-04-25' <class 'str'>With type detection:id1<class 'int'>details 'write about select' <class 'str'>deadlinedatetime.date(2016, 4, 25) <class 'datetime.date'>

4.2.定义字段

当定义一个新的字段类型时需要注册两个函数。 适配器 函数用于将 Python 对象做为输入转换为一个字节串存储在数据库中。转换器 函数从数据库中取出数据转换为一个 Python 对象。使用 register_adapter() 可以注册一个适配器函数， register_converter() 可以注册一个转换器函数

import pickleimport sqlite3db_filename = 'todo.db'def adapter_func(obj):"""从内存转换对象为可存储"""print('adapter_func({})\n'.format(obj))return pickle.dumps(obj)def converter_func(data):"""从存储数据中转换为一个内存中的对象"""print('converter_func({!r})\n'.format(data))return pickle.loads(data)class MyObj:def __init__(self, arg):self.arg = argdef __str__(self):return 'MyObj({!r})'.format(self.arg)# 注册函数控制字段类型sqlite3.register_adapter(MyObj, adapter_func)sqlite3.register_converter("MyObj", converter_func)# 创建需要保存的一些对象。使用包含一系列元组# 的列表便于直接传入 executemany()函数。to_save = [(MyObj('this is a value to save'),),(MyObj(42),),]with sqlite3.connect(db_filename,detect_types=sqlite3.PARSE_DECLTYPES) as conn:# 创建一个表包含 "MyObj" 的字段类型conn.execute("""create table if not exists obj (idinteger primary key autoincrement not null,dataMyObj)""")cursor = conn.cursor()# 在数据库中插入对象cursor.executemany("insert into obj (data) values (?)", to_save)# 查询刚刚插入的结果cursor.execute("select id, data from obj")for obj_id, obj in cursor.fetchall():print('Retrieved', obj_id, obj)print('with type', type(obj))print()

这个示例使用 pickle 模块将一个对象转换为字符串存储在数据库中，这是一种存储任意对象很有用的方法，但它无法基于对象的属性进行查询。一个真正的 对象关系映射 系统，比如 SQLAlchemy 有其自己的存储字段的方式，对存储海量数据是非常有效的。

python3 sqlite3_custom_type.pyadapter_func(MyObj('this is a value to save'))adapter_func(MyObj(42))converter_func(b'\x80\x03c__main__\nMyObj\nq\x00)\x81q\x01}q\x02X\x03\x00\x00\x00argq\x03X\x17\x00\x00\x00this is a value to saveq\x04sb.')converter_func(b'\x80\x03c__main__\nMyObj\nq\x00)\x81q\x01}q\x02X\x03\x00\x00\x00argq\x03K*sb.')Retrieved 1 MyObj('this is a value to save')with type <class '__main__.MyObj'>Retrieved 2 MyObj(42)with type <class '__main__.MyObj'>

5.事物

关系型数据库的一个核心特性就是使用 事务 来保证一致的内部状态。启用事务后，多项数据变更可以在一个连接内实现而不会影响到其他用户，直到结果已经 提交 并刷新至实际的数据库中。

5.1.提交事物

无论通过 insert 还是 update 语句，若想数据库变更得以保存，必须明确的调用 commit() 方法。这项要求使得应用程序可以将多项相关的数据修改一起提交，所以这属于 原子性 而非增量性，而且避免了多个客户端同时连接至数据库仅看到部分数据更新了的情况。

调用 commit() 方法的效果可以通过一个程序内多个数据库连接来查看。
下面示例中，一条新的数据通过第一个连接插入数据库，然后另外两个连接尝试去读取它。

import sqlite3db_filename = 'todo.db'def show_projects(conn):cursor = conn.cursor()cursor.execute('select name, description from project')for name, desc in cursor.fetchall():print('', name)with sqlite3.connect(db_filename) as conn1:print('Before changes:')show_projects(conn1)# 插入一个游标cursor1 = conn1.cursor()cursor1.execute("""insert into project (name, description, deadline)values ('virtualenvwrapper', 'Virtualenv Extensions','2011-01-01')""")print('\nAfter changes in conn1:')show_projects(conn1)# 通过另一个连接查询，先不提交print('\nBefore commit:')with sqlite3.connect(db_filename) as conn2:show_projects(conn2)# 提交后通过另一个连接查询conn1.commit()print('\nAfter commit:')with sqlite3.connect(db_filename) as conn3:show_projects(conn3)

conn1 修改了数据提交前调用 show_projects() 方法，查询的结果看到的是修改后的数据。
conn2 看到的是修改前的，因为conn1还没有提交。在提交修改后， conn3 也能看到新插入的数据了。

Before changes: pymotwAfter changes in conn1: pymotw virtualenvwrapperBefore commit: pymotwAfter commit: pymotw virtualenvwrapper

5.2.回滚

未提交的数据修改也可以通过调用 rollback() 方法完全放弃修改。通常在一个 try:except 代码块中使用 commit() 方法和 rollback() 方法处理数据的提交和出现错误时的数据回滚。

import sqlite3db_filename = 'todo.db'def show_projects(conn):cursor = conn.cursor()cursor.execute('select name, description from project')for name, desc in cursor.fetchall():print('', name)with sqlite3.connect(db_filename) as conn:print('Before changes:')show_projects(conn)try:# 插入cursor = conn.cursor()cursor.execute("""delete from project where name = 'virtualenvwrapper' """)# 显示设置print('\nAfter delete:')show_projects(conn)# 假装处理导致一个错误raise RuntimeError('simulated error')except Exception as err:# 放弃数据变更print('ERROR:', err)conn.rollback()else:# 保存数据变更conn.commit()# 显示结果print('\nAfter rollback:')show_projects(conn)

在调用 rollback() 方法后，将不存在对数据库的修改。

python3 sqlite3_transaction_rollback.pyBefore changes: pymotw virtualenvwrapperAfter delete: pymotwERROR: simulated errorAfter rollback: pymotw virtualenvwrapper

5.3.隔离级别

sqlite3 支持三种锁定模式，称为 隔离级别，它用于防止连接之间发生不兼容的数据变更。当连接打开时，可以通过传递一个字符串作为 isolation_level 的参数来设置隔离级别，因此不同的连接可以使用不同的隔离级别。

下面的这个程序演示了不同的隔离级别使用不同的连接至同一个数据库的线程事件顺序的影响。创建了四个线程，两个线程用于写入数据更新已经存在的两行数据，另外两个线程尝试读取表 task 的所有行

import loggingimport sqlite3import sysimport threadingimport timelogging.basicConfig(level=logging.DEBUG,format='%(asctime)s (%(threadName)-10s) %(message)s',)db_filename = 'todo.db'isolation_level = sys.argv[1]def writer():with sqlite3.connect(db_filename,isolation_level=isolation_level) as conn:cursor = conn.cursor()cursor.execute('update task set priority = priority + 1')logging.debug('waiting to synchronize')ready.wait()# 同步线程logging.debug('PAUSING')time.sleep(1)conn.commit()logging.debug('CHANGES COMMITTED')def reader():with sqlite3.connect(db_filename,isolation_level=isolation_level) as conn:cursor = conn.cursor()logging.debug('waiting to synchronize')ready.wait()# 同步线程logging.debug('wait over')cursor.execute('select * from task')logging.debug('SELECT EXECUTED')cursor.fetchall()logging.debug('results fetched')if __name__ == '__main__':ready = threading.Event()threads = [threading.Thread(name='Reader 1', target=reader),threading.Thread(name='Reader 2', target=reader),threading.Thread(name='Writer 1', target=writer),threading.Thread(name='Writer 2', target=writer),][t.start() for t in threads]time.sleep(1)logging.debug('setting ready')ready.set()[t.join() for t in threads]

这些线程使用 threading 模块中的 Event 对象同步。 writer() 方法连接并改变数据库，但在事件触发前并未提交。 reader() 方法连接并读取数据库直到事件同步生效

1.延迟模式

默认的隔离模式是 DEFERRED 。使用延迟模式时，只有在一个数据变更发生时是延迟的，所有示例都是使用延迟模式的。

python3 sqlite3_isolation_levels.py DEFERRED2016-08-20 17:46:26,972 (Reader 1) waiting to synchronize2016-08-20 17:46:26,972 (Reader 2) waiting to synchronize2016-08-20 17:46:26,973 (Writer 1) waiting to synchronize2016-08-20 17:46:27,977 (MainThread) setting ready2016-08-20 17:46:27,979 (Reader 1) wait over2016-08-20 17:46:27,979 (Writer 1) PAUSING2016-08-20 17:46:27,979 (Reader 2) wait over2016-08-20 17:46:27,981 (Reader 1) SELECT EXECUTED2016-08-20 17:46:27,982 (Reader 1) results fetched2016-08-20 17:46:27,982 (Reader 2) SELECT EXECUTED2016-08-20 17:46:27,982 (Reader 2) results fetched2016-08-20 17:46:28,985 (Writer 1) CHANGES COMMITTED2016-08-20 17:46:29,043 (Writer 2) waiting to synchronize2016-08-20 17:46:29,043 (Writer 2) PAUSING2016-08-20 17:46:30,044 (Writer 2) CHANGES COMMITTED

2.即时模式

当一个数据变更发生时，为了防止其他游标也去修改数据库，即时模式会迅速锁定数据库直到事务提交。它适用于复杂的写入数据库，而且是读取多于写入，因为在事务处理时读取不会被阻塞。

python3 sqlite3_isolation_levels.py IMMEDIATE2016-08-20 17:46:30,121 (Reader 1) waiting to synchronize2016-08-20 17:46:30,121 (Reader 2) waiting to synchronize2016-08-20 17:46:30,123 (Writer 1) waiting to synchronize2016-08-20 17:46:31,122 (MainThread) setting ready2016-08-20 17:46:31,122 (Reader 1) wait over2016-08-20 17:46:31,122 (Reader 2) wait over2016-08-20 17:46:31,122 (Writer 1) PAUSING2016-08-20 17:46:31,124 (Reader 1) SELECT EXECUTED2016-08-20 17:46:31,124 (Reader 2) SELECT EXECUTED2016-08-20 17:46:31,125 (Reader 2) results fetched2016-08-20 17:46:31,125 (Reader 1) results fetched2016-08-20 17:46:32,128 (Writer 1) CHANGES COMMITTED2016-08-20 17:46:32,199 (Writer 2) waiting to synchronize2016-08-20 17:46:32,199 (Writer 2) PAUSING2016-08-20 17:46:33,200 (Writer 2) CHANGES COMMITTED

3.独占模式

独占模式会对数据库的所有读取和写入操作锁定。当数据库性能很重要时，应该慎用独占模式，因为每个独占连接都会对其他用户阻塞

python3 sqlite3_isolation_levels.py EXCLUSIVE2016-08-20 17:46:33,320 (Reader 1) waiting to synchronize2016-08-20 17:46:33,320 (Reader 2) waiting to synchronize2016-08-20 17:46:33,324 (Writer 2) waiting to synchronize2016-08-20 17:46:34,323 (MainThread) setting ready2016-08-20 17:46:34,323 (Reader 1) wait over2016-08-20 17:46:34,323 (Writer 2) PAUSING2016-08-20 17:46:34,323 (Reader 2) wait over2016-08-20 17:46:35,327 (Writer 2) CHANGES COMMITTED2016-08-20 17:46:35,368 (Reader 2) SELECT EXECUTED2016-08-20 17:46:35,368 (Reader 2) results fetched2016-08-20 17:46:35,369 (Reader 1) SELECT EXECUTED2016-08-20 17:46:35,369 (Reader 1) results fetched2016-08-20 17:46:35,385 (Writer 1) waiting to synchronize2016-08-20 17:46:35,385 (Writer 1) PAUSING2016-08-20 17:46:36,386 (Writer 1) CHANGES COMMITTED

由于第一次写入已经开始修改数据，读取和第二次写入将阻塞直到第一次写入提交。 sleep() 在写入线程中引入了一个假设的延迟，为了突显出其他连接正在阻塞。

4.自动提交模式

将连接的 isolation_level 参数设为 None 会开启自动提交模式。当开启该模式时，每条 execute() 方法中的 SQL 语句执行后会立即提交。自动提交模式适用于简短的事务，例如插入少量数据至单个表。数据库被锁定的时间会尽可能的短，所以不同线程竞争的机会也会小很多。

在 sqlite3_autocommit.py 中，隔离级别被设为 None ，也移除了显式的调用 commit() 方法。输出是不同的，但两个写入的线程在任一读取线程开始查询时已经完成了。

python3 sqlite3_autocommit.py2016-08-20 17:46:36,451 (Reader 1) waiting to synchronize2016-08-20 17:46:36,451 (Reader 2) waiting to synchronize2016-08-20 17:46:36,455 (Writer 1) waiting to synchronize2016-08-20 17:46:36,456 (Writer 2) waiting to synchronize2016-08-20 17:46:37,452 (MainThread) setting ready2016-08-20 17:46:37,452 (Reader 1) wait over2016-08-20 17:46:37,452 (Writer 2) PAUSING2016-08-20 17:46:37,452 (Reader 2) wait over2016-08-20 17:46:37,453 (Writer 1) PAUSING2016-08-20 17:46:37,453 (Reader 1) SELECT EXECUTED2016-08-20 17:46:37,454 (Reader 2) SELECT EXECUTED2016-08-20 17:46:37,454 (Reader 1) results fetched2016-08-20 17:46:37,454 (Reader 2) results fetched

6.内存数据库

SQLite 支持在 RAM 中管理整个数据库，而无需一个硬盘文件。内存数据库对自动化测试非常有用，这种情况下，数据在不同的测试运行时不需要保留。或者在实验一些模式或其他数据库特性时，内存数据库也很有用。要打开一个内存数据库，在创建 Connection 时使用 ‘:memory:’ 字符串代替文件名称即可。每个 ‘:memory:’ 连接都会创建一个单独的数据库实例，所以一个实例的数据变更不会影响到其他连接

6.1.导出数据库内容

内存数据库的内容可以使用 Connection 的 iterdump() 方法保存。iterdump() 方法返回一个迭代器会产生一系列字符串，这些字符串一起构建 SQL 指令以重建数据库状态

import sqlite3schema_filename = 'todo_schema.sql'with sqlite3.connect(':memory:') as conn:conn.row_factory = sqlite3.Rowprint('Creating schema')with open(schema_filename, 'rt') as f:schema = f.read()conn.executescript(schema)print('Inserting initial data')conn.execute("""insert into project (name, description, deadline)values ('pymotw', 'Python Module of the Week','2010-11-01')""")data = [('write about select', 'done', '2010-10-03', 'pymotw'),('write about random', 'waiting', '2010-10-10', 'pymotw'),('write about sqlite3', 'active', '2010-10-17', 'pymotw'),]conn.executemany("""insert into task (details, status, deadline, project)values (?, ?, ?, ?)""", data)print('Dumping:')for text in conn.iterdump():print(text)

iterdump() 也可用于保存数据库为文件，但它对于一个不应被保存的数据库做防护会更有用。这里的输出结果在保证语法正确的情况下适配了页面的展示。

python3 sqlite3_iterdump.pyCreating schemaInserting initial dataDumping:BEGIN TRANSACTION;CREATE TABLE project (nametext primary key,description text,deadlinedate);INSERT INTO "project" VALUES('pymotw','Python Module of theWeek','2010-11-01');DELETE FROM "sqlite_sequence";INSERT INTO "sqlite_sequence" VALUES('task',3);CREATE TABLE task (id integer primary key autoincrement not null,priority integer default 1,detailstext,status text,deadline date,completed_on date,projecttext not null references project(name));INSERT INTO "task" VALUES(1,1,'write aboutselect','done','2010-10-03',NULL,'pymotw');INSERT INTO "task" VALUES(2,1,'write aboutrandom','waiting','2010-10-10',NULL,'pymotw');INSERT INTO "task" VALUES(3,1,'write aboutsqlite3','active','2010-10-17',NULL,'pymotw');COMMIT;

7.在 SQL 中使用 Python 函数

SQL 语法支持在查询语句中调用函数，无论是字段列表还是 select 语句或者 where 子句。 这项特性支持在将数据从查询中返回前处理数据，并可用于在不同数据格式之间转换，在纯 SQL 中执行笨拙的计算，并重用应用程序代码。

import codecsimport sqlite3db_filename = 'todo.db'def encrypt(s):print('Encrypting {!r}'.format(s))return codecs.encode(s, 'rot-13')def decrypt(s):print('Decrypting {!r}'.format(s))return codecs.encode(s, 'rot-13')with sqlite3.connect(db_filename) as conn:# 第一个参数是上面定义的函数conn.create_function('encrypt', 1, encrypt)conn.create_function('decrypt', 1, decrypt)cursor = conn.cursor()# 原始的值print('Original values:')query = "select id, details from task"cursor.execute(query)for row in cursor.fetchall():print(row)print('\nEncrypting...')query = "update task set details = encrypt(details)"cursor.execute(query)print('\nRaw encrypted values:')query = "select id, details from task"cursor.execute(query)for row in cursor.fetchall():print(row)print('\nDecrypting in query...')query = "select id, decrypt(details) from task"cursor.execute(query)for row in cursor.fetchall():print(row)print('\nDecrypting...')query = "update task set details = decrypt(details)"cursor.execute(query)

Connection 中的 create_function() 方法会将函数暴露出。参数包括函数在 SQL 中使用时的名称，函数需要多少个参数，在 Python 中要暴露的函数。

python3 sqlite3_create_function.pyOriginal values:(1, 'write about select')(2, 'write about random')(3, 'write about sqlite3')(4, 'finish reviewing markup')(5, 'revise chapter intros')(6, 'subtitle')Encrypting...Encrypting 'write about select'Encrypting 'write about random'Encrypting 'write about sqlite3'Encrypting 'finish reviewing markup'Encrypting 'revise chapter intros'Encrypting 'subtitle'Raw encrypted values:(1, 'jevgr nobhg fryrpg')(2, 'jevgr nobhg enaqbz')(3, 'jevgr nobhg fdyvgr3')(4, 'svavfu erivrjvat znexhc')(5, 'erivfr puncgre vagebf')(6, 'fhogvgyr')Decrypting in query...Decrypting 'jevgr nobhg fryrpg'Decrypting 'jevgr nobhg enaqbz'Decrypting 'jevgr nobhg fdyvgr3'Decrypting 'svavfu erivrjvat znexhc'Decrypting 'erivfr puncgre vagebf'Decrypting 'fhogvgyr'(1, 'write about select')(2, 'write about random')(3, 'write about sqlite3')(4, 'finish reviewing markup')(5, 'revise chapter intros')(6, 'subtitle')Decrypting...Decrypting 'jevgr nobhg fryrpg'Decrypting 'jevgr nobhg enaqbz'Decrypting 'jevgr nobhg fdyvgr3'Decrypting 'svavfu erivrjvat znexhc'Decrypting 'erivfr puncgre vagebf'Decrypting 'fhogvgyr'

8.在查询中使用正则

Sqlite 支持多种与 SQL 语法相关的特殊用户函数。 例如，regexp 函数可以在查询中使用，用下面的语法可以检查字段的字符串值是否与正则表达式匹配。

SELECT * FROM tableWHERE column REGEXP '.*pattern.*'

下面这个示例使用 Python 的 re模块将一个函数和 regexp() 关联起来去测试正确的值

import reimport sqlite3db_filename = 'todo.db'def regexp(pattern, input):return bool(re.match(pattern, input))with sqlite3.connect(db_filename) as conn:conn.row_factory = sqlite3.Rowconn.create_function('regexp', 2, regexp)cursor = conn.cursor()pattern = '.*[wW]rite [aA]bout.*'cursor.execute("""select id, priority, details, status, deadline from taskwhere details regexp :patternorder by deadline, priority""",{'pattern': pattern},)for row in cursor.fetchall():task_id, priority, details, status, deadline = rowprint('{:2d} [{:d}] {:<25} [{:<8}] ({})'.format(task_id, priority, details, status, deadline))

输出是所有匹配的字段的详细信息。

python3 sqlite3_regex.py 1 [9] write about select[done] (2016-04-25) 2 [9] write about random[done] (2016-08-22) 3 [9] write about sqlite3 [active] (2017-07-31)

9.自定义聚合

聚合函数表示会将许多单个的值以某种方式处理汇总为一个值。例如常见的内置函数 avg() ， min()， max()， count()。

sqlite3 中的聚合 API 需要定义一个类包含两个方法。 step() 方法用于在处理查询时对每一个数据调用一次。 finalize() 方法用于在查询结束需要对聚合结果进行返回时调用。下面示例实现了一个计算模式的聚合器，它会返回输入的最频繁的值。

import sqlite3import collectionsdb_filename = 'todo.db'class Mode:def __init__(self):self.counter = collections.Counter()def step(self, value):print('step({!r})'.format(value))self.counter[value] += 1def finalize(self):result, count = self.counter.most_common(1)[0]print('finalize() -> {!r} ({} times)'.format(result, count))return resultwith sqlite3.connect(db_filename) as conn:conn.create_aggregate('mode', 1, Mode)cursor = conn.cursor()cursor.execute("""select mode(deadline) from task where project = 'pymotw'""")row = cursor.fetchone()print('mode(deadline) is:', row[0])

聚合类通过 Connection 的 create_aggregate() 方法注册。参数为在 SQL 语句中要使用的方法名称，聚合类中 step() 方法需要的参数数量，以及聚合类。

python3 sqlite3_create_aggregate.pystep('2016-04-25')step('2016-08-22')step('2017-07-31')step('2016-11-30')step('2016-08-20')step('2016-11-01')finalize() -> '2016-11-01' (1 times)mode(deadline) is: 2016-11-01