pip install fastsql
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
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.
<a href="https://github.com/answerdotai/fastsql/blob/main/fastsql/core.py#LNone" target="_blank" style="float:right; font-size:smaller">source
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')
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