Skip to main content

Select rows

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

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

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

You can use comparators like is_ne, is_gt, is_lt, is_gte, is_lte, 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

Here is the full list of comparators:

Function NameSQL EquivalentDescription
is_eq(value)... = valueIs equal to the value
is_neq(value)... <> valueIs not equal to the value
is_gt(value)... > valueIs greater than the value
is_between(value1, value2)... BETWEEN value1 AND value2Is between value1 and value2
is_gte(value)... >= valueIs greater than or equal to the value
is_in(value)... IN valueIs in the list of values
is_lt(value)... < valueIs less than the value
is_like(value)... LIKE valueMatches the pattern in value
is_lte(value)... <= valueIs less than or equal to the value
is_not_in(value)... NOT IN valueIs not in the list of values
is_not_like(value)... NOT LIKE valueDoes not match the pattern in value
is_null()... IS NULLIs null
is_not_null()... IS NOT NULLIs not null
is_ilike(value)... ILIKE valueCase-insensitive version of is_like
is_not_ilike(value)... NOT ILIKE valueCase-insensitive version of is_not_like

Select one

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' }

Select df

Use select_df to output a pandas DataFrame.

from abstra.tables import select_df

users = select_df("users")

Complex queries

For more complex queries, you can use run