SQLite:修订间差异

→‎SQL
无编辑摘要
 
(未显示同一用户的3个中间版本)
第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)存储。


第61行: 第42行:
SQLite有5个日期和时间函数,每个函数都有一个参数时间字符串(timestring),后面跟着零个或多个参数修饰符(modifier)字符串。strftime()函数多一个参数格式化字符串(format),作为其第一个参数。其中strftime()函数是最基础函数,其他4个函数都可以用strftime()表示。提供其他4个而不仅一个函数,原因是使用更简单方便。
SQLite有5个日期和时间函数,每个函数都有一个参数时间字符串(timestring),后面跟着零个或多个参数修饰符(modifier)字符串。strftime()函数多一个参数格式化字符串(format),作为其第一个参数。其中strftime()函数是最基础函数,其他4个函数都可以用strftime()表示。提供其他4个而不仅一个函数,原因是使用更简单方便。
{| class="wikitable"
{| class="wikitable"
! 函数 !! 描述 !!  等同的strftime() !! 示例
! 函数 !! 描述 !!  示例
|-
|-
| '''date'''(timestring, modifier, modifier, ...) || 返回日期 || strftime('%Y-%m-%d',modifier, modifier, ...)||
| '''date'''(timestring, modifier, modifier, ...) || 返回日期 <br />等同于strftime('%Y-%m-%d', modifier, modifier, ...)|| <code>select date('2020-02-20');</code> 返回'2020-02-20' <br /><code>select date('2020-02-20', 'start of year');</code> 返回'2020-01-01' <br /><code>select date('now');</code>返回'2020-02-20'
|-
|-
| '''time'''(timestring, modifier, modifier, ...) || 返回时间 || strftime('%H:%M:%S',modifier, modifier, ...)||
| '''time'''(timestring, modifier, modifier, ...) || 返回时间 <br />等同于strftime('%H:%M:%S', modifier, modifier, ...)||
|-
|-
| '''datetime'''(timestring, modifier, modifier, ...)|| 返回日期时间 || strftime('%Y-%m-%d %H:%M:%S', modifier, modifier, ...)||
| '''datetime'''(timestring, modifier, modifier, ...)|| 返回日期时间 <br />等同于strftime('%Y-%m-%d %H:%M:%S', modifier, modifier, ...)||
|-
|-
|'''julianday'''(timestring, modifier, modifier, ...) || 返回Julian day天数 || strftime('%J', modifier, modifier, ...)||
|'''julianday'''(timestring, modifier, modifier, ...) || 返回Julian day天数 <br />等同于strftime('%J', modifier, modifier, ...)|| <code>SELECT julianday('now') - julianday('2001-01-01');</code>
|-
|-
| '''strftime'''(format, timestring, modifier, modifier, ...) || 返回格式化的日期时间字符串 || strftime(format, timestring, modifier, modifier, ...)||
| '''strftime'''(format, timestring, modifier, modifier, ...) || 返回格式化的日期时间字符串 <br /><br />输出格式format可选值:<br /><code>%d</code> day of month: 00 <br /><code>%f</code> fractional seconds: SS.SSS <br /><code>%H</code> hour: 00-24 <br /><code>%j</code> day of year: 001-366 <br /><code>%J</code> Julian day number <br /><code>%m</code> month: 01-12 <br /><code>%M</code> minute: 00-59 <br /><code>%s</code> seconds since 1970-01-01 <br /><code>%S</code> seconds: 00-59 <br /><code>%w</code> day of week 0-6 with Sunday==0 <br /><code>%W</code> week of year: 00-53 <br /><code>%Y</code> year: 0000-9999 <br /><code>%%</code> % ||
|}
|}


===时间字符串 Time Strings===
===时间字符串 Time Strings===
时间字符串(Time Strings)可以为下面任意一种格式:
时间字符串(timestring)可以为下面任意一种格式:
{| class="wikitable"  
{| class="wikitable"  
|-
|-
! 时间字符串格式
! 时间字符串格式
! 时间字符串例
! 示例
! 使用示例
|-
|-
| YYYY-MM-DD
| YYYY-MM-DD
| '2020-02-20'
| '2020-02-20'
| date('2020-02-20', 'start of year')
|-
|-
| YYYY-MM-DD HH:MM
| YYYY-MM-DD HH:MM
|
|  
|  
|-
|-
| YYYY-MM-DD HH:MM:SS
| YYYY-MM-DD HH:MM:SS
|
|  
|  
|-
|-
| YYYY-MM-DD HH:MM:SS.SSS
| YYYY-MM-DD HH:MM:SS.SSS  
|
|  
|  
|-
|-
| YYYY-MM-DDTHH:MM
| YYYY-MM-DDTHH:MM
|
|  
|  
|-
|-
| YYYY-MM-DDTHH:MM:SS
| YYYY-MM-DDTHH:MM:SS
|
|  
|  
|-
|-
| YYYY-MM-DDTHH:MM:SS.SSS
| YYYY-MM-DDTHH:MM:SS.SSS
|
|  
|  
|-
|-
| HH:MM
| HH:MM
|
|  
|  
|-
|-
| HH:MM:SS
| HH:MM:SS
|
|  
|  
|-
|-
| HH:MM:SS.SSS
| HH:MM:SS.SSS
|
|  
|  
|-
|-
| now
| now
| 'now'
| 'now'
| <code>SELECT date('now');</code> 计算当前日期
|-
|-
| DDDDDDDDDD
| DDDDDDDDDD
|
|  
|  
|}
|}
第133行: 第101行:


===修饰符 Modifiers===
===修饰符 Modifiers===
时间字符串后面可以跟0个或多个修饰符(Modifiers)。修饰符的顺序很重要,修饰符是从左到右依次转换数值。
时间字符串后面可以跟0个或多个修饰符(modifier)。修饰符的顺序很重要,修饰符是从左到右依次转换数值。
{| class="wikitable"
{| class="wikitable"
! 修饰符 !! 描述  !! 示例
! 修饰符 !! 描述  !! 示例
第203行: 第171行:
c = conn.cursor()
c = conn.cursor()


c.execute("SELECT name FROME sqlite_master WHERE type='table' ORDER BY name;")
c.execute("SELECT name FROM sqlite_master WHERE type='table' ORDER BY name;")
c.fetchall()
c.fetchall()
</syntaxhighlight>
</syntaxhighlight>
===修改数据===
===修改数据===
====删除数据====
====删除数据====
第215行: 第184行:
|[https://www.sqlite.org/lang.html SQLite 文档:SQLite 中的 SQL]
|[https://www.sqlite.org/lang.html SQLite 文档:SQLite 中的 SQL]
}}
}}
== 命令行程序 sqlite3 ==


==进阶教程==
==进阶教程==
第223行: 第196行:
|[https://www.sqlite.org/fileformat.html SQLite 文档:数据库文件格式]
|[https://www.sqlite.org/fileformat.html SQLite 文档:数据库文件格式]
}}
}}
== 导入与导出 ==
=== 导入 ===
{| class="wikitable"  style="width: 100%;
! 工具
! 导入类型
! 描述
|-
| sqlite3
| 导入CSV
| SQLite的命令工具 <br \>示例:
|-
|
|
|
|-
|
|
|
|}
=== 导出 ===
{| class="wikitable"  style="width: 100%;
! 工具
! 导出类型
! 描述
|-
| rowspan="2" | sqlite3
| [[SQL]]
| <br \>导出数据库的Schema,只包含数据库的描述如建表语句、视图索引等,不包含数据。示例:<br \><code>sqlite3 mysqlite.db .schema > schema.sql</code> <br \><br \>导出数据库全部内容,示例:<br \><code>sqlite3 mysqlite.db .dump > dump.sql</code>
|-
| [[CSV]]
| SQLite的命令工具 <br \>示例:<code>sqlite3 -header -csv mysqlite.db "select * from 表名;" > 表名.csv</code>
|-
| rowspan="2" | [[pandas]]
| SQL
| 可以使用read_sql()函数读取,DataFrame.to_sql()函数保存。
|-
| CSV、JSON、HTML、Excel、HDF5等
| pandas提供多种函数,用于读取和保存。 <br \>保存CSV示例: <syntaxhighlight lang="python">
import pandas as pd
import sqlite3
conn = sqlite3.connect('mysqlite.db')
df = pd.read_sql('SELECT * from 表名;', conn)
df.to_csv('表名.csv')
</syntaxhighlight>
|}
==Python中使用==
==Python中使用==
===标准库 sqlite3===
===标准库 sqlite3===

2022年1月1日 (六) 16:20的最新版本

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



命令行程序 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 文档: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()即可。

资源

官网

参考文献