slyce
Ruby utility to show summary statistics or export data from MySQL, SQLite, or DuckDB.
TODO
- Unify each each executable to have as close to the same features as possible
Usage
usage: slyce [options] <database> <table>
--csv Output comma separated values
--psv Output pipe separated values
--tsv Output tab separated values
-a, --ascii Convert data to ASCII using AnyAscii
-c, --columns Display column names and quit
-h, --help Show help and command usage
-H, --headerless Do not show headers when exporting delimited files
-n, --natural Sort naturally, not numerically
-s, --show <count> Show this many values
-t, --tables Display table names and quit
-v, --version Show version number
-w, --where <cond> Where clause (eg - 'age>50 and state='AZ')
-x, --extract <col1,col2,...> Comma separated list of columns to extract
Supported platforms
MySQL
The executable is called slyce
.
SQLite
The executable is called slyce3
and requires the regexp
sqlite3 extension from https://github.com/nalgeon/sqlean
You can download the latest version at: https://github.com/nalgeon/sqlean/releases/latest
For example, on Apple Silicon with macOS with an M1 you can use:
wget https://github.com/nalgeon/sqlean/releases/download/0.19.3/sqlean-macos-arm64.zip
unzip sqlean-macos-arm64.zip regexp.dylib
DuckDB
The executable is called slyced
. It requires the duckdb
gem, which can be installed with:
gem install duckdb
Example
The following will open my-database.duck
and will export the first 10 rows
with the last_name
field equal to Miller
and it will extract six fields
starting with id,first_name,...
. The output will be a csv
file to STDOUT
.
slyced my-database.duck members -r 10 -w "last_name='Miller'" -xid,first_name,city,state,zip,medical_plan --csv
The following will show the most frequent 5 values from the temp_table
table
in the nearsite
database for these fields: ef_id,external_id,insurance_plan
.
In addition, only rows with the condition specified in the -w
option are shown.
slyce nearsite temp_table -s 10 -w "ef_id>=251 and benefit_status='X'" -x ef_id,external_id,insurance_plan -r 5
The resulting table looks like:
ef_id
=====
781 255
35 253
31 510
19 251
5 258
-----
871 shown (top 5)
879 total (all 10)
external_id
===========
816 NULL
1 1235
1 1266
1 1320
1 1548
-----
820 shown (top 5)
879 total (all 64)
insurance_plan
==============
176 Aetna PPO
144 Aetna HSA
103 NULL
82 Kaiser Northern CA
73
-----
578 shown (top 5)
879 total (all 35)