answerdotai / fastlite

A bit of extra usability for sqlite

fastlite

fastlite provides some little quality-of-life improvements for interactive use of the wonderful sqlite-utils library. It’s likely to be particularly of interest to folks using Jupyter.

Install

pip install fastlite

Overview

from fastlite import *
from fastcore.utils import *
from fastcore.net import urlsave

We demonstrate fastlite‘s features here using the ’chinook’ sample database.

url = 'https://github.com/lerocha/chinook-database/raw/master/ChinookDatabase/DataSources/Chinook_Sqlite.sqlite'
path = Path('chinook.sqlite')
if not path.exists(): urlsave(url, path)

db = database("chinook.sqlite")

Databases have a t property that lists all tables:

dt = db.t
dt

Album, Artist, Customer, Employee, Genre, Invoice, InvoiceLine, MediaType, Playlist, PlaylistTrack, Track

You can use this to grab a single table…:

artist = dt.artists
artist

<Table artists (does not exist yet)>

artist = dt.Artist
artist

<Table Artist (ArtistId, Name)>

…or multiple tables at once:

dt['Artist','Album','Track','Genre','MediaType']

[<Table Artist (ArtistId, Name)>,
 <Table Album (AlbumId, Title, ArtistId)>,
 <Table Track (TrackId, Name, AlbumId, MediaTypeId, GenreId, Composer, Milliseconds, Bytes, UnitPrice)>,
 <Table Genre (GenreId, Name)>,
 <Table MediaType (MediaTypeId, Name)>]

It also provides auto-complete in Jupyter, IPython, and nearly any other interactive Python environment:

You can check if a table is in the database already:

'Artist' in dt

True

Column work in a similar way to tables, using the c property:

ac = artist.c
ac

ArtistId, Name

Auto-complete works for columns too:

Columns, tables, and view stringify in a format suitable for including in SQL statements. That means you can use auto-complete in f-strings.

qry = f"select * from {artist} where {ac.Name} like 'AC/%'"
print(qry)

select * from "Artist" where "Artist"."Name" like 'AC/%'

You can view the results of a select query using q:

db.q(qry)

[{'ArtistId': 1, 'Name': 'AC/DC'}]

Views can be accessed through the v property:

album = dt.Album

acca_sql = f"""select {album}.*
from {album} join {artist} using (ArtistId)
where {ac.Name} like 'AC/%'"""

db.create_view("AccaDaccaAlbums", acca_sql, replace=True)
acca_dacca = db.q(f"select * from {db.v.AccaDaccaAlbums}")
acca_dacca

[{'AlbumId': 1,
  'Title': 'For Those About To Rock We Salute You',
  'ArtistId': 1},
 {'AlbumId': 4, 'Title': 'Let There Be Rock', 'ArtistId': 1}]

Dataclass support

A dataclass type with the names, types, and defaults of the tables is created using dataclass():

album_dc = album.dataclass()

Let’s try it:

album_obj = album_dc(**acca_dacca[0])
album_obj

Album(AlbumId=1, Title='For Those About To Rock We Salute You', ArtistId=1)

You can get the definition of the dataclass using fastcore’s dataclass_src – everything is treated as nullable, in order to handle auto-generated database values:

src = dataclass_src(album_dc)
hl_md(src, 'python')

@dataclass
class Album:
    AlbumId: int | None = None
    Title: str | None = None
    ArtistId: int | None = None

Because dataclass() is dynamic, you won’t get auto-complete in editors like vscode – it’ll only work in dynamic environments like Jupyter and IPython. For editor support, you can export the full set of dataclasses to a module, which you can then import from:

create_mod(db, 'db_dc')

from db_dc import Track
Track()

Track(TrackId=None, Name=None, AlbumId=None, MediaTypeId=None, GenreId=None, Composer=None, Milliseconds=None, Bytes=None, UnitPrice=None)

Indexing into a table does a query on primary key:

dt.Track[1]

Track(TrackId=1, Name='For Those About To Rock (We Salute You)', AlbumId=1, MediaTypeId=1, GenreId=1, Composer='Angus Young, Malcolm Young, Brian Johnson', Milliseconds=343719, Bytes=11170334, UnitPrice=0.99)

There’s a shortcut to select from a table – just call it as a function. If you’ve previously called dataclass(), returned iterms will be constructed using that class by default. There’s lots of params you can check out, such as limit:

album(limit=2)

[Album(AlbumId=1, Title='For Those About To Rock We Salute You', ArtistId=1),
 Album(AlbumId=2, Title='Balls to the Wall', ArtistId=2)]

Pass a truthy value as with_pk and you’ll get tuples of primary keys and records:

album(with_pk=1, limit=2)

[(1,
  Album(AlbumId=1, Title='For Those About To Rock We Salute You', ArtistId=1)),
 (2, Album(AlbumId=2, Title='Balls to the Wall', ArtistId=2))]

Indexing also uses the dataclass by default:

album[5]

Album(AlbumId=5, Title='Big Ones', ArtistId=3)

If you set xtra fields, then indexing is also filtered by those. As a result, for instance in this case, nothing is returned since album 5 is not created by artist 1:

album.xtra(ArtistId=1)

try: album[5]
except NotFoundError: print("Not found")

Not found

The same filtering is done when using the table as a callable:

album()

[Album(AlbumId=1, Title='For Those About To Rock We Salute You', ArtistId=1),
 Album(AlbumId=4, Title='Let There Be Rock', ArtistId=1)]

Core design

The following methods accept **kwargs, passing them along to the first dict param:

• create
• transform
• transform_sql
• update
• insert
• upsert
• lookup

We can access a table that doesn’t actually exist yet:

cats = dt.cats
cats

<Table cats (does not exist yet)>

We can use keyword arguments to now create that table:

cats.create(id=int, name=str, weight=float, uid=int, pk='id')
hl_md(cats.schema, 'sql')

CREATE TABLE [cats] (
   [id] INTEGER PRIMARY KEY,
   [name] TEXT,
   [weight] FLOAT,
   [uid] INTEGER
)

It we set xtra then the additional fields are used for insert, update, and delete:

cats.xtra(uid=2)
cat = cats.insert(name='meow', weight=6)

The inserted row is returned, including the xtra ‘uid’ field.

cat

{'id': 1, 'name': 'meow', 'weight': 6.0, 'uid': 2}

Using ** in update here doesn’t actually achieve anything, since we can just pass a dict directly – it’s just to show that it works:

cat['name'] = "moo"
cat['uid'] = 1
cats.update(**cat)
cats()

[{'id': 1, 'name': 'moo', 'weight': 6.0, 'uid': 2}]

Attempts to update or insert with xtra fields are ignored.

An error is raised if there’s an attempt to update a record not matching xtra fields:

cats.xtra(uid=1)
try: cats.update(**cat)
except NotFoundError: print("Not found")

Not found

This all also works with dataclasses:

cats.xtra(uid=2)
cats.dataclass()
cat = cats[1]
cat

Cats(id=1, name='moo', weight=6.0, uid=2)

cats.drop()
cats

<Table cats (does not exist yet)>

Alternatively, you can create a table from a class. If it’s not already a dataclass, it will be converted into one. In either case, the dataclass will be created (or modified) so that None can be passed to any field (this is needed to support fields such as automatic row ids).

class Cat: id:int; name:str; weight:float; uid:int

cats = db.create(Cat)

hl_md(cats.schema, 'sql')

CREATE TABLE [cat] (
   [id] INTEGER PRIMARY KEY,
   [name] TEXT,
   [weight] FLOAT,
   [uid] INTEGER
)

cat = Cat(name='咪咪', weight=9)
cats.insert(cat)

Cat(id=1, name='咪咪', weight=9.0, uid=None)

cats.drop()

Manipulating data

We try to make the following methods as flexible as possible. Wherever possible, they support Python dictionaries, dataclasses, and classes.

.insert()

Creates a record.

Insert a dictionary.

cats.insert({'name': 'Rex', 'weight': 12.2})

Cat(id=1, name='Rex', weight=12.2, uid=UNSET)

Insert a dataclass.

CatDC = cats.dataclass()
cats.insert(CatDC(name='Tom', weight=10.2))

Cat(id=2, name='Tom', weight=10.2)

Insert a standard Python class

cat = cats.insert(Cat(name='Jerry', weight=5.2))

.update()

Updates a record.

Update a dictionary:

cats.update(dict(id=cat.id, name='Jerry', weight=6.2))

Cat(id=3, name='Jerry', weight=6.2)

Diagrams

If you have graphviz installed, you can create database diagrams:

diagram(db.tables)

Pass a subset of tables to just diagram those. You can also adjust the size and aspect ratio.

diagram(db.t['Artist','Album','Track','Genre','MediaType'], size=8, ratio=0.4)

fastlite's Issues

Table creation with foreign keys fails with error "unhashable list"

Passing foreign keys while creating table fails with error
Replication Steps

db = database('data/sample.db')
db['Table1'].create(id=int,name=str,pk='id')
db['Table2'].create(id=int,text=str,created_by=int,pk='id',foreign_keys=[('created_by','Table1','id')])

The below example works

db = database('data/sample.db')
db['Table1'].create(id=int,name=str,pk='int')
db['Table2'].create(id=int,text=str,created_by=int,pk='id')
db['Table2].add_foreign_key('created_by','Table1','id')

I confirmed it is sqlite_utils version issue. When I tried with Version: 3.37, passing foreign keys during table creation works fine.

from sqlite_utils import Database
db = Database('data/sample.db')
db['Table1'].create({"id":int,"name":str},pk='id')
db['Table2'].create({"id":int,"text":str,"created_by":int},pk='id'
                    ,foreign_keys=[('created_by','Table1','id')])

add `database(...)`

Add `get_last` and use it to set `last_pk` correctly and return updated row

Use flexiclass

Auto-get pks for `update`, and return updated record

Return updated value on insert/upsert

AttributeError: 'View' object has no attribute 'xtra'

When you create a view, you can't use xtra to do extra filtering like you can with tables. Would it make sense to add this?

`transform` keyword doesn't work outside core.ipynb

This script doesn't appear to work:

from fastlite import *

db = Database(':memory:')

print('original')
class User: name: str; age: int
print(User.__annotations__)
users = db.create(User, pk='name')
print(users.__repr__())

print('-'*20)
# Transform!
print('transformation to add pwd:str field')
class User: name: str; age: int; pwd: str
print(User.__annotations__)
users2 = db.create(User, pk='name', transform=True)
print(users2.__repr__())

This should return this string:

original
{'name': <class 'str'>, 'age': <class 'int'>}
<Table user (name, age)>
--------------------
transform
{'name': <class 'str'>, 'age': <class 'int'>, 'pwd': <class 'str'>}
<Table user (name, age, pwd)>

Instead, it returns this:

original
{'name': <class 'str'>, 'age': <class 'int'>}
<Table user (name, age)>
--------------------
transform
{'name': <class 'str'>, 'age': <class 'int'>, 'pwd': <class 'str'>}
<Table user (name, age)> # <----------------------------------------- missing pwd

Additionally, this doesn't work in index.ipynb of this repo. Add this to the bottom cell:

class Cat: id:int; name:str; weight:float; uid:int; breed:str
cats = db.create(Cat, transform=True)
cats

The result that should be returned is missing fields, specifically uid and breed:

<Table cat (id, name, weight)>

Vendor in sqlite-utils and remove dependency

Add `select` param

can you join dataclassed tables?

i have 2 tables, post and source, and i know what to do with them in normal SQL:

    postsview = f"""select {post}.*, url as source_url, {source}.title as source_title, {source}.description as source_description
    from {post} join {source} on {post}.primary_source_id = {source}.source_id
    """
    db.create_view("PostsView", postsview, replace=True)
    postsViewLimit = db.q(f"select * from {db.v.PostsView} limit 10")

but obviously the result doesnt have the nice dataclass __ft__ sugar. is there a way to do this with Dataclass stuff? can we document please?

`Database.create` for creating a table from a class

BUG: Compound primary keys fail on `__contains__` when done with tuple

Reference: AnswerDotAI/fasthtml#294 (comment)

Fix `None` checks in fastlite.kw

Filter table callable using `xtra`

Switch to sqlite-minutils

Add `ids_and_rows_where` and use it to work around sqlite-utils rowid bug