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 over first_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 a bindparam() 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

class coaster.sqlalchemy.comparators.SqlUuidB64Comparator(expression, splitindex=None)[source]

Allows comparing UUID fields with URL-safe Base64 (BUID) representations of the UUID

class coaster.sqlalchemy.comparators.SqlUuidB58Comparator(expression, splitindex=None)[source]

Allows comparing UUID fields with Base58 representations of the UUID