datasette-build

Build a directory full of files into a SQLite database

⚠️ Early alpha preview. Everything about this tool is likely to change.

Installation

Install this tool using pip or pipx:

pipx install datasette-build

This will provide the datasette-build CLI application.

You can also install it as a Datasette plugin. First install Datasette, then run:

datasette install datasette-build

This will provide a datasette build ... command that works the same as the datasette-build CLI application.

Or you can install it as a plugin for sqlite-utils. With that installed, run this:

sqlite-utils install datasette-build

Now you can access the tool as sqlite-utils build ...

Usage

The datasette-build (or datasette build or sqlite-utils build) command takes two arguments: a path to a SQLite database file and a path to a directory containing files to be loaded into that database:

datasette-build mydatabase.db myfiles/

The myfiles/ folder can contain a mixture of CSV, TSV and JSON files. Each file will be loaded into a table in the mydatabase.db SQLite database.

The database file will be created if it does not already exist.

Consider a myfiles/cities.csv file like this:

id,name,latitude,longitude,country
nyc,New York City,40.7128,-74.006,US
lon,London,51.5074,-0.1278,GB
tok,Tokyo,35.6895,139.6917,JP
par,Paris,48.8566,2.3522,FR
ber,Berlin,52.52,13.405,DE
syd,Sydney,-33.8688,151.2093,AU
cai,Cairo,30.0444,31.2357,EG
rio,Rio de Janeiro,-22.9068,-43.1729,BR
mos,Moscow,55.7558,37.6173,RU
mum,Mumbai,19.076,72.8777,IN

Since this has a id column the primary key for the table will be set to id. Without an id column the primary key will not be defined.

A myfiles/counties.tsv file could look like this:

id	name	population
US	United States	331002651
GB	United Kingdom	67886011
JP	Japan	126476461
FR	France	65273511
DE	Germany	83783942
AU	Australia	25499884
EG	Egypt	102334404
BR	Brazil	212559417
RU	Russia	145934462
IN	India	1380004385

And a myfiles/museums.json file like this:

[
  {
    "id": 1,
    "name": "Metropolitan Museum of Art",
    "city_id": "nyc"
  },
  {
    "id": 2,
    "name": "British Museum",
    "city_id": "lon"
  }
]

Running datasette-build mydatabase.db myfiles/ will create a SQLite database file containing three tables: cities, counties and museums. The schema will look like this:

CREATE TABLE [museums] (
   [id] INTEGER PRIMARY KEY,
   [name] TEXT,
   [city_id] TEXT
);
CREATE TABLE "cities" (
   [id] TEXT PRIMARY KEY,
   [name] TEXT,
   [latitude] FLOAT,
   [longitude] FLOAT,
   [country] TEXT
);
CREATE TABLE "countries" (
   [id] TEXT PRIMARY KEY,
   [name] TEXT,
   [population] INTEGER
);

Development

To set up this plugin locally, first checkout the code. Then create a new virtual environment:

cd datasette-build
python3 -m venv venv
source venv/bin/activate

Now install the dependencies and test dependencies:

pip install -e '.[test]'

To run the tests:

pytest