hytradboi-datasette.md

Datasette: a big bag of tricks for solving interesting problems using SQLite

Video: https://www.hytradboi.com/2022/datasette-a-big-bag-of-tricks-for-solving-interesting-problems-using-sqlite

CSV downloaded from from https://geodata.myfwc.com/datasets/myfwc::manatee-carcass-recovery-locations-in-florida/about

Commands run during the demo:

sqlite-utils insert manatees.db locations \
    Manatee_Carcass_Recovery_Locations_in_Florida.csv --csv

sqlite-utils schema manatees.db

sqlite-utils manatees.db "select * from locations limit 10"

sqlite-utils transform manatees.db locations \
  --rename LAT latitude \
  --rename LONG_ longitude \
  --type TLENGTH float \
  --drop created_user \
  --drop last_edited_user \
  --drop X \
  --drop Y \
  --drop STATE \
  --drop OBJECTID \
  --pk FIELDID

sqlite-utils convert manatees.db locations \
  REPDATE created_date last_edited_date \
  'r.parsedatetime(value)'

Further reading

• sqlite-utils command line tool documentation
• Executing advanced ALTER TABLE operations in SQLite
• Apply conversion functions to data in SQLite columns with the sqlite-utils CLI tool
• Refactoring databases with sqlite-utils extract