Datasette - a tool to automatically create web API by dropping in an SQLite file

Open Knowledge Labs
1

Hi everyone,

have you already heard of a tool called Datasette? I discovered it yesterday and find it really handy.

Github page:

The author presenting it (this is the best):

So basically you can just throw an SQLite database at it, and it creates a complete web-API for that dataset including facets, filtering, different output formats for filtered subsets, showing things on a map (if columns are called longitude and latitude), etc.

Does any of you have experience with it? I am considering to use it for out Open Power System Data project https://open-power-system-data.org/ where all our frictionless datapackage based Data Packages are also shipped with an SQLite database - so it would be a relatively easy set for us.

Best
Ingmar

2 Likes
2

Hey @ingmars, we (Catalyst) discovered Datasette a few months ago and have started using it to publish our PUDL data via Google Cloud Run. We’re planning to do much more of this going forward, and are looking at moving to SQLite as our archiving format partly as a result.

2 Likes
3

Datasette maintainer here. I’ve been meaning to reach out to OKFN people for a while. I think the project shares a lot of philosophical goals with OKFN - my aim is to make it as inexpensive and easy as possible to share data in as flexible a way as possible.

One thing I’d love advice on is how Datasette could incorporate data packages - as both an import and export format.

People may find a recent talk I gave interesting - it covers ny Dogsheep project for personal analytics built on top of Datasette. The forum won’t let me include links (presumably because my account is new) so search Google for “Personal Data Warehouses: Reclaiming Your Data”.

3 Likes
4

Thanks and welcome, @simonw!

Here’s the link to the ‘Personal Data Warehouses: Reclaiming Your Data’ talk you mentioned:

5

Hi Simon

For importing datapackages into datasette you can use GitHub - chris48s/datapackage-to-datasette: 📦 Import Frictionless Data Datapackages into SQLite and generate Datasette metadata

In terms of going in the other direction, datasette is fundamentally a frontend over a SQLite DB and we have libraries that can convert a SQLite DB to a datapackage so the lion’s share of what you need there exists. It would probably be a small job to wrap that in a datasette plugin to give you a ‘export to datapackage’ button/feature (as opposed to datapackage-to-datasette which is a ‘tool’, to use your own terminology).

Currently the FD ecosystem is in a slightly transitional place. datapackage-to-datasette uses GitHub - frictionlessdata/datapackage-py: A Python library for working with Data Packages. internally to import the datapackage into SQLite, but we are very close to releasing v4.0.0 of frictionless framework GitHub - frictionlessdata/framework: Data management framework for Python that provides functionality to describe, extract, validate, and transform tabular data Both of those packages will be able to convert a SQLite DB to a datapackage, but once we release v4 of frictionless-py that will become the recommended package for interacting with data packages in python. We’re close, but not quite there yet frictionless · PyPI If you’re looking to write a plugin I recommend planning to use frictionless-py for that using the SQL Adapter. The minimum viable implementation is

pip install frictionless[sql]

from frictionless import Package

package = Package.from_sql(url='sqlite:///mydb')
package.to_zip('/path/to/zipfile')

You could also look at reversing the process in GitHub - chris48s/datapackage-to-datasette: 📦 Import Frictionless Data Datapackages into SQLite and generate Datasette metadata to augment the package metadata.

1 Like