issues: 1193090967

This data as json

id node_id number title user state locked assignee milestone comments created_at updated_at closed_at author_association pull_request body repo type active_lock_reason performed_via_github_app reactions draft state_reason
1193090967 I_kwDOBm6k_c5HHR-X 1699 Proposal: datasette query 25778 open 0     6 2022-04-05T12:36:43Z 2022-04-11T01:32:12Z   CONTRIBUTOR  

I started sketching out a plugin to add a datasette query subcommand to export data from the command line. This is based on discussions in #1356 and #1605. Before I get too far down this rabbit hole, I figure it's worth getting some feedback here (unless this should happen in Discussions). Here's what I'm thinking:

At its most basic, it will write the results of a query to STDOUT.

sh datasette query -d data.db 'select * from data' > results.json

This isn't much improvement over using sqlite-utils. To make better use of datasette and its ecosystem, run datasette query using a canned query defined in a metadata.yml file.

For example, using the metadata file from alltheplaces-datasette:

sh cd alltheplaces-datasette datasette query -d alltheplaces.db -m metadata.yml count_by_spider

That query would be good to get as CSV, and we can auto-discover metadata and databases in the current directory:

sh cd alltheplaces-datasette datasette query count_by_spider -f csv

In this case, count_by_spider is a canned query defined on the alltheplaces database. If the same query is defined on multiple databases or its otherwise unclear which database query should use, pass the -d or --database option.

If a query takes parameters, I can pass them in at runtime, using the --param or -p option:

sh datasette query -d data.db -p value something 'select * from neighborhoods where some_column = :value'

I'm very interested in feedback on this, including whether it should be a plugin or in Datasette core. (I don't have a strong opinion about this, but I'm prototyping it as a plugin to start.)

 107914493 issue     
{
    "url": "https://api.github.com/repos/simonw/datasette/issues/1699/reactions",
    "total_count": 0,
    "+1": 0,
    "-1": 0,
    "laugh": 0,
    "hooray": 0,
    "confused": 0,
    "heart": 0,
    "rocket": 0,
    "eyes": 0
}
    

Links from other tables