Python sqlalchemy.orm.Query() Examples

def with_session(self, session):
        """Return a :class:`.Query` that will use the given :class:`.Session`.

        While the :class:`.Query` object is normally instantiated using the
        :meth:`.Session.query` method, it is legal to build the :class:`.Query`
        directly without necessarily using a :class:`.Session`.  Such a
        :class:`.Query` object, or any :class:`.Query` already associated
        with a different :class:`.Session`, can produce a new :class:`.Query`
        object associated with a target session using this method::

            from sqlalchemy.orm import Query

            query = Query([MyClass]).filter(MyClass.id == 5)

            result = query.with_session(my_session).one()

        """

        self.session = session 

def mongoquery(cls, query_or_session: Union[Query, Session] = None) -> MongoQuery:
        """ Build a MongoQuery

        Note that when `None` is given, the resulting Query is not bound to any session!
        You'll have to bind it manually, after calling .end()

        :param query_or_session: Query to start with, or a session object to initiate the query with
        :type query_or_session: sqlalchemy.orm.Query | sqlalchemy.orm.Session | None
        :rtype: mongosql.MongoQuery
        """
        if query_or_session is None:
            query = Query([cls])
        elif isinstance(query_or_session, Session):
            query = query_or_session.query(cls)
        elif isinstance(query_or_session, Query):
            query = query_or_session
        else:
            raise ValueError('Argument must be Query or Session')

        return cls._get_mongoquery().from_query(query) 

def outerjoin(self, *props, **kwargs):
        """Create a left outer join against this ``Query`` object's criterion
        and apply generatively, returning the newly resulting ``Query``.

        Usage is the same as the ``join()`` method.

        """
        aliased, from_joinpoint, full = kwargs.pop('aliased', False), \
            kwargs.pop('from_joinpoint', False), \
            kwargs.pop('full', False)
        if kwargs:
            raise TypeError("unknown arguments: %s" %
                            ', '.join(sorted(kwargs)))
        return self._join(props,
                          outerjoin=True, full=full, create_aliases=aliased,
                          from_joinpoint=from_joinpoint) 

def __init__(self,
                 name: str,
                 applied_to_transfer_types: AppliedToTypes,
                 application_filter: ApplicationFilter,
                 time_period_days: int,
                 aggregation_filter: AggregationFilter = matching_transfer_type_filter
                 ):
        """
        :param name:
        :param applied_to_transfer_types:
        :param application_filter:
        :param time_period_days: How many days back to include in aggregation
        :param aggregation_filter: An SQLAlchemy Query Filter
        """

        super().__init__(name, applied_to_transfer_types, application_filter)

        self.time_period_days = time_period_days

        self.custom_aggregation_filter = aggregation_filter 

def query_constructor_filter_specifiable(
            self,
            transfer: CreditTransfer,
            base_query: Query,
            custom_filter: AggregationFilter) -> Query:
        """
        Constructs a filtered query for aggregation, where the last filter step can be provided by the user
        :param transfer:
        :param base_query:
        :param custom_filter:
        :return: An SQLAlchemy Query Object
        """

        filter_list = combine_filter_lists(
            [
                matching_sender_user_filter(transfer),
                not_rejected_filter(),
                after_time_period_filter(self.time_period_days),
                custom_filter(transfer)
            ]
        )

        return base_query.filter(*filter_list) 

def __init__(self, app=None, use_native_unicode=True, session_options=None, metadata=None):

        if session_options is None:
            session_options = {}

        session_options.setdefault('scopefunc', connection_stack.__ident_func__)
        self.use_native_unicode = use_native_unicode
        self.session = self.create_scoped_session(session_options)
        self.Model = self.make_declarative_base(metadata)
        self.Query = BaseQuery
        self._engine_lock = Lock()
        self.app = app
        _include_sqlalchemy(self)

        if app is not None:
            self.init_app(app) 

def assertSelectedColumns(self, qs, *expected):
        """ Test that the query has certain columns in the SELECT clause

        :param qs: Query | query string
        :param expected: list of expected column names
        :returns: query string
        """
        # Query?
        if isinstance(qs, Query):
            qs = q2sql(qs)

        try:
            self.assertEqual(
                self._qs_selected_columns(qs),
                set(expected)
            )
            return qs
        except:
            print(qs)
            raise 

def execute_query(instance: PluginInstance, sql_query: Union[str, Query],
                  rows_as_dict: bool = False) -> web.Response:
    try:
        res: ResultProxy = instance.inst_db.execute(sql_query)
    except exc.IntegrityError as e:
        return resp.sql_integrity_error(e, sql_query)
    except exc.OperationalError as e:
        return resp.sql_operational_error(e, sql_query)
    data = {
        "ok": True,
        "query": str(sql_query),
    }
    if res.returns_rows:
        row: RowProxy
        data["rows"] = [({key: check_type(value) for key, value in row.items()}
                         if rows_as_dict
                         else [check_type(value) for value in row])
                        for row in res]
        data["columns"] = res.keys()
    else:
        data["rowcount"] = res.rowcount
    if res.is_insert:
        data["inserted_primary_key"] = res.inserted_primary_key
    return web.json_response(data) 

def statement(self):
        """The full SELECT statement represented by this Query.

        The statement by default will not have disambiguating labels
        applied to the construct unless with_labels(True) is called
        first.

        """

        stmt = self._compile_context(labels=self._with_labels).\
            statement
        if self._params:
            stmt = stmt.params(self._params)

        # TODO: there's no tests covering effects of
        # the annotation not being there
        return stmt._annotate({'no_replacement_traverse': True}) 

def query_model(self, query_obj: Union[Mapping, None] = None, from_query: Union[Query, None] = None) -> MongoQuery:
        """ Make a MongoQuery using the provided Query Object

            Note that you have to provide the MongoQuery yourself.
            This is because it has to be properly configured with handler_settings.

            :param query_obj: The Query Object to use
            :param from_query: An optional Query to initialize MongoQuery with
            :raises exc.InvalidColumnError: Invalid column name specified in the Query Object by the user
            :raises exc.InvalidRelationError: Invalid relationship name specified in the Query Object by the user
            :raises exc.InvalidQueryError: There is an error in the Query Object that the user has made
            :raises exc.DisabledError: A feature is disabled; likely, due to a configuration issue. See handler_settings.
        """
        # Validate
        if not isinstance(query_obj, (Mapping, NoneType)):
            raise exc.InvalidQueryError('Query Object must be either an object, or null')

        # Query
        return self._query_model(query_obj or {}, from_query)  # ensure dict 

def group_by(self, *criterion):
        """apply one or more GROUP BY criterion to the query and return
        the newly resulting :class:`.Query`

        All existing GROUP BY settings can be suppressed by
        passing ``None`` - this will suppress any GROUP BY configured
        on mappers as well.

        .. versionadded:: 1.1 GROUP BY can be cancelled by passing None,
           in the same way as ORDER BY.

        """

        if len(criterion) == 1:
            if criterion[0] is None:
                self._group_by = False
                return

        criterion = list(chain(*[_orm_columns(c) for c in criterion]))
        criterion = self._adapt_col_list(criterion)

        if self._group_by is False:
            self._group_by = criterion
        else:
            self._group_by = self._group_by + criterion 

def with_labels(self):
        """Apply column labels to the return value of Query.statement.

        Indicates that this Query's `statement` accessor should return
        a SELECT statement that applies labels to all columns in the
        form <tablename>_<columnname>; this is commonly used to
        disambiguate columns from multiple tables which have the same
        name.

        When the `Query` actually issues SQL to load rows, it always
        uses column labeling.

        .. note:: The :meth:`.Query.with_labels` method *only* applies
           the output of :attr:`.Query.statement`, and *not* to any of
           the result-row invoking systems of :class:`.Query` itself, e.g.
           :meth:`.Query.first`, :meth:`.Query.all`, etc.   To execute
           a query using :meth:`.Query.with_labels`, invoke the
           :attr:`.Query.statement` using :meth:`.Session.execute`::

                result = session.execute(query.with_labels().statement)


        """
        self._with_labels = True 

def __init__(self, query: Query):
        # The original query. We store it just in case.
        self._original_query = query

        # The current query
        # It differs from the originla query in that it is modified with a window function counting the rows
        self._query = query

        # The iterator for query results ; `None` if the query has not yet been executed
        # If the query has been executed, there is always an iterator available, even if there were no results
        self._query_iterator = None

        # The total count ; `None` if the query has not yet been executed
        self._count = None

        # Whether the query is going to return single entities
        self._single_entity = (  # copied from sqlalchemy.orm.loading.instances
            not getattr(query, '_only_return_tuples', False)  # accessing protected properties
            and len(query._entities) == 1
            and query._entities[0].supports_single_entity
        )

        # The method that will fix result rows
        self._row_fixer = self._fix_result_tuple__single_entity if self._single_entity else self._fix_result_tuple__tuple 

def with_transformation(self, fn):
        """Return a new :class:`.Query` object transformed by
        the given function.

        E.g.::

            def filter_something(criterion):
                def transform(q):
                    return q.filter(criterion)
                return transform

            q = q.with_transformation(filter_something(x==5))

        This allows ad-hoc recipes to be created for :class:`.Query`
        objects.  See the example at :ref:`hybrid_transformers`.

        .. versionadded:: 0.7.4

        """
        return fn(self) 

def _get_one(self, query_obj: Mapping, *filter, **filter_by) -> object:
        """ Utility method that fetches a single entity.

            You will probably want to override it with custom error handling

            :param query_obj: Query Object
            :param filter: Additional filter() criteria
            :param filter_by: Additional filter_by() criteria
            :raises exc.InvalidQueryError: Query Object errors made by the user
            :raises sqlalchemy.orm.exc.NoResultFound: Nothing found
            :raises sqlalchemy.orm.exc.MultipleResultsFound: Multiple found
        """
        # Query
        query = self._mquery(query_obj, *filter, **filter_by)

        # Result
        return query.one() 

def with_hint(self, selectable, text, dialect_name='*'):
        """Add an indexing or other executional context
        hint for the given entity or selectable to
        this :class:`.Query`.

        Functionality is passed straight through to
        :meth:`~sqlalchemy.sql.expression.Select.with_hint`,
        with the addition that ``selectable`` can be a
        :class:`.Table`, :class:`.Alias`, or ORM entity / mapped class
        /etc.

        .. seealso::

            :meth:`.Query.with_statement_hint`

        """
        if selectable is not None:
            selectable = inspect(selectable).selectable

        self._with_hints += ((selectable, text, dialect_name),) 

def enable_assertions(self, value):
        """Control whether assertions are generated.

        When set to False, the returned Query will
        not assert its state before certain operations,
        including that LIMIT/OFFSET has not been applied
        when filter() is called, no criterion exists
        when get() is called, and no "from_statement()"
        exists when filter()/order_by()/group_by() etc.
        is called.  This more permissive mode is used by
        custom Query subclasses to specify criterion or
        other modifiers outside of the usual usage patterns.

        Care should be taken to ensure that the usage
        pattern is even possible.  A statement applied
        by from_statement() will override any criterion
        set by filter() or order_by(), for example.

        """
        self._enable_assertions = value 

def _method_list_result_handler(self, query: Query) -> Union[int, Iterable[object], Iterable[Tuple]]:
        """ Handle the results from method_list() """
        # Handle: Query Object has count
        if self._mongoquery.result_is_scalar():
            return self._method_list_result__count(query.scalar())

        # Handle: Query Object has group_by and yields tuples
        if self._mongoquery.result_is_tuples():
            # zip() column names together with the values,
            # and make it into a dict
            return self._method_list_result__groups(
                dict(zip(row.keys(), row))
                for row in query)  # return a generator

        # Regular result: entities
        return self._method_list_result__entities(iter(query))  # Return an iterable that yields entities, not a list 

def _method_get(self, *filter, **filter_by) -> object:
        """ (CRUD method) Fetch a single entity: as in READ, single entity

            Normally, used when the user has supplied a primary key:

                GET /users/1

            :param query_obj: Query Object
            :param filter: Additional filter() criteria
            :param filter_by: Additional filter_by() criteria
            :raises sqlalchemy.orm.exc.NoResultFound: Nothing found
            :raises sqlalchemy.orm.exc.MultipleResultsFound: Multiple found
            :raises exc.InvalidQueryError: Query Object errors made by the user
        """
        self._current_crud_method = CRUD_METHOD.GET
        instance = self._get_one(self._get_query_object(), *filter, **filter_by)
        return instance 

def enable_eagerloads(self, value):
        """Control whether or not eager joins and subqueries are
        rendered.

        When set to False, the returned Query will not render
        eager joins regardless of :func:`~sqlalchemy.orm.joinedload`,
        :func:`~sqlalchemy.orm.subqueryload` options
        or mapper-level ``lazy='joined'``/``lazy='subquery'``
        configurations.

        This is used primarily when nesting the Query's
        statement into a subquery or other
        selectable, or when using :meth:`.Query.yield_per`.

        """
        self._enable_eagerloads = value 

def _with_invoke_all_eagers(self, value):
        """Set the 'invoke all eagers' flag which causes joined- and
        subquery loaders to traverse into already-loaded related objects
        and collections.

        Default is that of :attr:`.Query._invoke_all_eagers`.

        """
        self._invoke_all_eagers = value 

def correlate(self, *args):
        """Return a :class:`.Query` construct which will correlate the given
        FROM clauses to that of an enclosing :class:`.Query` or
        :func:`~.expression.select`.

        The method here accepts mapped classes, :func:`.aliased` constructs,
        and :func:`.mapper` constructs as arguments, which are resolved into
        expression constructs, in addition to appropriate expression
        constructs.

        The correlation arguments are ultimately passed to
        :meth:`.Select.correlate` after coercion to expression constructs.

        The correlation arguments take effect in such cases
        as when :meth:`.Query.from_self` is used, or when
        a subquery as returned by :meth:`.Query.subquery` is
        embedded in another :func:`~.expression.select` construct.

        """

        for s in args:
            if s is None:
                self._correlate = self._correlate.union([None])
            else:
                self._correlate = self._correlate.union(
                    sql_util.surface_selectables(_interpret_as_from(s))
                ) 

def populate_existing(self):
        """Return a :class:`.Query` that will expire and refresh all instances
        as they are loaded, or reused from the current :class:`.Session`.

        :meth:`.populate_existing` does not improve behavior when
        the ORM is used normally - the :class:`.Session` object's usual
        behavior of maintaining a transaction and expiring all attributes
        after rollback or commit handles object state automatically.
        This method is not intended for general use.

        """
        self._populate_existing = True 

def label(self, name):
        """Return the full SELECT statement represented by this
        :class:`.Query`, converted
        to a scalar subquery with a label of the given name.

        Analogous to :meth:`sqlalchemy.sql.expression.SelectBase.label`.

        .. versionadded:: 0.6.5

        """

        return self.enable_eagerloads(False).statement.label(name) 

def as_scalar(self):
        """Return the full SELECT statement represented by this
        :class:`.Query`, converted to a scalar subquery.

        Analogous to :meth:`sqlalchemy.sql.expression.SelectBase.as_scalar`.

        .. versionadded:: 0.6.5

        """

        return self.enable_eagerloads(False).statement.as_scalar() 

def _no_yield_per(self, message):
        raise sa_exc.InvalidRequestError(
            "The yield_per Query option is currently not "
            "compatible with %s eager loading.  Please "
            "specify lazyload('*') or query.enable_eagerloads(False) in "
            "order to "
            "proceed with query.yield_per()." % message) 

def with_lockmode(self, mode):
        """Return a new :class:`.Query` object with the specified "locking mode",
        which essentially refers to the ``FOR UPDATE`` clause.

        .. deprecated:: 0.9.0 superseded by :meth:`.Query.with_for_update`.

        :param mode: a string representing the desired locking mode.
         Valid values are:

         * ``None`` - translates to no lockmode

         * ``'update'`` - translates to ``FOR UPDATE``
           (standard SQL, supported by most dialects)

         * ``'update_nowait'`` - translates to ``FOR UPDATE NOWAIT``
           (supported by Oracle, PostgreSQL 8.1 upwards)

         * ``'read'`` - translates to ``LOCK IN SHARE MODE`` (for MySQL),
           and ``FOR SHARE`` (for PostgreSQL)

        .. seealso::

            :meth:`.Query.with_for_update` - improved API for
            specifying the ``FOR UPDATE`` clause.

        """
        self._for_update_arg = LockmodeArg.parse_legacy_query(mode) 

def autoflush(self, setting):
        """Return a Query with a specific 'autoflush' setting.

        Note that a Session with autoflush=False will
        not autoflush, even if this flag is set to True at the
        Query level.  Therefore this flag is usually used only
        to disable autoflush for a specific Query.

        """
        self._autoflush = setting 

def subquery(self, name=None, with_labels=False, reduce_columns=False):
        """return the full SELECT statement represented by
        this :class:`.Query`, embedded within an :class:`.Alias`.

        Eager JOIN generation within the query is disabled.

        :param name: string name to be assigned as the alias;
            this is passed through to :meth:`.FromClause.alias`.
            If ``None``, a name will be deterministically generated
            at compile time.

        :param with_labels: if True, :meth:`.with_labels` will be called
         on the :class:`.Query` first to apply table-qualified labels
         to all columns.

        :param reduce_columns: if True, :meth:`.Select.reduce_columns` will
         be called on the resulting :func:`.select` construct,
         to remove same-named columns where one also refers to the other
         via foreign key or WHERE clause equivalence.

         .. versionchanged:: 0.8 the ``with_labels`` and ``reduce_columns``
            keyword arguments were added.

        """
        q = self.enable_eagerloads(False)
        if with_labels:
            q = q.with_labels()
        q = q.statement
        if reduce_columns:
            q = q.reduce_columns()
        return q.alias(name=name) 

def whereclause(self):
        """A readonly attribute which returns the current WHERE criterion for
        this Query.

        This returned value is a SQL expression construct, or ``None`` if no
        criterion has been established.

        """
        return self._criterion