Here are the examples of the python api sqlalchemy.intersect taken from open source projects. By voting up you can indicate which examples are most useful and appropriate.
11 Examples
3
Source : test_query.py
with Apache License 2.0
from gethue
with Apache License 2.0
from gethue
def test_intersect(self):
i = intersect(
select([t2.c.col3, t2.c.col4]),
select([t2.c.col3, t2.c.col4], t2.c.col4 == t3.c.col3),
)
wanted = [("aaa", "bbb"), ("bbb", "ccc"), ("ccc", "aaa")]
found1 = self._fetchall_sorted(i.execute())
eq_(found1, wanted)
found2 = self._fetchall_sorted(i.alias("bar").select().execute())
eq_(found2, wanted)
@testing.requires.except_
3
Source : test_query.py
with Apache License 2.0
from gethue
with Apache License 2.0
from gethue
def test_intersect_unions(self):
u = intersect(
union(
select([t1.c.col3, t1.c.col4]), select([t3.c.col3, t3.c.col4])
),
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)
@testing.requires.intersect
3
Source : test_query.py
with Apache License 2.0
from gethue
with Apache License 2.0
from gethue
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)
@testing.requires.intersect
3
Source : test_query.py
with Apache License 2.0
from gethue
with Apache License 2.0
from gethue
def test_intersect_unions_3(self):
u = 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(),
)
wanted = [("aaa", "bbb"), ("bbb", "ccc"), ("ccc", "aaa")]
found = self._fetchall_sorted(u.execute())
eq_(found, wanted)
@testing.requires.intersect
3
Source : test_query.py
with MIT License
from sqlalchemy
with MIT License
from sqlalchemy
def test_intersect(self, connection):
t1, t2, t3 = self.tables("t1", "t2", "t3")
i = intersect(
select(t2.c.col3, t2.c.col4),
select(t2.c.col3, t2.c.col4).where(t2.c.col4 == t3.c.col3),
)
wanted = [("aaa", "bbb"), ("bbb", "ccc"), ("ccc", "aaa")]
found1 = self._fetchall_sorted(connection.execute(i))
eq_(found1, wanted)
found2 = self._fetchall_sorted(
connection.execute(i.alias("bar").select())
)
eq_(found2, wanted)
@testing.requires.except_
3
Source : test_query.py
with MIT License
from sqlalchemy
with MIT License
from sqlalchemy
def test_intersect_unions(self, connection):
t1, t2, t3 = self.tables("t1", "t2", "t3")
u = intersect(
union(select(t1.c.col3, t1.c.col4), select(t3.c.col3, t3.c.col4)),
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)
@testing.requires.intersect
3
Source : test_query.py
with MIT License
from sqlalchemy
with MIT License
from sqlalchemy
def test_intersect_unions_2(self, connection):
t1, t2, t3 = self.tables("t1", "t2", "t3")
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)
@testing.requires.intersect
3
Source : test_query.py
with MIT License
from sqlalchemy
with MIT License
from sqlalchemy
def test_intersect_unions_3(self, connection):
t1, t2, t3 = self.tables("t1", "t2", "t3")
u = 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(),
)
wanted = [("aaa", "bbb"), ("bbb", "ccc"), ("ccc", "aaa")]
found = self._fetchall_sorted(connection.execute(u))
eq_(found, wanted)
@testing.requires.intersect
0
Source : test_compiler.py
with Apache License 2.0
from gethue
with Apache License 2.0
from gethue
def test_compound_grouping(self):
s = select([column("foo"), column("bar")]).select_from(text("bat"))
self.assert_compile(
union(union(union(s, s), s), s),
"((SELECT foo, bar FROM bat UNION SELECT foo, bar FROM bat) "
"UNION SELECT foo, bar FROM bat) UNION SELECT foo, bar FROM bat",
)
self.assert_compile(
union(s, s, s, s),
"SELECT foo, bar FROM bat UNION SELECT foo, bar "
"FROM bat UNION SELECT foo, bar FROM bat "
"UNION SELECT foo, bar FROM bat",
)
self.assert_compile(
union(s, union(s, union(s, s))),
"SELECT foo, bar FROM bat UNION (SELECT foo, bar FROM bat "
"UNION (SELECT foo, bar FROM bat "
"UNION SELECT foo, bar FROM bat))",
)
self.assert_compile(
select([s.alias()]),
"SELECT anon_1.foo, anon_1.bar FROM "
"(SELECT foo, bar FROM bat) AS anon_1",
)
self.assert_compile(
select([union(s, s).alias()]),
"SELECT anon_1.foo, anon_1.bar FROM "
"(SELECT foo, bar FROM bat UNION "
"SELECT foo, bar FROM bat) AS anon_1",
)
self.assert_compile(
select([except_(s, s).alias()]),
"SELECT anon_1.foo, anon_1.bar FROM "
"(SELECT foo, bar FROM bat EXCEPT "
"SELECT foo, bar FROM bat) AS anon_1",
)
# this query sqlite specifically chokes on
self.assert_compile(
union(except_(s, s), s),
"(SELECT foo, bar FROM bat EXCEPT SELECT foo, bar FROM bat) "
"UNION SELECT foo, bar FROM bat",
)
self.assert_compile(
union(s, except_(s, s)),
"SELECT foo, bar FROM bat "
"UNION (SELECT foo, bar FROM bat EXCEPT SELECT foo, bar FROM bat)",
)
# this solves it
self.assert_compile(
union(except_(s, s).alias().select(), s),
"SELECT anon_1.foo, anon_1.bar FROM "
"(SELECT foo, bar FROM bat EXCEPT "
"SELECT foo, bar FROM bat) AS anon_1 "
"UNION SELECT foo, bar FROM bat",
)
self.assert_compile(
except_(union(s, s), union(s, s)),
"(SELECT foo, bar FROM bat UNION SELECT foo, bar FROM bat) "
"EXCEPT (SELECT foo, bar FROM bat UNION SELECT foo, bar FROM bat)",
)
s2 = union(s, s)
s3 = union(s2, s2)
self.assert_compile(
s3,
"(SELECT foo, bar FROM bat "
"UNION SELECT foo, bar FROM bat) "
"UNION (SELECT foo, bar FROM bat "
"UNION SELECT foo, bar FROM bat)",
)
self.assert_compile(
union(intersect(s, s), intersect(s, s)),
"(SELECT foo, bar FROM bat INTERSECT SELECT foo, bar FROM bat) "
"UNION (SELECT foo, bar FROM bat INTERSECT "
"SELECT foo, bar FROM bat)",
)
# tests for [ticket:2528]
# sqlite hates all of these.
self.assert_compile(
union(s.limit(1), s.offset(2)),
"(SELECT foo, bar FROM bat LIMIT :param_1) "
"UNION (SELECT foo, bar FROM bat LIMIT -1 OFFSET :param_2)",
)
self.assert_compile(
union(s.order_by(column("bar")), s.offset(2)),
"(SELECT foo, bar FROM bat ORDER BY bar) "
"UNION (SELECT foo, bar FROM bat LIMIT -1 OFFSET :param_1)",
)
self.assert_compile(
union(s.limit(1).alias("a"), s.limit(2).alias("b")),
"(SELECT foo, bar FROM bat LIMIT :param_1) "
"UNION (SELECT foo, bar FROM bat LIMIT :param_2)",
)
self.assert_compile(
union(s.limit(1).self_group(), s.limit(2).self_group()),
"(SELECT foo, bar FROM bat LIMIT :param_1) "
"UNION (SELECT foo, bar FROM bat LIMIT :param_2)",
)
self.assert_compile(
union(s.limit(1), s.limit(2).offset(3)).alias().select(),
"SELECT anon_1.foo, anon_1.bar FROM "
"((SELECT foo, bar FROM bat LIMIT :param_1) "
"UNION (SELECT foo, bar FROM bat LIMIT :param_2 OFFSET :param_3)) "
"AS anon_1",
)
# this version works for SQLite
self.assert_compile(
union(s.limit(1).alias().select(), s.offset(2).alias().select()),
"SELECT anon_1.foo, anon_1.bar "
"FROM (SELECT foo, bar FROM bat"
" LIMIT :param_1) AS anon_1 "
"UNION SELECT anon_2.foo, anon_2.bar "
"FROM (SELECT foo, bar "
"FROM bat"
" LIMIT -1 OFFSET :param_2) AS anon_2",
)
def test_cast(self):
0
Source : query.py
with GNU General Public License v3.0
from indralab
with GNU General Public License v3.0
from indralab
def _merge(*queries):
return intersect(*queries)
def _get_table(self, ro):
0
Source : test_compiler.py
with MIT License
from sqlalchemy
with MIT License
from sqlalchemy
def test_compound_grouping(self):
s = select(column("foo"), column("bar")).select_from(text("bat"))
self.assert_compile(
union(union(union(s, s), s), s),
"((SELECT foo, bar FROM bat UNION SELECT foo, bar FROM bat) "
"UNION SELECT foo, bar FROM bat) UNION SELECT foo, bar FROM bat",
)
self.assert_compile(
union(s, s, s, s),
"SELECT foo, bar FROM bat UNION SELECT foo, bar "
"FROM bat UNION SELECT foo, bar FROM bat "
"UNION SELECT foo, bar FROM bat",
)
self.assert_compile(
union(s, union(s, union(s, s))),
"SELECT foo, bar FROM bat UNION (SELECT foo, bar FROM bat "
"UNION (SELECT foo, bar FROM bat "
"UNION SELECT foo, bar FROM bat))",
)
self.assert_compile(
select(s.alias()),
"SELECT anon_1.foo, anon_1.bar FROM "
"(SELECT foo, bar FROM bat) AS anon_1",
)
self.assert_compile(
select(union(s, s).alias()),
"SELECT anon_1.foo, anon_1.bar FROM "
"(SELECT foo, bar FROM bat UNION "
"SELECT foo, bar FROM bat) AS anon_1",
)
self.assert_compile(
select(except_(s, s).alias()),
"SELECT anon_1.foo, anon_1.bar FROM "
"(SELECT foo, bar FROM bat EXCEPT "
"SELECT foo, bar FROM bat) AS anon_1",
)
# this query sqlite specifically chokes on
self.assert_compile(
union(except_(s, s), s),
"(SELECT foo, bar FROM bat EXCEPT SELECT foo, bar FROM bat) "
"UNION SELECT foo, bar FROM bat",
)
self.assert_compile(
union(s, except_(s, s)),
"SELECT foo, bar FROM bat "
"UNION (SELECT foo, bar FROM bat EXCEPT SELECT foo, bar FROM bat)",
)
# this solves it
self.assert_compile(
union(except_(s, s).alias().select(), s),
"SELECT anon_1.foo, anon_1.bar FROM "
"(SELECT foo, bar FROM bat EXCEPT "
"SELECT foo, bar FROM bat) AS anon_1 "
"UNION SELECT foo, bar FROM bat",
)
self.assert_compile(
except_(union(s, s), union(s, s)),
"(SELECT foo, bar FROM bat UNION SELECT foo, bar FROM bat) "
"EXCEPT (SELECT foo, bar FROM bat UNION SELECT foo, bar FROM bat)",
)
s2 = union(s, s)
s3 = union(s2, s2)
self.assert_compile(
s3,
"(SELECT foo, bar FROM bat "
"UNION SELECT foo, bar FROM bat) "
"UNION (SELECT foo, bar FROM bat "
"UNION SELECT foo, bar FROM bat)",
)
self.assert_compile(
union(intersect(s, s), intersect(s, s)),
"(SELECT foo, bar FROM bat INTERSECT SELECT foo, bar FROM bat) "
"UNION (SELECT foo, bar FROM bat INTERSECT "
"SELECT foo, bar FROM bat)",
)
# tests for [ticket:2528]
# sqlite hates all of these.
self.assert_compile(
union(s.limit(1), s.offset(2)),
"(SELECT foo, bar FROM bat LIMIT :param_1) "
"UNION (SELECT foo, bar FROM bat LIMIT -1 OFFSET :param_2)",
)
self.assert_compile(
union(s.order_by(column("bar")), s.offset(2)),
"(SELECT foo, bar FROM bat ORDER BY bar) "
"UNION (SELECT foo, bar FROM bat LIMIT -1 OFFSET :param_1)",
)
self.assert_compile(
union(
s.limit(1).alias("a").element, s.limit(2).alias("b").element
),
"(SELECT foo, bar FROM bat LIMIT :param_1) "
"UNION (SELECT foo, bar FROM bat LIMIT :param_2)",
)
self.assert_compile(
union(s.limit(1).self_group(), s.limit(2).self_group()),
"(SELECT foo, bar FROM bat LIMIT :param_1) "
"UNION (SELECT foo, bar FROM bat LIMIT :param_2)",
)
self.assert_compile(
union(s.limit(1), s.limit(2).offset(3)).alias().select(),
"SELECT anon_1.foo, anon_1.bar FROM "
"((SELECT foo, bar FROM bat LIMIT :param_1) "
"UNION (SELECT foo, bar FROM bat LIMIT :param_2 OFFSET :param_3)) "
"AS anon_1",
)
# this version works for SQLite
self.assert_compile(
union(s.limit(1).alias().select(), s.offset(2).alias().select()),
"SELECT anon_1.foo, anon_1.bar "
"FROM (SELECT foo, bar FROM bat"
" LIMIT :param_1) AS anon_1 "
"UNION SELECT anon_2.foo, anon_2.bar "
"FROM (SELECT foo, bar "
"FROM bat"
" LIMIT -1 OFFSET :param_2) AS anon_2",
)
def test_cast(self):