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))