Skip to content

Instantly share code, notes, and snippets.

@jph00

jph00/sqlite.ipynb

Last active December 9, 2024 20:44
Show Gist options
  • Save jph00/a41193f605d3eb63b583729f802b3d9a to your computer and use it in GitHub Desktop.
Save jph00/a41193f605d3eb63b583729f802b3d9a to your computer and use it in GitHub Desktop.
Download ZIP

Revisions

  1. jph00 revised this gist Sep 6, 2024. No changes.
  2. jph00 created this gist Sep 6, 2024.
    150 changes: 150 additions & 0 deletions sqlite.ipynb
    View file
    Original file line number Diff line number Diff line change
    @@ -0,0 +1,150 @@
    {
    "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
    }