Linux, Linux and Windows

Sqlite3 pretty output
#################

Lets make pretty sqlite output, or else it will look like below unpretty.

NOTE: here pretty means, how cool something looks and how easy it is to read

NOTE: below column examples are missing tabs that the actual output has, just pretend they are there

Pretty just means to add header row (followed by the dashed pretty seperator line). And also to tab out columns.

I show 2 ways to do each command (with and without an echo&pipe).
NOTE: we use this file for example /var/db/smart.sq3 (numbers have been changed to remove identity)

UNPRETTY:
=========

# sqlite3 /var/readynasd/smart.sq3 'select *,datetime(timestamp, "unixepoch") as time from smart_history;'
# echo 'select *,datetime(timestamp, "unixepoch") as time from smart_history;' | sqlite3 /var/readynasd/smart.sq3

Here is the output for both commands (notice its hard to read, and not that pretty)

ST33000650NS|Z397WQPC|0|0|0|0|0|0|0|0|1485182026|2014-11-23 04:47:06
ST33000650NS|Z398417W|0|0|0|0|0|0|0|0|1485182026|2014-11-23 04:47:06
ST33000650NS|Z39848SE|0|0|0|0|0|0|0|0|185182026|2014-11-23 04:47:06
ST33000650NS|Z39857M7|0|0|0|0|0|0|0|0|1485182026|2014-11-23 04:47:06
ST33000650NS|Z3984NSN|0|0|0|0|0|0|0|0|1485182027|2014-11-23 04:47:07
ST33000650NS|Z39824LN|0|0|0|0|0|0|0|0|1485182027|2014-11-23 04:47:07
ST33000650NS|Z3984N5Z|0|0|0|0|0|0|0|0|1485182027|2014-11-23 04:47:07
ST33000650NS|Z3984P62|0|0|0|0|0|0|0|0|1485182027|2014-11-23 04:47:07
ST33000650NS|Z39823LC|0|0|0|0|0|0|0|0|1485182027|2014-11-23 04:47:07
ST33000650NS|Z3984H8S|0|0|0|0|0|0|0|0|1485182027|2014-11-23 04:47:07
ST33000650NS|Z39857JP|0|0|0|0|0|0|0|0|1485182027|2014-11-23 04:47:07
ST33000650NS|Z39857PM|0|0|0|0|0|0|0|0|1485182027|2014-11-23 04:47:07

 

PRETTY:
=======

Method 1  (-header and -cmd ‘.mode columns’)

Use -header  for headers and -cmd ‘.mode columns’  for columns

# sqlite3 -header -cmd '.mode columns' /var/db/smart.sq3 'select *,datetime(timestamp, "unixepoch") as time from smart_history;'

The output is:

model serial realloc_sect realloc_evnt spin_retry_cnt ioedc cmd_timeouts pending_sect uncorrectable_err ata_errors timestamp time
------------ ---------- ------------ ------------ -------------- ---------- ------------ ------------ ----------------- ---------- ---------- -------------------
ST33000650NS Z397WQPC 0 0 0 0 0 0 0 0 1485182026 2013-11-23 04:47:06
ST33000650NS Z398417W 0 0 0 0 0 0 0 0 1485182026 2013-11-23 04:47:06
ST33000650NS Z39848SE 0 0 0 0 0 0 0 0 1485182026 2013-11-23 04:47:06
ST33000650NS Z39857M7 0 0 0 0 0 0 0 0 1485182026 2013-11-23 04:47:06
ST33000650NS Z3984NSN 0 0 0 0 0 0 0 0 1485182027 2013-11-23 04:47:07
ST33000650NS Z39824LN 0 0 0 0 0 0 0 0 1485182027 2013-11-23 04:47:07
ST33000650NS Z3984N5Z 0 0 0 0 0 0 0 0 1485182027 2013-11-23 04:47:07
ST33000650NS Z3984P62 0 0 0 0 0 0 0 0 1485182027 2013-11-23 04:47:07
ST33000650NS Z39823LC 0 0 0 0 0 0 0 0 1485182027 2013-11-23 04:47:07
ST33000650NS Z3984H8S 0 0 0 0 0 0 0 0 1485182027 2013-11-23 04:47:07
ST33000650NS Z39857JP 0 0 0 0 0 0 0 0 1485182027 2013-11-23 04:47:07
ST33000650NS Z39857PM 0 0 0 0 0 0 0 0 1485182027 2013-11-23 04:47:07

Method 2 (echo everything)

We will echo in the following thing into sqlite (the sqlite file will have 1 argument which will pointed at the database)

# echo -e '.mode columns\n.header on\nselect *,datetime(timestamp, "unixepoch") as time from smart_history;'
.mode columns
.header on
select *,datetime(timestamp, "unixepoch") as time from smart_history;

Here is the echo piped to sqlite for the pretty end result

# echo -e '.mode columns\n.header on\nselect *,datetime(timestamp, "unixepoch") as time from smart_history;' | sqlite3 /var/db/smart.sq3

...same output as above...

Method 3 (-header and -column)

Instead of saying “.header on” you can just put “-header” argument

# sqlite3 -header -column /var/db/smart.sq3 'select *,datetime(timestamp, "unixepoch") as time from smart_history;'

...same output as above...

 Method 4 (-header and -column and -cmd ‘.width NUM1 NUM@’)

Adjust widths as well

# sqlite3 -header -columnn -cmd '.width 20 20' /var/db/smart.sq3 'select *,datetime(timestamp, "unixepoch") as time from smart_history;'

...same output as above...

More info on width (it just sets how many chars wide each column is, and it will trim out chars if you pick a small number. we size the columns from left to right. it automatically sizes the columns you dont mention)

.width NUM1 NUM2 ... Set column widths for "column" mode
Negative values right-justify

Here is another example. Here is output without changing widths

# sqlite3 -header -column /var/readynasd/db.sq3 'select * from "$disk_info"'

Output without widths:

id volume_id channel present model serial fw_version rpm sectors capacity controller port hwif pool_type pool_state pool_hostid replaced_with_spare is_signed_by_ntgr
---------- ---------- ---------- ---------- ------------ ---------- ---------- ---------- ---------- ------------- ---------- ---------- ---------- ---------- ---------- ----------- ------------------- -----------------
sda data 0 1 ST31000340NS 9QJ2P2VP SN06 7200 1953525168 1000204886016 0 0 SATA raid 0 871017713 0 0
sdb data 1 1 ST31000340NS 9QJ2P30K SN06 7200 1953525168 1000204886016 0 1 SATA raid 0 871017713 0 0
sdc Sixer 2 1 ST6000DX000- Z4D03N1L CC46 7200 1172104516 6001175126016 0 2 SATA raid 0 0 0 0

Now lets change the width of ID to 3 (since drive letters are only 3 chars big), lets change VOLUME to 6 (since the biggest volume name has 6 chars), lets change the next column which is CHANNEL to 2 (because the values are 1 digit, and I want the column header to appear as CH which makes senses and has close resemblance to the word CHANNEL, setting it to 1 would be enough except the column header would only say C).

# sqlite3 -header -column -cmd '.width 3 6 2' /var/readynasd/db.sq3 'select * from "$disk_info"'

Output with widths 3 and 6 and 2:

id volume ch present model serial fw_version rpm sectors capacity controller port hwif pool_type pool_state pool_hostid replaced_with_spare is_signed_by_ntgr
--- ------ -- ---------- ------------ ---------- ---------- ---------- ---------- ------------- ---------- ---------- ---------- ---------- ---------- ----------- ------------------- -----------------
sda data 0 1 ST31000340NS 9QJ2P2VP SN06 7200 1953525168 1000204886016 0 0 SATA raid 0 871017713 0 0
sdb data 1 1 ST31000340NS 9QJ2P30K SN06 7200 1953525168 1000204886016 0 1 SATA raid 0 871017713 0 0
sdc Sixer 2 1 ST6000DX000- Z4D03N1L CC46 7200 1172104516 6001175126016 0 2 SATA raid 0 0 0 0

NOTE: notice how the remaining columns are sized automatically by sqlite3, and with the width command we size the columns from left to right.

More options on Pretty output 

All of this is from the man page of sqlite3

From this page: http://www.sqlite.org/cli.html

.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

.headers on|off Turn display of headers on or off

.width NUM1 NUM2 ... Set column widths for "column" mode
Negative values right-justify

 

Leave a Reply

Your email address will not be published. Required fields are marked *