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 (?, ?, ?)
    
  • An empty list, e.g.:

    stmt.where(column.in_([]))
    

    In this calling form, the expression renders a “false” expression, e.g.:

    WHERE 1 != 1
    

    This “false” expression has historically had different behaviors in older SQLAlchemy versions, see :paramref:`.create_engine.empty_in_strategy` for behavioral options.

    Changed in version 1.2: simplified the behavior of “empty in” expressions

  • 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

    The “expanding” feature in version 1.2 of SQLAlchemy does not support passing an empty list as a parameter value; however, version 1.3 does support this.

  • a 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 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.SqlHexUuidComparator(expression, splitindex=None)[source]

Allows comparing UUID fields with hex representations of the UUID

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

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

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

Allows comparing UUID fields with ShortUUID representations of the UUID