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:57Enter ".help" for usage hints.sqlite> .databaseseq  name             file                                                      ---  ---------------  ----------------------------------------------------------0    main             /home/admin/wei.xiew/mypyspider/data/project.db      

list tables and their schema

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

open another db file and show info

sqlite> .open ./task.db sqlite> .databaseseq  name             file                                                      ---  ---------------  ----------------------------------------------------------0    main             /home/admin/wei.xiew/mypyspider/data/./task.db            sqlite> .schemaCREATE 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;8sqlite> select name from projectdb;debug_dianpingtest_UrlCrawltest_dianpingtest_dianping_zkguitest_dianping_zkgui2test_douban_movietest_meituantest_scrapysqlite> select name, status, comments, updatetime from projectdb;test_scrapy|TODO||1440835716.78575test_douban_movie|STOP||1441091444.00502test_meituan|RUNNING||1442988518.57949test_UrlCrawl|CHECKING||1441089121.88823test_dianping|RUNNING||1442974044.22265test_dianping_zkgui|TODO||1441875429.98845test_dianping_zkgui2|STOP||1441711287.63994debug_dianping|TODO||1442477219.00787

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