Python sqlalchemy.sql.and_() Examples

def get_profit_for_pair(exchange, pair):
    """Iterates through all trades for given exchange pair over the course of trading. Starts by subtracting the long positions (the buys) and adding the short positions (the sells) to arrive at the difference (profit"""
    """The buys are always the even rows and the sells are the odd rows (buy always before sell starting from zero)"""
    profit = 0
    counter = 0
    s = select([database.TradingPositions]).where(and_(database.TradingPositions.c.Exchange == exchange, database.TradingPositions.c.Pair == pair))
    result = conn.execute(s)

    for row in result:
        if counter % 2 == 0:
            profit = profit - row[5]
            counter += 1
        else:
            profit = profit + row[5]
            counter += 1
        return profit 

def _create_join_conds(left_sel, right_sel, on):
    left_cols  = left_sel.columns  #lift_inner_cols(left_sel)
    right_cols = right_sel.columns #lift_inner_cols(right_sel)

    if callable(on):
        # callable, like with sql_on arg
        conds = [on(left_cols, right_cols)]
    else:
        # dict-like of form {left: right}
        conds = []
        for l, r in on.items():
            col_expr = left_cols[l] == right_cols[r]
            conds.append(col_expr)
            
    return sql.and_(*conds)
    

# Head ------------------------------------------------------------------------ 

def _expr(self,model,model_param,model_expr):
        if not isinstance(model_expr,list):
            raise UliwebError("only accept array in @expr, but get '%s'"%(model_expr))
        num = len(model_expr)
        if (num<2 or num>3):
            raise UliwebError("only accept 2 or 3 items in @expr, but get '%s'"%(model_expr))
        op = model_expr[-2]
        if op=='&':
            if num!=3:
                raise UliwebError("'&'(and) expression need 3 items, but get '%s'"%(model_expr))
            c1 = self._get_filter_condition(model,model_param,model_expr[0],expr=True)
            c2 = self._get_filter_condition(model,model_param,model_expr[2],expr=True)
            return and_(c1,c2)
        elif op=='|':
            if num!=3:
                raise UliwebError("'|'(or) expression need 3 items, but get '%s'"%(model_expr))
            c1 = self._get_filter_condition(model,model_param,model_expr[0],expr=True)
            c2 = self._get_filter_condition(model,model_param,model_expr[2],expr=True)
            return or_(c1,c2)
        elif op=='!':
            if num!=2:
                raise UliwebError("'!'(not) expression need 2 items, but get '%s'"%(model_expr))
            return not_(self._get_filter_condition(model,model_param,model_expr[1],expr=True))
        else:
            raise UliwebError("unknown operator: '%s'"%(op)) 

def _term_eval(term, column_variable, column_key):

    if term["type"].lower() == "conjunction":
        return and_(*((_term_eval(t, column_variable, column_key) for t in term["terms"])))
    elif term["type"].lower() == "disjunction":
        return or_(*((_term_eval(t, column_variable, column_key) for t in term["terms"])))
    elif term["type"].lower() == "literal":
        if "key" in term and term["key"]:
            key_operator = term.get("key_operator", "IN")
            if key_operator is None or key_operator == "IN":
                key_condition = column_key.in_(term["key"])
            elif key_operator=="ILIKE":
                key_condition = or_(*(column_key.ilike(pattern) for pattern in term["key"]))
            return and_(column_variable==term["variable"], key_condition)
        else:
            return column_variable==term["variable"] 

def find_all(query, model, kwargs):
    """
    Returns a query object that ensures that all kwargs
    are present.

    :param query:
    :param model:
    :param kwargs:
    :return:
    """
    conditions = []
    kwargs = filter_none(kwargs)
    for attr, value in kwargs.items():
        if not isinstance(value, list):
            value = value.split(",")

        conditions.append(get_model_column(model, attr).in_(value))

    return query.filter(and_(*conditions)) 

def validatePCode(self, sender, event):
        productCd = self.proVal.get_text()
        productGroup = self.productGroup.get_text()
        if self.product_code != productCd:
            product = self.session.query(Products).filter(
                and_(Products.id == productCd, Products.accGroup == productGroup)).first()
            if not product:
                self.proVal.modify_base(Gtk.StateType.NORMAL, self.redClr)
                msg = _("Product code is invalid")
                self.proVal.set_tooltip_text(msg)
                self.addStBar.push(1, msg)
                self.proNameLbl.set_text("")
                self.product = None
            else:
                self.proVal.modify_base(Gtk.StateType.NORMAL, self.whiteClr)
                self.proVal.set_tooltip_text("")
                # self.proSelected(code=product.code)
                self.proNameLbl.set_text(product.name)
                self.productGroup.set_text(product.accGroup)
                self.product = product
            self.product_code = productCd 

def test_select_whereclause(self):
        t1 = table("t1", column("q"), column("p"))

        x = 10
        y = 5

        def go():
            return select([t1]).where(lambda: and_(t1.c.q == x, t1.c.p == y))

        self.assert_compile(
            go(), "SELECT t1.q, t1.p FROM t1 WHERE t1.q = :x_1 AND t1.p = :y_1"
        )

        self.assert_compile(
            go(), "SELECT t1.q, t1.p FROM t1 WHERE t1.q = :x_1 AND t1.p = :y_1"
        ) 

def filter_timestamp_column(self, ts_col) -> ColumnElement:
        """
        Filter timestamp column using this hour interval.

        Parameters
        ----------
        ts_col : sqlalchemy column
            The timestamp column to filter.

        Returns
        -------
        sqlalchemy.sql.elements.ColumnElement
            Sqlalchemy expression representing the filtered timestamp column.
            This can be used in WHERE clauses of other sql queries.
        """

        return and_(
            self.start_hour.filter_timestamp_column(ts_col, cmp_op=greater_or_equal),
            self.stop_hour.filter_timestamp_column(ts_col, cmp_op=less_than),
            self.period.filter_timestamp_column_by_day_of_week(ts_col),
        ) 

def test_assignment_two(self, user_address_fixture):
        users, addresses = user_address_fixture

        x = 5
        z = 10

        def my_lambda():

            y = x + z

            expr1 = users.c.name > x
            expr2 = users.c.name < y
            return and_(expr1, expr2)

        expr = coercions.expect(roles.WhereHavingRole, my_lambda)
        self.assert_compile(
            expr,
            "users.name > :x_1 AND users.name < :x_1 + :z_1",
            params=expr._param_dict(),
            checkparams={"x_1": 5, "z_1": 10},
        )

        x = 15
        z = 18

        expr = coercions.expect(roles.WhereHavingRole, my_lambda)
        self.assert_compile(
            expr,
            "users.name > :x_1 AND users.name < :x_1 + :z_1",
            params=expr._param_dict(),
            checkparams={"x_1": 15, "z_1": 18},
        ) 

def get_pk_constraint(self, connection, table_name, schema=None, **kw):
        """
        Override
        TODO: Check if we need PRIMARY Indices or PRIMARY KEY Indices
        TODO: Check for border cases (No PK Indices)
        """

        if schema is None:
            schema = self.default_schema_name

        stmt = select([column('ColumnName'), column('IndexName')],
                      from_obj=[text('dbc.Indices')]).where(
                          and_(text('DatabaseName = :schema'),
                              text('TableName=:table'),
                              text('IndexType=:indextype'))
                      ).order_by(asc(column('IndexNumber')))

        # K for Primary Key
        res = connection.execute(stmt, schema=schema, table=table_name, indextype='K').fetchall()

        index_columns = list()
        index_name = None

        for index_column in res:
            index_columns.append(self.normalize_name(index_column['ColumnName']))
            index_name = self.normalize_name(index_column['IndexName']) # There should be just one IndexName

        return {
            "constrained_columns": index_columns,
            "name": index_name
        } 

def test_assignment_one(self, user_address_fixture):
        users, addresses = user_address_fixture

        x = 5

        def my_lambda():

            y = 10
            z = y + 18

            expr1 = users.c.name > x
            expr2 = users.c.name < z
            return and_(expr1, expr2)

        expr = coercions.expect(roles.WhereHavingRole, my_lambda)
        self.assert_compile(
            expr,
            "users.name > :x_1 AND users.name < :name_1",
            params=expr._param_dict(),
            checkparams={"name_1": 28, "x_1": 5},
        )

        expr = coercions.expect(roles.WhereHavingRole, my_lambda)
        self.assert_compile(
            expr,
            "users.name > :x_1 AND users.name < :name_1",
            params=expr._param_dict(),
            checkparams={"name_1": 28, "x_1": 5},
        ) 

def manufacture_criteria(mapped, values):
    """Given a mapper/class and a namespace of values, produce a WHERE clause.

    The class should be a mapped class and the entries in the dictionary
    correspond to mapped attribute names on the class.

    A value may also be a tuple in which case that particular attribute
    will be compared to a tuple using IN.   The scalar value or
    tuple can also contain None which translates to an IS NULL, that is
    properly joined with OR against an IN expression if appropriate.

    :param cls: a mapped class, or actual :class:`.Mapper` object.

    :param values: dictionary of values.

    """

    mapper = inspect(mapped)

    # organize keys using mapped attribute ordering, which is deterministic
    value_keys = set(values)
    keys = [k for k in mapper.column_attrs.keys() if k in value_keys]
    return sql.and_(*[
        _sql_crit(mapper.column_attrs[key].expression, values[key])
        for key in keys
    ]) 

def update_repository_table(self, startver, endver):
        """Update version_table with new information"""
        update = self.table.update(and_(self.table.c.version == int(startver),
             self.table.c.repository_id == str(self.repository.id)))
        self.engine.execute(update, version=int(endver)) 

def _get_nonansi_join_whereclause(self, froms):
        clauses = []

        def visit_join(join):
            if join.isouter:
                def visit_binary(binary):
                    if binary.operator == sql_operators.eq:
                        if join.right.is_derived_from(binary.left.table):
                            binary.left = _OuterJoinColumn(binary.left)
                        elif join.right.is_derived_from(binary.right.table):
                            binary.right = _OuterJoinColumn(binary.right)
                clauses.append(visitors.cloned_traverse(
                    join.onclause, {}, {'binary': visit_binary}))
            else:
                clauses.append(join.onclause)

            for j in join.left, join.right:
                if isinstance(j, expression.Join):
                    visit_join(j)
                elif isinstance(j, expression.FromGrouping):
                    visit_join(j.element)

        for f in froms:
            if isinstance(f, expression.Join):
                visit_join(f)

        if not clauses:
            return None
        else:
            return sql.and_(*clauses) 

def _get_nonansi_join_whereclause(self, froms):
        clauses = []

        def visit_join(join):
            if join.isouter:
                def visit_binary(binary):
                    if binary.operator == sql_operators.eq:
                        if join.right.is_derived_from(binary.left.table):
                            binary.left = _OuterJoinColumn(binary.left)
                        elif join.right.is_derived_from(binary.right.table):
                            binary.right = _OuterJoinColumn(binary.right)
                clauses.append(visitors.cloned_traverse(
                    join.onclause, {}, {'binary': visit_binary}))
            else:
                clauses.append(join.onclause)

            for j in join.left, join.right:
                if isinstance(j, expression.Join):
                    visit_join(j)
                elif isinstance(j, expression.FromGrouping):
                    visit_join(j.element)

        for f in froms:
            if isinstance(f, expression.Join):
                visit_join(f)

        if not clauses:
            return None
        else:
            return sql.and_(*clauses) 

def _get_nonansi_join_whereclause(self, froms):
        clauses = []

        def visit_join(join):
            if join.isouter:
                # https://docs.oracle.com/database/121/SQLRF/queries006.htm#SQLRF52354
                # "apply the outer join operator (+) to all columns of B in
                # the join condition in the WHERE clause" - that is,
                # unconditionally regardless of operator or the other side
                def visit_binary(binary):
                    if isinstance(binary.left, expression.ColumnClause) \
                            and join.right.is_derived_from(binary.left.table):
                        binary.left = _OuterJoinColumn(binary.left)
                    elif isinstance(binary.right, expression.ColumnClause) \
                            and join.right.is_derived_from(binary.right.table):
                        binary.right = _OuterJoinColumn(binary.right)
                clauses.append(visitors.cloned_traverse(
                    join.onclause, {}, {'binary': visit_binary}))
            else:
                clauses.append(join.onclause)

            for j in join.left, join.right:
                if isinstance(j, expression.Join):
                    visit_join(j)
                elif isinstance(j, expression.FromGrouping):
                    visit_join(j.element)

        for f in froms:
            if isinstance(f, expression.Join):
                visit_join(f)

        if not clauses:
            return None
        else:
            return sql.and_(*clauses) 

def get_indexes(self, connection, table_name, schema=None, **kw):
        """
        Overrides base class method
        """

        if schema is None:
            schema = self.default_schema_name

        stmt = select(["*"], from_obj=[text('dbc.Indices')]) \
            .where(and_(text('DatabaseName = :schema'),
                        text('TableName=:table'))) \
            .order_by(asc(column('IndexName')))

        res = connection.execute(stmt, schema=schema, table=table_name).fetchall()

        def grouper(fk_row):
            return {
                'name': fk_row.IndexName or fk_row.IndexNumber, # If IndexName is None TODO: Check what to do
                'unique': True if fk_row.UniqueFlag == 'Y' else False
            }

        # TODO: Check if there's a better way
        indices = list()
        for index_info, index_cols in groupby(res, grouper):
            index_dict = {
                'name': index_info['name'],
                'column_names': list(),
                'unique': index_info['unique']
            }

            for index_col in index_cols:
                index_dict['column_names'].append(self.normalize_name(index_col['ColumnName']))

            indices.append(index_dict)

        return indices 

def get_table_names(self, connection, schema=None, **kw):

        if schema is None:
            schema = self.default_schema_name

        stmt = select([column('tablename')],
                      from_obj=[text('dbc.TablesVX')]).where(
                      and_(text('DatabaseName = :schema'),
                          or_(text('tablekind=\'T\''),
                              text('tablekind=\'O\''))))
        res = connection.execute(stmt, schema=schema).fetchall()
        return [self.normalize_name(name['tablename']) for name in res] 

def test_assignment_three(self, user_address_fixture):
        users, addresses = user_address_fixture

        x = 5
        z = 10

        def my_lambda():

            y = 10 + z

            expr1 = users.c.name > x
            expr2 = users.c.name < y
            return and_(expr1, expr2)

        expr = coercions.expect(roles.WhereHavingRole, my_lambda)
        self.assert_compile(
            expr,
            "users.name > :x_1 AND users.name < :param_1 + :z_1",
            params=expr._param_dict(),
            checkparams={"x_1": 5, "z_1": 10, "param_1": 10},
        )

        x = 15
        z = 18

        expr = coercions.expect(roles.WhereHavingRole, my_lambda)
        self.assert_compile(
            expr,
            "users.name > :x_1 AND users.name < :param_1 + :z_1",
            params=expr._param_dict(),
            checkparams={"x_1": 15, "z_1": 18, "param_1": 10},
        ) 

def _get_nonansi_join_whereclause(self, froms):
        clauses = []

        def visit_join(join):
            if join.isouter:
                # https://docs.oracle.com/database/121/SQLRF/queries006.htm#SQLRF52354
                # "apply the outer join operator (+) to all columns of B in
                # the join condition in the WHERE clause" - that is,
                # unconditionally regardless of operator or the other side
                def visit_binary(binary):
                    if isinstance(binary.left, expression.ColumnClause) \
                            and join.right.is_derived_from(binary.left.table):
                        binary.left = _OuterJoinColumn(binary.left)
                    elif isinstance(binary.right, expression.ColumnClause) \
                            and join.right.is_derived_from(binary.right.table):
                        binary.right = _OuterJoinColumn(binary.right)
                clauses.append(visitors.cloned_traverse(
                    join.onclause, {}, {'binary': visit_binary}))
            else:
                clauses.append(join.onclause)

            for j in join.left, join.right:
                if isinstance(j, expression.Join):
                    visit_join(j)
                elif isinstance(j, expression.FromGrouping):
                    visit_join(j.element)

        for f in froms:
            if isinstance(f, expression.Join):
                visit_join(f)

        if not clauses:
            return None
        else:
            return sql.and_(*clauses) 

def _get_nonansi_join_whereclause(self, froms):
        clauses = []

        def visit_join(join):
            if join.isouter:
                def visit_binary(binary):
                    if binary.operator == sql_operators.eq:
                        if join.right.is_derived_from(binary.left.table):
                            binary.left = _OuterJoinColumn(binary.left)
                        elif join.right.is_derived_from(binary.right.table):
                            binary.right = _OuterJoinColumn(binary.right)
                clauses.append(visitors.cloned_traverse(join.onclause, {},
                                {'binary': visit_binary}))
            else:
                clauses.append(join.onclause)

            for j in join.left, join.right:
                if isinstance(j, expression.Join):
                    visit_join(j)
                elif isinstance(j, expression.FromGrouping):
                    visit_join(j.element)

        for f in froms:
            if isinstance(f, expression.Join):
                visit_join(f)

        if not clauses:
            return None
        else:
            return sql.and_(*clauses) 

def _get_nonansi_join_whereclause(self, froms):
        clauses = []

        def visit_join(join):
            if join.isouter:
                # https://docs.oracle.com/database/121/SQLRF/queries006.htm#SQLRF52354
                # "apply the outer join operator (+) to all columns of B in
                # the join condition in the WHERE clause" - that is,
                # unconditionally regardless of operator or the other side
                def visit_binary(binary):
                    if isinstance(binary.left, expression.ColumnClause) \
                            and join.right.is_derived_from(binary.left.table):
                        binary.left = _OuterJoinColumn(binary.left)
                    elif isinstance(binary.right, expression.ColumnClause) \
                            and join.right.is_derived_from(binary.right.table):
                        binary.right = _OuterJoinColumn(binary.right)
                clauses.append(visitors.cloned_traverse(
                    join.onclause, {}, {'binary': visit_binary}))
            else:
                clauses.append(join.onclause)

            for j in join.left, join.right:
                if isinstance(j, expression.Join):
                    visit_join(j)
                elif isinstance(j, expression.FromGrouping):
                    visit_join(j.element)

        for f in froms:
            if isinstance(f, expression.Join):
                visit_join(f)

        if not clauses:
            return None
        else:
            return sql.and_(*clauses) 

def _args_to_clause(self, args, clauses=()):
        clauses = list(clauses)
        for column, value in args.items():
            if not self.has_column(column):
                clauses.append(false())
            elif isinstance(value, (list, tuple)):
                clauses.append(self.table.c[column].in_(value))
            else:
                clauses.append(self.table.c[column] == value)
        return and_(*clauses) 

def distinct(self, *args, **_filter):
        """Return all the unique (distinct) values for the given ``columns``.
        ::

            # returns only one row per year, ignoring the rest
            table.distinct('year')
            # works with multiple columns, too
            table.distinct('year', 'country')
            # you can also combine this with a filter
            table.distinct('year', country='China')
        """
        if not self.exists:
            return iter([])

        filters = []
        for column, value in _filter.items():
            if not self.has_column(column):
                raise DatasetException("No such column: %s" % column)
            filters.append(self.table.c[column] == value)

        columns = []
        for column in args:
            if isinstance(column, ClauseElement):
                filters.append(column)
            else:
                if not self.has_column(column):
                    raise DatasetException("No such column: %s" % column)
                columns.append(self.table.c[column])

        if not len(columns):
            return iter([])

        q = expression.select(columns,
                              distinct=True,
                              whereclause=and_(*filters),
                              order_by=[c.asc() for c in columns])
        return self.db.query(q)

    # Legacy methods for running find queries. 

def average_assignment_code_score(self, assignment_id):
        """Compute the average code score for an assignment.

        Parameters
        ----------
        assignment_id : string
            the name of the assignment

        Returns
        -------
        score : float
            The average code score

        """

        assignment = self.find_assignment(assignment_id)
        if assignment.num_submissions == 0:
            return 0.0

        score_sum = self.db.query(func.coalesce(func.sum(Grade.score), 0.0))\
            .join(GradeCell, Notebook, Assignment)\
            .filter(and_(
                Assignment.name == assignment_id,
                Notebook.assignment_id == Assignment.id,
                GradeCell.notebook_id == Notebook.id,
                Grade.cell_id == GradeCell.id,
                GradeCell.cell_type == "code")).scalar()
        return score_sum / assignment.num_submissions 

def average_assignment_written_score(self, assignment_id):
        """Compute the average written score for an assignment.

        Parameters
        ----------
        assignment_id : string
            the name of the assignment

        Returns
        -------
        score : float
            The average written score

        """

        assignment = self.find_assignment(assignment_id)
        if assignment.num_submissions == 0:
            return 0.0

        score_sum = self.db.query(func.coalesce(func.sum(Grade.score), 0.0))\
            .join(GradeCell, Notebook, Assignment)\
            .filter(and_(
                Assignment.name == assignment_id,
                Notebook.assignment_id == Assignment.id,
                GradeCell.notebook_id == Notebook.id,
                Grade.cell_id == GradeCell.id,
                GradeCell.cell_type == "markdown")).scalar()
        return score_sum / assignment.num_submissions 

def average_assignment_task_score(self, assignment_id):
        """Compute the average task score for an assignment.

        Parameters
        ----------
        assignment_id : string
            the name of the assignment

        Returns
        -------
        score : float
            The average task score

        """

        assignment = self.find_assignment(assignment_id)
        if assignment.num_submissions == 0:
            return 0.0

        score_sum = self.db.query(func.coalesce(func.sum(Grade.score), 0.0))\
            .join(TaskCell, Notebook, Assignment)\
            .filter(and_(
                Assignment.name == assignment_id,
                Notebook.assignment_id == Assignment.id,
                TaskCell.notebook_id == Notebook.id,
                Grade.cell_id == TaskCell.id,
                TaskCell.cell_type == "markdown")).scalar()
        return score_sum / assignment.num_submissions 

def average_notebook_score(self, notebook_id: str, assignment_id: str) -> float:
        """Compute the average score for a particular notebook in an assignment.

        Parameters
        ----------
        notebook_id:
            the name of the notebook
        assignment_id:
            the name of the assignment

        Returns
        -------
        score:
            The average notebook score

        """

        notebook = self.find_notebook(notebook_id, assignment_id)
        if notebook.num_submissions == 0:
            return 0.0

        score_sum = self.db.query(func.coalesce(func.sum(Grade.score), 0.0))\
            .join(SubmittedNotebook, Notebook, Assignment)\
            .filter(and_(
                Notebook.name == notebook_id,
                Assignment.name == assignment_id)).scalar()
        return score_sum / notebook.num_submissions 

def average_notebook_code_score(self, notebook_id: str, assignment_id: str) -> float:
        """Compute the average code score for a particular notebook in an
        assignment.

        Parameters
        ----------
        notebook_id:
            the name of the notebook
        assignment_id:
            the name of the assignment

        Returns
        -------
        score:
            The average notebook code score

        """

        notebook = self.find_notebook(notebook_id, assignment_id)
        if notebook.num_submissions == 0:
            return 0.0

        score_sum = self.db.query(func.coalesce(func.sum(Grade.score), 0.0))\
            .join(GradeCell, Notebook, Assignment)\
            .filter(and_(
                Notebook.name == notebook_id,
                Assignment.name == assignment_id,
                Notebook.assignment_id == Assignment.id,
                GradeCell.notebook_id == Notebook.id,
                Grade.cell_id == GradeCell.id,
                GradeCell.cell_type == "code")).scalar()
        return score_sum / notebook.num_submissions 

def average_notebook_task_score(self, notebook_id: str, assignment_id: str) -> float:
        """Compute the average task score for a particular notebook in an
        assignment.

        Parameters
        ----------
        notebook_id:
            the name of the notebook
        assignment_id:
            the name of the assignment

        Returns
        -------
        score:
            The average notebook task score

        """

        notebook = self.find_notebook(notebook_id, assignment_id)
        if notebook.num_submissions == 0:
            return 0.0

        score_sum = self.db.query(func.coalesce(func.sum(Grade.score), 0.0))\
            .join(TaskCell, Notebook, Assignment)\
            .filter(and_(
                Notebook.name == notebook_id,
                Assignment.name == assignment_id,
                Notebook.assignment_id == Assignment.id,
                TaskCell.notebook_id == Notebook.id,
                Grade.cell_id == TaskCell.id,
                TaskCell.cell_type == "markdown")).scalar()
        return score_sum / notebook.num_submissions