Simple overview of fastlite and sqlite-utils

sqlite.ipynb

{
  "cells": [
    {
      "metadata": {},
      "id": "bac2de2b",
      "cell_type": "markdown",
      "source": "# Simple overview of fastlite and sqlite-utils"
    },
    {
      "metadata": {
        "trusted": true
      },
      "id": "c47d01a1",
      "cell_type": "code",
      "source": "from fastcore.all import *\nfrom httpx import get",
      "execution_count": 1,
      "outputs": []
    },
    {
      "metadata": {
        "trusted": true
      },
      "id": "b6ab3cb9",
      "cell_type": "code",
      "source": "fl_url = 'https://answerdotai.github.io/fastlite/index.html.md'\nsqu_url = 'https://sqlite-utils.datasette.io/en/stable/_sources/python-api.rst.txt'",
      "execution_count": 2,
      "outputs": []
    },
    {
      "metadata": {
        "trusted": true
      },
      "id": "b81a17c8",
      "cell_type": "code",
      "source": "fl_docs = get(fl_url).text\nsqu_docs = get(squ_url).text",
      "execution_count": 3,
      "outputs": []
    },
    {
      "metadata": {
        "trusted": true
      },
      "id": "b8e421bd",
      "cell_type": "markdown",
      "source": "### What does fastlite add to sqlite-utils? How might I combine them in practice? Refer to the documentation in `fl_docs` for fastlite and `squ_docs` for sqlite-utils."
    },
    {
      "metadata": {},
      "id": "82ba6881",
      "cell_type": "markdown",
      "source": "Fastlite adds several quality-of-life improvements to sqlite-utils for interactive use, particularly in Jupyter environments:\n\n1. Simplified table and column access using `db.t` and `table.c` properties with auto-completion.\n2. Easy creation of dataclasses from table schemas.\n3. Shorthand syntax for querying and indexing tables.\n4. Automatic extraction of columns to lookup tables.\n5. Simplified many-to-many relationship handling.\n\nTo combine them in practice:\n\n1. Use fastlite's `database()` function to create a Database object.\n2. Access tables via `db.t.TableName` instead of `db[\"TableName\"]`.\n3. Use `table.c.ColumnName` for column references in queries.\n4. Create dataclasses with `table.dataclass()` for type hinting and ORM-like usage.\n5. Use `table(limit=N)` for quick queries instead of writing full SQL.\n6. Leverage `table.m2m()` for easy many-to-many relationship management.\n7. Use sqlite-utils' more advanced features like full-text search, custom SQL functions, and SpatiaLite integration when needed.\n\nThis combination allows for rapid, interactive data exploration and manipulation while retaining access to sqlite-utils' powerful underlying features."
    },
    {
      "metadata": {
        "trusted": true
      },
      "id": "21f3b303",
      "cell_type": "code",
      "source": "from fastlite import *\nfrom sqlite_utils import Database",
      "execution_count": 10,
      "outputs": []
    },
    {
      "metadata": {
        "trusted": true
      },
      "id": "e0de5af9",
      "cell_type": "markdown",
      "source": "### Show how to create a table of dogs with `.create()`. Use a sqlite in memory DB."
    },
    {
      "metadata": {
        "trusted": true
      },
      "id": "6a93b6c2",
      "cell_type": "code",
      "source": "db = database(\":memory:\")\n\ndb.t.dogs.create(\n    id=int,\n    name=str,\n    breed=str,\n    age=int,\n    is_good_dog=bool,\n    pk=\"id\"\n)\n\nprint(db.t.dogs.schema)",
      "execution_count": 38,
      "outputs": [
        {
          "name": "stdout",
          "output_type": "stream",
          "text": "CREATE TABLE [dogs] (\n   [id] INTEGER PRIMARY KEY,\n   [name] TEXT,\n   [breed] TEXT,\n   [age] INTEGER,\n   [is_good_dog] INTEGER\n)\n"
        }
      ]
    },
    {
      "metadata": {
        "trusted": true
      },
      "id": "b6063201",
      "cell_type": "markdown",
      "source": "### Show how to insert and query with dataclasses."
    },
    {
      "metadata": {
        "trusted": true
      },
      "id": "e8836d5c",
      "cell_type": "code",
      "source": "# Create dataclass\nDog = db.t.dogs.dataclass()\n\n# Insert data using dataclass\ndb.t.dogs.insert(Dog(id=1, name=\"Buddy\", breed=\"Labrador\", age=5, is_good_dog=True))\ndb.t.dogs.insert(Dog(id=2, name=\"Max\", breed=\"German Shepherd\", age=3, is_good_dog=True))\n\n# Query using dataclass\nfor dog in db.t.dogs():\n    print(f\"{dog.name} is a {dog.age} year old {dog.breed}\")\n\n# Query with conditions\ngood_dogs = db.t.dogs(where=\"is_good_dog = 1\")\nprint(\"\\nGood dogs:\")\nfor dog in good_dogs:\n    print(dog.name)\n\n# Query by primary key\nbuddy = db.t.dogs[1]\nprint(f\"\\n{buddy.name} is {buddy.age} years old\")",
      "execution_count": 39,
      "outputs": [
        {
          "name": "stdout",
          "output_type": "stream",
          "text": "Buddy is a 5 year old Labrador\nMax is a 3 year old German Shepherd\n\nGood dogs:\nBuddy\nMax\n\nBuddy is 5 years old\n"
        }
      ]
    },
    {
      "metadata": {
        "trusted": true
      },
      "id": "9275b656",
      "cell_type": "code",
      "source": "",
      "execution_count": null,
      "outputs": []
    }
  ],
  "metadata": {
    "kernelspec": {
      "name": "python3",
      "display_name": "Python 3 (ipykernel)",
      "language": "python"
    },
    "language_info": {
      "name": "python",
      "version": "3.11.8",
      "mimetype": "text/x-python",
      "codemirror_mode": {
        "name": "ipython",
        "version": 3
      },
      "pygments_lexer": "ipython3",
      "nbconvert_exporter": "python",
      "file_extension": ".py"
    },
    "gist": {
      "id": "",
      "data": {
        "description": "nbs/sqlite.ipynb",
        "public": false
      }
    }
  },
  "nbformat": 4,
  "nbformat_minor": 5
}