onyxfish/csvkit — pull request #259 by jeroenjanssens (2014-03-18 16:18:08+00:00)
Title: Add new utility: sql2csv

This pull request adds a new utility to `csvkit`: `sql2csv`, which is a generic command-line tool that can connect a database, execute a query, and output csv. It might be considered as the complement of `csvsql`. The name `sql2csv` has been chosen in order to be consistent with `in2csv`. It borrows existing code from `csvsql` and `in2csv`, and introduces no additional dependencies. Appropriate tests are included.

SQL queries can specified via a `FILE`, `STDIN`, or `--query`. The latter, if specified, takes precedence. The `--no-header-row` and `--linenumbers` options are supported. Example usage:

```
$ echo 'select * from foo' | sql2csv --db 'sqlite:///foo.db'
a,b,c
1,2,3
```

The default database connection is `sqlite://`, which allows you execute a query that doesn't require a table:

```
$ sql2csv --query 'select 4+5 as answer'
answer
9
```

Interesting side-effect: by combining `sql2csv` with `csvsql`, you can effectively execute SQL queries on CSV files:

```bash
#!/usr/bin/env bash
TMPDB="$(mktemp)"
csvsql --db "sqlite:///${TMPDB}" --table 'csv' --insert
sql2csv --db "sqlite:///${TMPDB}" --query "$1"
rm $TMPDB
```