Search code examples
bashsqlitegnu-parallel

cat like behavior from a sqlite3 database file

Normally, when I have for example csv's in a flat file, I would do:

cat somefile.dat |gnu_parralel do_something.

Unfortunately now, I have an sqllite3 file, with column contents of type:

text, text, json

I would like to replicate the cat behaviour so that I can do all the downstream work with each row of the sqllite3 db, but I am not sure how to do it in sqlite3. So, Id like:

cat_in_sqllite3 somefile.db |gnu_parralel do_something.

PS: never used sqlite3 so I apologize if this is a daft Q.

Solution

  • You will need to know the names of the tables in the database. If I use this, I find mine has a single table called Data:

    sqlite3 database.db ".tables"  
                 
Data

    Now you can ask for all the records from that table:

    sqlite3 database.db "select * from Data"

0,"27576,28095,4757"
1,"24415,26688,21060"
2,"30487,13470,31387"
3,"10553,25709,27472"
4,"17074,14918,19630"

    You may not like the output format, so you can see the available modes with:

    sqlite3 database.db ".mode ?"

Error: mode should be one of: ascii box column csv html insert json line list markdown qbox quote table tabs tcl

    Then you can choose a different mode and print your data in that mode:

    sqlite3 database.db ".mode json" "select * from Data"
[{"id":0,"channel0":"27576,28095,4757"},
{"id":1,"channel0":"24415,26688,21060"},
{"id":2,"channel0":"30487,13470,31387"},
{"id":3,"channel0":"10553,25709,27472"},
{"id":4,"channel0":"17074,14918,19630"}]

    You might like to include column headers:

    sqlite3 readings.db ".headers on" "select * from Data"

id|channel0
0|27576,28095,4757
1|24415,26688,21060
2|30487,13470,31387
3|10553,25709,27472
4|17074,14918,19630

    Or suppress them:

    sqlite3 readings.db ".headers off" "select * from Data"

0|27576,28095,4757
1|24415,26688,21060
2|30487,13470,31387
3|10553,25709,27472
4|17074,14918,19630