Find stories in data

PyPI Changelog Python 3.x License discord mastodon: @datasette

Annotated version of this introductory video

Datasette is a tool for exploring and publishing data. It helps people take data of any shape, analyze and explore it, and publish it as an interactive website and accompanying API.

Datasette is aimed at data journalists, museum curators, archivists, local governments, scientists, researchers and anyone else who has data that they wish to share with the world. It is part of a wider ecosystem of 46 tools and 127 plugins dedicated to making working with structured data as productive as possible.

Try a demo and explore 33,000 power plants around the world, then follow the tutorial or take a look at some other examples of Datasette in action.

Then read how to get started with Datasette, subscribe to the monthly-ish newsletter and consider signing up for office hours for an in-person conversation about the project.

New: Datasette Desktop - a macOS desktop application for easily running Datasette on your own computer!

Exploratory data analysis

Import data from CSVs, JSON, database connections and more. Datasette will automatically show you patterns in your data and help you share your findings with your colleagues.

Instant data publishing

datasette publish lets you instantly publish your data to hosting providers like Google Cloud Run, Heroku or Vercel.

Rapid prototyping

Spin up a JSON API for any data in minutes. Use it to prototype and prove your ideas without building a custom backend.

Latest news

22nd August 2023 #

Datasette 1.0a4 has a fix for a security vulnerability in the Datasette 1.0 alpha series: the API explorer interface exposed the names of private databases and tables in public instances that were protected by a plugin such as datasette-auth-passwords, though not the actual content of those tables. See the security advisory for more details and workarounds for if you can't upgrade immediately. The latest edition of the Datasette Newsletter also talks about this issue.

15th August 2023 #

datasette-write-ui: a Datasette plugin for editing, inserting, and deleting rows introduces a new plugin adding add/edit/delete functionality to Datasette, developed by Alex Garcia. Alex built this for Datasette Cloud, and this post is the first announcement made on the new Datasette Cloud blog - see also Welcome to Datasette Cloud.

9th August 2023 #

Datasette 1.0a3 is an alpha release of Datasette that previews the new default JSON API design that’s coming in version 1.0 - the single most significant change planned for that 1.0 release.

1st July 2023 #

New tutorial: Data analysis with SQLite and Python. This tutorial, originally presented at PyCon 2023, includes a 2h45m video and an extensive handout that should be useful with or without the video. Topics covered include Python's sqlite3 module, sqlite-utils, Datasette, Datasette Lite, advanced SQL patterns and more.

24th March 2023 #

I built a ChatGPT plugin to answer questions about data hosted in Datasette describes a new experimental Datasette plugin to enable people to query data hosted in a Datasette interface via ChatGPT, asking human language questions that are automatically converted to SQL and used to generate a readable response.

23rd February 2023 #

Using Datasette in GitHub Codespaces is a new tutorial showing how Datasette can be run in GitHub's free Codespaces browser-based development environments, using the new datasette-codespaces plugin.

28th January 2023 #

Examples of sites built using Datasette now includes screenshots of Datasette deployments that illustrate a variety of problems that can be addressed using Datasette and its plugins.

13th January 2023 #

Semantic search answers: Q&A against documentation with GPT3 + OpenAI embeddings shows how Datasette can be used to implement semantic search and build a system for answering questions against an existing corpus of text, using two new plugins: datasette-openai and datasette-faiss, and a new tool: openai-to-sqlite.

9th January 2023 #

Datasette 0.64 is out, and includes a strong warning against running SpatiaLite in production without disabling arbitrary SQL queries, plus a new --setting default_allow_sql off setting to make it easier to do that. See Datasette 0.64, with a warning about SpatiaLite for more about this release. A new tutorial, Building a location to time zone API with SpatiaLite, describes how to safely use SpatiaLite and Datasette to build and deploy an API for looking up time zones for a latitude/longitude location.

15th December 2022 #

Datasette 1.0a2: Upserts and finely grained permissions describes the new upsert API and much improved permissions capabilities introduced in the latest Datasette 1.0a2 alpha release.

2nd December 2022 #

Datasette’s new JSON write API: The first alpha of Datasette 1.0 introduces the new write API shipped in the first of the Datasette 1.0 alpha series of releases, including detailed descriptions of two demos that show how the API can be used.

27th October 2022 #

Datasette 0.63 is out. Here are the annotated release notes.

8th September 2022 #

Exploring the training data behind Stable Diffusion describes the process of building and deploying a 4GB searchable SQLite database using Datasette, starting with Parquet data that was used to train the Stable Diffusion image generation model. See also Exploring 12 Million of the 2.3 Billion Images Used to Train Stable Diffusion’s Image Generator.

21st August 2022 #

Analyzing ScotRail audio announcements with Datasette—from prototype to production provides a detailed walk-through of the process of constructing an initial rapid prototype using Datasette Lite, extending it with a custom plugin and then deploying it as a full Datasette instance using GitHub Actions and Vercel.

14th August 2022 #

Datasette 0.62 introduces compatibility with Pyodide for Datasette Lite, and incorporates a number of bug fixes, plugin hook upgrades and other improvements.

All news

Latest releases

21st September 2023

datasette 1.0a7 - An open source multi-tool for exploring and publishing data

  • Fix for a crashing bug caused by viewing the table page for a named in-memory database. #2189

datasette 0.64.4

  • Fix for a crashing bug caused by viewing the table page for a named in-memory database. #2189

20th September 2023

datasette-upload-dbs 0.3.1 - Upload SQLite database files to Datasette

  • More self-explanatory copy on the upload database page. #11

datasette-mask-columns 0.2.2 - Datasette plugin that masks specified database columns

19th September 2023

llm 0.11 - A CLI utility and Python library for interacting with Large Language Models, including OpenAI, PaLM and local models installed on your own machine.

LLM now supports the new OpenAI gpt-3.5-turbo-instruct model, and OpenAI completion (as opposed to chat completion) models in general. #284

llm -m gpt-3.5-turbo-instruct 'Reasons to tame a wild beaver:'

OpenAI completion models like this support a -o logprobs 3 option, which accepts a number between 1 and 5 and will include the log probabilities (for each produced token, what were the top 3 options considered by the model) in the logged response.

llm -m gpt-3.5-turbo-instruct 'Say hello succinctly' -o logprobs 3

You can then view the logprobs that were recorded in the SQLite logs database like this:

sqlite-utils "$(llm logs path)" \
  'select * from responses order by id desc limit 1' | \
  jq '.[0].response_json' -r | jq

Truncated output looks like this:

      "text": "Hi",
      "top_logprobs": [
          "Hi": -0.13706253,
          "Hello": -2.3714375,
          "Hey": -3.3714373
      "text": " there",
      "top_logprobs": [
          " there": -0.96057636,
          "!\"": -0.5855763,
          ".\"": -3.2574513

Also in this release:

  • The llm.user_dir() function, used by plugins, now ensures the directory exists before returning it. #275
  • New LLM_OPENAI_SHOW_RESPONSES=1 environment variable for displaying the full HTTP response returned by OpenAI compatible APIs. #286
  • The llm embed-multi command now has a --batch-size X option for setting the batch size to use when processing embeddings - useful if you have limited memory available. #273
  • The collection.embed_multi() method also now accepts an optional batch_size=int argument.
  • Fixed two bugs with llm embed-multi --files relating to handling of directories. Thanks, ealvar3z. #274, #280

18th September 2023

datasette-write-ui 0.0.1a9

15th September 2023

datasette-export-notebook 1.0.1 - Datasette plugin providing instructions for exporting data to Jupyter or Observable

  • Now also works with the Datasette 1.0 alpha series. #17

12th September 2023

llm 0.10 - A CLI utility and Python library for interacting with Large Language Models, including OpenAI, PaLM and local models installed on your own machine.

The two major features in this release are the llm chat command and support for embedding binary data.

llm chat

The new llm chat command starts an ongoing chat conversation with a model in your terminal. It works with all models supported by LLM and its plugins, including locally installed models such as Llama 2. #231

This offers a big performance boost for local models, since they don't need to be freshly loaded into memory for each prompt.

Here's an example chat with Llama 2 13B, provided by the llm-mlc plugin.

llm  chat  -m  mlc-chat-Llama-2-13b-chat-hf-q4f16_1

This starts a chat session:

Type 'exit' or 'quit' to exit
Type '!multi' to enter multiple lines, then '!end' to finish
> Who are you?
Hello! I'm just an AI, here to assist you with any questions you may have.
My name is LLaMA, and I'm a large language model trained to provide helpful
and informative responses to a wide range of questions and topics. I'm here
to help you find the information you need, while ensuring a safe and
respectful conversation. Please feel free to ask me anything, and I'll do my
best to provide a helpful and accurate response.
> Tell me a joke about otters
Sure, here's a joke about otters:

Why did the otter go to the party?

Because he heard it was a "whale" of a time!

(Get it? Whale, like a big sea mammal, but also a "wild" or "fun" time.
Otters are known for their playful and social nature, so it's a lighthearted
and silly joke.)

I hope that brought a smile to your face! Do you have any other questions or
topics you'd like to discuss?
> exit

Chat sessions are logged to SQLite - use llm logs to view them. They can accept system prompts, templates and model options - consult the chat documentation for details.

Binary embedding support

LLM's embeddings feature has been expanded to provide support for embedding binary data, in addition to text. #254

This enables models like CLIP, supported by the new llm-clip plugin.

CLIP is a multi-modal embedding model which can embed images and text into the same vector space. This means you can use it to create an embedding index of photos, and then search for the embedding vector for "a happy dog" and get back images that are semantically closest to that string.

To create embeddings for every JPEG in a directory stored in a photos collection, run:

llm install  llm-clip
llm embed-multi  photos  --files  photos/  '*.jpg'  --binary  -m  clip

Now you can search for photos of racoons using:

llm similar photos -c 'raccoon'

This spits out a list of images, ranked by how similar they are to the string "raccoon":

{"id": "IMG_4801.jpeg", "score": 0.28125139257127457, "content": null, "metadata": null}
{"id": "IMG_4656.jpeg", "score": 0.26626441704164294, "content": null, "metadata": null}
{"id": "IMG_2944.jpeg", "score": 0.2647445926996852, "content": null, "metadata": null}
Also in this release
  • The LLM_LOAD_PLUGINS environment variable can be used to control which plugins are loaded when llm starts running. #256
  • The llm plugins --all option includes builtin plugins in the list of plugins. #259
  • The llm embed-db family of commands has been renamed to llm collections. #229
  • llm embed-multi --files now has an --encoding option and defaults to falling back to latin-1 if a file cannot be processed as utf-8. #225

llm 0.10a1

  • Support for embedding binary data. #254
  • llm chat now works for models with API keys. #247
  • llm chat -o for passing options to a model. #244
  • llm chat --no-stream option. #248
  • LLM_LOAD_PLUGINS environment variable. #256
  • llm plugins --all option for including builtin plugins. #259
  • llm embed-db has been renamed to llm collections. #229
  • Fixed bug where llm embed -c option was treated as a filepath, not a string. Thanks, mhalle. #263

11th September 2023

datasette-cluster-map 0.18a0 - Datasette plugin that shows a map for any data with latitude/longitude columns

  • Compatible with Datasette 1.0 alphas (but no longer compatible with the 0.x series, hence the alpha release). #42

9th September 2023

sqlite-utils 3.35.1 - CLI tool and Python library for manipulating SQLite databases

  • Fixed a bug where table.transform() would sometimes re-assign the rowid values for a table rather than keeping them consistent across the operation. (#592)

datasette-edit-schema 0.6 - Datasette plugin for modifying table schemas

New features: - Create a new table, setting the name of the primary key and adding multiple columns of different types. #5 - Set or change the primary key for a table. #1 - Add and edit foreign key relationships for a table. #7 - When altering a table, each column now shows a small sample of example values to help you decide the column's new name and type. #28 - Delete table button now asks for confirmation. #41

8th September 2023

datasette 1.0a6 - An open source multi-tool for exploring and publishing data

  • New plugin hook: actors_from_ids(datasette, actor_ids) and an internal method to accompany it, await .actors_from_ids(actor_ids). This mechanism is intended to be used by plugins that may need to display the actor who was responsible for something managed by that plugin: they can now resolve the recorded IDs of actors into the full actor objects. (#2181)
  • DATASETTE_LOAD_PLUGINS environment variable for controlling which plugins are loaded by Datasette. (#2164)
  • Datasette now checks if the user has permission to view a table linked to by a foreign key before turning that foreign key into a clickable link. (#2178)
  • The execute-sql permission now implies that the actor can also view the database and instance. (#2169)
  • Documentation describing a pattern for building plugins that themselves define further hooks for other plugins. (#1765)
  • Datasette is now tested against the Python 3.12 preview. (#2175)

7th September 2023

datasette-graphql 3.0a0 - Datasette plugin providing an automatic GraphQL API for your SQLite databases

  • Upgraded for compatibility with the Datasette 1.0 alpha series. #96

datasette-edit-schema 0.6a2 - Datasette plugin for modifying table schemas

  • Ability to create a new table. #5

All releases