Exporting a SQL query

TL;DR

How to export a query?

Shell mode

.format json
.format csv
.format plain
.format html

Flag argument

anyquery -q "SELECT * FROM table" --json
anyquery -q "SELECT * FROM table" --csv
anyquery -q "SELECT * FROM table" --plain
anyquery -q "SELECT * FROM table" --format html

Introduction

Anyquery allows you to export the result of a query to various formats. See below for the full list of supported formats.

Specify the format

Shell mode

To specify the format once you have entered the shell mode, run:

.format <format>

You can also use the short version for some formats:

.json
.csv

To revert back to the pretty format (the default one), run:

.format pretty
-- or
.mode pretty

Flag argument

To specify the format as a flag argument, run:

# JSON
anyquery -q "SELECT * FROM table" --json
# CSV
anyquery -q "SELECT * FROM table" --csv
# Plain text
anyquery -q "SELECT * FROM table" --plain
# HTML
anyquery -q "SELECT * FROM table" --format html

Redirecting the output

You can redirect the output to a file using the > operator. For example:

anyquery -q "SELECT * FROM table" --json > output.json

In shell mode, you can use .output to specify the output file:

.output output.json
SELECT * FROM table;

All the next queries will be written to the output.json file. To revert back to the standard output, run:

.output

Supported formats

CSV

Export the result of a query to a CSV (RFC 4180) file.

.format csv
-- or
.csv

anyquery -q "SELECT * FROM table" --csv

Example

Exporting the top three most starred repositories from simonw to CSV

anyquery> .csv
Output mode set to CSV

anyquery> SELECT name, stargazers_count as stars FROM github_repositories_from_user('simonw') ORDER BY stars DESC LIMIT 3;
name,stars
datasette,9133
llm,3556
shot-scraper,1591

HTML

Export the result of a query as an HTML table.

.format html

anyquery -q "SELECT * FROM table" --format html

Example

Exporting the top three most starred repositories from simonw to HTML

anyquery> .mode html
Output mode set to html

anyquery> SELECT name, stargazers_count as stars FROM github_repositories_from_user('simonw') ORDER BY stars DESC LIMIT 3;
<table>
    <thead>
    <tr>
        <th>name</th>
        <th>stars</th>
    </tr>
    </thead>
    <tbody>
    <tr>
        <td>datasette</td>
        <td>9133</td>
    </tr>
    <tr>
        <td>llm</td>
        <td>3556</td>
    </tr>
    <tr>
        <td>shot-scraper</td>
        <td>1591</td>
    </tr>
  </tbody>
</table>

JSON

Export the result of a query as a JSON array of objects. Each column is a key in the object.

.format json
-- or
.json

anyquery -q "SELECT * FROM table" --json

Example

Exporting the top three most starred repositories from simonw to JSON

anyquery> .json
Output mode set to JSON

anyquery> SELECT name, stargazers_count as stars FROM github_repositories_from_user('simonw') ORDER BY stars DESC LIMIT 3;
[
  {
    "name": "datasette",
    "stars": 9133
  },
  {
    "name": "llm",
    "stars": 3556
  },
  {
    "name": "shot-scraper",
    "stars": 1591
  }
]

JSONL

Export the result of a query as a JSON Lines file. Each line is a JSON object representing a row separated by a newline.

.format jsonl

anyquery -q "SELECT * FROM table" --format jsonl

Example

Exporting the top three most starred repositories from simonw to JSONL

anyquery> .mode jsonl
Output mode set to jsonl

anyquery> SELECT name, stargazers_count as stars FROM github_repositories_from_user('simonw') ORDER BY stars DESC LIMIT 3;
{"name":"datasette","stars":9133}
{"name":"llm","stars":3556}
{"name":"shot-scraper","stars":1591}

Line by line

Export the result of a query where each line is a column of a row. Rows are separated by ---.

.format linebyline

anyquery -q "SELECT * FROM table" --format linebyline

Example

Exporting the top three most starred repositories from simonw line by line

anyquery> .mode linebyline
Output mode set to linebyline

anyquery> SELECT name, stargazers_count as stars FROM github_repositories_from_user('simonw') ORDER BY stars DESC LIMIT 3;
name: datasette
stars: 9133
---
name: llm
stars: 3556
---
name: shot-scraper
stars: 1591

Markdown

Export the result of a query as a markdown table that can be pasted to GitHub, Notion, and pretty much any markdown editor.

.format markdown

anyquery -q "SELECT * FROM table" --format markdown

Example

Exporting the top three most starred repositories from simonw to Markdown

anyquery> .mode markdown
Output mode set to markdown

anyquery> SELECT name, stargazers_count as stars FROM github_repositories_from_user('simonw') ORDER BY stars DESC LIMIT 3;
| name | stars |  |
| ---- | ----- |
| datasette    | 9133  |
| llm          | 3556  |
| shot-scraper | 1591  |

Plain text

Export the result of a query as plain text (column values separated by a tab and rows separated by a newline). This is the default one if stdout is not a terminal.

.format plain

anyquery -q "SELECT * FROM table" --plain

Example

Exporting the top three most starred repositories from simonw to plain text

anyquery> .mode plain
Output mode set to plain

anyquery> SELECT name, stargazers_count as stars FROM github_repositories_from_user('simonw') ORDER BY stars DESC LIMIT 3;
datasette  9133
llm  3556
shot-scraper  1591

Plain with headers

Similar to the plain text format, but with the column names as the first row.

.format plainheader

anyquery -q "SELECT * FROM table" --format plainheader

Example

Exporting the top three most starred repositories from simonw to plain text with headers

anyquery> .mode plainheader
Output mode set to plainheader

anyquery> SELECT name, stargazers_count as stars FROM github_repositories_from_user('simonw') ORDER BY stars DESC LIMIT 3;
name  stars
datasette  9133
llm  3556
shot-scraper  1591

Pretty

Export the result of a query into a nice ASCII table. This is the default one if stdout is a terminal.

.format pretty

anyquery -q "SELECT * FROM table" --format pretty

Example

Exporting the top three most starred repositories from simonw to a pretty table

anyquery> .format pretty
Output mode set to pretty

anyquery> SELECT name, stargazers_count as stars FROM github_repositories_from_user('simonw') ORDER BY stars DESC LIMIT 3;
+--------------+-------+
|     name     | stars |
+--------------+-------+
| datasette    |  9133 |
| llm          |  3556 |
| shot-scraper |  1591 |
+--------------+-------+
3 results

TSV

Export the result of a query as a tab-separated values file (alias to plain with headers).

.format tsv

anyquery -q "SELECT * FROM table" --format tsv

Example

Exporting the top three most starred repositories from simonw to TSV

anyquery> .format tsv
Output mode set to tsv

anyquery> SELECT name, stargazers_count as stars FROM github_repositories_from_user('simonw') ORDER BY stars DESC LIMIT 3;
name  stars
datasette  9133
llm  3556
shot-scraper  1591

Ugly json

Similar to the JSON format, but without any indentation.

.format uglyjson

anyquery -q "SELECT * FROM table" --format uglyjson

Example

Exporting the top three most starred repositories from simonw to ugly JSON

anyquery> .format uglyjson
Output mode set to uglyjson

anyquery> SELECT name, stargazers_count as stars FROM github_repositories_from_user('simonw') ORDER BY stars DESC LIMIT 3;
[{"name":"datasette","stars":9133},{"name":"llm","stars":3556},{"name":"shot-scraper","stars":1591}]

Missing format?

If you need a format that is not supported, please open an issue to request it.