Escaping a SQL query to use with curl and Datasette#

I used this pattern to pass a SQL query to Datasette’s CSV export via curl and output the results, stripping off the first row (the header row) using tail -n +2.

SQL queries need to be URL-encoded - I did that be echoing the SQL query and piping it to a Python one-liner that calls the urllib.parse.quote() function.

1
curl -s "https://github-to-sqlite.dogsheep.net/github.csv?sql=$(echo '
2
select
3
  full_name
4
from
5
  repos
6
where
7
  rowid in (
8
    select
9
      repos.rowid
10
    from
11
      repos,
12
      json_each(repos.topics) j
13
    where
14
      j.value = "datasette-io"
15
  )
16
  and rowid in (
17
    select
18
      repos.rowid
19
    from
20
      repos,
21
      json_each(repos.topics) j
22
    where
23
      j.value = "datasette-plugin"
24
  )
25
order by
26
  updated_at desc
27
' | python3 -c \
28
  'import sys; import urllib.parse; print(urllib.parse.quote(sys.stdin.read()))')" \
29
  | tail -n +2

Here’s that SQL query in the Datasette web UI.

The output from the bash one-liner looks like this:

1
simonw/datasette-edit-schema
2
simonw/datasette-import-table
3
simonw/datasette-dateutil
4
simonw/datasette-seaborn
5
simonw/datasette-backup
6
simonw/datasette-yaml
7
simonw/datasette-schema-versions
8
simonw/datasette-graphql
9
simonw/datasette-insert
10
simonw/datasette-copyable
11
simonw/datasette-auth-passwords
12
simonw/datasette-glitch
13
simonw/datasette-block-robots
14
simonw/datasette-saved-queries
15
simonw/datasette-psutil
16
simonw/datasette-auth-tokens
17
simonw/datasette-permissions-sql
18
simonw/datasette-media
19
simonw/datasette-atom
20
simonw/datasette-vega
21
simonw/datasette-jellyfish
22
simonw/datasette-leaflet-geojson
23
simonw/datasette-template-sql
24
simonw/datasette-render-markdown
25
simonw/datasette-auth-github
26
simonw/datasette-mask-columns
27
simonw/datasette-jq
28
simonw/datasette-cluster-map
29
simonw/datasette-upload-csvs
30
simonw/datasette-publish-fly
31
simonw/datasette-render-images
32
simonw/datasette-render-timestamps
33
simonw/datasette-configure-fts
34
simonw/datasette-search-all
35
simonw/datasette-render-html
36
simonw/datasette-show-errors
37
simonw/datasette-column-inspect
38
simonw/datasette-ics
39
simonw/datasette-json-html
40
simonw/datasette-pretty-json
41
simonw/datasette-sqlite-fts4
42
simonw/datasette-bplist
43
simonw/datasette-render-binary
44
simonw/datasette-rure
45
simonw/datasette-haversine