SQLite3与Python3

SQLite是一个使用C语言编写的体积超小的嵌入式数据库, 一个数据库就是一个文件. 由于体积小并且迅速, 所以SQLite经常被嵌入到一些iOS或者Android的应用程序中. 这不, 在Python3的标准库中就嵌入了一个SQLite.

接下来我们就来说说这个小家伙以及怎么使用Python3的SQLite的标准库.

SQLite

很久之前我们就看过了Python3操作MySQL的操作, 现在SQLite和MySQL一样都是符合ACID的数据库, 但是稍微我们还是要了解一下他的. 来看看它有哪些神奇的特性和命令.

SQLite的命令

SQLite提供了很多有用的命令, 这些命令都不使用分号;结尾, 却是使用.号.开头的. 我们来看一下都有哪些, 在shell的模式下, 使用.help命令就可以获得所有的SQLite命令.

sqlite> .help
.auth ON|OFF           Show authorizer callbacks
.backup ?DB? FILE      Backup DB (default "main") to FILE
.bail on|off           Stop after hitting an error.  Default OFF
.binary on|off         Turn binary output on or off.  Default OFF
.changes on|off        Show number of rows changed by SQL
.check GLOB            Fail if output since .testcase does not match
.clone NEWDB           Clone data into NEWDB from the existing database
.databases             List names and files of attached databases
.dbinfo ?DB?           Show status information about the database
.dump ?TABLE? ...      Dump the database in an SQL text format
                         If TABLE specified, only dump tables matching
                         LIKE pattern TABLE.
.echo on|off           Turn command echo on or off
.eqp on|off|full       Enable or disable automatic EXPLAIN QUERY PLAN
.exit                  Exit this program
.explain ?on|off|auto? Turn EXPLAIN output mode on or off or to automatic
.fullschema ?--indent? Show schema and the content of sqlite_stat tables
.headers on|off        Turn display of headers on or off
.help                  Show this message
.import FILE TABLE     Import data from FILE into TABLE
.imposter INDEX TABLE  Create imposter table TABLE on index INDEX
.indexes ?TABLE?       Show names of all indexes
                         If TABLE specified, only show indexes for tables
                         matching LIKE pattern TABLE.
.limit ?LIMIT? ?VAL?   Display or change the value of an SQLITE_LIMIT
.log FILE|off          Turn logging on or off.  FILE can be stderr/stdout
.mode MODE ?TABLE?     Set output mode where MODE is one of:
                         ascii    Columns/rows delimited by 0x1F and 0x1E
                         csv      Comma-separated values
                         column   Left-aligned columns.  (See .width)
                         html     HTML <table> code
                         insert   SQL insert statements for TABLE
                         line     One value per line
                         list     Values delimited by .separator strings
                         quote    Escape answers as for SQL
                         tabs     Tab-separated values
                         tcl      TCL list elements
.nullvalue STRING      Use STRING in place of NULL values
.once FILENAME         Output for the next SQL command only to FILENAME
.open ?--new? ?FILE?   Close existing database and reopen FILE
                         The --new starts with an empty file
.output ?FILENAME?     Send output to FILENAME or stdout
.print STRING...       Print literal STRING
.prompt MAIN CONTINUE  Replace the standard prompts
.quit                  Exit this program
.read FILENAME         Execute SQL in FILENAME
.restore ?DB? FILE     Restore content of DB (default "main") from FILE
.save FILE             Write in-memory database into FILE
.scanstats on|off      Turn sqlite3_stmt_scanstatus() metrics on or off
.schema ?PATTERN?      Show the CREATE statements matching PATTERN
                          Add --indent for pretty-printing
.separator COL ?ROW?   Change the column separator and optionally the row
                         separator for both the output mode and .import
.shell CMD ARGS...     Run CMD ARGS... in a system shell
.show                  Show the current values for various settings
.stats ?on|off?        Show stats or turn stats on or off
.system CMD ARGS...    Run CMD ARGS... in a system shell
.tables ?TABLE?        List names of tables
                         If TABLE specified, only list tables matching
                         LIKE pattern TABLE.
.testcase NAME         Begin redirecting output to 'testcase-out.txt'
.timeout MS            Try opening locked tables for MS milliseconds
.timer on|off          Turn SQL timer on or off
.trace FILE|off        Output each SQL statement as it is run
.vfsinfo ?AUX?         Information about the top-level VFS
.vfslist               List all available VFSes
.vfsname ?AUX?         Print the name of the VFS stack
.width NUM1 NUM2 ...   Set column widths for "column" mode
                         Negative values right-justify

还是蛮多的, 不过常用的其实也就那些 我们来看一下, 关于输出(包括输出到标准输出的, 以及文件的)的:

命令	描述
.backup ?DB? FILE	备份 DB 数据库（默认是 “main”）到 FILE 文件
.dump ?TABLE?	以 SQL 文本格式转储数据库。如果指定了 TABLE 表，则只转储匹配 LIKE 模式的 TABLE 表
.header(s) ON OFF	开启或关闭头部显示。
.mode MODE	设置输出模式，MODE包括: ascii column csv html insert line list tabs tcl
.output FILENAME	发送输出到 FILENAME
.timer ON OFF	开启或者关闭定时器
.width NUM NUM	为column模式设置宽度.
.save FILENAME	将内存中的数据写入到文件( 其实还是.backup )

接着是一些打开, 导入导出和列出类的:

命令	描述
.databases	列出数据库的名称及其所依附的文件
.import FILE TABLE	导入来自 FILE 文件的数据到 TABLE 表中
.indices ?TABLE?	显示所有索引的名称。如果指定了 TABLE 表，则只显示匹配 LIKE 模式的 TABLE 表的索引
.schema ?TABLE?	显示 CREATE 语句。如果指定了 TABLE 表，则只显示匹配 LIKE 模式的 TABLE 表。
.read FILENAME	执行 FILENAME 文件中的 SQL.
.open	打开一个数据库文件
.tables ?PATTERN?	列出匹配 LIKE 模式的表的名称

退出可以使用.quit也可以使用.exit.

数据库 = 文件

由于SQLite不同于其他的关系型数据库, 它使用一个文件映射一个数据库的设计, 这就使得库级别的操作显得和其他的有点不一样.

例如, 创建数据库 其实就是打开文件, 而逻辑层面的数据库和物理层面的文件之间的连接就显得很重要了. 我们可以看到:

sqlite> .da
seq  name             file
---  ---------------  --------------------------
0    main             /private/tmp/testdb

这里main数据库就和对应的文件进行了连接. 对于SQLite来说, 数据库级别的操作, 最主要的就是这两个:

• ATTACH
• DETACH

我们刚刚说过了, 最重要的东西是连接, 那么就不存在删除数据库的操作了(因为就是一个文件, 实在不行你把文件删除就行了).

下面我们就来看看这两个数据库操作:

附加

附加就是一个建立连接的过程, 我们可以再把已经建立连接的文件再和一个数据库进行连接, 也可以开新的文件.

sqlite> attach testdb as "test";
sqlite> .databases
seq  name             file
---  ---------------  ----------------------------------------------------------
0    main             /private/tmp/testdb
2    test             /private/tmp/testdb

但是main和temp数据库是不可以附加上去的, 因为他们已经在使用, 不仅如此, 你也不能分离他们.

如果是附加一个已经存在的数据库文件的话, 就相当于是添加了一个别名的感觉.

分离

所谓分离其实就是结束掉连接:

sqlite> .databases
seq  name             file
---  ---------------  ----------------------------------------------------------
0    main             /private/tmp/testdb
2    test             /private/tmp/testdb
sqlite> detach test;
sqlite> .data
seq  name             file
---  ---------------  ----------------------------------------------------------
0    main             /private/tmp/testdb

其实使用起来还是蛮简单的, 主要就是数据库变成了文件这一点一开始可能会有一点不适应.

表

再说到表操作之前, 我们先来说说SQLite中的数据类型.

SQLite中的数据类型

和其他很多的关系数据库不同, SQLite使用一个更加动态的数据类型系统, 一个数据使用多少空间和他本身的值相关, 而和容器的关系不大. SQLite一共提供了5中数据存储类:

• NULL: 就是一个NULL值
• INTEGER: 一个带符号的整数, 根据值的大小可能是1, 2, 3, 4, 6, 8个字节的存储容量
• REAL: 浮点型, 8字节的IEEE浮点数字
• TEXT: 文本字符, 编码方式取决于数据库的编码(UTF-8, UTF-16BE 或者 UTF-16LE) 关于编码的相关可以查看我之前的文章 — 字符编码笔记
• BLOB: 是一个blob数据, 根据输入存储

这些存储类就是SQLite最终存储的数据类型了. 除了这些数据存储类, sqlite还有一个叫做亲和类的存储概念. SQLite会根据数据的值来进行转换, 主要有这些类型:

• TEXT
• NUMERIC
• INTEGER
• REAL
• NONE

转换规则是这样的:

TEXT会将数值型的数据转换成是文本数据, 根据数据类型对对应的亲和类型(这个对应表我们会在后面补充), SQLite引擎会进行相应的转换. 如果是NUMERIC, 那么SQLite就会尝试在不丢失信息的情况下将文本数据转换成为INTEGER或者REAL类型, 而INTEGER和REAL类型和NUMERIC的规则是一致的, 不同之处在于INTEGER在执行CAST表达式的时候有不同以及在REAL在转换的时候不会将”100.00”转换成为100. 如果是NONE就不做任何转换.

文本没有代码好懂啊…来转换一下:

TEXT: int -> string
NUMERIC: try { string -> integer/real } except { keep(string) }
NONE: None
INTEGER: NUMERIC_INTEGER()
REAL: NUMERIC_REAL()

那么到底哪些类型的亲和类型是什么呢 我们来看这个表:

数据类型	亲和类型
INT INTEGER TINYINT SMALLINT MEDIUMINT BIGINT UNSIGNED BIG INT INT2 INT8	INTEGER
CHARACTER(20) VARCHAR(255) VARYING CHARACTER(255) NCHAR(55) NATIVE CHARACTER(70) NVARCHAR(100) TEXT BLOB	TEXT
BLOB 或者是 (不指定)	NONE
REAL DOUBLE DOUBLE PRECISION FLOAT	REAL
NUMERIC DECIMAL(10,5) BOOLEAN DATE DATETIME	NUMERIC

举个小栗子:

sqlite> create table type (
   ...> id int,
   ...> name int
   ...> );
sqlite> insert into type values("jus", "jus");
# 没有报错
sqlite> select * from type;
id          name
----------  ----------
jus         jus
# 这根本就不是INT!!!

这里我们的SQLite在尝试转换成INTEGER或者REAL的过程中失败了, 所以就变成了TEXT存储类了.

这就是说 我们所制定的类型可能并不会按照按照制定的所存储, 这就要求我们在插入表的时候要仔细的过滤数据了.

简单了解之后我们就可以开始尝试表级别的操作了.

创建 删除

创建表的语法基本和你了解到的是一个样子的, 也没什么好说的:

sqlite> create table if not exists student(
   ...> id int primary key not null,
   ...> name text not null,
   ...> age int not null,
   ...> active int(1) not null
   ...> );
sqlite> .schema student
CREATE TABLE student(
id int primary key not null,
name text not null,
age int not null,
active int(1) not null
);

使用.schema命令来查看表的结构.

删除表的话, 就直接drop掉就可以了. 但是我们如果想要保留表结构只是清空数据的话, 在MySQL中是使用的truncate, 但是在SQLite中, 是没有这个的实现的. 如果想只清空数据的话, 就还是只能使用delete.

sqlite> delete from TABLE;

另外, 当我们需要修改已经常见的表的时候, 在SQLite中是不能修改已经存在的表结构的, 你只可以进行名称修改和添加:

sqlite> alter table test rename to testdb;
sqlite> alter table testdb add column age int not null default 0;

插入 查找 修改 删除

插入, 查找, 修改和删除的语法基本符合通常的SQL语句语法, 故不再赘述. 但是在这一部分我们补充下面两个小技巧: 我们知道一些命令只能在sqlite提示符中使用, 如果是在编程环境中怎么或者例如.schema的结果呢?

事实上, 在sqlite中维护着一个叫做sqlite_master的表, 这个表中涵盖了当前数据库中的索引, 表的信息.

来看一下:

sqlite> .mode csv
sqlite> select * from sqlite_master;
type,name,tbl_name,rootpage,sql
table,student,student,4,"CREATE TABLE student(
id int primary key not null,
name text not null,
age int not null,
active int(1) not null
)"
index,sqlite_autoindex_student_1,student,5,
table,testdb,testdb,2,"CREATE TABLE ""testdb"" (
id integer primary key autoincrement not null,
name text not null
, age int not null default 0)"
table,sqlite_sequence,sqlite_sequence,3,"CREATE TABLE sqlite_sequence(name,seq)"

这就是说, 我们在以后想要获得表的结构的话, 通过在这个表中进行查找操作就可以了.

另外, 在上面的这个表中我们看到了一个叫做sqlite_seq的表, 当你启动了autoincrement的时候, 这个表就会被自动创建然后记录当前这个递增的大小, 这也就是说你是可以通过操作这个表的数据值来控制自增量的.

假设我们的表中数据LIKE THIS:

sqlite> select * from test;
id          name        age
----------  ----------  ----------
1           Justin      20
2           Selena      23
3           Selena      21

有两个重复记录, 那么这个时候我们可以使用**distinct**关键字, 来进行去重:

sqlite> select distinct name from test;
name
----------
Justin
Selena

子句

SQLite的子句包括下面这些:

• WHERE子句
• LIKE子句
• GLOB子句
• LIMIT子句
• ORDER BY子句
• GROUP BY子句
• HAVING子句

其中我们只说一下GLOB子句, 其他的都和基础SQL一样的使用.

GLOB说来也很简单, 基本上和LIKE子句一样的使用方法, 不同的是, 通配符变成了遵循UNIX规范的*和?来分别表示%和_, 也就是0,1或者多个数字或者字符以及单一的数字或者字符.

高级查询

SQLite同样可以使用子查询, 连接和合并

索引

索引是为了更快的检索数据的特殊的表. 创建索引语法是这样的:

CREATE INDEX index_name ON table_name;

但是如果你的表太小或者是一个需要频繁更新和插入的表的话就不建议使用索引了, 因为这样反而会使得它降低性能.

我们向student表中插入了10000条数据来检测索引带来的性能提升:

sqlite> select id from student order by id desc limit 1;
id
----------
10001
sqlite> .timer on
sqlite> select * from student where name = "James Frost";
id          name                  age         gender      active
----------  --------------------  ----------  ----------  ----------
5020        James Frost           16          1           1
Run Time: real 0.001 user 0.000907 sys 0.000081
sqlite> .timer off
sqlite> create index student_index on student(name);
sqlite> .timer on
sqlite> select * from student (indexed by student_index) where name = "James Frost";
id          name                  age         gender      active
----------  --------------------  ----------  ----------  ----------
5020        James Frost           16          1           1
Run Time: real 0.000 user 0.000094 sys 0.000051

可以看出来性能确实是有个不小的提升. 可以在查询语句中加入indexed by来说明使用哪一个索引来进行查询.

事务

同样我们之前就说过SQLite是符合ACID的, 所以自然是支持事务的, 控制事务的三个语句:

• BEGIN TRANSACTION(BEGIN)
• ROLLBACK
• COMMIT(END TRANSACTION)

Python的SQLITE库

Python的sqlite库是从Python2.5之后的版本就有了, 在整套API中, 我们主要关注的就是两个对象: Connection和Cursor.

模块 sqlite3

首先, 我们还是从模块入手, sqlite3模块提供的使用率最高的方法就是connect, 其实这个方法就是返回一个Connection对象, 通过在这个方法中传递参数来控制一些连接的全局属性, 例如: 是否开启自动commit模式, 是否使用url进行连接, 连接超时时长等等.

import sqlite3

db = sqlite3.connect('file:path/to/database?mode=ro', uri=True, isolation_level=None)

这样就可以指明文件路径, 如果想要在内存中进行写入操作的话, 打开:memory:就可以了. 另外Connection对象有一个控制事务的属性是一个叫做隔离等级的属性: isolation_level, 当他设置成为None的时候就表明是自动提交的模式, 其余都需要使用者手动的执行commit操作. 默认情况下, 在执行一个DML语句的时候就会默认打开一个事务.

另外, 在3.6之后的版本 之后可以通过访问Connection的只读bool属性in_transaction来获得当前的事务状态.

接下里我们还要说一下Python中的数据类型和SQLite中的数据类型的互相转化:

Python type	SQLite type
None	NULL
int	INTEGER
float	REAL
str	TEXT
bytes	BLOB

如果是从SQLite到Python是这样的:

SQLite type	Python type
NULL	None
INTEGER	int
REAL	float
TEXT	depends on text_factory, str by default
BLOB	bytes

类 Connection

除了上面提到的几个属性, Connection还可以使用total_changes来获得从连接打开到调用为止被操作的总行数. 回到上面说的事务, 事务管理的任务都在Connection这对象身上, 通过下面两个(基本和SQL保持一致的):

• commit()
• rollback()

而当不需要使用的时候别忘记关闭连接, 使用close(), 如果在非自动提交的模式下使用了close()而没有commit的, 之前的修改会丢失的, 这点要注意.

通过这个Connection对象 我们还可以获得当前数据库的dump信息, 直接使用connection.iterdump方法就可以返回一个迭代器, 通过迭代就可以获得在shell中使用.dump一样的原始SQL信息.

import sqlite3

con = sqlite3.connect('existing_db.db')
with open('dump.sql', 'w') as f:
    for line in con.iterdump():
        f.write('%s\n' % line)

和数据库交互的总重要的一步就是进行SQL语句的执行了.

我们可以直接使用Connection对象执行 但是这实际上调用的还是下面说的Cursor对象的执行方法, (官方文档是这么说的: 不标准的快捷方式 - nonstandard shortcut )和其他很多DBAPI一样, 包含着这些:

• execute: 执行一个SQL语句, 可以使用占位符(包括?形式和:name命名形式)
• executemany: 执行几个相同但是参数不一样的SQL语句, 通过传递生成器来进行迭代的执行
• executescript: 直接像在执行rawSQL一样, 传递SQL脚本, 直接执行多条SQL语句

代码表示我们在后面的Cursor对象中再说.

那么怎么拿到一个Cursor对象, 就使用Connection的cursor方法就可以了.

cur = conn.cursor()

类 Cursor

Cursor对象无非就是做两个事情, 执行SQL和取得结果集. 先来看看Connection真正调用的三个执行方法是怎么样的:

import sqlite3

con = sqlite3.connect(":memory:")
cur = con.cursor()
cur.execute("create table people (name_last, age)")

who = "Yeltsin"
age = 72

# This is the qmark style:
cur.execute("insert into people values (?, ?)", (who, age))

# And this is the named style:
cur.execute("select * from people where name_last=:who and age=:age", {"who": who, "age": age})

而execute方法只可以执行单独的一条SQL语句. 假设一个场景是需要插入多条数据, 那么我们就可以考虑使用excutemany这个函数:

import sqlite3
import string

def char_generator():
    for c in string.ascii_lowercase:
        yield (c,)

con = sqlite3.connect(":memory:")
cur = con.cursor()
cur.execute("create table characters(c)")

cur.executemany("insert into characters(c) values (?)", char_generator())

最后一种执行方法, 就是直接将sql脚本传入的形式, 文档依然将这种声明成是不标准的简便方式:

import sqlite3

con = sqlite3.connect(":memory:")
cur = con.cursor()
cur.executescript("""
    create table person(
        firstname,
        lastname,
        age
    );

    create table book(
        title,
        author,
        published
    );

    insert into book(title, author, published)
    values (
        'Dirk Gently''s Holistic Detective Agency',
        'Douglas Adams',
        1987
    );
    """)

那么, 好了 现在我能够像数据库传递SQL了, 那么获取结果呢, 同样也有三种方式:

• fetchone: 获取一个结果(一个row), 如果没有结果了就会返回None
• fetchmany: 抓取size个结果, size的大小取决于调用时传递的参数, 或者是cursor对象的arraysize属性, 如果没有数据就返回一个空的列表
• fetchall: 获取所有的结果集

再说一下Cursor的关闭问题, 关闭的好处在于: 释放资源, 将这个游标标记成是不可用的, 这样当我们尝试使用的时候就可以通过错误捕获来增强程序的健壮性. 反正就是关闭了吧.

之前说了可以听过cursor对象的arraysize属性来设置抓取的数量(虽然我觉得修改这个值并不如在fetchmany的参数中传递更好), 那么Cursor对象还有那些属性呢?

• rowcount
  • 这个属性尽管sqlite3模块有实现, 但是在原生的数据库中却没有很好的实现, 导致这个值的获取变得奇奇怪怪的 在3.6.5的版本前, 如果执行了DELECT from table这样的不带条件的 rowcount就会被设置成0
• lastrowid
  • 只读属性, 返回最后操作的行的rowid, 只有在执行了INSERT和REPLACE并且是使用的execute函数才会被设置, 并且对REPLACE的支持实在3.6的版本中加入的.
• connection
  • 就是返回拿到这个cursor的connection是哪一个connection