Search my code examples

Run searches against all of the code examples I have ever included on my blog.

Owned by simonw, visibility: Public

Query parameters

SQL query
with results_stripped as (
  select id, title,
    replace(replace(replace(replace(replace(regexp_replace(
      (regexp_matches(body, '<pre>(.*?)</pre>', 'g'))[1],
        E'<[^>]+>', '', 'gi'
      ), '&quot' || chr(59), '"'), '&gt' || chr(59), '>'), '&lt' || chr(59), '<'), '&#039' || chr(59), chr(39)), '&amp' || chr(59), '&'
    ) as code
  from
    blog_entry
  where
    body ~ '<pre>.*<pre>'
)
select id, title, code, 'https://simonwillison.net/e/' || id as link from results_stripped
where code like '%%' || %(search)s || '%%' order by id desc limit 10

10 rows

id title code link
8089 Joining CSV and JSON data with an in-memory SQLite database $ curl -s 'https://api.github.com/users/dogsheep/repos' \ | sqlite-utils memory - ' select sum(size), sum(stargazers_count) from stdin ' -t sum(size) sum(stargazers_count) ----------- ----------------------- 843 934 https://simonwillison.net/e/8089
8089 Joining CSV and JSON data with an in-memory SQLite database select full_name, forks_count, stargazers_count as stars from stdin order by stars desc limit 3 https://simonwillison.net/e/8089
8089 Joining CSV and JSON data with an in-memory SQLite database $ curl -s 'https://api.github.com/users/dogsheep/repos' \ | sqlite-utils memory - ' select full_name, forks_count, stargazers_count as stars from stdin order by stars desc limit 3 ' [{"full_name": "dogsheep/twitter-to-sqlite", "forks_count": 12, "stars": 225}, {"full_name": "dogsheep/github-to-sqlite", "forks_count": 14, "stars": 139}, {"full_name": "dogsheep/dogsheep-photos", "forks_count": 5, "stars": 116}] https://simonwillison.net/e/8089
8089 Joining CSV and JSON data with an in-memory SQLite database $ curl -s 'https://api.github.com/users/dogsheep/repos' \ | sqlite-utils memory - ' select full_name, forks_count, stargazers_count as stars from stdin order by stars desc limit 3 ' -t --fmt=latex \begin{tabular}{lrr} \hline full\_name & forks\_count & stars \\ \hline dogsheep/twitter-to-sqlite & 12 & 225 \\ dogsheep/github-to-sqlite & 14 & 139 \\ dogsheep/dogsheep-photos & 5 & 116 \\ \hline \end{tabular} https://simonwillison.net/e/8089
8089 Joining CSV and JSON data with an in-memory SQLite database $ sqlite-utils memory us-states.csv vaccination_data.json " select max(t1.date), t1.state, t1.cases, t1.deaths, t2.Census2019, t2.Dist_Per_100K from t1 join t2 on t1.state = replace(t2.LongName, 'New York State', 'New York') group by t1.state order by Dist_Per_100K desc " -t max(t1.date) state cases deaths Census2019 Dist_Per_100K -------------- ------------------------ ------- -------- ------------ --------------- 2021-06-18 District of Columbia 49243 1141 705749 149248 2021-06-18 Vermont 24360 256 623989 146257 2021-06-18 Rhode Island 152383 2724 1059361 141291 2021-06-18 Massachusetts 709263 17960 6892503 139692 2021-06-18 Maryland 461852 9703 6045680 138193 2021-06-18 Maine 68753 854 1344212 136894 2021-06-18 Hawaii 35903 507 1415872 136024 ... https://simonwillison.net/e/8089
8089 Joining CSV and JSON data with an in-memory SQLite database $ curl -s 'https://api.github.com/users/dogsheep/repos' \ | sqlite-utils memory - ' select full_name, forks_count, stargazers_count as stars from stdin order by stars desc limit 3 ' --csv full_name,forks_count,stars dogsheep/twitter-to-sqlite,12,225 dogsheep/github-to-sqlite,14,139 dogsheep/dogsheep-photos,5,116 https://simonwillison.net/e/8089
8089 Joining CSV and JSON data with an in-memory SQLite database $ curl -s 'https://api.github.com/users/dogsheep/repos' \ | sqlite-utils memory - ' select full_name, forks_count, stargazers_count as stars from stdin order by stars desc limit 3 ' -t full_name forks_count stars -------------------------- ------------- ------- dogsheep/twitter-to-sqlite 12 225 dogsheep/github-to-sqlite 14 139 dogsheep/dogsheep-photos 5 116 https://simonwillison.net/e/8089
8084 Django SQL Dashboard select * from blog_entry_tags join blog_tag on blog_tag.id = blog_entry_tags.tag_id https://simonwillison.net/e/8084
8084 Django SQL Dashboard select "tag", count(*) as n from ( select * from blog_entry_tags join blog_tag on blog_tag.id = blog_entry_tags.tag_id ) as results group by "tag" order by n desc https://simonwillison.net/e/8084
8084 Django SQL Dashboard select id, title, code, 'https://simonwillison.net/e/' || id as link from results_stripped https://simonwillison.net/e/8084
Copy and export data

Duration: 1598.78ms