I ❤️ Datasette

Ray Voelker
ray.voelker@gmail.com
ray.voelker@cincinnatilibrary.org

What is Datasette?

• Datasette is described by its creator, Simon Willison, as "a tool for exploring and publishing data".
• “Datasette is aimed at journalists, museum curators, archivists, local governments and anyone else who has data that they wish to share with the world.”

Simon's Datasette Presentations / Demos

• “Introduction to Datasette and sqlite-utils”
  https://www.youtube.com/watch?v=7kDFBnXaw-c
• “Simon Willison - Instant serverless APIs, powered by SQLite - PyCon 2019”
  https://www.youtube.com/watch?v=pTr1uLQTJNE
• “Keynote: Simon Willson - Datasette and Dogsheep: Liberating your personal data” https://www.youtube.com/watch?v=UFn82w-97kI

Things I ❤️ about Datasette

• Written in Python / Available via pip
• Easy and intuitive to use
• Well-documented
• Useful, Large, and growing plugin library
• Built-in API... Using SQL!
• Open source + supportive developer
• Flexible deployment with scale-to-zero-cost / free hosting options in mind

Cincinnati & Hamilton County Public Library: Collection Analysis

Question...

• What types of item formats are there in the collection?

						
-- how many of each item type?
select
  distinct item.item_format,
  count(item.item_record_num) as count_total_items
from
  item
group by
  item.item_format
having
  count(item.item_record_num) >= 1000
order by
  count_total_items DESC					
						
					

Named parameters: Datasette provides a nice interface when a named parameter is placed within a SQL query

						
select
  bib.*
from
  bib
where
  indexed_subjects like '%' 
  || :subject 
  || '%'
						
					

Canned Queries

• Create something that looks more like a traditional REST API

						
# Datasette URI construction
scheme = "https://"
host = "ilsweb.cincinnatilibrary.org"
# path contains the path of the Datasette instance,
# and the name of the database
path = "/collection-analysis/current_collection"
canned_query_path = "/item_lookup_by_barcode"
response_format = '' # '' or '.csv' or '.json'
query_parameter = "?barcode="
query_parameter_value = "A000036107985"

full_request_uri = scheme + host \
    + path + canned_query_path + response_format \
    + query_parameter + query_parameter_value
						
					

Thanks, and please, let me know if you come up with any cool queries or ❤️ Datasette as much as I do!

Ray Voelker
ray.voelker@gmail.com
ray.voelker@cincinnatilibrary.org