4 ms·
You can do this with https://github.com/dinedal/textql https://github.com/dinedal/textql as well, and a little easier as you don't need to import the data expli
by dinedal 5y ago
You can do this with https://github.com/dinedal/textql https://github.com/dinedal/textql as well, and a little easier as you don't need to import the data explicitly.
SQL_QUERY=<<-EOS
SELECT
category.name AS category,
composition.key,
composition.composer,
composition.name AS composition,
concert.name AS concert
FROM
category,
concert,
composition,
program
WHERE
julianday(concert.date) < julianday('now')
AND composition.category = category.name
AND program.key = composition.key
AND program.date = concert.date
ORDER BY
category.sequence,
composition.key
;
EOS
textql --header --dlm=tab --sql $SQL_QUERY category.tsv composition.tsv concert.tsv program.tsv
- kingosticks 5y agoOr with https://simonwillison.net/2021/Jun/19/sqlite-utils-memory/ https://simonwillison.net/2021/Jun/19/sqlite-utils-memory/
- seppel 5y agoOr with http://harelba.github.io/q/ http://harelba.github.io/q/
- hnjst 5y agoOr with https://csvkit.readthedocs.io/en/latest/ https://csvkit.readthedocs.io/en/latest/
- zimpenfish 5y ago+1 for csvkit because it is extremely useful when dealing with people who love spreadsheets to the point of insanity.
- SahAssar 5y agoSeems like textql is a wrapper around sqlite, I'm guessing staying with sqlite directly makes it easier to interact with other tooling. One nitpick with textql is that it says "sqlite import will not accept stdin, breaking unix pipes. textql will happily do so.", but that's not true, you just need to tell it to use stdin by doing: sqlite3 '.import /dev/stdin {youtablename}' I do this all the time for using gzip and sqlite to get compressed import with progress. For example filtering a compressed CSV from a SQL query with progress for import: pv icons.csv.gz | gzip -dc | sqlite3 ':memory:' -csv '.import /dev/stdin data' '.output result.csv' '.headers on' "SELECT name, keywords from data where name = 'chart-area'"
- tyingq 5y agoDo keep in mind that /dev/stdin{out,err} aren't completely portable. Bash emulates them if they don't exist, but this usage bypasses Bash. So there are unix like platforms where this trick won't work. They do, though, seem to be supported on the ones that are most popular now (Linux, MacOS, FreeBSD).