simonw / yaml-to-sqlite

Utility for converting YAML files 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

yaml-to-sqlite's Issues

Error if nested data contains a date

e.g.

 -
   id: 49
   name: jAdis
   press:
   - title: Title
   - date: 2018-11-01

Produces this error:

~ $ yaml-to-sqlite /tmp/h.db h /tmp/h.yaml 
Traceback (most recent call last):
  File "/usr/local/bin/yaml-to-sqlite", line 11, in <module>
    sys.exit(cli())
  File "/usr/local/lib/python3.7/site-packages/click/core.py", line 764, in __call__
    return self.main(*args, **kwargs)
  File "/usr/local/lib/python3.7/site-packages/click/core.py", line 717, in main
    rv = self.invoke(ctx)
  File "/usr/local/lib/python3.7/site-packages/click/core.py", line 956, in invoke
    return ctx.invoke(self.callback, **ctx.params)
  File "/usr/local/lib/python3.7/site-packages/click/core.py", line 555, in invoke
    return callback(*args, **kwargs)
  File "/usr/local/lib/python3.7/site-packages/yaml_to_sqlite/cli.py", line 17, in cli
    db[table].upsert_all(yaml.safe_load(yaml_file), pk=pk)
  File "/usr/local/lib/python3.7/site-packages/sqlite_utils/db.py", line 1094, in upsert_all
    extracts=extracts,
  File "/usr/local/lib/python3.7/site-packages/sqlite_utils/db.py", line 1015, in insert_all
    record.get(key, None if key != hash_id else _hash(record))
  File "/usr/local/lib/python3.7/site-packages/sqlite_utils/db.py", line 1202, in jsonify_if_needed
    return json.dumps(value)
  File "/usr/local/Cellar/python/3.7.5/Frameworks/Python.framework/Versions/3.7/lib/python3.7/json/__init__.py", line 231, in dumps
    return _default_encoder.encode(obj)
  File "/usr/local/Cellar/python/3.7.5/Frameworks/Python.framework/Versions/3.7/lib/python3.7/json/encoder.py", line 199, in encode
    chunks = self.iterencode(o, _one_shot=True)
  File "/usr/local/Cellar/python/3.7.5/Frameworks/Python.framework/Versions/3.7/lib/python3.7/json/encoder.py", line 257, in iterencode
    return _iterencode(o, 0)
  File "/usr/local/Cellar/python/3.7.5/Frameworks/Python.framework/Versions/3.7/lib/python3.7/json/encoder.py", line 179, in default
    raise TypeError(f'Object of type {o.__class__.__name__} '
TypeError: Object of type date is not JSON serializable

Found this bug while working on simonw/museums#7

Ability to create a table from a list of items

I need this for simonw/datasette.io#56 - I want to have a YAML file like this:

- One
- Two
- Three

And be able to load that file into a table with a single primary key column.

error if new colujmn in table isn't in first item of DB

Hi -

I was modifying the following file by adding a key, need_to_reorder in one of the fields. I was unable to add it to the DB unless I added it inside the first column.

I solved this via a workaround in my github workflow of nuking the DB, but won't be appropriate for datasets with long build times I think

Would be possible to use aliases inside .yaml files (to cross-reference rows inside same file)?

I'm trying to use this in order to use .yaml files as Meeting Notes, and then store meetings info into the sqlite db.
Main value it will provide is being able to query which agreements were taken and when they were taken (by pointing the 'agreement_date' to the '&date'), despite of providing other informational session of each meeting.

[meeting_notes.yaml.txt](https://github.com/simonw/yaml-to-sqlite/files/8393529/meeting_notes.yaml.txt)

I'm guessing some meetin_notes.yaml file like:

- meeting_name: weekly
  &date: 20220330
  attendees:
  - Potato Mendez
  - Emily Laughter
  - Paul Bean
  absences:
  - Johny Walker
  - Rose White
  agenda:
  - Topic 1
  - Topic 2
  - Topic 3
  - Topic 4
  session_development:
  - Explanation about how 
  - the meeting has been evolved.
  Agreements:
  -
    agreement_date: *date
    tag: participation
    summary: This is the first agreement taken on this meeting.
    method: consens
  -
    agreement_date: *date
    tag: work
    summary: This is the second agreement taken on this meeting
    method: votation

Thanks all for your work! <3

Doesn't work unless you provide a `--pk`

Probably due to a sqlite-utils change from a while ago.

yaml-to-sqlite news.db news news.yaml
Traceback (most recent call last):
  File "/Users/simon/.local/share/virtualenvs/datasette.io-TK86ygSO/bin/yaml-to-sqlite", line 8, in <module>
    sys.exit(cli())
  File "/Users/simon/.local/share/virtualenvs/datasette.io-TK86ygSO/lib/python3.9/site-packages/click/core.py", line 829, in __call__
    return self.main(*args, **kwargs)
  File "/Users/simon/.local/share/virtualenvs/datasette.io-TK86ygSO/lib/python3.9/site-packages/click/core.py", line 782, in main
    rv = self.invoke(ctx)
  File "/Users/simon/.local/share/virtualenvs/datasette.io-TK86ygSO/lib/python3.9/site-packages/click/core.py", line 1066, in invoke
    return ctx.invoke(self.callback, **ctx.params)
  File "/Users/simon/.local/share/virtualenvs/datasette.io-TK86ygSO/lib/python3.9/site-packages/click/core.py", line 610, in invoke
    return callback(*args, **kwargs)
  File "/Users/simon/.local/share/virtualenvs/datasette.io-TK86ygSO/lib/python3.9/site-packages/yaml_to_sqlite/cli.py", line 22, in cli
    db[table].upsert_all(docs, pk=pk)
  File "/Users/simon/.local/share/virtualenvs/datasette.io-TK86ygSO/lib/python3.9/site-packages/sqlite_utils/db.py", line 1824, in upsert_all
    return self.insert_all(
  File "/Users/simon/.local/share/virtualenvs/datasette.io-TK86ygSO/lib/python3.9/site-packages/sqlite_utils/db.py", line 1702, in insert_all
    raise PrimaryKeyRequired("upsert() requires a pk")
sqlite_utils.db.PrimaryKeyRequired: upsert() requires a pk

Can YAML be read from stdin? (spoiler: yes)

Would like to run some json through jq before sending to yaml-to-sqlite.

I tried jq .items install-30d.json | yaml-to-sqlite install-30d.db install-30d - (because - is the unix convention for replacing a filename with STDIN), and it worked!

I'm opening this issue primarily for search-ability in the hopes that it helps others.

Thanks, @simonw!