CODEWITHSUNDEEP
pythonsqlsqlalchemyflask-sqlalchemy
BC00
BC00

Reputation: 1639

SQLAlchemy get max value

Im trying to get the max value in a table for an attribute, the attribute is risk:

I am running: 

func.max(HostsModel.risk)

I get back:

<sqlalchemy.sql.functions.max at 0x10f0721d0; max>

How do I actually get the value from this?

I see these as the available functions:

 dir(func.max(HostsModel.risk))
['__add__', '__and__', '__bool__', '__class__', '__contains__', '__delattr__', '__dict__', '__dir__', '__div__', '__doc__', '__eq__', '__format__', '__ge__', '__getattr__', '__getattribute__', '__getitem__', '__getstate__', '__gt__', '__hash__', '__init__', '__init_subclass__', '__invert__', '__le__', '__lshift__', '__lt__', '__mod__', '__module__', '__mul__', '__ne__', '__neg__', '__new__', '__nonzero__', '__or__', '__radd__', '__rdiv__', '__reduce__', '__reduce_ex__', '__repr__', '__rmod__', '__rmul__', '__rshift__', '__rsub__', '__rtruediv__', '__setattr__', '__sizeof__', '__slots__', '__str__', '__sub__', '__subclasshook__', '__truediv__', '__visit_name__', '__weakref__', '_allow_label_resolve', '_alt_names', '_annotate', '_annotations', '_bind', '_bind_param', '_clone', '_cloned_set', '_cols_populated', '_compare_name_for_result', '_compiler', '_compiler_dispatch', '_constructor', '_copy_internals', '_deannotate', '_execute_on_connection', '_execution_options', '_from_objects', '_generate', '_hide_froms', '_init_collections', '_is_clone_of', '_is_from_container', '_is_join', '_is_lexical_equivalent', '_is_select', '_key_label', '_label', '_make_proxy', '_memoized_property', '_negate', '_order_by_label_element', '_params', '_populate_column_collection', '_refresh_for_new_column', '_render_label_in_columns_clause', '_reset_exported', '_resolve_label', '_select_iterable', '_textual', '_translate_schema', '_with_annotations', 'alias', 'all_', 'anon_label', 'any_', 'asc', 'base_columns', 'between', 'bind', 'bool_op', 'c', 'cast', 'clause_expr', 'clauses', 'coerce_arguments', 'collate', 'columns', 'comparator', 'compare', 'compile', 'concat', 'contains', 'correspond_on_equivalents', 'corresponding_column', 'count', 'desc', 'description', 'distinct', 'endswith', 'execute', 'execution_options', 'expression', 'filter', 'foreign_keys', 'get_children', 'identifier', 'ilike', 'in_', 'is_', 'is_clause_element', 'is_derived_from', 'is_distinct_from', 'is_selectable', 'isnot', 'isnot_distinct_from', 'join', 'key', 'label', 'lateral', 'like', 'match', 'name', 'named_with_column', 'notilike', 'notin_', 'notlike', 'nullsfirst', 'nullslast', 'op', 'operate', 'outerjoin', 'over', 'packagenames', 'params', 'primary_key', 'proxy_set', 'replace_selectable', 'reverse_operate', 'scalar', 'schema', 'select', 'selectable', 'self_group', 'shares_lineage', 'startswith', 'supports_execution', 'tablesample', 'timetuple', 'type', 'unique_params', 'within_group', 'within_group_type']

Upvotes: 4

Views: 15197

Answers (1)

bsplosion
bsplosion

Reputation: 2866

Per the comments, you can see that this relates to some other answers, though none cover the whole solution. Rewritten, if you'd like to access the max(HostsModel.risk) value directly:

session.query(func.max(HostsModel.risk)).scalar()

You need to query using the max function, then get its scalar result. Since this query can only have one result (assuming no other groupings are specified), you'll end up with a value equal to the maximum HostsModel.risk value.

This also extends to other aggregates, e.g. avg, min, etc. Leveraging scalar in this way also works when filtering to a single column and record, even without an aggregate defined.

Upvotes: 11

Related Questions