Enhanced query and custom comparators¶
-
class
coaster.sqlalchemy.comparators.
Query
(entities, session=None)[source]¶ Extends flask_sqlalchemy.BaseQuery to add additional helper methods.
-
isempty
()[source]¶ Returns the equivalent of
not bool(query.count())
but using an efficient SQL EXISTS function, so the database stops counting after the first result is found.
-
notempty
()[source]¶ Returns the equivalent of
bool(query.count())
but using an efficient SQL EXISTS function, so the database stops counting after the first result is found.
-
one_or_404
()[source]¶ Extends
one_or_none()
to raise a 404 if no result is found. This method offers a safety net overfirst_or_404()
as it helps identify poorly specified queries that could have returned more than one result.
-
-
class
coaster.sqlalchemy.comparators.
SplitIndexComparator
(expression, splitindex=None)[source]¶ Base class for comparators that support splitting a string and comparing with one of the split values.
-
in_
(other)[source]¶ Implement the
in
operator.In a column context, produces the clause
column IN <other>
.The given parameter
other
may be:A list of literal values, e.g.:
stmt.where(column.in_([1, 2, 3]))
In this calling form, the list of items is converted to a set of bound parameters the same length as the list given:
WHERE COL IN (?, ?, ?)
A list of tuples may be provided if the comparison is against a
tuple_()
containing multiple expressions:from sqlalchemy import tuple_ stmt.where(tuple_(col1, col2).in_([(1, 10), (2, 20), (3, 30)]))
An empty list, e.g.:
stmt.where(column.in_([]))
In this calling form, the expression renders an “empty set” expression. These expressions are tailored to individual backends and are generally trying to get an empty SELECT statement as a subquery. Such as on SQLite, the expression is:
WHERE col IN (SELECT 1 FROM (SELECT 1) WHERE 1!=1)
Changed in version 1.4: empty IN expressions now use an execution-time generated SELECT subquery in all cases.
A bound parameter, e.g.
bindparam()
, may be used if it includes the :paramref:`.bindparam.expanding` flag:stmt.where(column.in_(bindparam('value', expanding=True)))
In this calling form, the expression renders a special non-SQL placeholder expression that looks like:
WHERE COL IN ([EXPANDING_value])
This placeholder expression is intercepted at statement execution time to be converted into the variable number of bound parameter form illustrated earlier. If the statement were executed as:
connection.execute(stmt, {"value": [1, 2, 3]})
The database would be passed a bound parameter for each value:
WHERE COL IN (?, ?, ?)
New in version 1.2: added “expanding” bound parameters
If an empty list is passed, a special “empty list” expression, which is specific to the database in use, is rendered. On SQLite this would be:
WHERE COL IN (SELECT 1 FROM (SELECT 1) WHERE 1!=1)
New in version 1.3: “expanding” bound parameters now support empty lists
a
_expression.select()
construct, which is usually a correlated scalar select:stmt.where( column.in_( select(othertable.c.y). where(table.c.x == othertable.c.x) ) )
In this calling form,
ColumnOperators.in_()
renders as given:WHERE COL IN (SELECT othertable.y FROM othertable WHERE othertable.x = table.x)
Parameters: other – a list of literals, a _expression.select()
construct, or abindparam()
construct that includes the :paramref:`.bindparam.expanding` flag set to True.
-
-
class
coaster.sqlalchemy.comparators.
SqlSplitIdComparator
(expression, splitindex=None)[source]¶ Allows comparing an id value with a column, useful mostly because of the splitindex feature, which splits an incoming string along the
-
character and picks one of the splits for comparison.
-
class
coaster.sqlalchemy.comparators.
SqlUuidHexComparator
(expression, splitindex=None)[source]¶ Allows comparing UUID fields with hex representations of the UUID