Skip to main content

Selecting rows

tip

For more complex queries, you can use run_sql

Basic Select​

To select data, you can call select function like this:

from abstra.tables import select

users = select("users")
users # [ { "id": 1, "name": "Michael" }, { "id": 2, "name": "Pam" }, ... ]

Which is equivalent to

SELECT * FROM users

Select with options​

You can pass extra options for filtering, sorting, etc..

from abstra.tables import select

users = select("users",
    where={ "id": 1 },
    order_by='created_at',
    order_desc=True,
    limit=5
    offset=2
)

Using Where​

where is a Dict[str, Any]. Passing many keys is equivalent to a AND clause:

Default behavior​

Any value will be compared with =:

from abstra.tables import select

users = select("users", where={"group_id": 123, "active": True})

which is equivalent to

SELECT * FROM users WHERE group_id = 123 AND active

None​

You can use None to check for NULL values:

from abstra.tables import select

users = select("users", where={"group_id": None})

which is equivalent to

SELECT * FROM users WHERE group_id IS NULL

Comparators​

For anything beyond equality as a WHERE condition, wrap the value in a comparator like is_neq, is_gt, is_lt, is_gte, is_lte, is_between, is_in, is_like, is_ilike, is_null, etc.

from abstra.tables import select, is_gt, is_between

users = select("users", where={"age": is_gt(18)}) # age > 18
users = select("users", where={"age": is_between(18, 30)}) # 18 <= age <= 30

See Using comparators for the full list and examples across select, update, and delete.

Select one (first matching row)​

You can use select_one to get the first result:

from abstra.tables import select_one

users = select_one("users",
    where={ "email": "foo@bar.com" },
) # { 'id': 123, 'name': 'foo', 'email': 'foo@bar.com' }

Select by ID​

You can use select_by_id as a simpler way to find a single row by id:

from abstra.tables import select_by_id

users = select_by_id("users", 123)
# { 'id': 123, 'name': 'foo', 'email': 'foo@bar.com' }

Returning a DataFrame​

Use select_df to output a pandas DataFrame.

from abstra.tables import select_df

users = select_df("users")