yaml-to-sqlite

yaml-to-sqlite

Load the contents of a YAML file into a SQLite database table.

$ yaml-to-sqlite --help
Usage: yaml-to-sqlite [OPTIONS] DB_PATH TABLE YAML_FILE

  Convert YAML files to SQLite

Options:
  --version             Show the version and exit.
  --pk TEXT             Column to use as a primary key
  --single-column TEXT  If YAML file is a list of values, populate this column
  --help                Show this message and exit.

Usage

Given a news.yml file containing the following:

- date: 2021-06-05
  body: |-
    [Datasette 0.57](https://docs.datasette.io/en/stable/changelog.html#v0-57) is out with an important security patch.
- date: 2021-05-10
  body: |-
    [Django SQL Dashboard](https://simonwillison.net/2021/May/10/django-sql-dashboard/) is a new tool that brings a useful authenticated subset of Datasette to Django projects that are built on top of PostgreSQL.

Running this command:

$ yaml-to-sqlite news.db stories news.yml

Will create a database file with this schema:

$ sqlite-utils schema news.db
CREATE TABLE [stories] (
   [date] TEXT,
   [body] TEXT
);

The --pk option can be used to set a column as the primary key for the table:

$ yaml-to-sqlite news.db stories news.yml --pk date
$ sqlite-utils schema news.db
CREATE TABLE [stories] (
   [date] TEXT PRIMARY KEY,
   [body] TEXT
);

Single column YAML lists

The --single-column option can be used when the YAML file is a list of values, for example a file called dogs.yml containing the following:

- Cleo
- Pancakes
- Nixie

Running this command:

$ yaml-to-sqlite dogs.db dogs.yaml --single-column=name

Will create a single dogs table with a single name column that is the primary key:

$ sqlite-utils schema dogs.db
CREATE TABLE [dogs] (
   [name] TEXT PRIMARY KEY
);
$ sqlite-utils dogs.db 'select * from dogs' -t
name
--------
Cleo
Pancakes
Nixie