Python sqlalchemy.union方法代码示例

# 需要导入模块: import sqlalchemy [as 别名]
# 或者: from sqlalchemy import union [as 别名]
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 

# 需要导入模块: import sqlalchemy [as 别名]
# 或者: from sqlalchemy import union [as 别名]
def activity_conversion(s, source_ids, null):
    # convert the query that gives any source id and select either those that are activities directly,
    # or activities associated with a topic (eg user entered activity notes)

    # for most queries, we have some source IDs and we want to know if they are activityjournal ids
    # (which we pass through) or activitytopicjournal ids (in which case we convert to activityjournal).
    source_ids = source_ids.cte()
    q_direct = s.query(ActivityJournal.id). \
        filter(ActivityJournal.id.in_(source_ids))
    q_via_topic = s.query(ActivityJournal.id). \
        join(FileHash). \
        join(ActivityTopicJournal). \
        filter(ActivityTopicJournal.id.in_(source_ids))
    q = aliased(union(q_direct, q_via_topic)).select()

    if null:
        # for 'is null' queries we are really asking if the data are missing (since values are not null constrained)
        # so we find what does exist and then invert it.  that inversion has to happen avter conversion
        return s.query(ActivityJournal.id).filter(not_(ActivityJournal.id.in_(q)))
    else:
        return q 

# 需要导入模块: import sqlalchemy [as 别名]
# 或者: from sqlalchemy import union [as 别名]
def test_chained_node(self):
        '''
        we can move one step along the chain by looking for nodes whose inputs will be deleted.
        '''
        q_counts = self.session.query(Node.id.label('id'), count(Connect.input_id).label('count')). \
            outerjoin(Connect, Node.id == Connect.output_id). \
            group_by(Node.id).order_by(Node.id).subquery()
        q_missing = self.session.query(Node.id.label('id')). \
            join(q_counts, q_counts.c.id == Node.id). \
            filter(Node.n_input != q_counts.c.count)
        q_chained = self.session.query(Node.id). \
            join(Connect, Node.id == Connect.output_id). \
            filter(Connect.input_id.in_(q_missing))
        q_all = union(q_missing, q_chained)
        print('\nchained node\n%s\n' % q_all.select())
        self.assertEqual([(5,), (7,)],
                         self.session.query(Node.id).filter(Node.id.in_(q_all.select())).order_by(Node.id).all()) 

# 需要导入模块: import sqlalchemy [as 别名]
# 或者: from sqlalchemy import union [as 别名]
def test_direct_correspondence_on_labels(self):
        # this test depends on labels being part
        # of the proxy set to get the right result

        l1, l2 = table1.c.col1.label("foo"), table1.c.col1.label("bar")
        sel = select([l1, l2])

        sel2 = sel.alias()
        assert sel2.corresponding_column(l1) is sel2.c.foo
        assert sel2.corresponding_column(l2) is sel2.c.bar

        sel2 = select([table1.c.col1.label("foo"), table1.c.col2.label("bar")])

        sel3 = sel.union(sel2).alias()
        assert sel3.corresponding_column(l1) is sel3.c.foo
        assert sel3.corresponding_column(l2) is sel3.c.bar 

# 需要导入模块: import sqlalchemy [as 别名]
# 或者: from sqlalchemy import union [as 别名]
def test_union_precedence(self):
        # conflicting column correspondence should be resolved based on
        # the order of the select()s in the union

        s1 = select([table1.c.col1, table1.c.col2])
        s2 = select([table1.c.col2, table1.c.col1])
        s3 = select([table1.c.col3, table1.c.colx])
        s4 = select([table1.c.colx, table1.c.col3])

        u1 = union(s1, s2).subquery()
        assert u1.corresponding_column(table1.c.col1) is u1.c.col1
        assert u1.corresponding_column(table1.c.col2) is u1.c.col2

        u1 = union(s1, s2, s3, s4).subquery()
        assert u1.corresponding_column(table1.c.col1) is u1.c.col1
        assert u1.corresponding_column(table1.c.col2) is u1.c.col2
        assert u1.corresponding_column(table1.c.colx) is u1.c.col2
        assert u1.corresponding_column(table1.c.col3) is u1.c.col1 

# 需要导入模块: import sqlalchemy [as 别名]
# 或者: from sqlalchemy import union [as 别名]
def test_foo(self):
        s1 = select([table1.c.col1, table1.c.col2])
        s2 = select([table1.c.col2, table1.c.col1])

        u1 = union(s1, s2).subquery()
        assert u1.corresponding_column(table1.c.col2) is u1.c.col2

        metadata = MetaData()
        table1_new = Table(
            "table1",
            metadata,
            Column("col1", Integer, primary_key=True),
            Column("col2", String(20)),
            Column("col3", Integer),
            Column("colx", Integer),
        )
        # table1_new = table1

        s1 = select([table1_new.c.col1, table1_new.c.col2])
        s2 = select([table1_new.c.col2, table1_new.c.col1])
        u1 = union(s1, s2).subquery()

        # TODO: failing due to proxy_set not correct
        assert u1.corresponding_column(table1_new.c.col2) is u1.c.col2 

# 需要导入模块: import sqlalchemy [as 别名]
# 或者: from sqlalchemy import union [as 别名]
def test_annotate_varied_annot_same_col(self):
        """test two instances of the same column with different annotations
        preserving them when deep_annotate is run on them.

        """
        t1 = table("table1", column("col1"), column("col2"))
        s = select([t1.c.col1._annotate({"foo": "bar"})])
        s2 = select([t1.c.col1._annotate({"bat": "hoho"})])
        s3 = s.union(s2)
        sel = sql_util._deep_annotate(s3, {"new": "thing"})

        eq_(
            sel.selects[0]._raw_columns[0]._annotations,
            {"foo": "bar", "new": "thing"},
        )

        eq_(
            sel.selects[1]._raw_columns[0]._annotations,
            {"bat": "hoho", "new": "thing"},
        ) 

# 需要导入模块: import sqlalchemy [as 别名]
# 或者: from sqlalchemy import union [as 别名]
def test_union_ordered(self, connection):
        (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_(connection.execute(u).fetchall(), wanted) 

# 需要导入模块: import sqlalchemy [as 别名]
# 或者: from sqlalchemy import union [as 别名]
def test_union_ordered_alias(self, connection):
        (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_(connection.execute(u.alias("bar").select()).fetchall(), wanted) 

# 需要导入模块: import sqlalchemy [as 别名]
# 或者: from sqlalchemy import union [as 别名]
def test_except_style1(self, connection):
        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(connection.execute(e.alias().select()))
        eq_(found, wanted) 

# 需要导入模块: import sqlalchemy [as 别名]
# 或者: from sqlalchemy import union [as 别名]
def test_intersect_unions_2(self, connection):
        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(connection.execute(u))

        eq_(found, wanted) 

# 需要导入模块: import sqlalchemy [as 别名]
# 或者: from sqlalchemy import union [as 别名]
def test_label_conflict_union(self):
        t1 = Table(
            "t1", MetaData(), Column("a", Integer), Column("b", Integer)
        )
        t2 = Table("t2", MetaData(), Column("t1_a", Integer))
        union = select([t2]).union(select([t2])).alias()

        t1_alias = t1.alias()
        stmt = (
            select([t1, t1_alias])
            .select_from(t1.join(union, t1.c.a == union.c.t1_a))
            .apply_labels()
        )
        comp = stmt.compile()
        eq_(
            set(comp._create_result_map()),
            set(["t1_1_b", "t1_1_a", "t1_a", "t1_b"]),
        )
        is_(comp._create_result_map()["t1_a"][1][2], t1.c.a) 

# 需要导入模块: import sqlalchemy [as 别名]
# 或者: from sqlalchemy import union [as 别名]
def test_correlate_to_union_newstyle(self):
        User = self.classes.User

        q = future_select(User).apply_labels()

        q = future_select(User).union(q).apply_labels().subquery()

        u_alias = aliased(User)

        raw_subq = exists().where(u_alias.id > q.c[0])

        self.assert_compile(
            future_select(q, raw_subq).apply_labels(),
            "SELECT anon_1.users_id AS anon_1_users_id, "
            "anon_1.users_name AS anon_1_users_name, "
            "EXISTS (SELECT * FROM users AS users_1 "
            "WHERE users_1.id > anon_1.users_id) AS anon_2 "
            "FROM ("
            "SELECT users.id AS users_id, users.name AS users_name FROM users "
            "UNION SELECT users.id AS users_id, users.name AS users_name "
            "FROM users) AS anon_1",
        ) 

# 需要导入模块: import sqlalchemy [as 别名]
# 或者: from sqlalchemy import union [as 别名]
def test_anonymous_expression_oldstyle(self):
        # relies upon _orm_only_from_obj_alias setting
        from sqlalchemy.sql import column

        sess = create_session()
        c1, c2 = column("c1"), column("c2")
        q1 = sess.query(c1, c2).filter(c1 == "dog")
        q2 = sess.query(c1, c2).filter(c1 == "cat")
        q3 = q1.union(q2)
        self.assert_compile(
            q3.order_by(c1),
            "SELECT anon_1.c1 AS anon_1_c1, anon_1.c2 "
            "AS anon_1_c2 FROM (SELECT c1, c2 WHERE "
            "c1 = :c1_1 UNION SELECT c1, c2 "
            "WHERE c1 = :c1_2) AS anon_1 ORDER BY anon_1.c1",
        ) 

# 需要导入模块: import sqlalchemy [as 别名]
# 或者: from sqlalchemy import union [as 别名]
def test_anonymous_expression_newstyle(self):
        from sqlalchemy.sql import column

        c1, c2 = column("c1"), column("c2")
        q1 = future_select(c1, c2).where(c1 == "dog")
        q2 = future_select(c1, c2).where(c1 == "cat")
        subq = q1.union(q2).subquery()
        q3 = future_select(subq).apply_labels()

        self.assert_compile(
            q3.order_by(subq.c.c1),
            "SELECT anon_1.c1 AS anon_1_c1, anon_1.c2 "
            "AS anon_1_c2 FROM (SELECT c1, c2 WHERE "
            "c1 = :c1_1 UNION SELECT c1, c2 "
            "WHERE c1 = :c1_2) AS anon_1 ORDER BY anon_1.c1",
        )