fastduck 0.0.10

A bit of extra usability for duckdb... inspired by fastlite and sqlite_utils

fastduck

fastduck provides some development experience improvements for the standard duckdb python API.

Install

pip install fastduck

How to use

import fastduck as fuck

from fastduck import database

db = database('../data/chinook.duckdb')
db

IOException: IO Error: Could not set lock on file "/Users/fredguth/Code/jeremy/fastduck/nbs/../data/chinook.duckdb": Conflicting lock is held in /opt/anaconda3/envs/fastduck/bin/python3.12 (PID 51108) by user fredguth. See also https://duckdb.org/docs/connect/concurrency

dt = db.t
dt

You can use this to grab a single table…

artist = dt.Artist
artist

customer = dt['Customer']
customer

… or multiple tables at once:

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

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

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

ac = artist.c
ac, artist.columns

Auto-complete works for columns too:

The tables and views of a database got some interesting new attributes….

artist.meta

artist.model

artist.cls, type(artist.cls)

duckdb replacement scans keep working and are wonderful for usage in SQL statements:

db.sql("select * from artist where artist.Name like 'AC/%'")

You can view the results of a query as records

db.sql("select * from artist where artist.Name like 'AC/%'").to_recs()

or as a list of lists

db.sql("select * from artist where artist.Name like 'AC/%'").to_list()

And you there is also an alias for sql with to_recs simply called q

db.q("select * from artist where artist.Name like 'AC/%'")

Dataclass support

As we briefly saw, a dataclass type with the names, types and defaults of the table is added to the Relation:

abm = db.t.Album
art = db.t.Artist
acca_sql = f"""
select abm.* 
from abm join art using (ArtistID)
where art.Name like 'AC/%'
"""
acca_dacca = db.q(acca_sql)
acca_dacca

let_b_rock_obj = abm.cls(**acca_dacca[-1])
let_b_rock_obj

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:

from fastcore.xtras import hl_md, dataclass_src

src = dataclass_src(db.t.Album.cls)
hl_md(src, 'python')