fastsql

A MiniDataAPI spec implementation for SQLAlchemy V2

Install

pip install fastsql

Creating a table

from fastsql import *
import sqlalchemy as sa

First we instantiate our database using FastSQL’s Database class:

db = Database("sqlite:///:memory:")

The main table object in FastSQL is DBTable, which you can create by calling DBTable(table) with a SQLAlchemy Table object, or by calling Database.create(cls) with a class (optionally a dataclass). We’ll demonstrate the latter with these classes:

class User: name:str; pwd:str
class Todo: title:str; name:str; id:int=None; done:bool=False; details:str=''

Equipped with our schemas, let’s turn them into database tables.

users = db.create(User, pk='name')
todos = db.create(Todo, pk='id')

Let’s confirm the table design:

print(db.schema())

Table: todo
  - title: VARCHAR
  - name: VARCHAR
  * id: INTEGER
  - done: BOOLEAN
  - details: VARCHAR
Table: user
  * name: VARCHAR
  - pwd: VARCHAR

We can check if a table exists:

users.exists()

True

Using FastSQL

Let’s create some dataclass objects representing users and todos.

u0 = User('jph','foo')
u1 = User('rlt','bar')
t0 = Todo('do it', 'jph')
t1 = Todo('build it', 'jph')
t2 = Todo('write book', 'rlt')

Let’s convert these dataclass objects into database records. To do that we insert them into their tables using the aply named insert method:

users.insert(u0)
users.insert(u1)
todos.insert(t0)
todos.insert(t1)
todos.insert(t2)

Todo(title='write book', name='rlt', id=3, done=False, details='')

To query a single table, call the table like a function (which is implemented in Python using the special __call__ method.

---

source

DBTable.__call__

 DBTable.__call__ (where:str|None=None,
                   where_args:Union[Iterable,dict,NoneType]=None,
                   order_by:str|None=None, limit:int|None=None,
                   offset:int|None=None, select:str='*', **kw)

Result of select query on the table

	Type	Default	Details
where	str | None	None	SQL where fragment to use, for example id > ?
where_args	Union	None	Parameters to use with where; iterable for id>?, or dict for id>:id
order_by	str | None	None	Column or fragment of SQL to order by
limit	int | None	None	Number of rows to limit to
offset	int | None	None	SQL offset
select	str	*	Comma-separated list of columns to select
kw
Returns	list		List of returned objects

users()

[User(name='jph', pwd='foo'), User(name='rlt', pwd='bar')]

Use where statement to filter records, in this case only jph’s todos.

todos(where="name = :name", name="jph")

[Todo(title='do it', name='jph', id=1, done=False, details=''),
 Todo(title='build it', name='jph', id=2, done=False, details='')]

Look only for those records with the word it in it.

todos(where="title LIKE :title", title="%% it%%")

[Todo(title='do it', name='jph', id=1, done=False, details=''),
 Todo(title='build it', name='jph', id=2, done=False, details='')]

You can also fetch a record just by the primary key by using [] with the table:

user = users['rlt']
user

User(name='rlt', pwd='bar')

Change a value in a record by updating an object, and passing the updated object to update():

user.pwd = 'baz'
users.update(user)
users['rlt']

User(name='rlt', pwd='baz')

Using SQLAlchemy

ut,uc = users.t
tt,tc = todos.t

query = sa.select(uc.name, uc.pwd, tc.title).select_from(
    tt.join(ut, tc.name == uc.name))
list(db.execute(query))

[('jph', 'foo', 'do it'),
 ('jph', 'foo', 'build it'),
 ('rlt', 'baz', 'write book')]

dbm = db.meta