laomie's blog

sqlite tips

sqlite 数据类型

INTEGER (INT, TINYINT, BIGINT)
TEXT (VARCHAR, TEXT, CLOB)
BLOB (BLOB)
REAL (FLOAT, DOUBLE)
NUMERIC (DECIMAL, BOOLEAN, DATE, DATETIME)

常用操作

-- upsert
UPDATE OR IGNORE …
INSERT OR IGNORE …

REPLACE INTO

-- rename table
ALTER TABLE foo RENAME TO bar

-- 查看表
.schema tbl1

数据导入导出

控制台方式

-- 导出csv
sqlite> .headers on
sqlite> .mode csv
sqlite> .output test.csv
sqlite> select * from tbl1;
sqlite> .output stdout

-- 导入csv
sqlite> .separator ,
sqlite> .import imei.csv tac_info

命令行方式

sqlite3 -header -csv /path/file.db "select * from tbl1;" > data.csv
sqlite3 -header -csv /path/file.db < query.sql > data.csv

数据转化

-- time format 
datetime('now', 'localtime')
-- format now strftime('%Y-%m-%d %H:%M:%S', ...)
cast(strftime('%Y%m%d','now') as integer);
cast(strftime('%H','now') as integer);

Other articles

python crawler

Published: 2017-06-14(三)
By laomie

In programming.

tags: 201706 python crawler

python爬虫
read more