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
8126 Datasette 0.59: The annotated release notes select * from content where created_time = '07:00' and author = :author https://simonwillison.net/e/8126
8126 Datasette 0.59: The annotated release notes select * from [cdcr-prison-totals] where "zipcode" = :zip https://simonwillison.net/e/8126
8118 Weeknotes: Getting my personal Dogsheep up and running again twitter-to-sqlite statuses-lookup twitter.db \ --sql='select tweetId from archive_like' \ --skip-existing Importing 33,382 tweets [------------------------------------] 0% 00:18:28 https://simonwillison.net/e/8118
8118 Weeknotes: Getting my personal Dogsheep up and running again sqlite-utils create-view twitter.db media_details " select json_object('img_src', media_url_https, 'width', 400) as img, tweets.full_text, tweets.created_at, tweets.id as tweet_id, users.screen_name, 'https://twitter.com/' || users.screen_name || '/status/' || tweets.id as tweet_url from media join media_tweets on media.id = media_tweets.media_id join tweets on media_tweets.tweets_id = tweets.id join users on tweets.user = users.id order by tweets.id desc " https://simonwillison.net/e/8118
8117 Datasette on Codespaces, sqlite-utils API reference documentation and other weeknotes select roadside_attractions.name, roadside_attractions.address, attraction_characteristic.name from roadside_attraction_characteristics join roadside_attractions on roadside_attractions.pk = roadside_attraction_characteristics.attraction_id join attraction_characteristic on attraction_characteristic.pk = roadside_attraction_characteristics.characteristic_id https://simonwillison.net/e/8117
8115 Django admin customization, JSON in our PostgreSQL select jsonb_extract_path( airtable_json, 'Reported by', 'name' ) as name, count(*) as n from call_report group by name order by n desc; https://simonwillison.net/e/8115
8111 A CSV export, JSON import workflow for bulk updating our data with extracted_idrefs as ( select public_id, name, (regexp_match(name, 'Walgreens.*#0*([0-9]{1,8})', 'i')) [1] as idref from location where regexp_match(name, 'Walgreens.*#0*([0-9]{1,8})', 'i') is not null ) select json_build_object( 'update', json_object_agg( public_id, json_build_object('add', json_build_array('walgreens:' || idref), 'comment', name) ) ) from extracted_idrefs where public_id not in ( select public_id from location where id in ( select location_id from concordance_location where concordanceidentifier_id in ( select id from concordance_identifier where authority = 'walgreens' ) ) ) https://simonwillison.net/e/8111
8111 A CSV export, JSON import workflow for bulk updating our data select state.name, count(*) as number_of_locations_with_no_county from location join state on state.id = location.state_id where location.county_id is null group by state.name https://simonwillison.net/e/8111
8111 A CSV export, JSON import workflow for bulk updating our data select public_id, latitude, longitude from location where state_id = ( select id from state where name = 'Oregon' ) and county_id is null https://simonwillison.net/e/8111
8111 A CSV export, JSON import workflow for bulk updating our data import csv import httpx import json rows = list(csv.DictReader(open("select-public-id--latitude--lof05de6.csv"))) def county_for_point(latitude, longitude): return httpx.get("https://us-counties.datasette.io/counties/county_for_latitude_longitude.json?_shape=array", params={ "latitude": latitude, "longitude": longitude, }).json()[0]['county_name'] counties = {} # Progress bar: from tqdm.notebook import tqdm for row in tqdm(rows): counties[row["public_id"]] = county_for_point( row["latitude"], row["longitude"] ) update = { "update": { key: { "state": "OR", "county": value } for key, value in counties.items() }, "revision_comment": "Added missing counties" } print(json.dumps(update)) https://simonwillison.net/e/8111
Copy and export data

Duration: 1789.70ms