Yifei’s notes: 所谓的事务、锁、存储过程、外键等等不适合互联网公司的业务场景，而更适合于“企业级”“IOE”这些应用。
数据类型
sqlite 是动态数据类型的，也就是实际上他并不会限定每一列一定要存什么数据。sqlite 中定义数据类型紧紧是为了兼容 sql 语句。
sqlite 中有五种数据类型：null, integer, real(float), text, blob. sqlite 使用了亲和度来表示一个列倾向于存储什么类型。也分为了五种：integer, read, numeric, text, blob. 其中 numeric 就是表示浮点和整数都可以。
mysql 中的 varchar, text, integer, datetime 等都是支持的，分别会对应到这五种类型中对应的亲和度。值得注意的是，datetime 对应到的是数字类型。
主键
使用 primary key 定义主键。默认情况下，主键就是自增的，但是有可能会复用被删除的 id, 使用 autoincrement 关键字之后就会严格自增，而不会复用之前的 id.
sqlite 中的索引可以使用 create index 语句创建：
CREATE [UNIQUE] INDEX index_name
on table_name (column_name1, column_name2);
或者在 create table 语句的字段后面直接添加 index 关键字。
插入数据
可以直接采用 MySQL 的多行语法批量插入数据：
INSERT INTO 'tablename' ('column1', 'column2') VALUES
('data1', 'data2'),
('data1', 'data2');
命令行操作
内置管理命令
% sqlite3 DATABASE # 打开一个数据库
.databases show databases connected to
.dump TABLE dump table in SQL format
.import FILE ?TABLE? import SQL data into table
.indices ?TABLE? show indices of table
.mode MODE set output mode(csv, column
.read FILE excute FILE
.schema ?TABLE? show create statement
.restore ?DB? FILE restore db from file
.headers ON|OFF show headers
美化输出
让 sqlite 的输出更美观，使用 .mode column 和 .headers on 两个命令
sqlite> select * from foo;
234|kshitiz|dba.se
sqlite> .mode column
sqlite> select * from foo;
234 kshitiz dba.se
sqlite> .headers on
sqlite> select * from foo;
bar baz baf
---------- ---------- ----------
234 kshitiz dba.se
导入导出 csv
导出
sqlite> .mode csv -- use ".separator SOME_STRING" for something other than a comma.
sqlite> .headers on
sqlite> .out file.csv
sqlite> select * from MyTable;
导入
sqlite> .mode csv
sqlite> .import CSV_FILE TABLE_NAME
备份还原
sqlite3 DATABASE .dump > backup.sql
sqlite3 DATABASE < backup.sql
或者
sqlite3> .read backup.sql
sqlite3> .dump backup.sql
在 Python 中使用 SQLite
首先连接到数据库，获得 connection 对象，然后再获得 cursor，使用 cursor 来执行 sql 语句并获取结果。
连接
import sqlite3
conn = sqlite3.connect("database.db") # connections
cursor = conn.cursor() # get cursor
cursor.execute("CREATE TABLE books (id int primary key, name text)")
cursor.execute("INSERT INTO books (name) VALUES ('war and peace')")
cursor.execute("INSERT INTO books (name) VALUES ('the bible')")
conn.commit() # always remember to commit
cursor.execute("SELECT * FROM books")
conn.close()
或者使用 with 语句
with sqlite3.connect("db") as conn:
cursor = conn.cursor()
# cursor executes
构建语句
# fabricating statement
cursor.execute("select from books where name = ?", ("the bible",))
# NOTE the param must be a sequence
# You could also use named placeholders
cursor.execute("insert into books (name) values (:name)", {name: "the bible"})
获取数据
有两种方式：
使用 fetchone, fetchmany(n), fetchall
r = c.execute("SELECT id FROM stocks WHERE name = "MSFT"")
id = r.fetchone()[0]
直接迭代返回结果
for row in c.execute("SELECT * FROM stocks ORDER BY price"):
print row[0], row[1], row["id"], row["name"]
注意结果每行是一个 tuple 或者一个 dict，即使 select 了一个元素，结果也是 tuple。
插入数据
lastrowid 属性。This read-only attribute provides the rowid of the last modified row. It is only set if you issued a INSERT statement using the execute()method. For operations other than INSERT or when executemany() is called, lastrowid is set to None.
使用最新版本的 sqlite3
要想使用最新版本的 sqlite3, 要么在编译的时候已经安装了，要么就需要指定一下链接的路径。
export CFLAGS="-DSQLITE_ENABLE_FTS3 \
-DSQLITE_ENABLE_FTS3_PARENTHESIS \
-DSQLITE_ENABLE_FTS4 \
-DSQLITE_ENABLE_FTS5 \
-DSQLITE_ENABLE_JSON1 \
-DSQLITE_ENABLE_LOAD_EXTENSION \
-DSQLITE_ENABLE_RTREE \
-DSQLITE_ENABLE_STAT4 \
-DSQLITE_ENABLE_UPDATE_DELETE_LIMIT \
-DSQLITE_SOUNDEX \
-DSQLITE_TEMP_STORE=3 \
-DSQLITE_USE_URI \
-O2 \
-fPIC"
export PREFIX="/usr/local"
LIBS="-lm" ./configure --disable-tcl --enable-shared --enable-tempstore=always --prefix="$PREFIX"
make
sudo make install # Install system-wide.
这时候还要在指定一下链接路径：
export LD_LIBRARY_PATH=/usr/local/lib
不过如果 prefix 直接使用了 /usr 就不需要了。
参考
- https://www.sqlite.org/datatype3.html
- https://stackoverflow.com/questions/1609637/is-it-possible-to-insert-multiple-rows-at-a-time-in-an-sqlite-database
及时获取更新，请关注公众号“爬虫技术学习(spider-learn)”
