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有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
命令行程序 sqlite3
进阶教程
数据库文件格式
完整的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 文档：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()即可。