{infiniteZest}
// Articles. Tutorials. Utilities.
Home  |   Search  |   Login  
Categories Skip Navigation Links
New / All
AJAX
Apple
ASP.NET
.NET
Git
Google / Android
Python / IronPython
Miscellaneous
SQL Server
Creating User Friendly Output from SQLite
Summary
This article explains how to get pretty / nice output from the SQLite command line shell program.
 
Table of Contents

The Commands

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

The Default Output

Code Listing 2. A simple db with contacts and addresses

The Current Settings

Code Listing 3. The Default Settings

Changing to Column Mode

Code Listing 4. The Column Display

The Column Headers

Code Listing 4. Turning the headers on

Width of the columns

Code Listing 5. Specifying the width of the columns

Other Options in Mode

Code Listing 6. Various modes of data display

 

The Commands

To begin with, for additional information on the SQLite shells, refer to the following articles:

Creating databases from SQLite shell

Using SQLite from Shell in Android

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 <table> 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;
<TR><TH>title</TH><TH>email</TH><TH>_id</TH><TH>addrid</TH><TH>name</TH></TR>
<TR><TD>Chairman</TD>
<TD>billg@microsoft.com</TD>
<TD>1</TD>
<TD>1</TD>
<TD>Bill Gates</TD>
</TR>
<TR><TD>CEO</TD>
<TD>steve@apple.com</TD>
<TD>2</TD>
<TD>2</TD>
<TD>Steve Jobs</TD>
</TR>
<TR><TD>Senator</TD>
<TD>hillary@senate.us.gov</TD>
<TD>3</TD>
<TD>3</TD>
<TD>Hillary Clinton</TD>
</TR>
<TR><TD>Senator</TD>
<TD>mccain@senate.us.gov</TD>
<TD>4</TD>
<TD>3</TD>
<TD>John McCain</TD>
</TR>
<TR><TD>Senator</TD>
<TD>obama@senate.us.gov</TD>
<TD>5</TD>
<TD>3</TD>
<TD>Barack Obama</TD>
</TR>


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’);

Bookmark and Share This

More Articles With Similar Tags
This article talks about various aspects of creating an SQLite database. It also uses Android SQLite shell as an example.
icon-sqlite-source-new-project-wizard.jpg
This article shows how to set up a project and compile the SQLite shell and database engine source code with Visual Studio. Screenshots included.
icon-android-shell-main-method.jpg
This article talks about the SQLite packages available in Android SDK.
icon-android-sqlite-query.jpg
This article looks at using SQLite from the Android shell.
icon-sqlite-db-browser-data.jpg
This article discusses creating SQLite databases using GUI-based database managers. The example used here is SQLite Database Browser.
About  Contact  Privacy Policy  Site Map