sqlite3: tool for sqlite

• install

get the source package or precompiled binary from http://www.sqlite.org/download.html

i just download a bin at my linux server, and ‘cp’ the sqlite3 into /usr/local/bin/

• command
  open a db file and show the database info

$sqlite3 project.db 
SQLite version 3.8.11.1 2015-07-29 20:00:57
Enter ".help" for usage hints.
sqlite> .database
seq  name             file                                                      
---  ---------------  ----------------------------------------------------------
0    main             /home/admin/wei.xiew/mypyspider/data/project.db      

list tables and their schema

sqlite> .tables
projectdb
sqlite> .schema
CREATE TABLE `projectdb` (
                name PRIMARY KEY,
                `group`,
                status, script, comments,
                rate, burst, updatetime
                );

open another db file and show info

sqlite> .open ./task.db 
sqlite> .database
seq  name             file                                                      
---  ---------------  ----------------------------------------------------------
0    main             /home/admin/wei.xiew/mypyspider/data/./task.db            
sqlite> .schema
CREATE TABLE `taskdb_test_douban_movie` (
                taskid PRIMARY KEY,
                project,
                url, status,
                schedule, fetch, process, track,
                lastcrawltime, updatetime
                );
CREATE INDEX `status_taskdb_test_douban_movie_index` ON `taskdb_test_douban_movie` (status);

• DML

sqlite> select count(*) from projectdb;
8
sqlite> select name from projectdb;
debug_dianping
test_UrlCrawl
test_dianping
test_dianping_zkgui
test_dianping_zkgui2
test_douban_movie
test_meituan
test_scrapy
sqlite> select name, status, comments, updatetime from projectdb;
test_scrapy|TODO||1440835716.78575
test_douban_movie|STOP||1441091444.00502
test_meituan|RUNNING||1442988518.57949
test_UrlCrawl|CHECKING||1441089121.88823
test_dianping|RUNNING||1442974044.22265
test_dianping_zkgui|TODO||1441875429.98845
test_dianping_zkgui2|STOP||1441711287.63994
debug_dianping|TODO||1442477219.00787

• references
  usage of sqlite3: http://blog.sina.com.cn/s/blog_74dfa9f401017s69.html