SQLite

SQLite是遵守ACID的关系数据库管理系统，它是一个C语言库，实现了小巧、 快速、自给、高可靠性、全功能SQL的数据库引擎。完整的SQLite数据库就是一个SQLite格式的文件。因为小巧，常作为嵌入式数据库，集成在应用程序。

SQLite实现了大多数SQL标准。它使用动态的、弱类型的SQL语法。SQLite的源代码和文档都位于公有领域，任何人可以免费自由的用于商业和非商业。

简介

时间轴

• 2000年8月17日，发布初始版本SQLite 1.0
• 2001年09年28日，发布SQLite 2.0.0
• 2004年06年18日，发布SQLite 3.0.0
• 2019年10日10日，发布SQLite 3.30.1

特点

了解更多 >> SQLite 文档：Features Of SQLite

数据类型

所有存入SQLite数据库的值都被保存为下表5中类型之一：

SQLite类型	描述
NULL	空值
INTEGER	整数，根据值的大小存储，最大8字节，即最大2^63-1=9223372036854775807。
REAL	浮点数, 存储为 8 字节的 IEEE 浮点数字。
TEXT	文本字符串，使用数据库编码（UTF-8、UTF-16BE 或 UTF-16LE）存储。
BLOB	二进制值，直接存储，不经过转换，如存储图片等。

布尔值在SQLite没有单独的存储类，使用INTEGER类的0 (false) 和 1 (true)存储。

SQLite也没有单独的日期时间数据类型，而是通过内置的时间日期函数，使用TEXT, REAL, 或INTEGER数据类型保存值。如下：

• TEXT，ISO8601格式的字符串 "YYYY-MM-DD HH:MM:SS.SSS"
• REAL，以Julian day numbers(公元前4713年1月1日中午12点)为起点开始计算，整数部分是天数，小数部分是当天时间数。
• INTEGER，unix时间戳，以1970-01-01 00:00:00 UTC为起点计算。

一般数据库每列数据为固定类型，但SQLite中可以将不同类型数据存储到同一列。为了最大化与和其他SQL数据库引擎之间的兼容性，SQLite使用一种"类型亲和性"（Type Affinity）概念。列的亲和类型是指该列的主要数据类型，当能够转化为该数据类型就转化，不能转化就按原数据类型存储。

了解更多 >> SQLite文档：SQLite 3 数据类型 python文档：sqlite3-SQLite 与 Python 类型 SQLite文档：限制列表

日期和时间

函数

SQLite有5个日期和时间函数，每个函数都有一个参数时间字符串（timestring），后面跟着零个或多个参数修饰符（modifier）字符串。strftime()函数多一个参数格式化字符串（format），作为其第一个参数。其中strftime()函数是最基础函数，其他4个函数都可以用strftime()表示。提供其他4个而不仅一个函数，原因是使用更简单方便。

函数	描述	示例
date(timestring, modifier, modifier, ...)	返回日期 等同于strftime('%Y-%m-%d', modifier, modifier, ...)	select date('2020-02-20'); 返回'2020-02-20' select date('2020-02-20', 'start of year'); 返回'2020-01-01' select date('now');返回'2020-02-20'
time(timestring, modifier, modifier, ...)	返回时间 等同于strftime('%H:%M:%S', modifier, modifier, ...)
datetime(timestring, modifier, modifier, ...)	返回日期时间 等同于strftime('%Y-%m-%d %H:%M:%S', modifier, modifier, ...)
julianday(timestring, modifier, modifier, ...)	返回Julian day天数 等同于strftime('%J', modifier, modifier, ...)	SELECT julianday('now') - julianday('2001-01-01');
strftime(format, timestring, modifier, modifier, ...)	返回格式化的日期时间字符串 输出格式format可选值： %d day of month: 00 %f fractional seconds: SS.SSS %H hour: 00-24 %j day of year: 001-366 %J Julian day number %m month: 01-12 %M minute: 00-59 %s seconds since 1970-01-01 %S seconds: 00-59 %w day of week 0-6 with Sunday==0 %W week of year: 00-53 %Y year: 0000-9999 %% %

时间字符串 Time Strings

时间字符串（timestring）可以为下面任意一种格式：

时间字符串格式	示例
YYYY-MM-DD	'2020-02-20'
YYYY-MM-DD HH:MM
YYYY-MM-DD HH:MM:SS
YYYY-MM-DD HH:MM:SS.SSS
YYYY-MM-DDTHH:MM
YYYY-MM-DDTHH:MM:SS
YYYY-MM-DDTHH:MM:SS.SSS
HH:MM
HH:MM:SS
HH:MM:SS.SSS
now	'now'
DDDDDDDDDD

修饰符 Modifiers

时间字符串后面可以跟0个或多个修饰符（modifier）。修饰符的顺序很重要，修饰符是从左到右依次转换数值。

修饰符	描述	示例
NNN days
NNN hours
NNN minutes
NNN.NNNN seconds
NNN months
NNN years
start of month
start of year
start of day
weekday N
unixepoch
localtime
utc

了解更多 >> SQLite文档：日期与时间函数

SQL

SQLite支持大多数标准的SQL。但是它确实省略了一些功能 ，同时又增加了一些自己的功能。

选取数据

基本选取

选取数据	语法	描述
选取单表所有数据	SELECT * FROM 表名;	* 号为通配符
选取单列数据	SELECT 列名 FROM 表名;
选取多列数据	SELECT 列名1, 列名2, 列名3 FROM 表名;	列名用逗号隔开，最后一个列名不加逗号。

数据库信息查询

查询一个数据库所有表名：

SELECT name FROME sqlite_master WHERE type='table' ORDER BY name;

以下示例，在Python中查询当前文件夹下'example.db'数据库文件所有的表，示例如下：

import sqlite3

conn = sqlite3.connect('example.db') 
c = conn.cursor()

c.execute("SELECT name FROM sqlite_master WHERE type='table' ORDER BY name;")
c.fetchall()

修改数据

删除数据

删除表中数据可以使用DELETE语句，使用时需注意指定删除那些记录，否则表中全部记录，DELETE语句格式如下：

DELETE FROM 表名称 WHERE  条件;

如

了解更多 >> SQLite 文档：SQLite 中的 SQL

进阶教程

数据库文件格式

完整的SQLite数据库是磁盘的一个文件，叫做“主数据库文件”（main database file）。在事务处理期间，SQLite将其他信息存储在称为“回滚日志”（rollback journal）的文件中，或者SQLite为WAL模式时，会有一个预先写入日志文件（write-ahead log file）。

了解更多 >> SQLite 文档：数据库文件格式

导入与导出

导入

工具	导入类型	描述
sqlite3	导入CSV	SQLite的命令工具 示例:

导出

工具	导出类型	描述
sqlite3	SQL	导出数据库的Schema，只包含数据库的描述如建表语句、视图索引等，不包含数据。示例： sqlite3 mysqlite.db .schema > schema.sql 导出数据库全部内容，示例： sqlite3 mysqlite.db .dump > dump.sql
	CSV	SQLite的命令工具 示例:sqlite3 -header -csv mysqlite.db "select * from 表名;" > 表名.csv
pandas	SQL	可以使用read_sql()函数读取，DataFrame.to_sql()函数保存。
	CSV、JSON、HTML、Excel、HDF5等	pandas提供多种函数，用于读取和保存。 保存CSV示例: import pandas as pd import sqlite3 conn = sqlite3.connect('mysqlite.db') df = pd.read_sql('SELECT * from 表名;', conn) df.to_csv('表名.csv')

Python中使用

标准库 sqlite3

Python 2.5及以上版本内置了sqlite3模块，在Python中使用SQLite，不需要安装任何东西，直接使用。sqlite3 模块由 Gerhard Häring 编写。它提供了符合 DB-API 2.0 规范的接口，这个规范是 PEP 249。

要使用这个模块，必须先创建一个 Connection 对象，它代表数据库。下面例子中，数据将存储在当前文件夹的 example.db 文件中，也可以使用 :memory: 来创建一个内存中的数据库。当有了 Connection 对象后，你可以创建一个 Cursor 游标对象，然后调用它的 execute() 方法来执行 SQL 语句：

import sqlite3

#创建Connection 对象
conn = sqlite3.connect('example.db') 

#创建Cursor 对象
c = conn.cursor()

# 调用execute()方法执行创建表格的sql语句
c.execute('''CREATE TABLE stocks
             (date text, trans text, symbol text, qty real, price real)''')

# 插入一行数据
c.execute("INSERT INTO stocks VALUES ('2006-01-05','BUY','RHAT',100,35.14)")

# 调用commit()方法，保存更改
conn.commit()

# 如果不需要使用可以关闭数据库连接
# 确认所有更改已经保存，不然会丢失。
conn.close()

模块函数和常量

函数或常量	描述	示例
sqlite3.version	这个模块的版本号，是一个字符串。不是 SQLite 库的版本号。	sqlite3.version
sqlite3.version_info	这个模块的版本号，是一个由整数组成的元组。不是 SQLite 库的版本号。	sqlite3.version_info
sqlite3.sqlite_version	模块使用的 SQLite 库的版本号，是一个字符串。	sqlite3.sqlite_version
sqlite3.sqlite_version_info	使用中的 SQLite 库的版本号，是一个整数组成的元组。	sqlite3.sqlite_version_info
sqlite3.PARSE_DECLTYPES	这个常量可以作为 connect() 函数的 detect_types 参数。 设置这个参数后，sqlite3 模块将解析它返回的每一列申明的类型。它会申明的类型的第一个单词，比如“integer primary key”，它会解析出“integer”，再比如“number(10)”，它会解析出“number”。然后，它会在转换器字典里查找那个类型注册的转换器函数，并调用它。
sqlite3.PARSE_COLNAMES	这个常量可以作为 connect() 函数的 detect_types 参数。 设置此参数可使得 SQLite 接口解析它所返回的每一列的列名。 它将在其中查找形式为 [mytype] 的字符串，然后将 'mytype' 确定为列的类型。 它将尝试在转换器字典中查找 'mytype' 条目，然后用找到的转换器函数来返回值。 在 Cursor.description 中找到的列名并不包括类型，举例来说，如果你在你的 SQL 中使用了像 'as "Expiration date [datetime]"' 这样的写法，那么我们将解析出在第一个 then we will parse out everything until the first '[' 之前的所有内容并去除前导空格作为列名：即列名将为 "Expiration date"。
sqlite3.connect(database[, timeout, detect_types, isolation_level, check_same_thread, factory, cached_statements, uri])	连接 SQLite 数据库。默认返回 Connection 对象，除非使用了自定义的 factory 参数。
sqlite3.register_converter(typename, callable)	注册一个回调对象 callable, 用来转换数据库中的字节串为自定的 Python 类型。所有类型为 typename 的数据库的值在转换时，都会调用这个回调对象。通过指定 connect() 函数的 detect-types 参数来设置类型检测的方式。注意，typename 与查询语句中的类型名进行匹配时不区分大小写。
sqlite3.register_adapter(type, callable)	注册一个回调对象 callable，用来转换自定义Python类型为一个 SQLite 支持的类型。 这个回调对象 callable 仅接受一个 Python 值作为参数，而且必须返回以下某个类型的值：int，float，str 或 bytes。
sqlite3.complete_statement(sql)	如果字符串 sql 包含一个或多个完整的 SQL 语句（以分号结束）则返回 True。它不会验证 SQL 语法是否正确，仅会验证字符串字面上是否完整，以及是否以分号结束。
sqlite3.enable_callback_tracebacks(flag)	默认情况下，您不会获得任何用户定义函数中的回溯消息，比如聚合，转换器，授权器回调等。如果要调试它们，可以设置 flag 参数为 True 并调用此函数。 之后，回调中的回溯信息将会输出到 sys.stderr。 再次使用 False 来禁用该功能。

Connection 对象

sqlite3 数据库连接对象（Connection）可以通过sqlite3.connect()函数创建，Connection 对象有如下的属性和方法：

属性或方法	描述	示例
isolation_level	获取或设置当前默认的隔离级别。 表示自动提交模式的 None 以及 "DEFERRED", "IMMEDIATE" 或 "EXCLUSIVE" 其中之一。 详细描述请参阅控制事务。
in_transaction	如果是在活动事务中（还没有提交改变），返回 True，否则，返回 False。它是一个只读属性。3.2 新版功能。
cursor(factory=Cursor)	这个方法接受一个可选参数 factory，如果要指定这个参数，它必须是一个可调用对象，而且必须返回 Cursor 类的一个实例或者子类。
commit()	这个方法提交当前事务。如果没有调用这个方法，那么从上一次提交 commit() 以来所有的变化在其他数据库连接上都是不可见的。如果你往数据库里写了数据，但是又查询不到，请检查是否忘记了调用这个方法。
rollback()	这个方法回滚从上一次调用 commit() 以来所有数据库的改变。
close()	关闭数据库连接。注意，它不会自动调用 commit() 方法。如果在关闭数据库连接之前没有调用 commit()，那么你的修改将会丢失！
execute(sql[, parameters])	这是一个非标准的快捷方法，它会调用 cursor() 方法来创建一个游标对象，并使用给定的 parameters 参数来调用游标对象的 execute() 方法，最后返回这个游标对象。
executemany(sql[, parameters])	这是一个非标准的快捷方法，它会调用 cursor() 方法来创建一个游标对象，并使用给定的 parameters 参数来调用游标对象的 executemany() 方法，最后返回这个游标对象。
executescript(sql_script)	这是一个非标准的快捷方法，它会调用 cursor() 方法来创建一个游标对象，并使用给定的 sql_script 参数来调用游标对象的 executescript() 方法，最后返回这个游标对象。
create_function(name, num_params, func, *, deterministic=False)	创建一个可以在 SQL 语句中使用的用户自定义函数，函数名为 name。 num_params 为该函数所接受的形参个数（如果 num_params 为 -1，则该函数可接受任意数量的参数）， func 是一个 Python 可调用对象，它将作为 SQL 函数被调用。 如果 deterministic 为真值，则所创建的函数将被标记为 deterministic，这允许 SQLite 执行额外的优化。 此旗标在 SQLite 3.8.3 或更高版本中受到支持，如果在旧版本中使用将引发 NotSupportedError。
create_aggregate(name, num_params, aggregate_class)	创建一个自定义的聚合函数。 参数中 aggregate_class 类必须实现两个方法：step 和 finalize。step 方法接受 num_params 个参数（如果 num_params 为 -1，那么这个函数可以接受任意数量的参数）；finalize 方法返回最终的聚合结果。 finalize 方法可以返回任何 SQLite 支持的类型：bytes，str，int，float 和 None。
create_collation(name, callable)	使用 name 和 callable 创建排序规则。这个 callable 接受两个字符串对象，如果第一个小于第二个则返回 -1， 如果两个相等则返回 0，如果第一个大于第二个则返回 1。注意，这是用来控制排序的（SQL 中的 ORDER BY），所以它不会影响其它的 SQL 操作。 注意，这个 callable 可调用对象会把它的参数作为 Python 字节串，通常会以 UTF-8 编码格式对它进行编码。
interrupt()	可以从不同的线程调用这个方法来终止所有查询操作，这些查询操作可能正在连接上执行。此方法调用之后， 查询将会终止，而且查询的调用者会获得一个异常。
set_authorizer(authorizer_callback)	此方法注册一个授权回调对象。每次在访问数据库中某个表的某一列的时候，这个回调对象将会被调用。如果要允许访问，则返回 SQLITE_OK，如果要终止整个 SQL 语句，则返回 SQLITE_DENY，如果这一列需要当做 NULL 值处理，则返回 SQLITE_IGNORE。这些常量可以在 sqlite3 模块中找到。
set_progress_handler(handler, n)	此例程注册回调。 对SQLite虚拟机的每个多指令调用回调。 如果要在长时间运行的操作期间从SQLite调用（例如更新用户界面），这非常有用。
set_trace_callback(trace_callback)	为每个 SQLite 后端实际执行的 SQL 语句注册要调用的 trace_callback。3.3 新版功能。
enable_load_extension(enabled)	此例程允许/禁止SQLite引擎从共享库加载SQLite扩展。 SQLite扩展可以定义新功能，聚合或全新的虚拟表实现。 一个众所周知的扩展是与SQLite一起分发的全文搜索扩展。默认情况下禁用可加载扩展。3.2 新版功能。
load_extension(path)	此例程从共享库加载SQLite扩展。 在使用此例程之前，必须使用 enable_load_extension() 启用扩展加载。默认情况下禁用可加载扩展。 3.2 新版功能。
row_factory	您可以将此属性更改为可接受游标和原始行作为元组的可调用对象，并将返回实际结果行。 这样，您可以实现更高级的返回结果的方法，例如返回一个可以按名称访问列的对象。
text_factory	使用此属性可以控制为 TEXT 数据类型返回的对象。 默认情况下，此属性设置为 str 和 sqlite3 模块将返回 TEXT 的 Unicode 对象。 如果要返回字节串，可以将其设置为 bytes。	conn.text_factory = bytes
total_changes	返回自打开数据库连接以来已修改，插入或删除的数据库行的总数。	conn.total_changes
iterdump()	返回以SQL文本格式转储数据库的迭代器。 保存内存数据库以便以后恢复时很有用。 此函数提供与 sqlite3 shell 中的 .dump 命令相同的功能。
backup(target, *, pages=0, progress=None, name="main", sleep=0.250)	即使在 SQLite 数据库被其他客户端访问时，或者同时由同一连接访问，该方法也会对其进行备份。 该副本将写入强制参数 target，该参数必须是另一个 Connection 实例。3.7 新版功能。

Cursor 对象

游标对象（Cursor ）可以通过Connection对象调用cursor()方法创建。Cursor 游标实例具有以下属性和方法。

属性或方法	描述	示例
execute(sql[, parameters])	执行SQL语句。 可以是参数化 SQL 语句（即，在 SQL 语句中使用占位符）。sqlite3 模块支持两种占位符：问号（qmark风格）和命名占位符（命名风格）。
executemany(sql, seq_of_parameters)	基于在序列 seq_of_parameters 中找到的所有形参序列或映射执行一条 SQL 命令。 sqlite3 模块还允许使用 iterator 代替序列来产生形参。
executescript(sql_script)	这是一个非标准的便捷方法，可用于一次执行多条 SQL 语句。 它会首先执行一条 COMMIT 语句，再执行以形参方式获取的 SQL 脚本。sql_script 可以是一个 str 类的实例。
fetchone()	获取一个查询结果集的下一行，返回一个单独序列，或是在没有更多可用数据时返回 None。
fetchmany(size=cursor.arraysize)	获取下一个多行查询结果集，返回一个列表。 当没有更多可用行时将返回一个空列表。 每次调用获取的行数由 size 形参指定。 如果没有给出该形参，则由 cursor 的 arraysize 决定要获取的行数。 此方法将基于 size 形参值尝试获取指定数量的行。 如果获取不到指定的行数，则可能返回较少的行。
fetchall()	获取一个查询结果的所有（剩余）行，返回一个列表。 请注意 cursor 的 arraysize 属性会影响此操作的执行效率。 当没有可用行时将返回一个空列表。
close()	立即关闭 cursor（而不是在当 __del__ 被调用的时候）。 从这一时刻起该 cursor 将不再可用，如果再尝试用该 cursor 执行任何操作将引发 ProgrammingError 异常。
rowcount	虽然 sqlite3 模块的 Cursor 类实现了此属性，但数据库引擎本身对于确定 "受影响行"/"已选择行" 的支持并不完善。
lastrowid	这个只读属性会提供最近修改行的 rowid。 它只在你使用 execute() 方法执行 INSERT 或 REPLACE 语句时会被设置。 对于 INSERT 或 REPLACE 以外的操作或者当 executemany() 被调用时，lastrowid 会被设为 None。在 3.6 版更改: 增加了 REPLACE 语句的支持。
arraysize	用于控制 fetchmany() 返回行数的可读取/写入属性。 该属性的默认值为 1，表示每次调用将获取单独一行。
description	这个只读属性将提供上一次查询的列名称。 为了与 Python DB API 保持兼容，它会为每个列返回一个 7 元组，每个元组的最后六个条目均为 None。
connection	这个只读属性返回 Cursor 对象所使用的 SQLite 数据库 Connection。

SQLite 与 Python 类型

SQLite类型	Python类型 （Python类型转SQLite类型）	Python类型 （SQLite类型转为Python类型，默认）
NULL	None	None
INTEGER	int	int
REAL	float	float
TEXT	str	取决于 text_factory , 默认为 str
BLOB	bytes	bytes

了解更多 >> Python 文档：sqlite3 - SQLite 与 Python 类型

了解更多 >> Python 文档：Python标准库 - sqlite3

pandas 函数

Pandas提供了一些SQL数据库接口函数，使用方便。pandas操作SQLite数据库默认使用python的sqlite3模块。主要为下面四个函数或方法：

函数或方法	描述	示例
read_sql()	通过SQL语句或表名读取数据生成DataFrame对象
read_sql_table()	read_sql()函数的封装，通过表名读取数据库生成DataFrame对象
read_sql_query()	read_sql()函数的封装，通过SQL语句读取数据库生成DataFrame对象
DataFrame.to_sql()	DataFrame对象保存到SQL类型数据库

读取数据库

使用read_sql_query()读取数据示例，如查询test.db数据库中stocks表的所有数据：

import pandas as pd
conn = sqlite3.connect('test.db')
df = pd.read_sql_query("select * from stocks;", conn)
print(df)

保存到数据库

了解更多 >> pandas 文档：IO接口 sql-queries

可能出现问题

• 问题：OperationalError: database is locked

可能由于前面使用写入数据库的连接没有关闭。使用后关闭 Connection 对象，比如前面设置Connection 对象实例的名称conn，调用conn.close()即可。

资源

官网

• SQLite 官网
• SQLite 文档
• SQLite 文档：sqlite3

参考文献

• 维基百科：SQLite