Request Builders

pgrest.request_builder.RequestBuilder

select(self, *columns, *, count=None)

Run a SELECT query to fetch data.

Parameters:

Name	Type	Description	Default
columns	str	The names of columns to retrieve. Pass * for all columns.	()
count	Optional[CountMethod]	The method to be used to get the count of records that will be returned. One of "exact", "planned" or "estimated".	None

Returns:

Type	Description
SelectRequestBuilder	SelectRequestBuilder

insert(self, row, *, count=None, upsert=False)

Run an INSERT query to add data to a table.

Parameters:

Name	Type	Description	Default
row	dict	The row to be inserted, as a dictionary, with the column names as keys.	required
count	Optional[CountMethod]	The method to be used to get the count of records that will be returned. One of "exact", "planned" or "estimated".	None
upsert	bool	Whether to run an upsert.	False

Returns:

Type	Description
QueryRequestBuilder	QueryRequestBuilder

insert_many(self, rows, *, count=None, upsert=False)

Insert multiple rows to the same table at once.

Parameters:

Name	Type	Description	Default
rows	list[dict]	The list of rows to be inserted, where each row is a dictionary, with the column names as keys.	required
count	Optional[CountMethod]	The method to be used to get the count of records that will be returned. One of "exact", "planned" or "estimated".	None
upsert	bool	Whether to run an upsert.	False

Returns:

Type	Description
QueryRequestBuilder	QueryRequestBuilder

update(self, data, *, count=None)

Run an UPDATE query.

Parameters:

Name	Type	Description	Default
data	dict	The new row data, as a dictionary, with the column names as keys.	required
count	Optional[CountMethod]	The method to be used to get the count of records that will be returned. One of "exact", "planned" or "estimated".	None

Returns:

Type	Description
FilterRequestBuilder	FilterRequestBuilder

delete(self, *, count=None)

Run a DELETE query to remove rows from a table.

Parameters:

Name	Type	Description	Default
count	Optional[CountMethod]	The method to be used to get the count of records that will be returned. One of "exact", "planned" or "estimated".	None

Returns:

Type	Description
FilterRequestBuilder	FilterRequestBuilder

pgrest.request_builder.QueryRequestBuilder

execute(self)

Execute a query to get the response.

Returns:

Type	Description
TableResponse	A two-tuple, with the first element being the rows returned, and the second element being the count.

pgrest.request_builder.FilterRequestBuilder (QueryRequestBuilder)

not_ property readonly

Negate the next filter that is applied.

where(self, condition)

Apply filters to your query, equivalent to the WHERE clause in SQL.

Parameters:

Name	Type	Description	Default
condition	Condition	The filter conditions to apply	required

Returns:

Type	Description
FilterRequestBuilder	FilterRequestBuilder

Examples:

from pgrest import Column
# single filter
res = await client.from_("countries").select("*").where(Column("name") == "India")

# filters can also be chained into a long condition
res = await client.from_("countries").select("*").where(Column("name") == "India" & Column("population") > 100000)

Note

This form of querying turns out to be more clear in some cases, but in some cases, using the filter method might be better.

filter(self, column, operator, criteria)

Apply filters to your query, equivalent to the WHERE clause in SQL.

Parameters:

Name	Type	Description	Default
column	str	The column to filter by.	required
operator	str	The operator to filter with.	required
criteria	str	The value to filter with.	required

Returns:

Type	Description
FilterRequestBuilder	FilterRequestBuilder

Examples:

# single filter
res = await client.from_("countries").select("*").filter("name", "eq", "India").execute()

# filters can be chained
res = await client
    .from_("countries")
    .select("*")
    .eq("name", "India")
    .ilike("capital", "%el%")
    .execute()

Note

The filter methods all return an instance of FilterRequestBuilder, allowing for rich chaining of filters.

Tip

Refer the PostgREST docs for more info about operators.

eq(self, column, value)

Operator: "equals to"

neq(self, column, value)

Operator: "not equal to"

gt(self, column, value)

Operator: "greater than"

gte(self, column, value)

Operator: "greater than or equal to"

lt(self, column, value)

Operator: "less than"

lte(self, column, value)

Operator: "less than or equal to"

is_(self, column, value)

Operator: "is" (checking for exact equality, like null, true, false)

like(self, column, pattern)

Operator: "like" for matching based on patterns

ilike(self, column, pattern)

Operator: "ilike", case-insensitive LIKE.

fts(self, column, query)

Operator: Full-Text search, using to_tsquery

plfts(self, column, query)

Operator: Full-Text search using plainto_tsquery

phfts(self, column, query)

Operator: Full-Text search using phraseto_tsquery

wfts(self, column, query)

Operator: Full-Text search using websearch_to_tsquery

in_(self, column, values)

Operator: "in". Check if column is in values

cs(self, column, values)

Operator: contains

cd(self, column, values)

Operator: contained in

ov(self, column, values)

Operator: overlap (have points in common)

sl(self, column, range)

Operator: strictly left of

sr(self, column, range)

Operator: strictly right of

nxl(self, column, range)

Operator: does not extend to the left of

nxr(self, column, range)

Operator: does not extend to the right of

adj(self, column, range)

Operator: is adjacent to

pgrest.request_builder.SelectRequestBuilder (FilterRequestBuilder)

order(self, column, *, desc=False, nullsfirst=False)

Sort the query response in some order. Equivalent to SQL ORDER BY column ASC | DESC

Parameters:

Name	Type	Description	Default
column	str	Name of the column to sort by	required
desc	bool	Whether to sort in descending order	False
nullsfirst	bool	Nulls first	False

Returns:

Type	Description
SelectRequestBuilder	SelectRequestBuilder

limit(self, size, *, start=0)

Limit the number of rows returned by the query.

Parameters:

Name	Type	Description	Default
size	int	The number of rows to return	required
start	int	The index of rows to start from (OFFSET)	0

Returns:

Type	Description
SelectRequestBuilder	SelectRequestBuilder

range(self, start, end)

Retrieve only rows in a specific range.

Parameters:

Name	Type	Description	Default
start	int	The index of rows to start from	required
end	int	The index of the last row to retrieve	required

Returns:

Type	Description
SelectRequestBuilder	SelectRequestBuilder

single(self)

Return only a single row.

Warning

This method will raise an error if the query matched more than one valid row.