查看“SQLite”的源代码

SQLite是遵守ACID的[[关系数据库]]管理系统，它是一个[[C]]语言库，实现了小巧、 快速、自给、高可靠性、[https://www.sqlite.org/fullsql.html 全功能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

===特点===


{{了解更多
|[https://www.sqlite.org/features.html SQLite  文档：Features Of SQLite]
}}

==数据类型==

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

{| class="wikitable" style="width: 100%;
|-
! 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）概念。列的亲和类型是指该列的主要数据类型，当能够转化为该数据类型就转化，不能转化就按原数据类型存储。

{{了解更多
|[https://www.sqlite.org/datatype3.html SQLite文档：SQLite 3 数据类型]
|[https://docs.python.org/zh-cn/3.8/library/sqlite3.html#sqlite-and-python-types python文档：sqlite3-SQLite 与 Python 类型]
|[https://www.sqlite.org/limits.html SQLite文档：限制列表]
}}

==日期和时间==
===函数===
SQLite有5个日期和时间函数，每个函数都有一个参数时间字符串（timestring），后面跟着零个或多个参数修饰符（modifier）字符串。strftime()函数多一个参数格式化字符串（format），作为其第一个参数。其中strftime()函数是最基础函数，其他4个函数都可以用strftime()表示。提供其他4个而不仅一个函数，原因是使用更简单方便。
{| class="wikitable"
! 函数 !! 描述 !!  等同的strftime() !! 示例
|-
| '''date'''(timestring, modifier, modifier, ...) || 返回日期 || strftime('%Y-%m-%d',modifier, modifier, ...)||
|-
| '''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, ...)||
|-
| '''strftime'''(format, timestring, modifier, modifier, ...) || 返回格式化的日期时间字符串 || strftime(format, timestring, modifier, modifier, ...)||
|}

===时间字符串 Time Strings===
时间字符串（Time Strings）可以为下面任意一种格式：
{| class="wikitable" 
|-
! 时间字符串格式
! 时间字符串例
! 使用示例
|-
| YYYY-MM-DD
| '2020-02-20'
| date('2020-02-20', 'start of year')
|-
| 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'
| <code>SELECT date('now');</code> 计算当前日期
|-
| DDDDDDDDDD
| 
| 
|}


===修饰符 Modifiers===
时间字符串后面可以跟0个或多个修饰符（Modifiers）。修饰符的顺序很重要，修饰符是从左到右依次转换数值。
{| class="wikitable"
! 修饰符 !! 描述  !! 示例
|-
| 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 ||  || 
|}

{{了解更多
|[https://www.sqlite.org/lang_datefunc.html SQLite文档：日期与时间函数]
}}

==SQL==
SQLite支持大多数标准的[[SQL]]。但是它确实[https://www.sqlite.org/omitted.html 省略了一些功能] ，同时又增加了一些自己的功能。

===选取数据===
====基本选取====
{| class="wikitable"  style="width: 100%;
! 选取数据
! 语法
! 描述
|-
| 选取单表所有数据
| SELECT * FROM 表名;
| <code>*</code> 号为通配符
|-
| 选取单列数据
| SELECT 列名 FROM 表名;
|
|-
| 选取多列数据
| SELECT 列名1, 列名2, 列名3 FROM 表名;
| 列名用逗号隔开，最后一个列名不加逗号。
|-
|}

====数据库信息查询====
查询一个数据库所有表名：
 SELECT name FROME sqlite_master WHERE type='table' ORDER BY name;

以下示例，在Python中查询当前文件夹下'example.db'数据库文件所有的表，示例如下：
<syntaxhighlight lang="python">
import sqlite3

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

c.execute("SELECT name FROME sqlite_master WHERE type='table' ORDER BY name;")
c.fetchall()
</syntaxhighlight>
===修改数据===
====删除数据====
删除表中数据可以使用DELETE语句，使用时需注意指定删除那些记录，否则表中全部记录，DELETE语句格式如下：
 DELETE FROM 表名称 WHERE  条件;
如

{{了解更多 
|[https://www.sqlite.org/lang.html SQLite 文档：SQLite 中的 SQL]
}}

==进阶教程==
===数据库文件格式===
完整的SQLite数据库是磁盘的一个文件，叫做“主数据库文件”（main database file）。在事务处理期间，SQLite将其他信息存储在称为“回滚日志”（rollback journal）的文件中，或者SQLite为[https://www.sqlite.org/wal.html WAL模式]时，会有一个预先写入日志文件（write-ahead log file）。

{{了解更多 
|[https://www.sqlite.org/fileformat.html SQLite 文档：数据库文件格式]
}}
==Python中使用==
===标准库 sqlite3===
[[Python]] 2.5及以上版本内置了sqlite3模块，在[[Python]]中使用SQLite，不需要安装任何东西，直接使用。sqlite3 模块由 Gerhard Häring 编写。它提供了符合 DB-API 2.0 规范的接口，这个规范是 [https://www.python.org/dev/peps/pep-0249 PEP 249]。

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

====模块函数和常量====
{| class="wikitable"  style="width: 100%;
! 函数或常量
! 描述
! 示例
|-
| 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 参数。<br \>设置这个参数后，sqlite3 模块将解析它返回的每一列申明的类型。它会申明的类型的第一个单词，比如“integer primary key”，它会解析出“integer”，再比如“number(10)”，它会解析出“number”。然后，它会在转换器字典里查找那个类型注册的转换器函数，并调用它。
| 
|-
| sqlite3.PARSE_COLNAMES
| 这个常量可以作为 connect() 函数的 detect_types 参数。<br \>设置此参数可使得 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 对象有如下的属性和方法：
{| class="wikitable"  style="width: 100%;
! 属性或方法
! 描述
! 示例
|-
| isolation_level
| 获取或设置当前默认的隔离级别。 表示自动提交模式的 None 以及 "DEFERRED", "IMMEDIATE" 或 "EXCLUSIVE" 其中之一。 详细描述请参阅[https://docs.python.org/zh-cn/3/library/sqlite3.html#sqlite3-controlling-transactions 控制事务]。
|
|-
| 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)
| 创建一个自定义的聚合函数。<br \> 参数中 aggregate_class 类必须实现两个方法：step 和 finalize。step 方法接受 num_params 个参数（如果 num_params 为 -1，那么这个函数可以接受任意数量的参数）；finalize 方法返回最终的聚合结果。<br \>finalize 方法可以返回任何 SQLite 支持的类型：bytes，str，int，float 和 None。
|
|-
| create_collation(name, callable)
| 使用 name 和 callable 创建排序规则。这个 callable 接受两个字符串对象，如果第一个小于第二个则返回 -1， 如果两个相等则返回 0，如果第一个大于第二个则返回 1。注意，这是用来控制排序的（SQL 中的 ORDER BY），所以它不会影响其它的 SQL 操作。<br \>注意，这个 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。
| <code>conn.text_factory = bytes</code>
|-
| total_changes
| 返回自打开数据库连接以来已修改，插入或删除的数据库行的总数。
| <code>conn.total_changes</code>
|-
| 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 游标实例具有以下属性和方法。
{| class="wikitable"  style="width: 100%;
! 属性或方法
! 描述
! 示例
|-
| 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)
| 获取下一个多行查询结果集，返回一个列表。 当没有更多可用行时将返回一个空列表。<br \>每次调用获取的行数由 size 形参指定。 如果没有给出该形参，则由 cursor 的 arraysize 决定要获取的行数。 此方法将基于 size 形参值尝试获取指定数量的行。 如果获取不到指定的行数，则可能返回较少的行。
| 
|-
| fetchall()
| 获取一个查询结果的所有（剩余）行，返回一个列表。 请注意 cursor 的 arraysize 属性会影响此操作的执行效率。 当没有可用行时将返回一个空列表。
| 
|-
| close()
| 立即关闭 cursor（而不是在当 __del__ 被调用的时候）。<br \>从这一时刻起该 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 类型====
{| class="wikitable" style="width: 100%;
|-
! SQLite类型
! Python类型<br \>（Python类型转SQLite类型）
! Python类型<br \>（SQLite类型转为Python类型，默认）
|-
| NULL
| None
| None
|-
| INTEGER
| int
| int
|-
| REAL
| float
| float
|-
| TEXT
| str
| 取决于 text_factory , 默认为 str
|-
| BLOB
| bytes
| bytes
|}

{{了解更多
|[https://docs.python.org/zh-cn/3.8/library/sqlite3.html#sqlite-and-python-types Python 文档：sqlite3 - SQLite 与 Python 类型]
}}



{{了解更多
|[https://docs.python.org/zh-cn/3.8/library/sqlite3.html Python 文档：Python标准库 - sqlite3]
}}

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

{| class="wikitable" 
|-
!函数或方法
!描述
!示例
|-
| 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表的所有数据：
<syntaxhighlight lang="python">
import pandas as pd
conn = sqlite3.connect('test.db')
df = pd.read_sql_query("select * from stocks;", conn)
print(df)
</syntaxhighlight>

====保存到数据库====


{{了解更多
|[https://pandas.pydata.org/pandas-docs/stable/user_guide/io.html#sql-queries pandas 文档：IO接口 sql-queries]
}}
===可能出现问题===
*问题：OperationalError: database is locked
可能由于前面使用写入数据库的连接没有关闭。使用后关闭 Connection 对象，比如前面设置Connection 对象实例的名称conn，调用conn.close()即可。

==资源==
===官网===
*[https://www.sqlite.org/index.html SQLite 官网]
*[https://www.sqlite.org/docs.html SQLite 文档]
*[https://www.sqlite.org/cli.html SQLite 文档：sqlite3]

==参考文献==
*[https://zh.wikipedia.org/wiki/SQLite 维基百科：SQLite]

[[分类:数据库]]