tutorials
6 rows
| path ▼ | title | body |
|---|---|---|
| /tutorials/clean-data | Cleaning data with sqlite-utils and Datasette | Tutorials > Cleaning data with sqlite-utils and Datasette Cleaning data with sqlite-utils and Datasette This tutorial will show you how to use the sqlite-utils command line tool import data into SQLite, clean it and then explore it with Datasette. The concepts in this tutorial are also demonstrated in this video of a talk I gave at the HYTRADBOI conference in April 2022. Start with some CSV data The Florida Fish and Wildlife Conservation Commission maintain a database of manatee carcass recovery locations, with details dating back to April 1974. You can request the latest copy as a CSV file from their website - or download this copy of the CSV: Manatee_Carcass_Recovery_Locations_in_Florida.csv generated on July 31st 2022. Install sqlite-utils Here are the installation instructions. If you are on a Mac with Homebrew installed you can run brew install sqlite-utils. Python users can run pip install sqlite-utils. Import the CSV into a SQLite database sqlite-utils is a command line tool, so you'll need to open up a terminal on your computer and change directory to the place where you saved the CSV file. You can create a new manatees.db SQLite database like this: sqlite-utils insert manatees.db locations \ Manatee_Carcass_Recovery_Locations_in_Florida.csv --csv -d This sqlite-utils insert command inserts data into a database table, creating the table if necessary. manatees.db is the SQLite database file we are using - this will be created if it doesn't exist yet. locations is the name of the table we are creating Manatee_Carcass_Recovery_Locations_in_Florida.csv is the CSV file we are importing --csv tells the tool to treat the incoming data as CSV data The -d option tells the tool to automatically detect the types of data. Without this, every column will be treated as text. With this option columns that have numeric data in will be used to populate numeric columns. Having created the database, we can use the schema command to view its schema: sqlite-utils schema manatees.db The output of that command looks… |
| /tutorials/codespaces | Using Datasette in GitHub Codespaces | Tutorials > Using Datasette in GitHub Codespaces Using Datasette in GitHub Codespaces GitHub Codespaces is a free tool from GitHub that gives you a full Linux development environment running in the cloud, accessed entirely through your browser. It's a really neat way to get started with Datasette, in particular as a tool to build your own private Datasette instance and start using it to explore and analyze data. Please note that Codespaces is not designed to host public websites. Any Datasette instances you create within a Codespace will only be accessible as private instances for your personal use. This tutorial will show how to install and run Datasette in a Codespaces environment. Step 1: Create a Codespace Each Codespace needs to be attached to a GitHub repository. You can use any of your existing repositories, or create a brand new one. Or if you don't want to create your own repository you can use my simonw/codespaces-datasette repository instead. Use the "Code" button, select the "Codespaces" tab and then click "Create codespace on main" to start a new codespace. After a short delay while the codespace starts running you should see an interface that looks like this: We will be working exclusively in the Terminal panel at the bottom of the page. Step 2: Install Datasette Before we can run Datasette, we need to install it. We can do that using the following command: pipx install datasette This will install Datasette using pipx, which ensures it is kept in its own separate environment where it can't clash with any other Python code you may decide to run in your codespace. To confirm that Datasette has installed successfully, run this: datasette --version We also need to install a plugin: datasette install datasette-codespaces The datasette-codespaces plugin makes some small changes to Datasette to better run in the Codespaces environment. You can run Datasette without it but you may run into some frustrating issues, such as internal links not working correctly. Step 3: Start Datasette To start Datasette… |
| /tutorials/data-analysis | Data analysis with SQLite and Python | Tutorials > Data analysis with SQLite and Python Data analysis with SQLite and Python A 2h45m video tutorial about SQLite, Python, sqlite-utils and Datasette presented at PyCon 2023. What you'll need python3 and pip Optional: GitHub Codespaces sqlite-utils Using the command-line tools to clean data Exploring data with Datasette Installing Datasette locally Try a database: legislators.db Install some plugins Learning SQL with Datasette Using sqlite-utils as a Python library, to import all the PEPs Enabling full-text search Publishing a database to Vercel Other publishing options Datasette Lite Loading SQLite, CSV and JSON data Installing plugins Further reading Advanced SQL Aggregations Subqueries CTEs JSON Window functions Baked Data Niche Museums and TILs Generating a newsletter with an Observable notebook More demos and further reading Fun demos SpatiaLite What you'll need python3 and pip For the first part of this tutorial, you'll need a Python 3 interpreter with the sqlite3 standard library module available. You can run this on your own computer, or use a browser-based environment. https://pyodide.org/en/stable/console.html will work for a purely browser-based (WebAssembly) environment. For the second part, you'll also need the ability to pip install Python packages. Python 3 on your own laptop (maybe in a fresh virtual environment) is a good option here. You could also use GitHub Codespaces or Google Colab or Jupyter Hub or your online notebook solution of choice. Optional: GitHub Codespaces I'll be working through the tutorial using GitHub Codespaces, using https://github.com/github/codespaces-jupyter sqlite-utils sqlite-utils provides "CLI tool and Python utility functions for manipulating SQLite databases". You can install it the same way as Datasette: pip install sqlite-utils Or with pipx: pipx install sqlite-utils Or with Home… |
| /tutorials/explore | Exploring a database with Datasette | Tutorials > Exploring a database with Datasette Exploring a database with Datasette This tutorial will teach you how to use Datasette to explore a new database. Datasette offers flexible tools for exploring data tables. It's always worth spending time familiarizing yourself with data in its raw, tabula form before thinking about ways to apply more sophisticated analysis or visualization. The congress-legislators data We'll be using an example database of Members of the United States Congress, 1789 to present. I built this example using data from the unitedstates/congress-legislators project on GitHub, maintained by Joshua Tauberer, Eric Mill and over 100 other contributors. Access the example in Datasette here: https://datasette.io/legislators Understanding tables The key to understanding relational databases such as SQLite (which we are using here), MySQL, PostgreSQL, Oracle and so on is to learn to think in tables. Database tables are like spreadsheets - they have columns and rows, and each row provides a value for every column. Unlike spreadsheets you can't merge cells together or apply formatting - a database table is a straight-forward two-dimensional grid. It's just the data. Each column in a table has a type. In SQLite these types can be one of text, integer, real (for floating point values) and blob (for binary data). The type of the column matters because it affects what happens when you sort the table by that column, or which mathematical operations you can use against those values. Browsing tables The tables in our example database are: legislators - over 12,000 rows - individuals who have served as senators or representatives legislator_terms - over 45,000 rows - terms served by legislators, back to 1789 executives - 80 rows - individuals who have served as President or Vice President executive_terms - over 130 rows - terms served by executives offices - over 1,300 rows - district officers maintained by current legislators social_media - over 500 rows - social media accounts for current legislato… |
| /tutorials/learn-sql | Learn SQL with Datasette | Tutorials > Learn SQL with Datasette Learn SQL with Datasette This tutorial follows the tutorial on exploring data with Datasette and shows how you can use Datasette to start learning to write custom SQL queries of your own. We will be using the same example database as that tutorial: https://datasette.io/legislators This database contains information about US Presidents, Vice Presidents and Members of Congress from 1789 to today. You can follow the previous tutorial to become familiar with the data. SQL and SQLite Datasette is software that runs on top of a SQL database. There are many different SQL database systems, such as MySQL, PostgreSQL, Microsoft SQL Server and Oracle. The database used by Datasette is called SQLite. You may not realize it, but you already use SQLite every day: it is built into many popular applications, including Google Chrome and Firefox, and runs on laptops, iPhones, Android phones and all sorts of other smaller devices. SQL stands for Structured Query Language - it is a text language for running queries against a database. Every database implements a slightly different dialect of SQL - for this tutorial I will try to keep to the subset of SQLite's dialect that is most likely to work across other databases as well. View and edit SQL Every table page in Datasette - such as this one - includes a "View and edit SQL" link that looks like this: Click that link to see the SQL query that was used for the current page - including any filters that have been applied, in this case the filter for bio_gender = 'F'. You can then edit that query to make it do something else! Exercises Start with that example, then: Click "Format SQL" to tidy this up and make it easier to read and edit Modify that query to return only the id and name columns Get it to order by name instead of sorting by id Change the p0 value to M to return male instead of female legislators Try replacing the list of columns with select * from - a shortcut for all columns The structure of a basic select query Here's the formatt… |
| /tutorials/spatialite | Building a location to time zone API with SpatiaLite | Tutorials > Building a location to time zone API with SpatiaLite Building a location to time zone API with SpatiaLite The SpatiaLite extension for SQLite adds a large number of functions for geospatial analysis, which can be used with Datasette to build GIS (Geographic Information System) applications. This tutorial will show how SpatiaLite and Datasette can be combined to create a JSON API that can return the time zone for a specific latitude and longitude point on earth. What we are going to build You can try the API out here. Give it a latitude and longitude and it will return the corresponding time zone ID: https://timezones.datasette.io/timezones/by_point Some examples: Brighton, England is in Europe/London (in JSON) San Francisco, USA is in America/Los_Angeles (in JSON) Tokyo, Japan is Asia/Tokyo (in JSON) Setting up a development environment You will need two things in place for this tutorial: SpatiaLite installed on your system A Python installation that allows the sqlite3 module to load additional extensions Recommended: Use GitHub Codespaces GitHub Codespaces can provide you with a free development environment for this project accessible through your web browser, with all of the tools you need pre-installed. This is the easiest way to work through this tutorial. Visit this link to create a new Codespace with everything you will need for the rest of the tutorial. Using a Mac On a Mac you can install SpatiaLite and Datasette using Homebrew like so: brew install spatialite-tools datasette If you are on a Mac you may find that your installation of Python cannot load external SQLite modules. You can check by running the following: datasette --load-extension spatialite If you get an error message about enable_load_extension then consult this page for hints on how to fix the problem. Building the database To build this project we first need geographic shapes for all of the world's time zones. timezone-boundary-builder is a project by Evan Siroky which uses data from OpenStreetMap to create detailed t… |
Table schema
CREATE TABLE [tutorials] ( [path] TEXT PRIMARY KEY, [title] TEXT, [body] TEXT );