SQLite:修订间差异

→‎SQL
无编辑摘要
第21行: 第21行:


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


{| 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没有单独的存储类,使用INTEGER类的0 (false) 和 1 (true)存储。



2020年10月24日 (六) 07:29的版本

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个而不仅一个函数,原因是使用更简单方便。

函数 描述 等同的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)可以为下面任意一种格式:

时间字符串格式 时间字符串例 使用示例
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' SELECT date('now'); 计算当前日期
DDDDDDDDDD


修饰符 Modifiers

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

修饰符 描述 示例
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 FROME 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 文档:数据库文件格式


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()即可。

资源

官网

参考文献