rows: --transpose or psql extended view-like functionality

[chapmanjacobd]

It would be nice if the rows subcommand had a flag, perhaps called --transpose which would print in long form instead of wide. Similar to extended display mode in psql (\x)

In other words instead of this:

sqlite-utils rows  --limit 5 --fmt github track_metadata.db songs

track_id	title	song_id	release	artist_id	artist_mbid	artist_name	duration	artist_familiarity	artist_hotttnesss	year	track_7digitalid	shs_perf	shs_work
TRMMMYQ128F932D901	Silent Night	SOQMMHC12AB0180CB8	Monster Ballads X-Mas	ARYZTJS1187B98C555	357ff05d-848a-44cf-b608-cb34b5701ae5	Faster Pussy cat	252.055	0.649822	0.394032	2003	7032331	-1	0
TRMMMKD128F425225D	Tanssi vaan	SOVFVAK12A8C1350D9	Karkuteillä	ARMVN3U1187FB3A1EB	8d7ef530-a6fd-4f8f-b2e2-74aec765e0f9	Karkkiautomaatti	156.551	0.439604	0.356992	1995	1514808	-1	0
TRMMMRX128F93187D9	No One Could Ever	SOGTUKN12AB017F4F1	Butter	ARGEKB01187FB50750	3d403d44-36ce-465c-ad43-ae877e65adc4	Hudson Mohawke	138.971	0.643681	0.437504	2006	6945353	-1	0
TRMMMCH128F425532C	Si Vos Querés	SOBNYVR12A8C13558C	De Culo	ARNWYLR1187B9B2F9C	12be7648-7094-495f-90e6-df4189d68615	Yerba Brava	145.058	0.448501	0.372349	2003	2168257	-1	0
TRMMMWA128F426B589	Tangle Of Aspens	SOHSBXH12A8C13B0DF	Rene Ablaze Presents Winter Sessions	AREQDTE1269FB37231		Der Mystic	514.298	0	0	0	2264873	-1	0

The output would look something like this:

$ for col in (sqlite-columns track_metadata.db songs)
    sqlite-utils --fmt github track_metadata.db "select $col from songs order by rowid desc limit 5"
end

track_id
TRYYYVU12903CD01E3
TRYYYDJ128F9310A21
TRYYYMG128F4260ECA
TRYYYJO128F426DA37
TRYYYUS12903CD2DF0
title
-------------------------------------
Fernweh feat. Sektion Kuchikäschtli
Faraday
Novemba
Jago Chhadeo
O Samba Da Vida
song_id
--------------------
SOWXJXQ12AB0189F43
SOLXGOR12A81C21EB7
SOHODZI12A8C137BB3
SOXQYIQ12A8C137FBB
SOTXAME12AB018F136
release
---------------------------------
So Oder So
The Trance Collection Vol. 2
Dub_Connected: electronic music
Naale Baba Lassi Pee Gya
Pacha V.I.P.
artist_id
--------------------
AR7PLM21187B990D08
ARCMCOK1187B9B1073
ARZ3R6M1187B9AF750
ART5FZD1187B9A7FCF
AR7Z4J81187FB3FC59
artist_mbid
--------------------------------------
3af2b07e-c91c-4160-9bda-f0b9e3144ed3
4ac5f3de-c5ad-475e-ad50-41f1ef9dba20
8b97e9c8-61f5-4615-9a96-276f24204e34
2357c400-9109-42b6-b3fe-9e2d9f8e3872
9d50cb20-7e42-45cc-b0dd-154c3e92a577
artist_name
----------------
Texta
Elude
Gabriel Le Mar
Kuldeep Manak
Kiko Navarro
duration
------------
295.079
484.519
553.038
244.166
217.443
artist_familiarity
----------------------
0.552977
0.403668
0.556918
0.4015
0.528617
artist_hotttnesss
---------------------
0.454869
0.256935
0.336914
0.374866
0.411595
year
--------
2004
0
0
0
0
track_7digitalid
--------------------
8486723
5472456
2219291
1632096
7522478
shs_perf
------------
-1
-1
-1
-1
-1
shs_work
------------
0
0
0
0
0

[chapmanjacobd]

piping to jq is good enough usually

[erlend-aasland]

piping to jq is good enough usually

... or python -m json.tool¹, if you don't have jq installed.

Footnotes

1. no fancy colouring, like jq; only pretty-printing ↩