Simon Willison's Weblog: yaml

Have your agent record video demos of its work with shot-scraper video

2026-06-30T16:54:26+00:00

shot-scraper video is a new command introduced in today's shot-scraper 1.10 release which accepts a storyboard.yml file defining a routine to run against a web application and uses Playwright to record a video of that routine. I've written before about the importance of having coding agents produce demos of their work; this is my latest attempt at enabling them to do that.

Here's an example video created using shot-scraper video, exercising a still in development feature adding the ability to create new tables in Datasette from pasted CSV, TSV or JSON data:

That video was created by running this command:

shot-scraper video datasette-bulk-insert-storyboard.yml \
  --auth datasette-demo-auth.json --mp4

(That --auth JSON file contains a cookie, as described here in the documentation.)

Here's the datasette-bulk-insert-storyboard.yml file:

output: /tmp/datasette-bulk-insert-demo.webm
server:
  - uv
  - --directory
  - /Users/simon/Dropbox/dev/datasette
  - run
  - datasette
  - -p
  - 6419
  - --root
  - --secret
  - "1"
  - /tmp/demo.db
url: http://127.0.0.1:6419/demo/tasks
viewport:
  width: 1280
  height: 720
cursor: true
wait_for: 'button[data-table-action="insert-row"]'
javascript: |
  (() => {
    let clipboardText = "";
    Object.defineProperty(navigator, "clipboard", {
      configurable: true,
      get: () => ({
        writeText: async (text) => {
          clipboardText = String(text);
        },
        readText: async () => clipboardText,
      }),
    });
  })();
scenes:
  - name: Bulk insert existing table rows
    do:
      - pause: 0.8
      - click: 'button[data-table-action="insert-row"]'
      - wait_for: "#row-edit-dialog[open]"
      - pause: 0.5
      - click: ".row-edit-bulk-insert"
      - wait_for: ".row-edit-bulk-textarea"
      - pause: 0.5
      - click: ".row-edit-copy-template"
      - wait_for: "text=Copied"
      - pause: 0.8
      - fill:
          into: ".row-edit-bulk-textarea"
          text: |
            title,owner,status,priority,notes
            Prepare release video,Ana,doing,1,Recorded with shot-scraper
            Check pasted CSV import,Ben,review,3,Previewed before inserting
            Share the branch demo,Chen,queued,2,Bulk insert creates three rows
      - pause: 0.8
      - click: ".row-edit-save"
      - wait_for: "text=Previewing 3 rows."
      - pause: 1.2
      - click: ".row-edit-save"
      - wait_for: "text=3 rows inserted."
      - pause: 1.0
      - click: ".row-edit-cancel"
      - wait_for: "text=Prepare release video"
      - pause: 1.0
  - name: Create a table from pasted CSV
    open: http://127.0.0.1:6419/demo
    wait_for: 'details.actions-menu-links summary'
    do:
      - pause: 0.8
      - click: 'details.actions-menu-links summary'
      - click: 'button[data-database-action="create-table"]'
      - wait_for: "#table-create-dialog[open]"
      - pause: 0.5
      - fill:
          into: ".table-create-table-name"
          text: "launch_metrics"
      - click: ".table-create-from-data"
      - wait_for: ".table-create-data-textarea"
      - pause: 0.5
      - fill:
          into: ".table-create-data-textarea"
          text: |
            metric_id,name,score,recorded_on
            m001,Activation rate,87.5,2026-06-29
            m002,Retention check,72.25,2026-06-30
            m003,CSV import health,95,2026-07-01
      - pause: 0.8
      - click: ".table-create-save"
      - wait_for: "text=Previewing 3 rows."
      - pause: 1.2
      - click: ".table-create-save"
      - wait_for_url: "**/demo/launch_metrics"
      - wait_for: "text=Activation rate"
      - pause: 1.2

The video command documentation includes simpler examples, but for the purpose of this post I thought I'd go with something more comprehensive.

That demo YAML storyboard was constructed entirely by GPT-5.5 xhigh running in Codex Desktop, using the following prompt run inside my ~/dev/datasette checkout of this branch:

Review the changes on this branch.

cd to ~/dev/shot-scraper and run the command "uv run shot-scraper video --help"

Now use that new video command to record a video demo of the new features from this branch, including running a "uv run datasette -p 6419 --root --secret 1 /tmp/demo.db" development server so you can record the video against a demo DB that you first create.

Now that I've released the feature the prompt could say "run uvx shot-scraper video --help" instead and it should achieve the same result.

I really like this pattern where the --help output for a command provides enough detail that a coding agent can use it - it works kind of like bundling a SKILL.md file directly inside the tool. I used the same pattern for showboat and rodney.

How I built this

shot-scraper video started as an experimental prototype. shot-scraper is built on top of Playwright, and the key feature it needed was for Playwright to be able to record video of browser sessions with enough control to create the desired demo.

I first tried this a few years ago and found that the Playwright-produced videos included additional chrome that was useful for debugging a test failure but unwanted for a product demo.

They fixed that a while ago, but there were still some minor blockers. In particular I was getting a few white frames at the start of the videos, since the recording mechanism kicked in before the first URL was loaded by the browser.

Playwright 1.59 added a new screencast mechanism providing much more finely grained control over video recording. This was very nearly what I needed, but the resulting videos were fixed at 800px wide.

I found a landed PR fixing that but it wasn't yet in a release. Then yesterday they shipped it in playwright-python 1.61.0 and I was finally unblocked to finish implementing the feature!

The code itself was all written by GPT-5.5 xhigh in Codex Desktop. I had it write the documentation as well which gave me a very useful frame for reviewing the design - much of the iteration on the feature came from reviewing that documentation, spotting things that were redundant, inconsistent or confusing, and requesting (or dictating) a better design.

The YAML format itself was mostly defined by the coding agent. I had it use Pydantic to both define and validate the format, partly to make the design easier to review.

This is a great example of the kind of feature that I almost certainly wouldn't have taken on without coding agent support. I filed the original issue in February 2024, and had difficulty finding the necessary time to solve this in amongst all of my other projects.

Tags: projects, python, yaml, ai, datasette, playwright, shot-scraper, generative-ai, llms, pydantic, coding-agents, agentic-engineering

datasette.io news preview

2026-04-16T00:18:03+00:00

Tool: datasette.io news preview

The datasette.io website has a news section built from this news.yaml file in the underlying GitHub repository. The YAML format looks like this:

- date: 2026-04-15
  body: |-
    [Datasette 1.0a27](https://docs.datasette.io/en/latest/changelog.html#a27-2026-04-15) changes how CSRF protection works in a way that simplifies form and API integration, and introduces a new `RenameTableEvent` for when a table is renamed by a SQL query.
- date: 2026-03-18
  body: |-
    ...

This format is a little hard to edit, so I finally had Claude build a custom preview UI to make checking for errors have slightly less friction.

I built it using standard claude.ai and Claude Artifacts, taking advantage of Claude's ability to clone GitHub repos and look at their content as part of a regular chat:

Clone https://github.com/simonw/datasette.io and look at the news.yaml file and how it is rendered on the homepage. Build an artifact I can paste that YAML into which previews what it will look like, and highlights any markdown errors or YAML errors

Tags: tools, yaml, datasette, claude, vibe-coding

model.yaml

2025-06-21T17:15:21+00:00

model.yaml

From their GitHub repo it looks like this effort quietly launched a couple of months ago, driven by the LM Studio team. Their goal is to specify an "open standard for defining crossplatform, composable AI models".

A model can be defined using a YAML file that looks like this:

model: mistralai/mistral-small-3.2
base:
  - key: lmstudio-community/mistral-small-3.2-24b-instruct-2506-gguf
    sources:
      - type: huggingface
        user: lmstudio-community
        repo: Mistral-Small-3.2-24B-Instruct-2506-GGUF
metadataOverrides:
  domain: llm
  architectures:
    - mistral
  compatibilityTypes:
    - gguf
  paramsStrings:
    - 24B
  minMemoryUsageBytes: 14300000000
  contextLengths:
    - 4096
  vision: true

This should be enough information for an LLM serving engine - such as LM Studio - to understand where to get the model weights (here that's lmstudio-community/Mistral-Small-3.2-24B-Instruct-2506-GGUF on Hugging Face, but it leaves space for alternative providers) plus various other configuration options and important metadata about the capabilities of the model.

I like this concept a lot. I've actually been considering something similar for my LLM tool - my idea was to use Markdown with a YAML frontmatter block - but now that there's an early-stage standard for it I may well build on top of this work instead.

I couldn't find any evidence that anyone outside of LM Studio is using this yet, so it's effectively a one-vendor standard for the moment. All of the models in their Model Catalog are defined using model.yaml.

Tags: standards, yaml, ai, generative-ai, llms, llm, lm-studio

openai/openai-openapi

2024-12-22T22:59:25+00:00

openai/openai-openapi

Seeing as the LLM world has semi-standardized on imitating OpenAI's API format for a whole host of different tools, it's useful to note that OpenAI themselves maintain a dedicated repository for a OpenAPI YAML representation of their current API.

(I get OpenAI and OpenAPI typo-confused all the time, so openai-openapi is a delightfully fiddly repository name.)

The openapi.yaml file itself is over 26,000 lines long, defining 76 API endpoints ("paths" in OpenAPI terminology) and 284 "schemas" for JSON that can be sent to and from those endpoints. A much more interesting view onto it is the commit history for that file, showing details of when each different API feature was released.

Browsing 26,000 lines of YAML isn't pleasant, so I got Claude to build me a rudimentary YAML expand/hide exploration tool. Here's that tool running against the OpenAI schema, loaded directly from GitHub via a CORS-enabled fetch() call: https://tools.simonwillison.net/yaml-explorer#.eyJ1c... - the code after that fragment is a base64-encoded JSON for the current state of the tool (mostly Claude's idea).

The tool is a little buggy - the expand-all option doesn't work quite how I want - but it's useful enough for the moment.

Update: It turns out the petstore.swagger.io demo has an (as far as I can tell) undocumented ?url= parameter which can load external YAML files, so here's openai-openapi/openapi.yaml in an OpenAPI explorer interface.

Tags: apis, tools, yaml, ai, openai, generative-ai, llms, ai-assisted-programming, claude-3-5-sonnet

YAML Explorer

2024-12-22T22:44:37+00:00

Tool: YAML Explorer

Tags: yaml

JSON to YAML Converter

2024-12-15T20:47:29+00:00

Tool: JSON to YAML Converter

Tags: yaml

Auto-formatting YAML files with yamlfmt

2023-07-13T14:11:44+00:00

TIL: Auto-formatting YAML files with yamlfmt

Tags: yaml

Automating screenshots for the Datasette documentation using shot-scraper

2022-10-14T23:44:03+00:00

I released shot-scraper back in March as a tool for keeping screenshots in documentation up-to-date.

It's very easy for feature screenshots in documentation for a web application to drift out-of-date with the latest design of the software itself.

shot-scraper is a command-line tool that aims to solve this.

You can use it to take one-off screenshots like this:

shot-scraper https://latest.datasette.io/ --height 800

Or you can define multiple screenshots in a single YAML file - let's call this shots.yml:

- url: https://latest.datasette.io/
  height: 800
  output: index.png
- url: https://latest.datasette.io/fixtures
  height: 800
  output: database.png

And run them all at once like this:

shot-scraper multi shots.yml

This morning I used shot-scraper to replace all of the existing screenshots in the Datasette documentation with up-to-date, automated equivalents.

I decided to use this as an opportunity to create a more detailed tutorial for how to use shot-scraper for this kind of screenshot automation project.

Four screenshots to replace

Datasette's documentation included four screenshots that I wanted to replace with automated equivalents.

full_text_search.png illustrates the full-text search feature:

advanced_export.png displays Datasette's "advanced export" dialog:

binary_data.png displays just a small fragment of a table with binary download links:

facets.png demonstrates faceting against a table:

I'll walk through each screenshot in turn.

full_text_search.png

I decided to use a different example for the new screenshot, because I don't currently have a live instance for that table running against the most recent Datasette release.

I went with https://register-of-members-interests.datasettes.com/regmem/items?_search=hamper&_sort_desc=date - a search against the UK register of members interests for "hamper" (see Exploring the UK Register of Members Interests with SQL and Datasette).

The existing image in the documentation was 960 pixels wide, so I stuck with that and tried a few iterations until I found a height that I liked.

I installed shot-scraper and ran the following, in my /tmp directory:

shot-scraper 'https://register-of-members-interests.datasettes.com/regmem/items?_search=hamper&_sort_desc=date' \
  -h 585 \
  -w 960

This produced a register-of-members-interests-datasettes-com-regmem-items.png file which looked good when I opened it in Preview.

I turned that into the following YAML in my shots.yml file:

- url: https://register-of-members-interests.datasettes.com/regmem/items?_search=hamper&_sort_desc=date
  height: 585
  width: 960
  output: regmem-search.png

Running shot-scraper multi shots.yml against that file produced this regmem-search.png image:

advanced_export.png

This next image isn't a full page screenshot - it's just a small fragment of the page.

shot-scraper can take partial screenshots based on one or more CSS selectors. Given a CSS selector the tool draws a box around just that element and uses that to take the screenshot - adding optional padding.

Here's the recipe for the advanced export box - I used the same register-of-members-interests.datasettes.com example for it as this had enough rows to trigger all of the advanced options to be displayed:

shot-scraper 'https://register-of-members-interests.datasettes.com/regmem/items?_search=hamper' \
  -s '#export' \
  -p 10

The -p 10 here specifies 10px of padding, needed to capture the drop shadow on the box.

Here's the equivalent YAML:

- url: https://register-of-members-interests.datasettes.com/regmem/items?_search=hamper
  selector: "#export"
  output: advanced-export.png
  padding: 10

And the result:

binary_data.png

This screenshot required a different trick.

I wanted to take a screenshot of the table on this page.

The full table looks like this, with three rows:

I only wanted the first two of these to be shown in the screenshot though.

shot-scraper has the ability to execute JavaScript on the page before the screenshot is taken. This can be used to remove elements first.

Here's the JavaScript I came up with to remove all but the first two rows (actually the first three, because the table header counts as a row too):

Array.from(
  document.querySelectorAll('tr:nth-child(n+3)'),
  el => el.parentNode.removeChild(el)
);

I did it this way so that if I add any more rows to that test table in the future the code will still remove everything but the first two.

The CSS selector tr:nth-child(n+3) selects all rows that are not the first three (one header plus two content rows).

Here's how to run that from the command-line, and then take a 10 pixel padded screenshot of just the table on the page after it has been modified by the JavaScript:

shot-scraper 'https://latest.datasette.io/fixtures/binary_data' \
  -j 'Array.from(document.querySelectorAll("tr:nth-child(n+3)"), el => el.parentNode.removeChild(el));' \
  -s table -p 10

The YAML I added to shots.yml:

- url: https://latest.datasette.io/fixtures/binary_data
  selector: table
  javascript: |-
    Array.from(
      document.querySelectorAll('tr:nth-child(n+3)'),
      el => el.parentNode.removeChild(el)
    );
  padding: 10
  output: binary-data.png

And the resulting image:

facets.png

I left the most complex screenshot to last.

For the faceting screenshot, I wanted to include the "suggested facet" links at the top of the page, a set of active facets and then the first three rows of the following table.

But... the table has quite a lot of columns. For a neater screenshot I only wanted to include a subset of columns in the final shot.

Here's the screenshot I ended up taking:

And the YAML recipe:

- url: https://congress-legislators.datasettes.com/legislators/legislator_terms?_facet=type&_facet=party&_facet=state&_facet_size=10
  selectors_all:
  - .suggested-facets a
  - tr:not(tr:nth-child(n+4)) td:not(:nth-child(n+11))
  padding: 10
  output: faceting-details.png

The key trick I'm using here is that selectors_all list.

The usual shot-scraper selector option finds the first element on the page matching the specified CSS selector and takes a screenshot of that.

--selector-all - or the YAML equivalent selectors_all - instead finds EVERY element that matches any of the specified selectors and draws a bounding box containing all of them.

I wanted that bounding box to surround a subset of the table cells on the page. I used this CSS selector to indicate that subset:

tr:not(tr:nth-child(n+4)) td:not(:nth-child(n+11))

Here's what GPT-3 says if you ask it to explain the selector:

Explain this CSS selector:

tr:not(tr:nth-child(n+4)) td:not(:nth-child(n+11))

This selector is selecting all table cells in rows that are not the fourth row or greater, and are not in columns that are the 11th column or greater.

(See also this TIL.)

Automating everything using GitHub Actions

Here's the full shots.yml YAML needed to generate all four of these screenshots:

- url: https://register-of-members-interests.datasettes.com/regmem/items?_search=hamper&_sort_desc=date
  height: 585
  width: 960
  output: regmem-search.png
- url: https://register-of-members-interests.datasettes.com/regmem/items?_search=hamper
  selector: "#export"
  output: advanced-export.png
  padding: 10
- url: https://congress-legislators.datasettes.com/legislators/legislator_terms?_facet=type&_facet=party&_facet=state&_facet_size=10
  selectors_all:
  - .suggested-facets a
  - tr:not(tr:nth-child(n+4)) td:not(:nth-child(n+11))
  padding: 10
  output: faceting-details.png
- url: https://latest.datasette.io/fixtures/binary_data
  selector: table
  javascript: |-
    Array.from(
      document.querySelectorAll('tr:nth-child(n+3)'),
      el => el.parentNode.removeChild(el)
    );
  padding: 10
  output: binary-data.png

Running shot-scraper shots shots.yml against this file takes all four screenshots.

But I want this to be fully automated! So I turned to GitHub Actions.

A while ago I created a template repository for setting up GitHub Actions to take screenshots using shot-scraper and write them back to the same repo. I wrote about that in Instantly create a GitHub repository to take screenshots of a web page.

I had previously used that recipe to create my datasette-screenshots repository - with its own shots.yml file.

So I added the new YAML to that existing file, committed the change, waited a minute and the result was all four images stored in that repository!

My datasette-screenshots workflow actually has two key changes from my default template. First, it takes every screenshot twice - once as a retina image and once as a regular image:

    - name: Take retina shots
      run: |
        shot-scraper multi shots.yml --retina
    - name: Take non-retina shots
      run: |
        mkdir -p non-retina
        cd non-retina
        shot-scraper multi ../shots.yml
        cd ..

This provides me with both a high quality image and a smaller, faster-loading image for each screenshot.

Secondly, it runs oxipng to optimize the PNGs before committing them to the repo:

    - name: Optimize PNGs
      run: |-
        oxipng -o 4 -i 0 --strip safe *.png
        oxipng -o 4 -i 0 --strip safe non-retina/*.png

The shot-scraper documentation describes this pattern in more detail.

With all of that in place, simply committing a change to the shots.yml file is enough to generate and store the new screenshots.

Linking to the images

One last problem to solve: I want to include these images in my documentation, which means I need a way to link to them.

I decided to use GitHub to host these directly, via the raw.githubusercontent.com domain - which is fronted by the Fastly CDN.

I care about up-to-date images, but I also want different versions of the Datasette documentation to reflect the corresponding design in their screenshots - so I needed a way to snapshot those screenshots to a known version.

Repository tags are one way to do this.

I tagged the datasette-screenshots repository with 0.62, since that's the version of Datasette that the screenshots were taken for.

This gave me the following URLs for the images:

To save on page loading time I decided to use the non-retina URLs for the two larger images.

Here's the commit that updated the Datasette documentation to link to these new images (and deleted the old images from the repo).

You can see the new images in the documentation on these pages:

Tags: documentation, yaml, datasette, github-actions, shot-scraper

shot-scraper: automated screenshots for documentation, built on Playwright

2022-03-10T00:13:30+00:00

shot-scraper is a new tool that I’ve built to help automate the process of keeping screenshots up-to-date in my documentation. It also doubles as a scraping tool - hence the name - which I picked as a complement to my git scraping and help scraping techniques.

Update 13th March 2022: The new shot-scraper javascript command can now be used to scrape web pages from the command line.

Update 14th October 2022: Automating screenshots for the Datasette documentation using shot-scraper offers a tutorial introduction to using the tool.

The problem

I like to include screenshots in documentation. I recently started writing end-user tutorials for Datasette, which are particularly image heavy (for example).

As software changes over time, screenshots get out-of-date. I don't like the idea of stale screenshots, but I also don't want to have to manually recreate them every time I make the tiniest tweak to the visual appearance of my software.

Introducing shot-scraper

shot-scraper is a tool for automating this process. You can install it using pip like this:

pip install shot-scraper
shot-scraper install

That second shot-scraper install line will install the browser it needs to do its job - more on that later.

You can use it in two ways. To take a one-off screenshot, you can run it like this:

shot-scraper https://simonwillison.net/ -o simonwillison.png

Or if you want to take a set of screenshots in a repeatable way, you can define them in a YAML file that looks like this:

- url: https://simonwillison.net/
  output: simonwillison.png
- url: https://www.example.com/
  width: 400
  height: 400
  quality: 80
  output: example.jpg

And then use shot-scraper multi to execute every screenshot in one go:

% shot-scraper multi shots.yml 
Screenshot of 'https://simonwillison.net/' written to 'simonwillison.png'
Screenshot of 'https://www.example.com/' written to 'example.jpg'

The documentation describes all of the available options you can use when taking a screenshot.

Each option can be provided to the shot-scraper one-off tool, or can be embedded in the YAML file for use with shot-scraper multi.

JavaScript and CSS selectors

The default behaviour for shot-scraper is to take a full page screenshot, using a browser width of 1280px.

For documentation screenshots you probably don't want the whole page though - you likely want to create an image of one specific part of the interface.

The --selector option allows you to specify an area of the page by CSS selector. The resulting image will consist just of that part of the page.

What if you want to modify the page in addition to selecting a specific area?

The --javascript option lets you pass in a block of JavaScript code which will be injected into the page and executed after the page has loaded, but before the screenshot is taken.

The combination of these two options - also available as javascript: and selector: keys in the YAML file - should be flexible enough to cover the custom screenshot case for documentation.

A complex example

To prove to myself that the tool works, I decided to try replicating this screenshot from my tutorial.

I made the original using CleanShot X, manually adding the two pink arrows:

This is pretty tricky!

It's not this whole page, just a subset of the page
The cog menu for one of the columns is open, which means the cog icon needs to be clicked before taking the screenshot
There are two pink arrows superimposed on the image

I decided to do use just one arrow for the moment, which should hopefully result in a clearer image.

I started by creating my own pink arrow SVG using Figma:

I then fiddled around in the Firefox developer console for quite a while, working out the JavaScript needed to trim the page down to the bit I wanted, open the menu and position the arrow.

With the JavaScript figured out, I pasted it into a YAML file called shot.yml:

- url: https://congress-legislators.datasettes.com/legislators/executive_terms?start__startswith=18&type=prez
  javascript: |
    new Promise(resolve => {
      // Run in a promise so we can sleep 1s at the end
      function remove(el) { el.parentNode.removeChild(el);}
      // Remove header and footer
      remove(document.querySelector('header'));
      remove(document.querySelector('footer'));
      // Remove most of the children of .content
      Array.from(document.querySelectorAll('.content > *:not(.table-wrapper,.suggested-facets)')).map(remove)
      // Bit of breathing room for the screenshot
      document.body.style.marginTop = '10px';
      // Add a bit of padding to .content
      var content = document.querySelector('.content');
      content.style.width = '820px';
      content.style.padding = '10px';
      // Open the menu - it's an SVG so we need to use dispatchEvent here
      document.querySelector('th.col-executive_id svg').dispatchEvent(new Event('click'));
      // Remove all but table header and first 11 rows
      Array.from(document.querySelectorAll('tr')).slice(12).map(remove);
      // Add a pink SVG arrow
      let div = document.createElement('div');
      div.innerHTML = `<svg width="104" height="60" fill="none" xmlns="http://www.w3.org/2000/svg">
        <g filter="url(#a)">
          <path fill-rule="evenodd" clip-rule="evenodd" d="m76.7 1 2 2 .2-.1.1.4 20 20a3.5 3.5 0 0 1 0 5l-20 20-.1.4-.3-.1-1.9 2a3.5 3.5 0 0 1-5.4-4.4l3.2-14.4H4v-12h70.6L71.3 5.4A3.5 3.5 0 0 1 76.7 1Z" fill="#FF31A0"/>
        </g>
        <defs>
          <filter id="a" x="0" y="0" width="104" height="59.5" filterUnits="userSpaceOnUse" color-interpolation-filters="sRGB">
              <feFlood flood-opacity="0" result="BackgroundImageFix"/>
              <feColorMatrix in="SourceAlpha" values="0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 127 0" result="hardAlpha"/>
              <feOffset dy="4"/>
              <feGaussianBlur stdDeviation="2"/>
              <feComposite in2="hardAlpha" operator="out"/>
              <feColorMatrix values="0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0.25 0"/>
              <feBlend in2="BackgroundImageFix" result="effect1_dropShadow_2_26"/>
              <feBlend in="SourceGraphic" in2="effect1_dropShadow_2_26" result="shape"/>
          </filter>
        </defs>
      </svg>`;
      let svg = div.firstChild;
      content.appendChild(svg);
      content.style.position = 'relative';
      svg.style.position = 'absolute';
      // Give the menu time to finish fading in
      setTimeout(() => {
        // Position arrow pointing to the 'facet by this' menu item
        var pos = document.querySelector('.dropdown-facet').getBoundingClientRect();
        svg.style.left = (pos.left - pos.width) + 'px';
        svg.style.top = (pos.top - 20) + 'px';
        resolve();
      }, 1000);
    });
  output: annotated-screenshot.png
  selector: .content

And ran this command to generate the screenshot:

shot-scraper multi shot.yml

The generated annotated-screenshot.png image looks like this:

I'm pretty happy with this! I think it works very well as a proof of concept for the process.

How it works: Playwright

I built the first prototype of shot-scraper using Puppeteer, because I had used that before.

Then I noticed that the puppeteer-cli package I was using hadn't had an update in two years, which reminded me to check out Playwright.

I've been looking for an excuse to learn Playwright for a while now, and this project turned out to be ideal.

Playwright is Microsoft's open source browser automation framework. They promote it as a testing tool, but it has plenty of applications outside of testing - screenshot automation and screen scraping being two of the most obvious.

Playwright is comprehensive: it downloads its own custom browser builds, and can run tests across multiple different rendering engines.

The second prototype used the Playwright CLI utility instead, executed via npx:

subprocess.run(
    [
        "npx",
        "playwright",
        "screenshot",
        "--full-page",
        url,
        output,
    ],
    capture_output=True,
)

This could take a full page screenshot, but that CLI tool wasn't flexible enough to take screenshots of specific elements. So I needed to switch to the Playwright programmatic API.

I started out trying to get Python to generate and pass JavaScript to the Node.js library... and then I spotted the official Playwright for Python package.

pip install playwright

It's amazing! It has the exact same functionality as the JavaScript library - the same classes, the same methods. Everything just works, in both languages.

I was curious how they pulled this off, so I dug inside the playwright Python package in my site-packages folder... and found it bundles a full Node.js binary executable and uses it to bridge the two worlds! What a wild hack.

Thanks to Playwright, the entire implementation of shot-scraper is currently just 181 lines of Python code - it's all glue code tying together a Click CLI interface with some code that calls Playwright to do the actual work.

I couldn't be more impressed with Playwright. I'll definitely be using it for other projects - for one thing, I think I'll finally be able to add automated tests to my Datasette Desktop Electron application.

Hooking shot-scraper up to GitHub Actions

I built shot-scraper very much with GitHub Actions in mind.

My shot-scraper-demo repository is my first live demo of the tool.

Once a day, it runs this shots.yml file, generates two screenshots and commits them back to the repository.

One of them is the tutorial screenshot described above.

The other is a screenshot of the list of "recently spotted owls" from this page on owlsnearme.com. I wanted a page that would change on an occasional basis, to demonstrate GitHub's neat image diffing interface.

I may need to change that demo though! That page includes "spotted 5 hours ago" text, which means that there's almost always a tiny pixel difference, like this one (use the "swipe" comparison tool to watch 6 hours ago change to 7 hours ago under the top left photo).

Storing image files that change frequently in a free repository on GitHub feels rude to me, so please use this tool cautiously there!

What's next?

I had ambitious plans to add utilities to the tool that would help with annotations, such as adding pink arrows and drawing circles around different elements on the page.

I've shelved those plans for the moment: as the demo above shows, the JavaScript hook is good enough. I may revisit this later once common patterns have started to emerge.

So really, my next step is to start using this tool for my own projects - to generate screenshots for my documentation.

I'm also very interested to see what kinds of things other people use this for.

Tags: cli, documentation, projects, scraping, yaml, github-actions, git-scraping, puppeteer, playwright, shot-scraper

yaml-to-sqlite 1.0

2021-06-13T04:45:52+00:00

Release: yaml-to-sqlite 1.0

Tags: sqlite, yaml

yaml-to-sqlite 0.3.1

2020-12-07T21:12:55+00:00

Release: yaml-to-sqlite 0.3.1

Tags: sqlite, yaml

Controlling the style of dumped YAML using PyYAML

2020-12-07T20:48:39+00:00

TIL: Controlling the style of dumped YAML using PyYAML

Tags: yaml

datasette-yaml 0.1.1

2020-12-03T00:09:35+00:00

Release: datasette-yaml 0.1.1

Tags: yaml, datasette

datasette-yaml 0.1

2020-09-14T22:13:36+00:00

Release: datasette-yaml 0.1

Tags: yaml, datasette

Weeknotes: airtable-export, generating screenshots in GitHub Actions, Dogsheep!

2020-09-03T23:28:29+00:00

This week I figured out how to populate Datasette from Airtable, wrote code to generate social media preview card page screenshots using Puppeteer, and made a big breakthrough with my Dogsheep project.

airtable-export

I wrote about Rocky Beaches in my weeknotes two weeks ago. It's a new website built by Natalie Downe that showcases great places to go rockpooling (tidepooling in American English), mixing in tide data from NOAA and species sighting data from iNaturalist.

Rocky Beaches is powered by Datasette, using a GitHub Actions workflow that builds the site's underlying SQLite database using API calls and YAML data stored in the GitHub repository.

Natalie wanted to use Airtable to maintain the structured data for the site, rather than hand-editing a YAML file. So I built airtable-export, a command-line script for sucking down all of the data from an Airtable instance and writing it to disk as YAML or JSON.

You run it like this:

airtable-export out/ mybaseid table1 table2 --key=key

This will create a folder called out/ with a .yml file for each of the tables.

Sadly the Airtable API doesn't yet provide a mechanism to list all of the tables in a database (a long-running feature request) so you have to list the tables yourself.

We're now running that command as part of the Rocky Beaches build script, and committing the latest version of the YAML file back to the GitHub repo (thus gaining a full change history for that data).

I really like social media cards - og:image HTML meta attributes for Facebook and twitter:image for Twitter. I wanted them for articles on my TIL website since I often share those via Twitter.

One catch: my TILs aren't very image heavy. So I decided to generate screenshots of the pages and use those as the 2x1 social media card images.

The best way I know of programatically generating screenshots is to use Puppeteer, a Node.js library for automating a headless instance of the Chrome browser that is maintained by the Chrome DevTools team.

My first attempt was to run Puppeteer in an AWS Lambda function on Vercel. I remembered seeing an example of how to do this in the Vercel documentation a few years ago. The example isn't there any more, but I found the original pull request that introduced it.

Since the example was MIT licensed I created my own fork at simonw/puppeteer-screenshot and updated it to work with the latest Chrome.

It's pretty resource intensive, so I also added a secret ?key= mechanism so only my own automation code could call my instance running on Vercel.

I needed to store the generated screenshots somewhere. They're pretty small - on the order of 60KB each - so I decided to store them in my SQLite database itself and use my datasette-media plugin (see Fun with binary data and SQLite) to serve them up.

This worked! Until it didn't... I ran into a showstopper bug when I realized that the screenshot process relies on the page being live on the site... but when a new article is added it's not live when the build process works, so the generated screenshot is of the 404 page.

So I reworked it to generate the screenshots inside the GitHub Action as part of the build script, using puppeteer-cli.

My generate_screenshots.py script handles this, by first shelling out to datasette --get to render the HTML for the page, then running puppeteer to generate the screenshot. Relevant code:

def png_for_path(path):
    # Path is e.g. /til/til/python_debug-click-with-pdb.md
    page_html = str(TMP_PATH / "generate-screenshots-page.html")
    # Use datasette to generate HTML
    proc = subprocess.run(["datasette", ".", "--get", path], capture_output=True)
    open(page_html, "wb").write(proc.stdout)
    # Now use puppeteer screenshot to generate a PNG
    proc2 = subprocess.run(
        [
            "puppeteer",
            "screenshot",
            page_html,
            "--viewport",
            "800x400",
            "--full-page=false",
        ],
        capture_output=True,
    )
    png_bytes = proc2.stdout
    return png_bytes

This worked great! Except for one thing... the site is hosted on Vercel, and Vercel has a 5MB response size limit.

Every time my GitHub build script runs it downloads the previous SQLite database file, so it can avoid regenerating screenshots and HTML for pages that haven't changed.

The addition of the binary screenshots drove the size of the SQLite database over 5MB, so the part of my script that retrieved the previous database no longer worked.

I needed a reliable way to store that 5MB (and probably eventually 10-50MB) database file in between runs of my action.

The best place to put this would be an S3 bucket, but I find the process of setting up IAM permissions for access to a new bucket so infuriating that I couldn't bring myself to do it.

So... I created a new dedicated GitHub repository, simonw/til-db, and updated my action to store the binary file in that repo - using a force push so the repo doesn't need to maintain unnecessary version history of the binary asset.

This is an abomination of a hack, and it made me cackle a lot. I tweeted about it and got the suggestion to try Git LFS instead, which would definitely be a more appropriate way to solve this problem.

Rendering Markdown

I write my blog entries in Markdown and transform them into HTML before I post them on my blog. Some day I'll teach my blog to render Markdown itself, but so far I've got by through copying and pasting into Markdown tools.

My favourite Markdown flavour is GitHub's, which adds a bunch of useful capabilities - most notably the ability to apply syntax highlighting. GitHub expose an API that applies their Markdown formatter and returns the resulting HTML.

I built myself a quick and scrappy tool in JavaScript that sends Markdown through their API and then applies a few DOM manipulations to clean up what comes back. It was a nice opportunity to write some modern vanilla JavaScript using fetch():

async function render(markdown) {
    return (await fetch('https://api.github.com/markdown', {
        method: 'POST',
        headers: {
            'Content-Type': 'application/json'
        },
        body: JSON.stringify({'mode': 'markdown', 'text': markdown})
    })).text();
}

const button = document.getElementsByTagName('button')[0];
const output = document.getElementById('output');
const preview = document.getElementById('preview');

button.addEventListener('click', async function() {
    const rendered = await render(input.value);
    output.value = rendered;
    preview.innerHTML = rendered;
});

Dogsheep Beta

My most exciting project this week was getting out the first working version of Dogsheep Beta - the search engine that ties together results from my Dogsheep family of tools for personal analytics.

I'm giving a talk about this tonight at PyCon Australia: Build your own data warehouse for personal analytics with SQLite and Datasette. I'll be writing up detailed notes in the next few days, so watch this space.

TIL this week

Releases this week

dogsheep-beta 0.4.1 - 2020-09-03
dogsheep-beta 0.4 - 2020-09-03
dogsheep-beta 0.4a1 - 2020-09-03
dogsheep-beta 0.4a0 - 2020-09-03
dogsheep-beta 0.3 - 2020-09-02
dogsheep-beta 0.2 - 2020-09-01
dogsheep-beta 0.1 - 2020-09-01
dogsheep-beta 0.1a2 - 2020-09-01
dogsheep-beta 0.1a - 2020-09-01
airtable-export 0.4 - 2020-08-30
datasette-yaml 0.1a - 2020-08-29
airtable-export 0.3.1 - 2020-08-29
airtable-export 0.3 - 2020-08-29
airtable-export 0.2 - 2020-08-29
airtable-export 0.1.1 - 2020-08-29
airtable-export 0.1 - 2020-08-29
datasette 0.49a0 - 2020-08-28
sqlite-utils 2.16.1 - 2020-08-28

Tags: projects, yaml, markdown, dogsheep, weeknotes, github-actions, airtable, puppeteer

datasette-yaml 0.1a

2020-08-29T22:35:22+00:00

Release: datasette-yaml 0.1a

Tags: yaml, datasette

airtable-export

2020-08-29T21:48:37+00:00

airtable-export

I wrote a command-line utility for exporting data from Airtable and dumping it to disk as YAML, JSON or newline delimited JSON files. This means you can backup an Airtable database from a GitHub Action and get a commit history of changes made to your data.

Tags: json, projects, yaml, airtable

Goodbye Zeit Now v1, hello datasette-publish-now - and talking to myself in GitHub issues

2020-04-08T03:32:24+00:00

This week I’ve been mostly dealing with the finally announced shutdown of Zeit Now v1. And having long-winded conversations with myself in GitHub issues.

How Zeit Now inspired Datasette

I first started experiencing with Zeit’s serverless Now hosting platform back in October 2017, when I used it to deploy json-head.now.sh - an updated version of an API tool I originally built for Google App Engine in July 2008.

I liked Zeit Now, a lot. Instant, inexpensive deploys of any stateless project that could be defined using a Dockerfile? Just type now to deploy the project in your current directory? Every deployment gets its own permanent URL? Amazing!

There was just one catch: Since Now deployments are ephemeral applications running on them need to be stateless. If you want a database, you need to involve another (potentially costly) service. It's a limitation shared by other scalable hosting solutions - Heroku, App Engine and so on. How much interesting stuff can you build without a database?

I was musing about this in the shower one day (that old cliche really happened for me) when I had a thought: sure, you can't write to a database... but if your data is read-only, why not bundle the database alongside the application code as part of the Docker image?

Ever since I helped launch the Datablog at the Guardian back in 2009 I had been interested in finding better ways to publish data journalism datasets than CSV files or a Google spreadsheets - so building something that could package and bundle read-only data was of extreme interest to me.

In November 2017 I released the first version of Datasette. The original idea was very much inspired by Zeit Now.

I gave a talk about Datasette at the Zeit Day conference in San Francisco in April 2018. Suffice to say I was a huge fan!

Goodbye, Zeit Now v1

In November 2018, Zeit announced Now v2. And it was, different.

v2 is an entirely different architecture from v1. Where v1 built on Docker containers, v2 is built on top of serverless functions - AWS Lambda in particular.

I can see why Zeit did this. Lambda functions can launch from cold way faster - v1's Docker infrastructure had tough cold-start times. They are much cheaper to run as well - crucial for Zeit given their extremely generous pricing plans.

But it was bad news for my projects. Lambdas are tightly size constrained, which is tough when you're bundling potentially large SQLite database files with your deployments.

More importantly, in 2018 Amazon were deliberately excluding the Python sqlite3 standard library module from the Python Lambda environment! I guess they hadn't considered people who might want to work with read-only database files.

So Datasette on Now v2 just wasn't going to work. Zeit kept v1 supported for the time being, but the writing was clearly on the wall.

In April 2019 Google announced Cloud Run, a serverless, scale-to-zero hosting environment based around Docker containers. In many ways it's Google's version of Zeit Now v1 - it has many of the characteristics I loved about v1, albeit with a clunkier developer experience and much more friction in assigning nice URLs to projects. Romain Primet contributed Cloud Run support to Datasette and it has since become my preferred hosting target for my new projects (see Deploying a data API using GitHub Actions and Cloud Run).

Last week, Zeit finally announced the sunset date for v1. From 1st of May new deploys won't be allowed, and on the 7th of August they'll be turning off the old v1 infrastructure and deleting all existing Now v1 deployments.

I engaged in an extensive Twitter conversation about this, where I praised Zeit's handling of the shutdown while bemoaning the loss of the v1 product I had loved so much.

Migrating my projects

My newer projects have been on Cloud Run for quite some time, but I still have a bunch of old projects that I care about and want to keep running past the v1 shutdown.

The first project I ported was latest.datasette.io, a live demo of Datasette which updates with the latest code any time I push to the Datasette master branch on GitHub.

Any time I do some kind of ops task like this I've gotten into the habit of meticulously documenting every single step in comments on a GitHub issue. Here's the issue for porting latest.datasette.io to Cloud Run (and switching from Circle CI to GitHub Actions at the same time).

My next project was global-power-plants-datasette, a small project which takes a database of global power plants published by the World Resources Institute and publishes it using Datasette. It checks for new updates to their repo once a day. I originally built it as a demo for datasette-cluster-map, since it's fun seeing 33,000 power plants on a single map. Here's that issue.

Having warmed up with these two, my next target was the most significant: porting my Niche Museums website.

Niche Museums is the most heavily customized Datasette instance I've run anywhere - it incorporates custom templates, CSS and plugins.

Here's the tracking issue for porting it to Cloud Run. I ran into a few hurdles with DNS and TLS certificates, and I had to do some additional work to ensure niche-museums.com redirects to www.niche-musums.com, but it's now fully migrated.

Hello, Zeit Now v2

In complaining about the lack of that essential sqlite3 module I figured it would be responsible to double-check and make sure that was still true.

It was not! Today Now's Python environment includes sqlite3 after all.

Datasette's publish_subcommand() plugin hook lets plugins add new publishing targets to the datasette publish command (I used it to build datasette-publish-fly last month). How hard would it be to build a plugin for Zeit Now v2?

I fired up a new lengthy talking-to-myself GitHub issue and started prototyping.

Now v2 may not support Docker, but it does support the ASGI Python standard (the asynchronous alternative to WSGI, shepherded by Andrew Godwin).

Zeit are keen proponents of the Jamstack approach, where websites are built using static pre-rendered HTML and JavaScript that calls out to APIs for dynamic data. v2 deployments are expected to consist of static HTML with "serverless functions" - standalone server-side scripts that live in an api/ directory by convention and are compiled into separate lambdas.

Datasette works just fine without JavaScript, which means it needs to handle all of the URL routes for a site. Essentually I need to build a single function that runs the whole of Datasette, then route all incoming traffic to it.

It took me a while to figure it out, but it turns out the Now v2 recipe for that is a now.json file that looks like this:

{
    "version": 2,
    "builds": [
        {
            "src": "index.py",
            "use": "@now/python"
        }
    ],
    "routes": [
        {
            "src": "(.*)",
            "dest": "index.py"
        }
    ]
}

Thanks Aaron Boodman for the tip.

Given the above configuration, Zeit will install any Python dependencies in a requirements.txt file, then treat an app variable in the index.py file as an ASGI application it should route all incoming traffic to. Exactly what I need to deploy Datasette!

This was everything I needed to build the new plugin. datasette-publish-now is the result.

Here's the generated source code for a project deployed using the plugin, showing how the underlyinng ASGI application is configured.

It's currently an alpha - not every feature is supported (see this milestone) and it relies on a minor deprecated feature (which I've implored Zeit to reconsider) but it's already full-featured enough that I can start using it to upgrade some of my smaller existing Now projects.

The first I upgraded is one of my favourites: polar-bears.now.sh, which visualizes tracking data from polar bear ear tags (using datasette-cluster-map) that was published by the USGS Alaska Science Center, Polar Bear Research Program.

Here's the command I used to deploy the site:

$ pip install datasette-publish-now
$ datasette publish now2 polar-bears.db \
    --title "Polar Bear Ear Tags, 2009-2011" \
    --source "USGS Alaska Science Center, Polar Bear Research Program" \
    --source_url "https://alaska.usgs.gov/products/data.php?dataid=130" \
    --install datasette-cluster-map \
    --project=polar-bears

I exported a full list of my Now v1 projects from their handy active v1 instances page.

The rest of my projects

I scraped the page using the following JavaScript, constructed with the help of the instant evaluation console feature in Firefox 75:

console.log(
  JSON.stringify(
    Array.from(
      Array.from(
        document.getElementsByTagName("table")[1].
          getElementsByTagName("tr")
      ).slice(1).map(
        (tr) =>
          Array.from(
            tr.getElementsByTagName("td")
        ).map((td) => td.innerText)
      )
    )
  )
);

Then I loaded them into Datasette for analysis.

After filtering out the datasette-latest-commithash.now.sh projects I had deployed for every push to GitHub it turns out I have 34 distinct projects running there.

I won't port all of them, but given datasette-publish-now I should be able to port the ones that I care about without too much trouble.

Debugging Datasette with git bisect run

I fixed two bugs in Datasette this week using git bisect run - a tool I've been meaning to figure out for years, which lets you run an automated binary search against a commit log to find the source of a bug.

Since I was figuring out a new tool, I fired up another GitHub issue self-conversation: in issue #716 I document my process of both learning to use git bisect run and using it to find a solution to that particular bug.

It worked great, so I used the same trick on issue 689 as well.

Watching git bisect run churn through 32 revisions in a few seconds and pinpoint the exact moment a bug was introduced is pretty delightful:

$ git bisect start master 0.34
Bisecting: 32 revisions left to test after this (roughly 5 steps)
[dc80e779a2e708b2685fc641df99e6aae9ad6f97] Handle scope path if it is a string
$ git bisect run python check_templates_considered.py
running python check_templates_considered.py
Traceback (most recent call last):
...
AssertionError
Bisecting: 15 revisions left to test after this (roughly 4 steps)
[7c6a9c35299f251f9abfb03fd8e85143e4361709] Better tests for prepare_connection() plugin hook, refs #678
running python check_templates_considered.py
Traceback (most recent call last):
...
AssertionError
Bisecting: 7 revisions left to test after this (roughly 3 steps)
[0091dfe3e5a3db94af8881038d3f1b8312bb857d] More reliable tie-break ordering for facet results
running python check_templates_considered.py
Traceback (most recent call last):
...
AssertionError
Bisecting: 3 revisions left to test after this (roughly 2 steps)
[ce12244037b60ba0202c814871218c1dab38d729] Release notes for 0.35
running python check_templates_considered.py
Traceback (most recent call last):
...
AssertionError
Bisecting: 1 revision left to test after this (roughly 1 step)
[70b915fb4bc214f9d064179f87671f8a378aa127] Datasette.render_template() method, closes #577
running python check_templates_considered.py
Traceback (most recent call last):
...
AssertionError
Bisecting: 0 revisions left to test after this (roughly 0 steps)
[286ed286b68793532c2a38436a08343b45cfbc91] geojson-to-sqlite
running python check_templates_considered.py
70b915fb4bc214f9d064179f87671f8a378aa127 is the first bad commit
commit 70b915fb4bc214f9d064179f87671f8a378aa127
Author: Simon Willison
Date:   Tue Feb 4 12:26:17 2020 -0800

    Datasette.render_template() method, closes #577

    Pull request #664.

:040000 040000 def9e31252e056845609de36c66d4320dd0c47f8 da19b7f8c26d50a4c05e5a7f05220b968429725c M	datasette
bisect run success

Supporting metadata.yaml

The other Datasette project I completed this week is a relatively small feature with hopefully a big impact: you can now use YAML for Datasette's metadata configuration as an alternative to JSON.

I'm not crazy about YAML: I still don't feel like I've mastered it, and I've been tracking it for 18 years! But it has one big advantage over JSON for configuration files: robust support for multi-line strings.

Datasette's metadata file can include lengthy SQL statements and strings of HTML, both of which benefit from multi-line strings.

I first used YAML for metadata for my Analyzing US Election Russian Facebook Ads project. The metadata file for that demonstrates both embedded HTML and embedded SQL - and an accompanying build_metadata.py script converted it to JSON at build time. I've since used the same trick for a number of other projects.

The next release of Datasette (hopefully within a week) will ship the new feature, at which point those conversion scripts won't be necessary.

This should work particularly well with the forthcoming ability for a canned query to write to a database. Getting that wrapped up and shipped will be my focus for the next few days.

Tags: git, github, projects, yaml, zeit-now, datasette, weeknotes, github-issues

yaml-to-sqlite 0.3

2019-11-29T20:07:05+00:00

Release: yaml-to-sqlite 0.3

Tags: sqlite, yaml

niche-museums.com, powered by Datasette

2019-11-25T22:27:46+00:00

I just released a major upgrade to my www.niche-museums.com website (launched last month).

The site is now rendered server-side. The previous version used lit-html to render content using JavaScript.
Each museum now has its own page. Here's today's new museum listing for the Conservatory of Flowers in San Francisco. These pages have a map on them.
The site has an about page.
You can now link to the page for a specific latitude and longitude, e.g. this location in Golden Gate Park.
The source code for the site is now available on GitHub.

Notably, the site is entirely powered by Datasette. It's a heavily customized Datasette instance, making extensive use of custom templates and plugins.

It's a really fun experiment. I'm essentially using Datasette as a weird twist on a static site generator - no moving parts since the database is immutable but there's still stuff happening server-side to render the pages.

Continuous deployment

The site is entirely stateless and is published using Circle CI to a serverless hosting provider (currently Zeit Now v1, but I'll probably move it to Google Cloud Run in the near future.)

The site content - 46 museums and counting - lives in the museums.yaml file. I've been adding a new museum listing every day by editing the YAML file using Working Copy on my iPhone.

The build script runs automatically on every commit. It converts the YAML file into a SQLite database using my yaml-to-sqlite tool, then runs datasette publish now... to deploy the resulting database.

The full deployment command is as follows:

datasette publish now browse.db about.db \
    --token=$NOW_TOKEN \
    --alias=www.niche-museums.com \
    --name=niche-museums \
    --install=datasette-haversine \
    --install=datasette-pretty-json \
    --install=datasette-template-sql \
    --install=datasette-json-html \
    --install=datasette-cluster-map~=0.8 \
    --metadata=metadata.json \
    --template-dir=templates \
    --plugins-dir=plugins \
    --branch=master

There's a lot going on here.

browse.db is the SQLite database file that was built by running yaml-to-sqlite.

about.db is an empty database built using sqlite3 about.db '' - more on this later.

The --alias= option tells Zeit Now to alias that URL to the resulting deployment. This is the single biggest feature that I'm missing from Google Cloud Run at the moment. It's possible to point domains at deployments there but it's not nearly as easy to script.

The --install= options tell datasette publish which plugins should be installed on the resulting instance.

--metadata=, --template-dir= and --plugins-dir= are the options that customize the instance.

--branch=master means we always deploy the latest master of Datasette directly from GitHub, ignoring the most recent release to PyPI. This isn't strictly necessary here.

Customization

The site itself is built almost entirely using Datasette custom templates. I have four of them:

index.html is the template used for the homepage, and for the page you see when you search for museums near a specific latitude and longitude.
row-browse-museums.html is the template used for the individual museum pages. It includes the JavaScript used for the map (which is powered by Leaflet and uses Wikimedia's OpenStreetMap tiles, which I discovered thanks to this Observable notebook by Tom MacWright).
_museum_card.html is an included template rendering a card for a museum, shared by the index and museum pages.
database-about.html is the template for the about page.

The about page uses a particularly devious hack.

Datasette doesn't have an easy way to create additional custom pages with URLs at the moment (without abusing the asgi_wrapper() hook, which is pretty low-level).

But... every attached database gets its own URL at /database-name.

So, to create the /about page I create an empty database called about.db using the sqlite3 about.db "" command. I serve that using Datasette, then create a custom template for that specific database using Datasette's template naming conventions.

I'll probably come up with a less grotesque way of doing this and bake it into Datasette in the future. For the moment this seems to work pretty well.

Plugins

The two key plugins here are datasette-haversine and datasette-template-sql.

datasette-haversine adds a custom SQL function to Datasette called haversine(), which calculates the haversine distance between two latitude/longitude points.

It's used by the SQL query which finds the nearest museums to the user.

This is very inefficient - it's essentially a brute-force approach which calculates that distance for every museum in the database and sorts them accordingly - but it will be years before I have enough museums listed for that to cause any kind of performance issue.

datasette-template-sql is the new plugin I described last week, made possible by Datasette dropping Python 3.5 support. It allows SQL queries to be executed directly from templates. I'm using it here to run the queries that power homepage.

I tried to get the site working just using code in the templates, but it got pretty messy. Instead, I took advantage of Datasette's --plugins-dir option, which causes Datasette to treat all Python modules in a specific directory as plugins and attempt to load them.

index_vars.py is a single custom plugin that I'm bundling with the site. It uses the extra_template_vars() plugin took to detect requests to the index page and inject some additional custom template variables based on values read from the querystring.

This ends up acting a little bit like a custom Django view function. It's a slightly weird pattern but again it does the job - and helps me further explore the potential of Datasette as a tool for powering websites in addition to just providing an API.

Weeknotes

This post is standing in for my regular weeknotes, because it represents most of what I achieved this last week. A few other bits and pieces:

I've been exploring ways to enable CSV upload directly into a Datasette instance. I'm building a prototype of this on top of Starlette, because it has solid ASGI file upload support. This is currently a standalone web application but I'll probably make it work as a Datasette ASGI plugin once I have something I like.
Shortcuts in iOS 13 got some very interesting new features, most importantly the ability to trigger shortcuts automatically on specific actions - including every time you open a specific app. I've been experimenting with using this to automatically copy data from my iPhone up to a custom web application - maybe this could help ingest notes and photos into Dogsheep.
Posted seven new museums to niche-museums.com:
- Cable Car Museum in San Francisco
- Audium in San Francisco
- House of Broel Dollhouse Museum in New Orleans
- Neptune Society Columbarium in San Francisco
- Recoleta Cemetery in Buenos Aires
- NASA Glenn Visitor Center in Cleveland
- Conservatory of Flowers in San Francisco
I composed devious SQL query for generating the markdown for the seven most recently added museums.

Tags: museums, projects, yaml, datasette, weeknotes, baked-data

yaml-to-sqlite 0.2.1

2019-11-08T06:46:02+00:00

Release: yaml-to-sqlite 0.2.1

Tags: sqlite, yaml

yaml-to-sqlite 0.2

2019-06-23T22:55:50+00:00

Release: yaml-to-sqlite 0.2

Tags: sqlite, yaml

Analyzing US Election Russian Facebook Ads

2018-08-06T16:01:18+00:00

Two interesting data sources have emerged in the past few weeks concerning the Russian impact on the 2016 US elections.

FiveThirtyEight published nearly 3 million tweets from accounts associated with the Russian “Internet Research Agency” - see my article and searchable tweet archive here.

Separately, the House Intelligence Committee Minority released 3,517 Facebook ads that were reported to have been bought by the Russian Internet Research Agency as a set of redacted PDF files.

Exploring the Russian Facebook Ad spend

The initial data was released as zip files full of PDFs, one of the least friendly formats you can use to publish data.

Ed Summers took on the intimidating task of cleaning that up. His results are incredible: he used the pytesseract OCR library and PyPDF2 to extract both the images and the associated metadata and convert the whole lot into a single 3.9MB JSON file.

I wrote some code to convert his JSON file to SQLite (more on the details later) and the result can be found here:

https://russian-ira-facebook-ads.datasettes.com/

Here’s an example search for “cops” ordered by the USD equivalent spent on the ad (some of the spends are in rubles, so I convert those to USD using today’s exchange rate of 0.016).

One of the most interesting things about this data is that it includes the Facebook ad targetting options that were used to promote the ads. I’ve built a separate interface for browsing those - you can see the most frequently applied targets:

And by browsing through the different facets you can construct e.g. a search for all ads that targeted people interested in both interests:Martin Luther King and interests:Police Brutality is a Crime: https://russian-ira-facebook-ads.datasettes.com/russian-ads-919cbfd/display_ads?_targets_json=["d6ade"%2C"40c27"]

New tooling under the hood

I ended up spinning up several new projects to help process and explore this data.

sqlite-utils

The first is a new library called sqlite-utils. If data is already in CSV I tend to convert it using csvs-to-sqlite, but if data is in a less tabular format (JSON or XML for example) I have to hand-write code. Here’s a script I wrote to process the XML version of the UK Register of Members Interests for example.

My goal with sqlite-utils is to take some of the common patterns from those scripts and make them as easy to use as possible, in particular when running inside a Jupyter notebook. It’s still very early, but the script I wrote to process the Russian ads JSON is a good example of the kind of thing I want to do with it.

datasette-json-html

The second new tool is a new Datasette plugin (and corresponding plugin hook) called datasette-json-html. I used this to solve the need to display both rendered images and customized links as part of the regular Datasette instance.

It’s a pretty crazy solution (hence why it’s implemented as a plugin and not part of Datasette core) but it works surprisingly well. The basic idea is to support a mini JSON language which can be detected and rendered as HTML. A couple of examples:

{
  "img_src": "https://raw.githubusercontent.com/edsu/irads/03fb4b/site/images/0771.png",
  "width": 200
}

Is rendered as an HTML <img src=""> element.

[
  {
    "label": "location:United States",
    "href": "/russian-ads/display_ads?_target=ec3ac"
  },
  {
    "label": "interests:Martin Luther King",
    "href": "/russian-ads/display_ads?_target=d6ade"
  },
  {
    "label": "interests:Jr.",
    "href": "/russian-ads/display_ads?_target=8e7b3"
  }
]

Is rendered as a comma-separated list of HTML links.

Why use JSON for this? Because SQLite has some incredibly powerful JSON features, making it trivial to output JSON as part of the result of a SQL query. Most interestingly of all it has json_group_array() which can work as an aggregation function to combine a set of related rows into a single JSON array.

The display_ads page shown above is powered by a SQL view. Here’s the relevant subset of that view:

select ads.id,
    case when image is not null then
        json_object("img_src", "https://raw.githubusercontent.com/edsu/irads/03fb4b/site/" || image, "width", 200)
    else
        "no image"
    end as img,
    json_group_array(
        json_object(
            "label", targets.name,
            "href", "/russian-ads/display_ads?_target="
                || urllib_quote_plus(targets.id)
        )
    ) as targeting
from ads
    join ad_targets on ads.id = ad_targets.ad_id
    join targets on ad_targets.target_id = targets.id
group by ads.id limit 10

I’m using SQLite’s JSON functions to dynamically assemble the JSON format that datasette-json-html knows how to render. I’m delighted at how well it works.

I’ve turned off arbitrary SQL querying against the main Facebook ads Datasette instance, but there’s a copy running at https://russian-ira-facebook-ads-sql-allowed.now.sh/russian-ads if you want to play with these queries.

Weird implementation details

The full source code for my implementation is available on GitHub.

I ended up using an experimental plugin hook to enable additional custom filtering on Datasette views in order to support showing ads against multiple m2m targets, but hopefully that will be made unnecessary as work on Datasette’s support for m2m relationships progresses.

I also experimented with YAML to generate the metadata.json file as JSON strings aren’t a great way of representing multi-line HTML and SQL. And if you want to see some really convoluted SQL have a look at how the canned query for the faceted targeting interface works.

This was a really fun project, which further stretched my ideas about what Datasette should be capable of out of the box. I’m hoping that the m2m work will make a lot of these crazy hacks redundant.

Tags: politics, projects, yaml, datasette, sqlite-utils

twitter-text-conformance

2010-02-06T15:39:27+00:00

twitter-text-conformance

This is a neat idea: Twitter have released open source libraries for parsing standard tweet syntax in Ruby and Java, but they’ve also released a set of YAML unit tests aimed at anyone who wants to implement the same parsing logic in other languages.

Via Twitter Engineering Blog

Tags: java, ruby, testing, twitter, yaml, conformance-suites

More YAML

2003-02-05T23:49:43+00:00

Paul Tchistopolskii's XML Alternatives reminded me to take another look at YAML. The specification has been updated since I last looked and seems to be a bit more complicated, but it's still a very nicely designed format. Implementations are available for Perl, Python and Ruby with C and Java on the way but strangely no one seems to be doing one for PHP yet. I'm doing a course at Uni on compilers at the moment which includes quite a lot of stuff about writing parsers so I'm very tempted to have a go at a YAML implementation in the next few weeks just to try stuff out. The possibility of easily swapping relatively complex data structures between PHP and Python is pretty tempting as well.

Tags: xml, yaml

YAML

2002-12-05T02:49:08+00:00

I forget quite how I got there, but the other day I found myself reading about YAML - YAML Ain't Markup Language. It looks really interesting. YAML aims to be an easily human readable format for storing and transferring structured data - so far, so XML. Where it differs from the IT world's favourite buzzword is that YAML is specifically designed to handle the three most common data structures - scalars (single values), lists and dictionaries. Here's a sample (taken from the official specification):

Time: 2001-11-23 15:01:42 -05:00
User: ed
Warning: >
  This is an error message
  for the log file

YAML has a number of obvious influences, including Python and MIME. Implementations already exist for Perl, Python and Java. XML-RPC aptly demonstrates how powerful the combination of lists, dictionaries and arrays can be for exchanging data between different systems and YAML looks like it offers a very nice alternative to XML based data structure syntax. I have to admit to being slightly concerned by the length of the specification - while YAML is definitely human readable it looks like it could take a while for a human to learn to write it. Then again, the actual generation of the format is meant to be handled by computers (I imagine that humans will make simple edits to YAML files more often than they create them from scratch) so the complexity of the more advanced parts of the specification is probably not too much of a problem.

Tags: markup, yaml

Simon Willison's Weblog: yaml

Have your agent record video demos of its work with shot-scraper video

How I built this

datasette.io news preview

model.yaml

openai/openai-openapi

YAML Explorer

JSON to YAML Converter

Auto-formatting YAML files with yamlfmt

Automating screenshots for the Datasette documentation using shot-scraper

Four screenshots to replace

full_text_search.png

advanced_export.png

binary_data.png

facets.png

Automating everything using GitHub Actions

Linking to the images

shot-scraper: automated screenshots for documentation, built on Playwright

The problem

Introducing shot-scraper

JavaScript and CSS selectors

A complex example

How it works: Playwright

Hooking shot-scraper up to GitHub Actions

What's next?

yaml-to-sqlite 1.0

yaml-to-sqlite 0.3.1

Controlling the style of dumped YAML using PyYAML

datasette-yaml 0.1.1

datasette-yaml 0.1

Weeknotes: airtable-export, generating screenshots in GitHub Actions, Dogsheep!

airtable-export

Social media cards for my TILs

Rendering Markdown

Dogsheep Beta

TIL this week

Releases this week

datasette-yaml 0.1a

airtable-export

Goodbye Zeit Now v1, hello datasette-publish-now - and talking to myself in GitHub issues

How Zeit Now inspired Datasette

Goodbye, Zeit Now v1

Migrating my projects

Hello, Zeit Now v2

The rest of my projects

Debugging Datasette with git bisect run

Supporting metadata.yaml

yaml-to-sqlite 0.3

niche-museums.com, powered by Datasette

Continuous deployment

Customization

Plugins

Weeknotes

yaml-to-sqlite 0.2.1

yaml-to-sqlite 0.2

Analyzing US Election Russian Facebook Ads

Exploring the Russian Facebook Ad spend

New tooling under the hood

sqlite-utils

datasette-json-html

Weird implementation details

twitter-text-conformance

More YAML

YAML