SQLite format Output

The following commands can be used to get formatted output from the sqlite3 shell program.

• .header(s) ON|OFF
• .mode
• .output
• .prompt
• .separator
• .show
• .width

Following are the short help strings from the command prompt for the above commands.

Code Listing 1. Short descriptions of output formatting commands from SQLite shell

.header(s) ON|OFF Turn display of headers on or off

.mode MODE ?TABLE? Set output mode where MODE is one of:
csv Comma-separated values
column Left-aligned columns. (See .width)
html HTML

code
insert SQL insert statements for TABLE
line One value per line
list Values delimited by .separator string
tabs Tab-separated values
tcl TCL list elements

.nullvalue STRING Print STRING in place of NULL values

.output FILENAME Send output to FILENAME

.output stdout Send output to the screen

.prompt MAIN CONTINUE Replace the standard prompts

.separator STRING Change separator used by output mode and .import

.show Show the current values for various settings

.width NUM NUM ... Set column widths for "column" mode

The Default Output

As you can see in the listing below, the formatting commands will be executed on a simple database with contacts and addresses.

Code Listing 2. A simple db with contacts and addresses

D:\Research\sqlite\sqlite-3_5_7>sqlite3.exe contactsext.db
SQLite version 3.5.7
Enter ".help" for instructions


sqlite> .tables
Address Contacts


sqlite> .schema
CREATE TABLE Address (_id INTEGER PRIMARY KEY, city TEXT, country TEXT, line1 TE
XT, line2 TEXT, region TEXT);
CREATE TABLE Contacts (title TEXT, email TEXT, _id INTEGER PRIMARY KEY, addrid N
UMERIC, name TEXT);


sqlite> select * from contacts;
Chairman|billg@microsoft.com|1|1|Bill Gates
CEO|steve@apple.com|2|2|Steve Jobs
Senator|hillary@senate.us.gov|3|3|Hillary Clinton
Senator|mccain@senate.us.gov|4|3|John McCain
Senator|obama@senate.us.gov|5|3|Barack Obama


sqlite> select * from address;
1|Seattle|USA|A Street||Washington
2|California|USA|B Street||Cupertino
3|Washington|USA|K Street||DC

If you look at the output from contacts and address, the output is not very pretty. It’s barely readable. And, it would be hard to understand the data without the columns (especially if the tables are non-obvious unlike contacts and address).

The Current Settings

We can see the current settings by using the command .show. Since no changes were made to the settings, these are the default settings.

Code Listing 3. The Default Settings

sqlite> .show
echo: off
explain: off
headers: off
mode: list
nullvalue: ""
output: stdout
separator: "|"
width:

Changing to Column Mode

The mode by default is list. Using the .mode command, lot of formatting can be accomplished. Setting the display to column, you will see a spread-out column display for the results.

Code Listing 4. The Column Display

sqlite> .mode column


sqlite> .show
echo: off
explain: off
headers: off
mode: column
nullvalue: ""
output: stdout
separator: "|"
width:


sqlite> select * from contacts;
Chairman billg@microsoft.com 1 1 Bill Gates
CEO steve@apple.com 2 2 Steve Jobs
Senator hillary@senate.us.g 3 3 Hillary Cl
Senator mccain@senate.us.go 4 3 John McCai
Senator obama@senate.us.gov 5 3 Barack Oba

The Column Headers

In the above output, we don’t know the column names. For obvious tables like contacts, you probably don’t need column names, but in general they will be helpful. The command ‘.headers on’ would turn on the headers.

Code Listing 4. Turning the headers on

sqlite> .headers on


sqlite> .show
echo: off
explain: off
headers: on
mode: column
nullvalue: ""
output: stdout
separator: "|"
width:


sqlite> select * from contacts;
title email _id addrid name
---------- ------------------- ---------- ---------- ----------
Chairman billg@microsoft.com 1 1 Bill Gates
CEO steve@apple.com 2 2 Steve Jobs
Senator hillary@senate.us.g 3 3 Hillary Cl
Senator mccain@senate.us.go 4 3 John McCai
Senator obama@senate.us.gov 5 3 Barack Oba

The first line from the results to the SELECT statement is the listing of columns. There is one more problem – the output (email and names) is truncated beyond the tenth character.

Width of the columns

Using the .width command, you can adjust the width of the columns so that you can see more of the data. Separate the width of the columns (in number of characters) by a space.

Code Listing 5. Specifying the width of the columns

sqlite> .width 10 25 3 6 15


sqlite> .show
echo: off
explain: off
headers: on
mode: column
nullvalue: ""
output: stdout
separator: "|"
width: 10 25 3 6 15


sqlite> select * from contacts;
title email _id addrid name
---------- ------------------------- --- ------ ---------------
Chairman billg@microsoft.com 1 1 Bill Gates
CEO steve@apple.com 2 2 Steve Jobs
Senator hillary@senate.us.gov 3 3 Hillary Clinton
Senator mccain@senate.us.gov 4 3 John McCain
Senator obama@senate.us.gov 5 3 Barack Obama

As shown in the above listing, you can see that the column headers are there, all the data is shown in a column fashion.

Other Options in Mode

The command .mode is pretty powerful. Setting it to csv will give you the results in a ‘Comma Separated Values’ format – which is good for Excel, etc. You can also generate HTML code by setting the mode to html. You can get the columns on a line by choosing line. Or separate the values by tabs. You can also generate insert statements.

Code Listing 6. Various modes of data display

sqlite> .mode csv
sqlite> select * from contacts;
title,email,_id,addrid,name
Chairman,billg@microsoft.com,1,1,"Bill Gates"
CEO,steve@apple.com,2,2,"Steve Jobs"
Senator,hillary@senate.us.gov,3,3,"Hillary Clinton"
Senator,mccain@senate.us.gov,4,3,"John McCain"
Senator,obama@senate.us.gov,5,3,"Barack Obama"


sqlite> .mode html
sqlite> select * from contacts;

sqlite> .mode line
sqlite> select * from contacts;
title = Chairman
email = billg@microsoft.com
_id = 1
addrid = 1
name = Bill Gates

title = CEO
email = steve@apple.com
_id = 2
addrid = 2
name = Steve Jobs

title = Senator
email = hillary@senate.us.gov
_id = 3
addrid = 3
name = Hillary Clinton

title = Senator
email = mccain@senate.us.gov
_id = 4
addrid = 3
name = John McCain

title = Senator
email = obama@senate.us.gov
_id = 5
addrid = 3
name = Barack Obama


sqlite> .mode tab
sqlite> select * from contacts;
title email _id addrid name
Chairman billg@microsoft.com 1 1 Bill Gates
CEO steve@apple.com 2 2 Steve Jobs
Senator hillary@senate.us.gov 3 3 Hillary Clinton
Senator mccain@senate.us.gov 4 3 John McCain
Senator obama@senate.us.gov 5 3 Barack Obama

sqlite> .mode insert
sqlite> select * from contacts;
INSERT INTO table VALUES(’Chairman’,’billg@microsoft.com’,1,1,’Bill Gates’);
INSERT INTO table VALUES(’CEO’,’steve@apple.com’,2,2,’Steve Jobs’);
INSERT INTO table VALUES(’Senator’,’hillary@senate.us.gov’,3,3,’Hillary Clinton’
);
INSERT INTO table VALUES(’Senator’,’mccain@senate.us.gov’,4,3,’John McCain’);
INSERT INTO table VALUES(’Senator’,’obama@senate.us.gov’,5,3,’Barack Obama’);

title	email	_id	addrid	name
Chairman	billg@microsoft.com	1	1	Bill Gates
CEO	steve@apple.com	2	2	Steve Jobs
Senator	hillary@senate.us.gov	3	3	Hillary Clinton
Senator	mccain@senate.us.gov	4	3	John McCain
Senator	obama@senate.us.gov	5	3	Barack Obama