Esempio n. 1
0
def build_comparisons(s, ast, with_conversion):
    qname, op, value = ast
    owner, name, group = StatisticName.parse(qname,
                                             default_activity_group=UNDEF)
    if value is None:
        if op == '=':
            return get_source_ids_for_null(s, owner, name, group,
                                           with_conversion), True
        else:
            return aliased(
                union(*[
                    get_source_ids(s, owner, name, op, value, group, type)
                    for type in StatisticJournalType
                    if type != StatisticJournalType.STATISTIC
                ])).select(), False
    elif isinstance(value, str):
        return get_source_ids(s, owner, name, op, value, group,
                              StatisticJournalType.TEXT), False
    elif isinstance(value, dt.datetime):
        return get_source_ids(s, owner, name, op, value, group,
                              StatisticJournalType.TIMESTAMP), False
    else:
        qint = get_source_ids(s, owner, name, op, value, group,
                              StatisticJournalType.INTEGER)
        qfloat = get_source_ids(s, owner, name, op, value, group,
                                StatisticJournalType.FLOAT)
        return aliased(union(qint, qfloat)).select(), False
Esempio n. 2
0
    def eval(self, builder):
        """ return a query object
        """

        dbh = builder._get_dbh()

        tokens = self.value[1:]
        expr_1 = self.value[0].eval( builder )

        while tokens:
            op = tokens[0]
            eval_2 = tokens[1].eval( builder )
            tokens = tokens[2:]

            if op == '|':
                eval_1 = sqla.union(eval_1, eval_2)
            elif op == '&':
                eval_1 = sqla.intersect(eval_1, eval_2)
            elif op == ':':
                eval_1 = sqla.except_(
                    dbh.session().query(dbh.Sample.id).filter(
                        dbh.Sample.id.in_( sqla.union(eval_1, eval_2)) ),
                    dbh.session().query(dbh.Sample.id).filter(
                        dbh.Sample.iid.n_( sqla.intersect(eval_1, eval_2)) )
                    )

        q = dbh.session().query(dbh.Sample.id).filter( dbh.Sample.id.in_( eval_1 ) )
        return q
Esempio n. 3
0
    def test_sqlalchemy(self):

        engine = create_engine('sqlite:///:memory:')
        Base = declarative_base()
        Session = sessionmaker(engine)

        class Table(Base):
            __tablename__ = 'table'
            id = Column(Integer, primary_key=True)

        Base.metadata.create_all(engine)

        with Session() as s:
            q1 = select(Table).filter(Table.id == 1)
            q2 = select(Table).filter(Table.id == 2)
            q3 = union(q1, q2).subquery()

        with Session() as s:
            q1 = select(Table).filter(Table.id == 1)
            q2 = select(Table).filter(Table.id == 2)
            cte = union(q1, q2).cte()
            q3 = select(Table).filter(Table.id.in_(cte))
            q4 = select(Table).filter(Table.id.in_(cte))
            q5 = union(q3, q4).subquery()
            log.debug(q5)
Esempio n. 4
0
    def test_intersect_unions_2(self):
        u = intersect(
            union(select([t1.c.col3, t1.c.col4]), select([t3.c.col3, t3.c.col4])).alias().select(),
            union(select([t2.c.col3, t2.c.col4]), select([t3.c.col3, t3.c.col4])).alias().select(),
        )
        wanted = [("aaa", "ccc"), ("bbb", "aaa"), ("ccc", "bbb")]
        found = self._fetchall_sorted(u.execute())

        eq_(found, wanted)
Esempio n. 5
0
    def test_intersect_unions_2(self):
        u = intersect(
            union(
                select([t1.c.col3, t1.c.col4]),
                select([t3.c.col3, t3.c.col4]),
            ).alias().select(),
            union(
                select([t2.c.col3, t2.c.col4]),
                select([t3.c.col3, t3.c.col4]),
            ).alias().select())
        wanted = [('aaa', 'ccc'), ('bbb', 'aaa'), ('ccc', 'bbb')]
        found = self._fetchall_sorted(u.execute())

        eq_(found, wanted)
Esempio n. 6
0
    def test_intersect_unions_2(self):
        u = intersect(
            union(
                select([t1.c.col3, t1.c.col4]),
                select([t3.c.col3, t3.c.col4]),
            ).alias().select(),
            union(
                select([t2.c.col3, t2.c.col4]),
                select([t3.c.col3, t3.c.col4]),
            ).alias().select()
        )
        wanted = [('aaa', 'ccc'), ('bbb', 'aaa'), ('ccc', 'bbb')]
        found = self._fetchall_sorted(u.execute())

        eq_(found, wanted)
Esempio n. 7
0
    def timeseries_all(cls, table_names, agg_unit, start, end, geom=None):
        # For each table in table_names, generate a query to be unioned
        selects = []
        for name in table_names:
            table = cls.get_by_dataset_name(name)
            ts_select = table.timeseries(agg_unit, start, end, geom)
            selects.append(ts_select)

        # Union the time series selects to get a panel
        panel_query = sa.union(*selects)\
                        .order_by('dataset_name')\
                        .order_by('time_bucket')
        panel_vals = session.execute(panel_query)

        panel = []
        for dataset_name, ts in groupby(panel_vals,
                                        lambda row: row.dataset_name):

            # ts gets closed after it's been iterated over once,
            # so we need to store the rows somewhere to iterate over them twice.
            rows = [row for row in ts]
            # If no records were found, don't include this dataset
            if all([row.count == 0 for row in rows]):
                continue

            ts_dict = {'dataset_name': dataset_name, 'items': []}

            for row in rows:
                ts_dict['items'].append({
                    'datetime': row.time_bucket.date(),  # UTC time
                    'count': row.count
                })
            panel.append(ts_dict)

        return panel
Esempio n. 8
0
 def test_union(self):
     t1 = table(
         't1', column('col1'), column('col2'),
         column('col3'), column('col4'))
     t2 = table(
         't2', column('col1'), column('col2'),
         column('col3'), column('col4'))
     s1, s2 = select(
         [t1.c.col3.label('col3'), t1.c.col4.label('col4')],
         t1.c.col2.in_(['t1col2r1', 't1col2r2'])), \
         select([t2.c.col3.label('col3'), t2.c.col4.label('col4')],
                t2.c.col2.in_(['t2col2r2', 't2col2r3']))
     u = union(s1, s2, order_by=['col3', 'col4'])
     self.assert_compile(u,
                         'SELECT t1.col3 AS col3, t1.col4 AS col4 '
                         'FROM t1 WHERE t1.col2 IN (:col2_1, '
                         ':col2_2) UNION SELECT t2.col3 AS col3, '
                         't2.col4 AS col4 FROM t2 WHERE t2.col2 IN '
                         '(:col2_3, :col2_4) ORDER BY col3, col4')
     self.assert_compile(u.alias('bar').select(),
                         'SELECT bar.col3, bar.col4 FROM (SELECT '
                         't1.col3 AS col3, t1.col4 AS col4 FROM t1 '
                         'WHERE t1.col2 IN (:col2_1, :col2_2) UNION '
                         'SELECT t2.col3 AS col3, t2.col4 AS col4 '
                         'FROM t2 WHERE t2.col2 IN (:col2_3, '
                         ':col2_4)) AS bar')
Esempio n. 9
0
def main():
    db = define.connect()
    all_media = sa.union(
        sa.select([orm.UserMediaLink.mediaid]),
        sa.select([orm.SubmissionMediaLink.mediaid]),
        sa.select([orm.MediaMediaLink.describee_id]),
        sa.select([orm.MediaMediaLink.described_with_id]),
    ).alias('all_media')
    q = (
        db.query(orm.MediaItem)
        .with_polymorphic([orm.DiskMediaItem])
        .outerjoin(all_media)
        .filter(all_media.c.mediaid == None))
    count = q.count()
    for e, media_item in enumerate(q, start=1):
        sys.stdout.write('\r%d/%d' % (e, count))
        sys.stdout.flush()
        db.delete(media_item)
        try:
            os.unlink(media_item.full_file_path)
        except OSError as e:
            if e.errno == errno.ENOENT:
                continue
            raise
    db.flush()
    print
Esempio n. 10
0
def getTokenUserFunds(request, token):
    """Get the sum of a token for all users

    Arguments:
        request {[type]} -- [description]
        token {[type]} -- [description]

    Returns:
        [type] -- [description]
    """

    users_r = request.dbsession.query(
        Funds.user_id.label('user_id'),
        func.sum(Funds.value).label("total")).filter(
            Funds.token == token).group_by(Funds.user_id)
    users_s = request.dbsession.query(
        Funds.sender_id.label('user_id'),
        func.sum(-Funds.value).label("total")).filter(
            Funds.sender_id.isnot(None)).filter(Funds.token == token).group_by(
                Funds.sender_id)
    users_u = union(users_r, users_s).alias('total')
    return request.dbsession.query(
        users_u.columns.user_id,
        func.sum(users_u.columns.total).label('total')).group_by(
            users_u.columns.user_id)
Esempio n. 11
0
    def query(cls):
        def select_1():
            stmt = sqlalchemy.text("select * from pytis_view_user_menu()")
            u = stmt.columns(sqlalchemy.Column(
                'menuid', sqlalchemy.Integer()), ).alias('u')
            h = sql.t.EvPytisHelp.alias('h')
            return sqlalchemy.select(
                sql.reorder_columns(cls._exclude(h), [
                    'help_id', 'menuid', 'fullname', 'title', 'description',
                    'menu_help', 'spec_name', 'spec_description', 'spec_help',
                    'page_id', 'parent', 'ord', 'content', 'position',
                    'position_nsub', 'changed', 'removed'
                ]),
                from_obj=[h.join(u, h.c.menuid == u.c.menuid)])

        def select_2():
            h = sql.t.EvPytisHelp.alias('h')
            return sqlalchemy.select(
                sql.reorder_columns(cls._exclude(h), [
                    'help_id', 'menuid', 'fullname', 'title', 'description',
                    'menu_help', 'spec_name', 'spec_description', 'spec_help',
                    'page_id', 'parent', 'ord', 'content', 'position',
                    'position_nsub', 'changed', 'removed'
                ]),
                from_obj=[h],
                whereclause=h.c.menuid.is_(None),
            )

        return sqlalchemy.union(select_1(), select_2())
Esempio n. 12
0
 def test_union_label(self):
     s1 = select([func.foo("hoho").label('x')])
     s2 = select([func.foo("Bar").label('y')])
     stmt = union(s1, s2).order_by("x")
     self.assert_compile(
         stmt,
         "SELECT foo(:foo_1) AS x UNION SELECT foo(:foo_2) AS y ORDER BY x")
Esempio n. 13
0
    def update(self, engine):
        with engine.begin() as conn:
            # delete removed relations
            conn.execute(self.delete(self.rels.select_delete()))
            # collect all changed relations in a temporary table
            # 1. relations added or modified
            sels = [sa.select([self.rels.cc.id])]
            # 2. relations with modified geometries
            w = self.ways
            sels.append(
                sa.select([saf.func.unnest(w.c.rels).label('id')],
                          distinct=True).where(
                              w.c.id.in_(w.select_add_modify())))

            conn.execute(
                'DROP TABLE IF EXISTS __tmp_osgende_routes_updaterels')
            conn.execute(
                CreateTableAs('__tmp_osgende_routes_updaterels',
                              sa.union(*sels),
                              temporary=False))
            tmp_rels = sa.Table('__tmp_osgende_routes_updaterels',
                                sa.MetaData(),
                                autoload_with=conn)

            # 3. parent relation of all of them
            conn.execute(tmp_rels.insert().from_select(
                tmp_rels.c,
                sa.select([self.rtree.c.parent], distinct=True).where(
                    self.rtree.c.child.in_(sa.select([tmp_rels.c.id])))))

            # and insert/update all
            self._insert_objects(conn, self.rels.c.id.in_(tmp_rels.select()))

            tmp_rels.drop(conn)
Esempio n. 14
0
    def get_published_briefs(self):
        team_brief_query = (db.session.query(
            TeamBrief.brief_id.label('brief_id'),
            func.array_agg(func.substring(
                User.email_address,
                '@(.*)')).label('domain')).join(Team).join(User).filter(
                    Team.status == 'completed').group_by(TeamBrief.brief_id))

        brief_user_query = (db.session.query(
            BriefUser.brief_id.label('brief_id'),
            func.array_agg(
                func.substring(User.email_address,
                               '@(.*)')).label('domain')).join(User).group_by(
                                   BriefUser.brief_id))
        subquery = union(team_brief_query, brief_user_query).alias('result')

        result = (db.session.query(
            Brief.id, Brief.data['organisation'].astext.label('organisation'),
            Brief.published_at, Brief.withdrawn_at,
            Brief.data['title'].astext.label('title'),
            Brief.data['sellerSelector'].astext.label('openTo'),
            Brief.data['areaOfExpertise'].astext.label('brief_category'),
            Lot.name.label('brief_type'),
            subquery.columns.domain[1].label('publisher_domain')).join(
                subquery,
                Brief.id == subquery.columns.brief_id).join(Lot).filter(
                    Brief.published_at.isnot(None)).order_by(Brief.id).all())

        return [r._asdict() for r in result]
Esempio n. 15
0
def determine_fetches(db_session, cred):
    for thread in db_session.query(Thread).filter_by(closed=False):
        update_thread_status(thread, cred)
    db_session.flush()
    incomplete_page_ids = (
        sa.select([ThreadPost.page_id])
        .group_by(ThreadPost.page_id)
        .having(sa.func.count(ThreadPost.id) < 40)
        .as_scalar()
    )
    incomplete_pages = sa.select(
        [ThreadPage.thread_id, ThreadPage.page_num], from_obj=sa.join(ThreadPage, Thread)
    ).where(sa.and_(ThreadPage.id.in_(incomplete_page_ids), Thread.closed == sa.false()))
    fetch_status = (
        sa.select(
            [ThreadPage.thread_id.label("thread_id"), sa.func.max(ThreadPage.page_num).label("last_fetched_page")]
        )
        .group_by(ThreadPage.thread_id)
        .alias("fetch_status")
    )
    unfetched_pages = sa.select(
        [
            Thread.id.label("thread_id"),
            sa.func.generate_series(fetch_status.c.last_fetched_page + 1, Thread.page_count).label("page_num"),
        ],
        from_obj=sa.join(Thread, fetch_status, Thread.id == fetch_status.c.thread_id),
    )
    fetched_first_pages = sa.select([ThreadPage.thread_id]).where(ThreadPage.page_num == 1).as_scalar()
    unfetched_first_pages = sa.select(
        [Thread.id.label("thread_id"), sa.literal(1, sa.Integer).label("page_num")], from_obj=Thread
    ).where(Thread.id.notin_(fetched_first_pages))
    q = sa.union(incomplete_pages, unfetched_pages, unfetched_first_pages)
    q = q.order_by(q.c.thread_id.asc(), q.c.page_num.asc())
    return db_session.execute(q).fetchall()
Esempio n. 16
0
    def query(cls):
        def select_1():
            h = sql.t.EvPytisHelp.alias("h")
            u = sqlalchemy.select(["*"], from_obj=["pytis_view_user_menu()"]).alias("u")
            return sqlalchemy.select(
                sql.reorder_columns(
                    cls._exclude(h),
                    [
                        "help_id",
                        "menuid",
                        "fullname",
                        "title",
                        "description",
                        "menu_help",
                        "spec_name",
                        "spec_description",
                        "spec_help",
                        "page_id",
                        "parent",
                        "ord",
                        "content",
                        "position",
                        "position_nsub",
                        "changed",
                        "removed",
                    ],
                ),
                from_obj=[h.join(u, sql.gR("h.menuid = u.menuid"))],
            )

        def select_2():
            h = sql.t.EvPytisHelp.alias("h")
            return sqlalchemy.select(
                sql.reorder_columns(
                    cls._exclude(h),
                    [
                        "help_id",
                        "menuid",
                        "fullname",
                        "title",
                        "description",
                        "menu_help",
                        "spec_name",
                        "spec_description",
                        "spec_help",
                        "page_id",
                        "parent",
                        "ord",
                        "content",
                        "position",
                        "position_nsub",
                        "changed",
                        "removed",
                    ],
                ),
                from_obj=[h],
                whereclause="h.menuid is null",
            )

        return sqlalchemy.union(select_1(), select_2())
Esempio n. 17
0
def top_10_fields_by_prob():
    a = LocalizationTile
    b = FieldTile
    a_lo = a.nested_lo.label('a_lo')
    a_hi = a.nested_hi.label('a_hi')
    b_lo = b.nested_lo.label('b_lo')
    b_hi = b.nested_hi.label('b_hi')

    query1 = db.session.query(
        a_lo, a_hi, b_lo, b_hi, FieldTile.field_id.label('field_id'),
        LocalizationTile.localization_id.label('localization_id'),
        LocalizationTile.probdensity.label('probdensity'))

    query2 = union(
        query1.join(b, a_lo.between(b_lo, b_hi)),
        query1.join(b, b_lo.between(a_lo, a_hi)),
    ).cte()

    lo = func.greatest(query2.c.a_lo, query2.c.b_lo)
    hi = func.least(query2.c.a_hi, query2.c.b_hi)
    area = (hi - lo + 1) * healpix.PIXEL_AREA
    prob = func.sum(query2.c.probdensity * area).label('probability')

    query = db.session.query(query2.c.localization_id, query2.c.field_id,
                             prob).group_by(query2.c.localization_id,
                                            query2.c.field_id).order_by(
                                                prob.desc()).limit(10)

    return query.all()
    def get_unclassified(self, all_snapshots, on_time_grads, late_grads,
                         dropouts, unconfirmed_high_gpa, unconfirmed_low_gpa):
        student_lookup = all_snapshots.c.student_lookup
        grade = all_snapshots.c.grade
        on_time_lookups = sql.select([on_time_grads.c.student_lookup])
        late_lookups = sql.select([late_grads.c.student_lookup])
        dropout_lookups = sql.select([dropouts.c.student_lookup])
        unconfirmed_high_lookups = sql.select(
            [unconfirmed_high_gpa.c.student_lookup])
        unconfirmed_low_lookups = sql.select(
            [unconfirmed_low_gpa.c.student_lookup])

        u = sql.union(on_time_lookups, late_lookups, dropout_lookups,
                      unconfirmed_high_lookups, unconfirmed_low_lookups)

        return\
            sql.select([
                sql.distinct(student_lookup).label('student_lookup'),
                sql.literal(original_labels_config.unclassified).label('label')
            ]).\
            where(
                db_expr.and_(
                    student_lookup.notin_(u),
                    grade >= 12
                )
            )
    def get_supplier_contacts_union(self):
        authorised_representative = select([Supplier.code, Supplier.data['email'].astext.label('email_address')])
        business_contact = select([Supplier.code, Supplier.data['contact_email'].astext.label('email_address')])
        user_email_addresses = (select([User.supplier_code.label('code'), User.email_address])
                                .where(User.active))

        return union(authorised_representative, business_contact, user_email_addresses).alias('email_addresses')
Esempio n. 20
0
    def test_select_from_plain_union(self):
        table = self.tables.some_table
        s1 = select([table]).where(table.c.id == 2)
        s2 = select([table]).where(table.c.id == 3)

        u1 = union(s1, s2).alias().select()
        self._assert_result(u1.order_by(u1.c.id), [(2, 2, 3), (3, 3, 4)])
Esempio n. 21
0
    def timeseries_all(cls, table_names, agg_unit, start, end, geom=None):
        # For each table in table_names, generate a query to be unioned
        selects = []
        for name in table_names:
            table = cls.get_by_dataset_name(name)
            ts_select = table.timeseries(agg_unit, start, end, geom)
            selects.append(ts_select)

        # Union the time series selects to get a panel
        panel_query = sa.union(*selects)\
                        .order_by('dataset_name')\
                        .order_by('time_bucket')
        panel_vals = session.execute(panel_query)

        panel = []
        for dataset_name, ts in groupby(panel_vals, lambda row: row.dataset_name):

            # ts gets closed after it's been iterated over once,
            # so we need to store the rows somewhere to iterate over them twice.
            rows = [row for row in ts]
            # If no records were found, don't include this dataset
            if all([row.count == 0 for row in rows]):
                continue

            ts_dict = {'dataset_name': dataset_name,
                       'items': []}

            for row in rows:
                ts_dict['items'].append({
                    'datetime': row.time_bucket.date(),  # UTC time
                    'count':    row.count
                })
            panel.append(ts_dict)

        return panel
Esempio n. 22
0
def test_distinct_selectable_in_unions(faux_conn):
    table = some_table(faux_conn)
    s1 = select([table]).where(table.c.id == 2).distinct()
    s2 = select([table]).where(table.c.id == 3).distinct()

    u1 = union(s1, s2).limit(2)
    assert_result(faux_conn, u1.order_by(u1.c.id), [(2, 2, 3), (3, 3, 4)])
Esempio n. 23
0
def fetch_submissions(hdlr_name=None):
    """
    :param hdlr_name: Name of the handler that requests subsmissions.
    :return:
        Return submissions with 'pid' greater than the milestones added by the
        handler with 'handler_name' and sorted by 'pid', which is equivalent to
        sorted by 'submit_time'.
        If hdlr_name is not specified or there are not any milestones under
        the name of a handler, all submissions are returned.
        An empty list is returned if there are no available submissions.
    :rtype: [Submission]
    :caller: Handler
    """
    mlst = select([t_milestone.c.submission_pid, t_milestone.c.handler_name]) \
        .where(t_milestone.c.handler_name == hdlr_name) \
        .order_by(t_milestone.c.submission_pid.desc()).limit(1)
    mlst = union(select([mlst]), select([None, None]).where(~exists(mlst)))
    s = select([t.c.oj,
                t.c.problem_id,
                t.c.problem_title,
                t.c.problem_url,
                t.c.submit_time,
                t.c.timezone,
                t.c.pid]) \
        .where((mlst.c.submission_pid == None) |
               (t.c.pid > mlst.c.submission_pid) & (mlst.c.handler_name == hdlr_name)) \
        .order_by(t.c.pid)
    with engine.connect() as conn:
        return [Submission(*d) for d in conn.execute(s)]
Esempio n. 24
0
 def test_union(self):
     t1 = table(
         't1', column('col1'), column('col2'),
         column('col3'), column('col4'))
     t2 = table(
         't2', column('col1'), column('col2'),
         column('col3'), column('col4'))
     s1, s2 = select(
         [t1.c.col3.label('col3'), t1.c.col4.label('col4')],
         t1.c.col2.in_(['t1col2r1', 't1col2r2'])), \
         select([t2.c.col3.label('col3'), t2.c.col4.label('col4')],
                t2.c.col2.in_(['t2col2r2', 't2col2r3']))
     u = union(s1, s2, order_by=['col3', 'col4'])
     self.assert_compile(u,
                         'SELECT t1.col3 AS col3, t1.col4 AS col4 '
                         'FROM t1 WHERE t1.col2 IN (:col2_1, '
                         ':col2_2) UNION SELECT t2.col3 AS col3, '
                         't2.col4 AS col4 FROM t2 WHERE t2.col2 IN '
                         '(:col2_3, :col2_4) ORDER BY col3, col4')
     self.assert_compile(u.alias('bar').select(),
                         'SELECT bar.col3, bar.col4 FROM (SELECT '
                         't1.col3 AS col3, t1.col4 AS col4 FROM t1 '
                         'WHERE t1.col2 IN (:col2_1, :col2_2) UNION '
                         'SELECT t2.col3 AS col3, t2.col4 AS col4 '
                         'FROM t2 WHERE t2.col2 IN (:col2_3, '
                         ':col2_4)) AS bar')
Esempio n. 25
0
    def test_compare_col_identity(self):
        stmt1 = (
            select([table_a.c.a, table_b.c.b])
            .where(table_a.c.a == table_b.c.b)
            .alias()
        )
        stmt1_c = (
            select([table_a.c.a, table_b.c.b])
            .where(table_a.c.a == table_b.c.b)
            .alias()
        )

        stmt2 = union(select([table_a]), select([table_b]))

        equivalents = {table_a.c.a: [table_b.c.a]}

        is_false(
            stmt1.compare(stmt2, use_proxies=True, equivalents=equivalents)
        )

        is_true(
            stmt1.compare(stmt1_c, use_proxies=True, equivalents=equivalents)
        )
        is_true(
            (table_a.c.a == table_b.c.b).compare(
                stmt1.c.a == stmt1.c.b,
                use_proxies=True,
                equivalents=equivalents,
            )
        )
Esempio n. 26
0
    def moveNode(self, id_, moveTo):
        # update t_data
        upd = update(
            self.t_data).where(self.t_data.c.node == 5).values(parent=3)
        self.connection.execute(upd)

        # update t_path
        subtree = select([self.t_path.c.node
                          ]).where(self.t_path.c.ancestor == 5)
        path = select([self.t_path.c.ancestor]).where(self.t_path.c.node == 5)

        # deletion
        cond = and_(
            or_(self.t_path.c.node == 5, self.t_path.c.node.in_(subtree)),
            self.t_path.c.ancestor.in_(path))
        delet = self.t_path.delete().where(cond)
        self.connection.execute(delet)

        # insertion
        subtree = select([self.t_path.c.node
                          ]).where(self.t_path.c.ancestor == 5)
        path = select([self.t_path.c.ancestor]).where(self.t_path.c.node == 5)
        path_y = select([self.t_path.c.ancestor
                         ]).where(self.t_path.c.node == 3)
        subset = select([column('node'), column('ancestor')]).select_from(
            union(
                select([
                    literal_column('1').label('sortir'),
                    literal_column('5').label('node'),
                    literal_column('3').label('ancestor')
                ]), select([2, 5, path_y]), select([3, subtree, 3]),
                select([4, subtree, path_y])).order_by('sortir'))
        addToPath = self.t_path.insert().from_select(['node', 'ancestor'],
                                                     subset)
        self.connection.execute(addToPath)
Esempio n. 27
0
    def _do_get_provider_count_and_objs(self, **kw):
        '''Custom getter function respecting lesson

        Returns the result count from the database and a query object
        '''
        # TODO: Code duplication with CRC?!

        qry = Submission.query

        # Process lesson filter
        if self.lesson:
            q1 = (qry.join(Submission.user).join(lesson_members).join(Lesson)
                .filter(Lesson.id == self.lesson.id).order_by(None))
            q2 = (qry.join(Submission.user).join(team_members).join(Team)
                .filter(Team.lesson_id == self.lesson.id).order_by(None))
            qry = qry.select_from(union(q1, q2)).order_by(Submission.id)

        filters = kw.pop('filters', dict())
        for filter in filters:
            if isinstance(filters[filter], (list, tuple, set)):
                qry = qry.filter(getattr(Submission, filter).in_(filters[filter]))
            else:
                qry = qry.filter(getattr(Submission, filter) == filters[filter])

        # Process filters from url
        kwfilters = kw
        exc = False
        try:
            kwfilters = self.__provider__._modify_params_for_dates(self.__model__, kwfilters)
        except ValueError as e:
            log.info('Could not parse date filters', exc_info=True)
            flash('Could not parse date filters: "%s".' % e.message, 'error')
            exc = True

        try:
            kwfilters = self.__provider__._modify_params_for_relationships(self.__model__, kwfilters)
        except (ValueError, AttributeError) as e:
            log.info('Could not parse relationship filters', exc_info=True)
            flash('Could not parse relationship filters: "%s". '
                  'You can only filter by the IDs of relationships, not by their names.' % e.message, 'error')
            exc = True
        if exc:
            # Since non-parsed kwfilters are bad, we just have to ignore them now
            kwfilters = {}

        for field_name, value in kwfilters.iteritems():
            field = getattr(self.__model__, field_name)
            try:
                if self.__provider__.is_relation(self.__model__, field_name) and isinstance(value, list):  # pragma: no cover
                    value = value[0]
                    qry = qry.filter(field.contains(value))
                else:
                    qry = qry.filter(field == value)
            except:
                log.warn('Could not create filter on query', exc_info=True)

        # Get total count
        count = qry.count()

        return count, qry
Esempio n. 28
0
    def test_distinct_selectable_in_unions(self):
        table = self.tables.some_table
        s1 = select([table]).where(table.c.id == 2).distinct()
        s2 = select([table]).where(table.c.id == 3).distinct()

        u1 = union(s1, s2).limit(2)
        self._assert_result(u1.order_by(u1.c.id), [(2, 2, 3), (3, 3, 4)])
Esempio n. 29
0
    def test_compare_col_identity(self):
        stmt1 = (
            select([table_a.c.a, table_b.c.b])
            .where(table_a.c.a == table_b.c.b)
            .alias()
        )
        stmt1_c = (
            select([table_a.c.a, table_b.c.b])
            .where(table_a.c.a == table_b.c.b)
            .alias()
        )

        stmt2 = union(select([table_a]), select([table_b]))

        stmt3 = select([table_b])

        equivalents = {table_a.c.a: [table_b.c.a]}

        is_false(
            stmt1.compare(stmt2, use_proxies=True, equivalents=equivalents)
        )

        is_true(
            stmt1.compare(stmt1_c, use_proxies=True, equivalents=equivalents)
        )
        is_true(
            (table_a.c.a == table_b.c.b).compare(
                stmt1.c.a == stmt1.c.b,
                use_proxies=True,
                equivalents=equivalents,
            )
        )
Esempio n. 30
0
    def _lookup(self, assignment_id, *args):
        try:
            assignment_id = int(assignment_id)
            # TODO: Use SQLAlchemy magic on model to make queries on assignment easier
            q1 = (Assignment.query.filter(Assignment.id == assignment_id).join(
                Assignment._lti).order_by(None))
            q2 = (Assignment.query.filter(
                Assignment.id == assignment_id).join(Sheet).join(Event).join(
                    Event.lti).order_by(None))
            assignment = Assignment.query.select_from(union(q1, q2)).one()
        except ValueError:
            flash('Invalid LTI Assignment id: %s' % assignment_id, 'error')
            abort(400)
        except NoResultFound:
            flash('LTI Assignment %d not found' % assignment_id, 'error')
            abort(404)
        except MultipleResultsFound:  # pragma: no cover
            log.error('Database inconsistency: LTI Assignment %d',
                      assignment_id,
                      exc_info=True)
            flash(
                'An error occurred while accessing LTI Assignment %d' %
                assignment_id, 'error')
            abort(500)

        controller = LTIAssignmentController(assignment)
        return controller, args
Esempio n. 31
0
    def _lookup(self, assignment_id, *args):
        try:
            assignment_id = int(assignment_id)
            # TODO: Use SQLAlchemy magic on model to make queries on assignment easier
            q1 = Assignment.query.filter(Assignment.id == assignment_id).join(Assignment._lti).order_by(None)
            q2 = (
                Assignment.query.filter(Assignment.id == assignment_id)
                .join(Sheet)
                .join(Event)
                .join(Event.lti)
                .order_by(None)
            )
            assignment = Assignment.query.select_from(union(q1, q2)).one()
        except ValueError:
            flash("Invalid LTI Assignment id: %s" % assignment_id, "error")
            abort(400)
        except NoResultFound:
            flash("LTI Assignment %d not found" % assignment_id, "error")
            abort(404)
        except MultipleResultsFound:  # pragma: no cover
            log.error("Database inconsistency: LTI Assignment %d", assignment_id, exc_info=True)
            flash("An error occurred while accessing LTI Assignment %d" % assignment_id, "error")
            abort(500)

        controller = LTIAssignmentController(assignment)
        return controller, args
Esempio n. 32
0
    def list_worksheets(self, owner_id=None):
        '''
        Return a list of row dicts, one per worksheet. These dicts do NOT contain
        ALL worksheet items; this method is meant to make it easy for a user to see
        the currently existing worksheets. Included worksheet items are those that
        define metadata that one will likely want to see in a list view (e.g. title).
        '''
        cols_to_select = [cl_worksheet.c.id,
                          cl_worksheet.c.uuid,
                          cl_worksheet.c.name,
                          cl_worksheet.c.owner_id,
                          cl_group_object_permission.c.permission]
        if owner_id is None:
            # query for public worksheets
            stmt = select(cols_to_select).\
                where(cl_worksheet.c.uuid == cl_group_object_permission.c.object_uuid).\
                where(cl_group_object_permission.c.group_uuid == self.public_group_uuid)
        else:
            # query for worksheets owned by owner_id
            cols1 = cols_to_select[:4]
            cols1.extend([literal(GROUP_OBJECT_PERMISSION_ALL).label('permission')])
            stmt1 = select(cols1).where(cl_worksheet.c.owner_id == owner_id)
            # query for worksheets visible to owner_id or co-owned by owner_id
            stmt2_groups = select([cl_user_group.c.group_uuid]).\
                where(cl_user_group.c.user_id == owner_id)
            stmt2 = select(cols_to_select).\
                where(cl_worksheet.c.uuid == cl_group_object_permission.c.object_uuid).\
                where(or_(
                    cl_group_object_permission.c.group_uuid.in_(stmt2_groups),
                    cl_group_object_permission.c.group_uuid == self.public_group_uuid)).\
                where(cl_worksheet.c.owner_id != owner_id)
            stmt = union(stmt1, stmt2)

        with self.engine.begin() as connection:
            rows = connection.execute(stmt).fetchall()
            if not rows:
                return []
            uuids = set(row.uuid for row in rows)
            item_rows = connection.execute(
                cl_worksheet_item.select().\
                where(cl_worksheet_item.c.worksheet_uuid.in_(uuids)).\
                where(or_(
                    cl_worksheet_item.c.type == 'title',
                    cl_worksheet_item.c.type == 'description'))
            ).fetchall()

        row_dicts = [dict(row) for row in sorted(rows, key=lambda item: item['id'])]
        uuid_index_map = {}
        for i in range(0, len(row_dicts)):
            row_dict = row_dicts[i]
            row_dict.update({'items': []})
            uuid_index_map[row_dict['uuid']] = i
        for item_row in item_rows:
            idx = uuid_index_map.get(item_row.worksheet_uuid, -1)
            if idx < 0:
                raise IntegrityError('Got item %s without worksheet' % (item_row,))
            row_dicts[idx]['items'].append(dict(item_row))

        return row_dicts
Esempio n. 33
0
    def test_union_limit_offset(self):
        emp, dep, conn = self._emp_d_fixture()

        s1 = select([emp.c.name])
        s2 = select([dep.c.name])
        u1 = union(s1, s2).order_by(emp.c.name).limit(3).offset(2)
        r = self._exec_stmt(conn, u1)
        eq_(r.fetchall(), [('Sales', ), ('ed', ), ('jack', )])
Esempio n. 34
0
 def query(class_):
     return sqlalchemy.union(
         sqlalchemy.select([sql.c.Foo.id, sql.c.Bar.description],
                           from_obj=[sql.t.Foo.join(sql.t.Bar)]),
         sqlalchemy.select([
             sql.c.Foo2.id,
             sqlalchemy.literal_column("'xxx'", sqlalchemy.String)
         ]))
Esempio n. 35
0
 def test_union_label(self):
     s1 = select([func.foo("hoho").label("x")])
     s2 = select([func.foo("Bar").label("y")])
     stmt = union(s1, s2).order_by("x")
     self.assert_compile(
         stmt,
         "SELECT foo(:foo_1) AS x UNION SELECT foo(:foo_2) AS y ORDER BY x",
     )
Esempio n. 36
0
 def members_query(self, qry=None):
     if not qry:
         qry = User.query
     qry = qry.select_from(union(
             qry.join(lesson_members).filter_by(lesson_id=self.id).order_by(None),
             qry.join(team_members).join(Team).filter_by(lesson_id=self.id).order_by(None),
         )).order_by(User.id)
     return qry
Esempio n. 37
0
 def test_union_column(self):
     s1 = select([table1])
     s2 = select([table1])
     stmt = union(s1, s2).order_by("name")
     self.assert_compile(
         stmt,
         "SELECT mytable.myid, mytable.name, mytable.description FROM "
         "mytable UNION SELECT mytable.myid, mytable.name, "
         "mytable.description FROM mytable ORDER BY name")
Esempio n. 38
0
    def _list_by_category(self, balance_uid, expenses = True, incomes = True):
        model = request.environ['sqlalchemy.model']
        db = request.environ['sqlalchemy.session']

        try:
            user_uid = h.authenticated_user().uid
        except:
            return { "failure": Messages.pemissionDenied() }
        if not Operations(db, model).can_see_balance(user_uid, balance_uid):
            return { "failure": Messages.permissionDenied() }

        now = date.today()
       
        select_expenses = select(
            [model.ExpenseCategory.c.uid.label('uid'), model.ExpenseCategory.c.name.label('name'), func.sum(model.BalanceChange.c.amount).label('summary')], 
            and_(model.BalanceChange.is_income==False,
                model.ExpenseCategory.uid==model.BalanceChange.expense_category_uid,
                model.BalanceChange.balance_uid==balance_uid,
                model.BalanceChange.occurred_on >= date(now.year, now.month, 1)), 
            from_obj=[model.expense_categories_table, model.balance_changes_table],
            group_by=[model.ExpenseCategory.c.uid, model.ExpenseCategory.c.name])

        select_incomes = select(
            [model.IncomeCategory.c.uid.label('uid'), model.IncomeCategory.c.name.label('name'), func.sum(model.BalanceChange.c.amount).label('summary')], 
            and_(model.BalanceChange.is_income==True,
                model.IncomeCategory.uid==model.BalanceChange.income_category_uid,
                model.BalanceChange.balance_uid==balance_uid,
                model.BalanceChange.occurred_on >= date(now.year, now.month, 1)), 
            from_obj=[model.income_categories_table, model.balance_changes_table],
            group_by=[model.IncomeCategory.c.uid, model.IncomeCategory.c.name])

        if expenses and incomes:
            query = union(select_expenses, select_incomes)
        else:
            query = expenses and select_expenses or select_incomes

        balance_changes = db.execute(query.order_by('name')).fetchall()
        total = len(balance_changes)
        try:
            page_nr = request.params['page_nr']
        except:
            page_nr = 1

        try:
            items_per_page = int(request.params['items_per_page'])
        except:
            items_per_page = 15
        
        subset = Page(balance_changes, item_count=total, current_page=page_nr, items_per_page=items_per_page)
        return { 
            "totalItems" : total,
            "itemsFound" : len(subset),
            "items" : [{ 
                "uid" : item.uid,
                "name" : item.name,
                "summary" : h.format_decimal(Decimal(item.summary))} for item in subset ]
        }
Esempio n. 39
0
 def test_compound(self):
     t1 = table('t1', column('c1'), column('c2'), column('c3'))
     t2 = table('t2', column('c1'), column('c2'), column('c3'))
     self.assert_compile(union(t1.select(), t2.select()),
                         'SELECT t1.c1, t1.c2, t1.c3 FROM t1 UNION '
                         'SELECT t2.c1, t2.c2, t2.c3 FROM t2')
     self.assert_compile(except_(t1.select(), t2.select()),
                         'SELECT t1.c1, t1.c2, t1.c3 FROM t1 MINUS '
                         'SELECT t2.c1, t2.c2, t2.c3 FROM t2')
Esempio n. 40
0
    def test_union_heterogeneous_columns(self):
        emp, dep, conn = self._emp_d_fixture()

        s1 = select([emp.c.name])
        s2 = select([dep.c.name])
        u1 = union(s1, s2)
        r = self._exec_stmt(conn, u1)
        eq_(r.fetchall(), [('ed', ), ('wendy', ), ('jack', ),
                           ('Engineering', ), ('Accounting', ), ('Sales', )])
Esempio n. 41
0
    def test_union_all(self):
        e = union_all(select([t1.c.col3]), union(select([t1.c.col3]), select([t1.c.col3])))

        wanted = [("aaa",), ("aaa",), ("bbb",), ("bbb",), ("ccc",), ("ccc",)]
        found1 = self._fetchall_sorted(e.execute())
        eq_(found1, wanted)

        found2 = self._fetchall_sorted(e.alias("foo").select().execute())
        eq_(found2, wanted)
Esempio n. 42
0
    def test_union_ordered_alias(self):
        (s1, s2) = (
            select([t1.c.col3.label("col3"), t1.c.col4.label("col4")], t1.c.col2.in_(["t1col2r1", "t1col2r2"])),
            select([t2.c.col3.label("col3"), t2.c.col4.label("col4")], t2.c.col2.in_(["t2col2r2", "t2col2r3"])),
        )
        u = union(s1, s2, order_by=["col3", "col4"])

        wanted = [("aaa", "aaa"), ("bbb", "bbb"), ("bbb", "ccc"), ("ccc", "aaa")]
        eq_(u.alias("bar").select().execute().fetchall(), wanted)
Esempio n. 43
0
    def test_union_homogeneous(self):
        emp, dep, conn = self._emp_d_fixture()

        s1 = select([emp.c.name]).where(emp.c.name == "wendy")
        s2 = select([emp.c.name
                     ]).where(or_(emp.c.name == 'wendy', emp.c.name == 'jack'))
        u1 = union(s1, s2)
        r = self._exec_stmt(conn, u1)
        eq_(r.fetchall(), [('wendy', ), ('jack', )])
Esempio n. 44
0
        def test_limit_render_multiple_times(self, connection):
            table = self.tables.some_table
            stmt = select(table.c.id).order_by(table.c.id).limit(1).scalar_subquery()

            u = sqlalchemy.union(select(stmt), select(stmt)).subquery().select()

            self._assert_result(
                connection, u, [(1,)],
            )
Esempio n. 45
0
 def test_compound(self):
     t1 = table('t1', column('c1'), column('c2'), column('c3'))
     t2 = table('t2', column('c1'), column('c2'), column('c3'))
     self.assert_compile(union(t1.select(), t2.select()),
                         'SELECT t1.c1, t1.c2, t1.c3 FROM t1 UNION '
                         'SELECT t2.c1, t2.c2, t2.c3 FROM t2')
     self.assert_compile(except_(t1.select(), t2.select()),
                         'SELECT t1.c1, t1.c2, t1.c3 FROM t1 MINUS '
                         'SELECT t2.c1, t2.c2, t2.c3 FROM t2')
        def __select_all__(cls):
            two_values = sqlalchemy.orm.Query(
                sqlalchemy.union(
                    sqlalchemy.select([1]),
                    sqlalchemy.select([2]),
                )).subquery()

            return super(Label, cls).__select_all__() \
                .join(two_values, sqlalchemy.literal(True))
Esempio n. 47
0
    def test_limit_offset_aliased_selectable_in_unions(self):
        table = self.tables.some_table
        s1 = select([table]).where(table.c.id == 2).\
            limit(1).order_by(table.c.id).alias().select()
        s2 = select([table]).where(table.c.id == 3).\
            limit(1).order_by(table.c.id).alias().select()

        u1 = union(s1, s2).limit(2)
        self._assert_result(u1.order_by(u1.c.id), [(2, 2, 3), (3, 3, 4)])
Esempio n. 48
0
    def test_union_heterogeneous_types(self):
        emp, dep, conn = self._emp_d_fixture()

        s1 = select([emp.c.name, emp.c.fullname]).where(emp.c.name == 'jack')
        s2 = select([dep.c.dep_id, dep.c.name])
        u1 = union(s1, s2)
        r = self._exec_stmt(conn, u1)
        eq_(r.fetchall(), [('jack', 'Jack Smith'), (1, 'Engineering'),
                           (2, 'Accounting'), (3, 'Sales')])
Esempio n. 49
0
def test_limit_offset_aliased_selectable_in_unions(faux_conn):
    table = some_table(faux_conn)
    s1 = (select([table]).where(table.c.id == 2).limit(1).order_by(
        table.c.id).alias().select())
    s2 = (select([table]).where(table.c.id == 3).limit(1).order_by(
        table.c.id).alias().select())

    u1 = union(s1, s2).limit(2)
    assert_result(faux_conn, u1.order_by(u1.c.id), [(2, 2, 3), (3, 3, 4)])
Esempio n. 50
0
def test_date(session):
    dates = (
        date(2016, 1, 1),
        date(2016, 1, 2),
    )
    selects = tuple(sa.select((MakeADate(d),)) for d in dates)
    data = sa.alias(sa.union(*selects, use_labels=True), 'dates')
    stmt = sa.select((data,))
    result = session.execute(stmt).fetchall()
    assert tuple(chain.from_iterable(result)) == dates
Esempio n. 51
0
 def test_union_column(self):
     s1 = select([table1])
     s2 = select([table1])
     stmt = union(s1, s2).order_by("name")
     self.assert_compile(
         stmt,
         "SELECT mytable.myid, mytable.name, mytable.description FROM "
         "mytable UNION SELECT mytable.myid, mytable.name, "
         "mytable.description FROM mytable ORDER BY name",
     )
Esempio n. 52
0
    def test_except_style1(self):
        e = except_(
            union(select([t1.c.col3, t1.c.col4]), select([t2.c.col3, t2.c.col4]), select([t3.c.col3, t3.c.col4])),
            select([t2.c.col3, t2.c.col4]),
        )

        wanted = [("aaa", "aaa"), ("aaa", "ccc"), ("bbb", "aaa"), ("bbb", "bbb"), ("ccc", "bbb"), ("ccc", "ccc")]

        found = self._fetchall_sorted(e.alias().select().execute())
        eq_(found, wanted)
Esempio n. 53
0
    def test_select_from_plain_union(self):
        table = self.tables.some_table
        s1 = select([table]).where(table.c.id == 2)
        s2 = select([table]).where(table.c.id == 3)

        u1 = union(s1, s2).alias().select()
        self._assert_result(
            u1.order_by(u1.c.id),
            [(2, 2, 3), (3, 3, 4)]
        )
Esempio n. 54
0
    def list_worksheets(self, user_id=None):
        '''
        Return a list of row dicts, one per worksheet. These dicts do NOT contain
        ALL worksheet items; this method is meant to make it easy for a user to see
        their existing worksheets.
        '''
        cols_to_select = [cl_worksheet.c.id,
                          cl_worksheet.c.uuid,
                          cl_worksheet.c.name,
                          cl_worksheet.c.owner_id,
                          cl_group_object_permission.c.permission]
        cols1 = cols_to_select[:4]
        cols1.extend([literal(GROUP_OBJECT_PERMISSION_ALL).label('permission')])
        if user_id == self.root_user_id:
            # query all worksheets
            stmt = select(cols1)
        elif user_id is None:
            # query for public worksheets (only used by the webserver when user is not logged in)
            stmt = select(cols_to_select).\
                where(cl_worksheet.c.uuid == cl_group_object_permission.c.object_uuid).\
                where(cl_group_object_permission.c.group_uuid == self.public_group_uuid)
        else:
            # 1) Worksheets owned by owner_id
            stmt1 = select(cols1).where(cl_worksheet.c.owner_id == user_id)

            # 2) Worksheets visible to owner_id or co-owned by owner_id
            stmt2_groups = select([cl_user_group.c.group_uuid]).\
                where(cl_user_group.c.user_id == user_id)
            # List worksheets where one of our groups has permission.
            stmt2 = select(cols_to_select).\
                where(cl_worksheet.c.uuid == cl_group_object_permission.c.object_uuid).\
                where(or_(
                    cl_group_object_permission.c.group_uuid.in_(stmt2_groups),
                    cl_group_object_permission.c.group_uuid == self.public_group_uuid)).\
                where(cl_worksheet.c.owner_id != user_id)  # Avoid duplicates

            stmt = union(stmt1, stmt2)

        with self.engine.begin() as connection:
            rows = connection.execute(stmt).fetchall()
            if not rows:
                return []

        # Get permissions of the worksheets
        worksheet_uuids = [row.uuid for row in rows]
        uuid_group_permissions = dict(zip(worksheet_uuids, self.batch_get_group_permissions(worksheet_uuids)))

        # Put the permissions into the worksheets
        row_dicts = []
        for row in sorted(rows, key=lambda item: item['id']):
            row = dict(row)
            row['group_permissions'] = uuid_group_permissions[row['uuid']]
            row_dicts.append(row)

        return row_dicts
Esempio n. 55
0
    def test_composite_alias(self):
        ua = intersect(
            select([t2.c.col3, t2.c.col4]),
            union(select([t1.c.col3, t1.c.col4]), select([t2.c.col3, t2.c.col4]), select([t3.c.col3, t3.c.col4]))
            .alias()
            .select(),
        ).alias()

        wanted = [("aaa", "bbb"), ("bbb", "ccc"), ("ccc", "aaa")]
        found = self._fetchall_sorted(ua.select().execute())
        eq_(found, wanted)
Esempio n. 56
0
    def test_except_style1(self):
        e = except_(union(
            select([t1.c.col3, t1.c.col4]),
            select([t2.c.col3, t2.c.col4]),
            select([t3.c.col3, t3.c.col4]),
        ), select([t2.c.col3, t2.c.col4]))

        wanted = [('aaa', 'aaa'), ('aaa', 'ccc'), ('bbb', 'aaa'),
                  ('bbb', 'bbb'), ('ccc', 'bbb'), ('ccc', 'ccc')]

        found = self._fetchall_sorted(e.alias().select().execute())
        eq_(found, wanted)
Esempio n. 57
0
    def test_limit_offset_aliased_selectable_in_unions(self):
        table = self.tables.some_table
        s1 = select([table]).where(table.c.id == 2).\
            limit(1).order_by(table.c.id).alias().select()
        s2 = select([table]).where(table.c.id == 3).\
            limit(1).order_by(table.c.id).alias().select()

        u1 = union(s1, s2).limit(2)
        self._assert_result(
            u1.order_by(u1.c.id),
            [(2, 2, 3), (3, 3, 4)]
        )
Esempio n. 58
0
    def test_distinct_selectable_in_unions(self):
        table = self.tables.some_table
        s1 = select([table]).where(table.c.id == 2).\
            distinct()
        s2 = select([table]).where(table.c.id == 3).\
            distinct()

        u1 = union(s1, s2).limit(2)
        self._assert_result(
            u1.order_by(u1.c.id),
            [(2, 2, 3), (3, 3, 4)]
        )
Esempio n. 59
0
    def test_b_ab1_union_b_ab2(self):
        j1 = a.join(b1)
        j2 = a.join(b2)

        b_j1 = b.join(j1)
        b_j2 = b.join(j2)

        s = union(
            select([b_j1], use_labels=True), select([b_j2], use_labels=True)
        ).select(use_labels=True)

        self._test(s, self._b_ab1_union_c_ab2)