Command line shell for SQLite (CLI)

sqlite.org/cli

sqlite> create table tbl1(one text, two int);
sqlite> insert into tbl1 values('hello!',10);
sqlite> insert into tbl1 values('goodbye', 20);
sqlite> select * from tbl1;
hello!|10
goodbye|20
sqlite>

Changing output formats

The sqlite3 program is able to show the results of a query in 14 different output formats:

You can use the “.mode” dot command to switch between these output formats. The default output mode is “list”.

sqlite> .mode list
sqlite> .mode
sqlite> .separator ", "

Querying the database schema

sqlite> .tables
sqlite> .databases
sqlite> .schema
create table tbl1(one varchar(10), two smallint)
CREATE TABLE tbl2 (
  f1 varchar(30) primary key,
  f2 text,
  f3 real
);
sqlite> .schema tbl2
CREATE TABLE tbl2 (
  f1 varchar(30) primary key,
  f2 text,
  f3 real
);
sqlite>

Opening database files

Use the name “:memory:” to open a new in-memory database.

The —readonly option opens the database in read-only mode. Write will be prohibited.The —readonly option opens the database in read-only mode. Write will be prohibited.

The —append option causes the SQLite database to be appended to an existing file rather than working as a stand-alone file.

Redirecting I/O

Writing results to a file

sqlite> .mode list
sqlite> .separator |
sqlite> .output test_file_1.txt
sqlite> select * from tbl1;
sqlite> .exit
$ cat test_file_1.txt
hello|10
goodbye|20
$

Reading SQL from a file

sqlite> .read myscript.sql
sqlite> .read |myscript.bat

Importing files as CSV or other formats

Use the “.import” command to import CSV (comma separated value) or similarly delimited data into an SQLite table.Use the “.import” command to import CSV (comma separated value) or similarly delimited data into an SQLite table.

sqlite> .import --csv --skip 1 --schema temp C:/work/somedata.csv tab1

Export to CSV

sqlite> .headers on
sqlite> .mode csv
sqlite> .once c:/work/dataout.csv
sqlite> SELECT * FROM tab1;
sqlite> .system c:/work/dataout.csv

Export to Excel

sqlite> .excel
sqlite> SELECT * FROM tab;

Converting An Entire Database To A Text File

$ sqlite3 ex1 .dump | gzip -c >ex1.dump.gz

This generates a file named ex1.dump.gz that contains everything you need to reconstruct the database at a later time, or on another machine. To reconstruct the database, just type:

$ zcat ex1.dump.gz | sqlite3 ex2