基本概念

大小写敏感性

有个重要的点值得注意，SQLite 是不区分大小写的，但也有一些命令是大小写敏感的，比如 GLOB 和 glob 在 SQLite 的语句中有不同的含义。
注释

SQLite 注释是附加的注释，可以在 SQLite 代码中添加注释以增加其可读性，他们可以出现在任何空白处，包括在表达式内和其他 SQL 语句的中间，但它们不能嵌套。
SQL 注释以两个连续的 “-” 字符（ASCII 0x2d）开始，并扩展至下一个换行符（ASCII 0x0a）或直到输入结束，以先到者为准。
您也可以使用 C 风格的注释，以 “/" 开始，并扩展至下一个 "/” 字符对或直到输入结束，以先到者为准。

main database^[1]

The “.databases” command shows a list of all databases open in the current connection. There will always be at least 2. The first one is “main”, the original database opened. The second is “temp”, the database used for temporary tables. There may be additional databases listed for databases attached using the ATTACH statement. The first output column is the name the database is attached with, and the second column is the filename of the external file.

如果导入同一个库两次，.table会显示什么？

sqlite> .open test.db
sqlite> .database
main: /Users/Aether/Documents/hexo_file/source/_posts/test.db
sqlite> ATTACH DATABASE 'test.db' as 'foo';
sqlite> .databases
main: /Users/Aether/Documents/hexo_file/source/_posts/test.db
foo: /Users/Aether/Documents/hexo_file/source/_posts/test.db
sqlite> .table
COMPANY      foo.COMPANY

回滚日志^[2]

如果崩溃或断电发生在事务的中间某时刻，则在硬盘上会留有回滚日志。在下次另外一个应用程序尝试打开数据库文件时，它会通知存在回滚日志(“热日志”)，并使用日志中的信息来把数据库恢复到未完成事务开始之前的状态。这就是SQLite实现原子提交的基本原理。

TEMP^[2]

临时数据库文件总是在数据库连接关闭时自动删除。

字段类型

SQLite是无类型的. 这意味着你可以保存任何类型的数据到你所想要保存的任何表的任何列中, 无论这列声明的数据类型是什么(只有自动递增Integer Primary Key才有用). 对于SQLite来说对字段不指定类型是完全有效的.
建表时声明啥类型或者不声明类型都可以,也就意味着创表语句可以这么写:create table if not exists t_student(name, age);

一般数据采用的固定的静态数据类型，而SQLite采用的是动态数据类型，会根据存入值自动判断。SQLite的数据存储类型有: integer (有符号整形), real (浮点值,以8字节IEEE浮点数存放), text(文本字符串,使用数据库编码UTF-8，UTF-16BE或者UTF-16LE存放), blob(是一个数据块,完全按照输入存放; 二进制数据,比如: 文件) 布尔类型 Sqlite没有单独的布尔存储类型，它使用INTEGER作为存储类型，0为false，1为true

IS_OPENING BOOLEAN NOT NULL CHECK (IS_OPENING IN (0,1))

NUMERIC类型：当文本数据被插入到亲缘性为NUMERIC的字段中时，如果转换操作不会导致数据信息丢失以及完全可逆，那么SQLite就会将该文本数据转换为INTEGER或REAL类型的数据，如果转换失败，SQLite仍会以TEXT方式存储该数据。对于NULL或BLOB类型的新数据，SQLite将不做任何转换，直接以NULL或BLOB的方式存储该数据。需要额外说明的是，对于浮点格式的常量文本，如"30000.0"，如果该值可以转换为INTEGER同时又不会丢失数值信息，那么SQLite就会将其转换为INTEGER的存储方式。

Date 与 Time 数据类型
SQLite 没有一个单独的用于存储日期和/或时间的存储类，但 SQLite 能够把日期和时间存储为 TEXT、REAL 或 INTEGER 值。

虽然可以不写类型，但为了以后方便理解，最好还是写上。

sqlite_master & sqlite_temp_master

sqlite查询库里所有表名
每一个 SQLite 数据库都有一个叫 SQLITE_MASTER 的表，里面存储着数据库的数据结构（表结构、视图结构、索引结构等），只可以对他使用查询语句
SQLITE_MASTER 表DDL信息如下:
CREATE TABLE sqlite_master (type TEXT,name TEXT,tbl_name TEXT,rootpage INTEGER,sql TEXT);

SELECT name FROM sqlite_master WHERE type=’table’ ORDER BY name;

NOTE: ‘TEMP’ is in sqlite_temp_master

SQL语句的种类

数据定义语句: (DDL:Data Definition Language)
包括create和drop等操作; 例如:在数据库中创建新表或删除表(create table或drop table)
数据操作语句(DML:Data Manipulation Language)
包括insert、update、delete等操作(添加、修改(更新)、删除表中的数据)
数据查询语句(DQL:Data Query Language)
可以用于查询获得表中的数据, 关键字select是DQL(也是所有SQL)用得最多的操作其他DQL常用的关键字有where,order by,group by和having

transaction

BEGIN TRANSACTION：开始事务处理。
COMMIT：保存更改，或者可以使用 END TRANSACTION 命令。
ROLLBACK：回滚所做的更改。
By default, each INSERT statement is its own transaction. But if you surround multiple INSERT statements with BEGIN…COMMIT then all the inserts are grouped into a single transaction. The time needed to commit the transaction is amortized over all the enclosed insert statements and so the time per insert statement is greatly reduced.

about Savepoint

可以参考：SQLite3源码学习（29）用户事务和Savepoint

VACUUM

通过复制主数据库中的内容到一个临时数据库文件，然后清空主数据库，并从副本中重新载入原始的数据库文件。这消除了空闲页，把表中的数据排列为连续的，另外会清理数据库文件结构。(ct: sqlite是以树结构存储数据的)

约束

NOT NULL 约束：确保某列不能有 NULL 值。
DEFAULT 约束：当某列没有指定值时，为该列提供默认值。
UNIQUE 约束：确保某列中的所有值是不同的。
PRIMARY Key 约束：唯一标识数据库表中的各行/记录。
CHECK 约束：CHECK 约束确保某列中的所有值满足一定条件。

SQLite 支持 ALTER TABLE 的有限子集。重命名列，删除一列，或从一个表中添加或删除约束都是不可能的!!!

操作

dot command

你不能从Python中调用点命令，因为点命令是特定于命令行shell工具的。

.schema table_name: 列出创建表的语句。.sch也可以
.tables: 列出所有表名
.headers on 列出表头(column names)

command line

$sqlite3 testDB.db .dump > testDB.sql

INSERT

insert by python dictionary

1
2

values = {'title':'jack', 'type':None, 'genre':'Action', 'onchapter':None,'chapters':6,'status':'Ongoing'}
cur.execute('INSERT INTO Media (id, title, type, onchapter, chapters, status) VALUES (:id, :title, :type, :onchapter, :chapters, :status);'), values)

ATTACH

The ATTACH DATABASE statement adds another database file to the current database connection. Database files that were previously attached can be removed using the DETACH DATABASE command.

VIEW(视图)

SQLite 视图是只读的，因此可能无法在视图上执行 DELETE、INSERT 或 UPDATE 语句。但是可以在视图上创建一个触发器，当尝试 DELETE、INSERT 或 UPDATE 视图时触发，需要做的动作在触发器内容中定义。

最简单的视图：
CREATE VIEW testview AS SELECT * FROM testtable WHERE first_col > 100;

创建临时视图：
sqlite> CREATE TEMP VIEW tempview AS SELECT * FROM testtable WHERE first_col > 100;

“IF NOT EXISTS”
sqlite> CREATE VIEW IF NOT EXISTS testview AS SELECT * FROM testtable WHERE first_col > 100;

删除视图
sqlite> DROP VIEW IF EXISTS testview;

ALTER TABLE

只有两个用法：

重命名表
ALTER TABLE database_name.table_name RENAME TO new_table_name;
在已有的表中添加列
ALTER TABLE database_name.table_name ADD COLUMN column_def...;
注意，新添加的列是以 NULL 值来填充的

select

日期表达式
返回当前系统日期和时间值，注意是UTC+0时间
SELECT CURRENT_TIMESTAMP;

例子^[3]

sqlite> SELECT * FROM COMPANY WHERE AGE >= 25 OR SALARY >= 65000;
sqlite>  SELECT * FROM COMPANY WHERE AGE IS NOT NULL;
sqlite> SELECT * FROM COMPANY WHERE AGE NOT IN ( 25, 27 ); /* 既不是 25 也不是 27 的所有记录 */
sqlite> SELECT * FROM COMPANY WHERE AGE BETWEEN 25 AND 27;

UPDATE

1 2	sqlite> UPDATE COMPANY SET ADDRESS = 'Texas' WHERE ID = 6; sqlite> UPDATE COMPANY SET ADDRESS = 'Texas', SALARY = 20000.00; /* 修改所有 */

UPDATE multiple rows

参考：
Update multiple rows with different values in a single SQL query
sqlite update 多行

DELETE

1 2	sqlite> DELETE FROM COMPANY WHERE ID = 7; sqlite> DELETE FROM COMPANY; /* 删除所有 */

字符串匹配

GLOB大小写敏感；LIKE大小写不敏感

GLOB

星号（*）
代表零个、一个或多个数字或字符。
问号（?）
代表一个单一的数字或字符。这些符号可以被组合使用。

LIKE

百分号（%）
代表零个、一个或多个数字或字符。
下划线（_）
代表一个单一的数字或字符。这些符号可以被组合使用。

python

connection^[4]

# Fill the table
persons = [
    ("Hugo", "Boss"),
    ("Calvin", "Klein")
    ]
con.executemany("insert into person(firstname, lastname) values (?, ?)", persons)
# Print the table contents
for row in con.execute("select firstname, lastname from person"):
    print(row)
print("I just deleted", con.execute("delete from person").rowcount, "rows")

security^[5]
Use ? as a placeholder!
Use Python’s string operations to assemble a query makes program vulnerable to an SQL injection attack
if connection is not closed

When the connection is closed explicitly by code or implicitly by program exit then any outstanding transaction is rolled back.

connection as a context manager^[4]

Connection objects can be used as context managers that automatically commit or rollback transactions. In the event of an exception, the transaction is rolled back; otherwise, the transaction is committed.

# Successful, con.commit() is called automatically afterwards
with con:
    con.execute("insert into person(firstname) values (?)", ("Joe",))
# con.rollback() is called after the with block finishes with an exception!
try:
    with con:
        con.execute("insert into person(firstname) values (?)", ("Joe",))
except sqlite3.IntegrityError:
    print("couldn't add Joe twice")

cursor

FIRST OF ALL: There’re two distinct ‘cursor’ concept:

In standard SQL, it’s like concept of iterator^[6]:

In computer science and technology, a database cursor is a control structure that enables traversal over the records in a database. Cursors facilitate subsequent processing in conjunction with the traversal, such as retrieval, addition and removal of database records. The database cursor characteristic of traversal makes cursors akin to the programming language concept of iterator.
In sqlite module of python, it’s basically the same with connection. Or say, connection is a shortcut of cursor (make things easier).^[6]

Connection.execute
This is a nonstandard shortcut that creates a cursor object by calling the cursor() method, calls the cursor’s execute() method with the parameters given, and returns the cursor.

As long as you use the standard methods of the Cursor object, you can be sure that if you switch to another database implementation that follows the above specification, your code will be fully portable. Perhaps you will only need to change the import line.

cursor in sqlite module of python^[6]

Cursor objects allow you to keep track of which result set is which, since it’s possible to run multiple queries before you’re done fetching the results of the first.

commit

ref: how often do I have to commit?

What concurrent readers see

When a change to the database is committed, it becomes visible for other connections. Unless it is committed, it remains visible only locally for the connection to which the change was done. Because of the limited concurrency features of sqlite, the database can only be read while a transaction is open.
balance between Performance and Exclusively locking the database

Actually, SQLite will easily do 50,000 or more INSERT statements per second on an average desktop computer. But it will only do a few dozen transactions per second.
sqlite3 in python
By default, the ‘isolation_level’ parameter of a connection is set as DEFERRED.
Set ‘isolation_level’ to ‘None’, this will leave the underlying sqlite3 library operating in autocommit mode. (autocommit mode means that statements that modify the database take effect immediately.)

read datetime.datetime from sqlite3^[7]

for more about read datetime.datetime or even adapter, see doc: DB-API 2.0 interface for SQLite databases

建立connect时加上detect_types=sqlite3.PARSE_DECLTYPES, 就可以直接读写datetime类型:

   >>> db = sqlite3.connect('test.db', detect_types=sqlite3.PARSE_DECLTYPES)
   >>> c = db.cursor()
   >>> c.execute('create table foo (bar integer, baz timestamp)')
<sqlite3.Cursor object at 0x40fc50>
   >>> c.execute('insert into foo values(?, ?)', (23, datetime.datetime.now()))

Using adapters to store additional Python types^[8]

simple example
read & write boolean directly:

1 2	sqlite3.register_adapter(bool, int) sqlite3.register_converter("BOOLEAN", lambda v: bool(int(v)))

define __conform__ in your class

class Point:
    def __init__(self, x, y):
        self.x, self.y = x, y
    def __conform__(self, protocol):
        if protocol is sqlite3.PrepareProtocol:
            return "%f;%f" % (self.x, self.y)

1
2
3

def adapt_point(point):
    return "%f;%f" % (point.x, point.y)
sqlite3.register_adapter(Point, adapt_point)

datatime.date and datetime.datetime can be directly written to sqlite3

sqlite3 module has two default adapters for Python’s built-in datetime.date and datetime.datetime types.

PRAGMA

cur.execute("PRAGMA table_info('my_table_name')").fetchall()
返回值是a list of tuple; tuple分别含有下列元素：
cid name type notnull dflt_value pk
e.g.:
[(0, 'ORDER_IN_PAIR_UTCTIME', 'TIMESTAMP', 1, None, 0)]

dot command in python

First of all, dot command is just for sql shell.

.schema – corresponding to PRAGMA

参数占位

Best Practice

Don’t use autoincrement^[9]

If ‘WITHOUT ROWID’ option isn’t specified, you always get an implicit auto increment column called rowid.
If you don’t specify the rowid value or use a NULL value when you insert a new row, SQLite assigns an integer that is one larger than the largest rowid in the table.
The maximum value of rowid column is 9223372036854775807.
diference between ‘autoincrement’ and ‘rowid’: ‘autoincrement’ only complains when rowid is overflow.

sqlite note

基本概念

main database^[1]

回滚日志^[2]

TEMP^[2]

字段类型

sqlite_master & sqlite_temp_master

SQL语句的种类

transaction

about Savepoint

VACUUM

约束

操作

dot command

command line

INSERT

ATTACH

VIEW(视图)

ALTER TABLE

select

UPDATE

UPDATE multiple rows

DELETE

字符串匹配

GLOB

LIKE

python

connection^[4]

connection as a context manager^[4]

cursor

cursor in sqlite module of python^[6]

commit

read datetime.datetime from sqlite3^[7]

Using adapters to store additional Python types^[8]

PRAGMA

dot command in python

参数占位

Best Practice

Don’t use autoincrement^[9]

reference

基本概念

main database[1]

回滚日志[2]

TEMP[2]

字段类型

sqlite_master & sqlite_temp_master

SQL语句的种类

transaction

about Savepoint

VACUUM

约束

操作

dot command

command line

INSERT

ATTACH

VIEW(视图)

ALTER TABLE

select

UPDATE

UPDATE multiple rows

DELETE

字符串匹配

GLOB

LIKE

python

connection[4]

connection as a context manager[4]

cursor

cursor in sqlite module of python[6]

commit

read datetime.datetime from sqlite3[7]

Using adapters to store additional Python types[8]

PRAGMA

dot command in python

参数占位

Best Practice

Don’t use autoincrement[9]

reference

main database^[1]

回滚日志^[2]

TEMP^[2]

connection^[4]

connection as a context manager^[4]

cursor in sqlite module of python^[6]

read datetime.datetime from sqlite3^[7]

Using adapters to store additional Python types^[8]

Don’t use autoincrement^[9]