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

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 Name	SQL Equivalent	Description
is_eq(value)	... = value	Is equal to the value
is_neq(value)	... <> value	Is not equal to the value
is_gt(value)	... > value	Is greater than the value
is_between(value1, value2)	... BETWEEN value1 AND value2	Is between value1 and value2
is_gte(value)	... >= value	Is greater than or equal to the value
is_in(value)	... IN value	Is in the list of values
is_lt(value)	... < value	Is less than the value
is_like(value)	... LIKE value	Matches the pattern in value
is_lte(value)	... <= value	Is less than or equal to the value
is_not_in(value)	... NOT IN value	Is not in the list of values
is_not_like(value)	... NOT LIKE value	Does not match the pattern in value
is_null()	... IS NULL	Is null
is_not_null()	... IS NOT NULL	Is not null
is_ilike(value)	... ILIKE value	Case-insensitive version of is_like
is_not_ilike(value)	... NOT ILIKE value	Case-insensitive version of is_not_like

Select one

You can use select_one to get the first result:

from abstra.tables import select

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

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

users = select_df("users")

Complex queries

For more complex queries, you can use run