Retrieving Data¶
For all the examples in this sections we will use the following Tables for a blog
Selecting multiple records¶
You can use Table.select
to retrieve rows from the tables. Table.select
always returns a python list of Table
instances.
>>> User.select().all() # or User.all()
[{"id": 1, "lastname": "SQL", "role": "Member", "firstname": "Fy"}, {"id": 2, "lastname": "Bon", "role": "Member", "firstname": "Jean"}]
Selecting a single record¶
>>> User.get(User.id == 1)
{"id": 1, "lastname": "SQL", "role": "Member", "firstname": "Fy"}
Filtering records¶
On Table.select
and Table.count
you can use limit()
and/or where()/filter()
with fysql operators.
>>> print User.filter(User.id == 2).all()
[{"id": 2, "lastname": "Bon", "role": "Member", "firstname": "Jean"}]
>>> print User.filter((User.id == 2) | (User.id==1)).all()
[{"id": 1, "lastname": "SQL", "role": "Member", "firstname": "Fy"}, {"id": 2, "lastname": "Bon", "role": "Member", "firstname": "Jean"}]
>>> print Users.filter(User.id << [1,2]).all()
[{"id": 1, "lastname": "SQL", "role": "Member", "firstname": "Fy"}, {"id": 2, "lastname": "Bon", "role": "Member", "firstname": "Jean"}]
>>> print Users.filter(User.id << [1,2]).limit(1)
[{"id": 1, "lastname": "SQL", "role": "Member", "firstname": "Fy"}]
>>> print Post.filter(User.firstname.contains('Je')).all()
[{"id": 3, "id_user": 2, "user": {"id": 2, "lastname": "Bon", "role": "Member", "firstname": "Jean"}, "title": "Mon giga post 3"}]
Custom Selection¶
You can do custom select on your tables, it will also return a python list of Table
instances with only the selected values.
>>> print User.select(User.id, User.lastname).where(User.lastname=='Bon').all()
[{"id": 2, "lastname": "Bon", "role": null, "firstname": null}]
>>> print User.select(User.id, User.lastname).where(User.lastname << ['Bon', 'SQL']).limit(1)
[{"id": 1, "lastname": "SQL", "role": null, "firstname": null}]
Note
You can’t use save()
on incomplete instances (readonly if use of select(*args)
)