Here are the examples of the python api sqlalchemy.union_all taken from open source projects. By voting up you can indicate which examples are most useful and appropriate.
4 Examples
3
Example 1
Project: glottolog3 Source File: check_db_consistency.py
def invalid_query(self, session, **kw):
cte = session.query(
Languoid.pk, Languoid.pk.label('father_pk'), literal(0).label('depth'))\
.cte(recursive=True)
parent = orm.aliased(Languoid)
cte = cte.union_all(session.query(cte.c.pk, parent.father_pk, cte.c.depth + 1)
.join(parent, cte.c.father_pk == parent.pk)
.filter(parent.father_pk != None)) # noqa
tree1 = session.query(
TreeClosureTable.child_pk,
TreeClosureTable.parent_pk,
TreeClosureTable.depth)
tree2 = session.query(cte.c.pk, cte.c.father_pk, cte.c.depth)
diff = union_all(tree1.except_all(tree2), tree2.except_all(tree1))
return session.query(diff.alias())
0
Example 2
Project: sqlalchemy Source File: test_query.py
@testing.crashes('oracle', 'FIXME: unknown, verify not fails_on')
@testing.fails_on(
'firebird',
"has trouble extracting anonymous column from union subquery")
@testing.fails_on('mysql', 'FIXME: unknown')
@testing.fails_on('sqlite', 'FIXME: unknown')
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)
0
Example 3
Project: sqlalchemy Source File: test_query.py
def test_union_all_lightweight(self):
"""like test_union_all, but breaks the sub-union into
a subquery with an explicit column reference on the outside,
more palatable to a wider variety of engines.
"""
u = union(
select([t1.c.col3]),
select([t1.c.col3]),
).alias()
e = union_all(
select([t1.c.col3]),
select([u.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)
0
Example 4
Project: glottolog3 Source File: util.py
def glottocode(name, conn, codes=None):
letters = slug(name)[:4].ljust(4, 'a')
active = select([cast(func.substring(Languoid.id, 5), Integer).label('number')])\
.where(Languoid.id.startswith(letters))
legacy = select([cast(func.substring(LegacyCode.id, 5), Integer).label('number')])\
.where(LegacyCode.id.startswith(letters))
if not codes:
known = union_all(active, legacy)
else:
dirty = select([cast(func.substring(literal_column('dirty'), 5), Integer).label('number')])\
.select_from(func.unnest(list(codes)).alias('dirty'))\
.where(literal_column('dirty').startswith(letters))
known = union_all(active, legacy, dirty)
number = conn.execute(select([func.coalesce(func.max(literal_column('number') + 1), 1234)])\
.select_from(known.alias())).scalar()
number = str(number)
assert len(number) == 4
res = letters + number
assert GLOTTOCODE_PATTERN.match(res)
if codes is not None:
codes[res] = True
return res