def test_join_to_join_entities(self):
sess = create_session()
pa = with_polymorphic(Person, [Engineer])
pa_alias = with_polymorphic(Person, [Engineer], aliased=True)
eq_(
[
(p1.name, type(p1), p2.name, type(p2))
for (p1, p2) in sess.query(pa, pa_alias)
.join(
pa_alias,
or_(
pa.Engineer.primary_language
== pa_alias.Engineer.primary_language,
and_(
pa.Engineer.primary_language == None, # noqa
pa_alias.Engineer.primary_language == None,
pa.person_id > pa_alias.person_id,
),
),
)
.order_by(pa.name, pa_alias.name)
],
[
("dilbert", Engineer, "dilbert", Engineer),
("dogbert", Manager, "pointy haired boss", Boss),
("vlad", Engineer, "vlad", Engineer),
("wally", Engineer, "wally", Engineer),
],
)
def test_withoutjoinedload(self):
Thing, tests, options = (
self.classes.Thing,
self.tables.tests,
self.tables.options,
)
s = create_session()
result = (
s.query(Thing).select_from(
tests.outerjoin(
options,
sa.and_(
tests.c.id == options.c.test_id,
tests.c.owner_id == options.c.owner_id,
),
)).filter(
sa.and_(
tests.c.owner_id == 1,
sa.or_(
options.c.someoption == None, # noqa
options.c.someoption == False,
),
)))
result_str = ["%d %s" % (t.id, t.category.name) for t in result]
eq_(result_str, ["1 Some Category", "3 Some Category"])
def test_withjoinedload(self):
"""
Test that an joinedload locates the correct "from" clause with which to
attach to, when presented with a query that already has a complicated
from clause.
"""
Thing, tests, options = (
self.classes.Thing,
self.tables.tests,
self.tables.options,
)
s = create_session()
q = s.query(Thing).options(sa.orm.joinedload("category"))
result = q.select_from(
tests.outerjoin(
options,
sa.and_(
tests.c.id == options.c.test_id,
tests.c.owner_id == options.c.owner_id,
),
)).filter(
sa.and_(
tests.c.owner_id == 1,
sa.or_(
options.c.someoption == None,
options.c.someoption == False, # noqa
),
))
result_str = ["%d %s" % (t.id, t.category.name) for t in result]
eq_(result_str, ["1 Some Category", "3 Some Category"])
def test_join_to_join_columns(self):
sess = create_session()
pa = with_polymorphic(Person, [Engineer])
pa_alias = with_polymorphic(Person, [Engineer], aliased=True)
eq_(
[
row
for row in sess.query(
pa.name,
pa.Engineer.primary_language,
pa_alias.name,
pa_alias.Engineer.primary_language,
)
.join(
pa_alias,
or_(
pa.Engineer.primary_language
== pa_alias.Engineer.primary_language,
and_(
pa.Engineer.primary_language == None, # noqa
pa_alias.Engineer.primary_language == None,
pa.person_id > pa_alias.person_id,
),
),
)
.order_by(pa.name, pa_alias.name)
],
[
("dilbert", "java", "dilbert", "java"),
("dogbert", None, "pointy haired boss", None),
("vlad", "cobol", "vlad", "cobol"),
("wally", "c++", "wally", "c++"),
],
)
def test_compare_clauselist_assoc_different_operator(self):
l1 = and_(self.a.c.x == self.b.c.y, self.a.c.y == self.b.c.z)
l2 = or_(self.a.c.y == self.b.c.z, self.a.c.x == self.b.c.y)
is_false(l1.compare(l2))
def test_where_empty(self):
table1 = self.tables.mytable
self.assert_compile(
table1.delete().where(and_()), "DELETE FROM mytable"
)
self.assert_compile(
table1.delete().where(or_()), "DELETE FROM mytable"
)
def test_match_across_joins(self):
results = (matchtable.select().where(
and_(
cattable.c.id == matchtable.c.category_id,
or_(
cattable.c.description.match("Ruby"),
matchtable.c.title.match("nutshells"),
),
)).order_by(matchtable.c.id).execute().fetchall())
eq_([1, 3, 5], [r.id for r in results])
def test_or_match(self):
results1 = (matchtable.select().where(
or_(
matchtable.c.title.match("nutshells"),
matchtable.c.title.match("rubies"),
)).order_by(matchtable.c.id).execute().fetchall())
eq_([3, 5], [r.id for r in results1])
results2 = (matchtable.select().where(
matchtable.c.title.match("nutshells | rubies")).order_by(
matchtable.c.id).execute().fetchall())
eq_([3, 5], [r.id for r in results2])
def test_delete_rollback_with_fetch(self):
User = self.classes.User
sess = Session()
john, jack, jill, jane = sess.query(User).order_by(User.id).all()
sess.query(User).filter(
or_(User.name == "john",
User.name == "jill")).delete(synchronize_session="fetch")
assert john not in sess and jill not in sess
sess.rollback()
assert john in sess and jill in sess
def test_where_empty(self):
table1 = self.tables.mytable
self.assert_compile(
table1.update().where(and_()),
"UPDATE mytable SET myid=:myid, name=:name, "
"description=:description",
)
self.assert_compile(
table1.update().where(or_()),
"UPDATE mytable SET myid=:myid, name=:name, "
"description=:description",
)
def test_delete(self):
User = self.classes.User
sess = Session()
john, jack, jill, jane = sess.query(User).order_by(User.id).all()
sess.query(User).filter(or_(User.name == "john",
User.name == "jill")).delete()
assert john not in sess and jill not in sess
eq_(sess.query(User).order_by(User.id).all(), [jack, jane])
def test_outer_join(self):
"""Query.outerjoin"""
Order, User, Address = (
self.classes.Order,
self.classes.User,
self.classes.Address,
)
session = create_session()
q = (session.query(User).outerjoin("orders", "addresses").filter(
sa.or_(Order.id == None, Address.id == 1))) # noqa
eq_(set([User(id=7), User(id=8), User(id=10)]), set(q.all()))
def test_delete_without_session_sync(self):
User = self.classes.User
sess = Session()
john, jack, jill, jane = sess.query(User).order_by(User.id).all()
sess.query(User).filter(
or_(User.name == "john",
User.name == "jill")).delete(synchronize_session=False)
assert john in sess and jill in sess
eq_(sess.query(User).order_by(User.id).all(), [jack, jane])
def test_outer_join_count(self):
"""test the join and outerjoin functions on Query"""
Order, User, Address = (
self.classes.Order,
self.classes.User,
self.classes.Address,
)
session = create_session()
q = (session.query(User).outerjoin("orders", "addresses").filter(
sa.or_(Order.id == None, Address.id == 1))) # noqa
eq_(q.count(), 4)
def test_from(self):
users, Order, User, Address, orders, addresses = (
self.tables.users,
self.classes.Order,
self.classes.User,
self.classes.Address,
self.tables.orders,
self.tables.addresses,
)
session = create_session()
sel = users.outerjoin(orders).outerjoin(
addresses, orders.c.address_id == addresses.c.id)
q = (session.query(User).select_from(sel).filter(
sa.or_(Order.id == None, Address.id == 1))) # noqa
eq_(set([User(id=7), User(id=8), User(id=10)]), set(q.all()))
def test_col_expression_base_plus_two_subs(self):
sess = create_session()
pa = with_polymorphic(Person, [Engineer, Manager])
eq_(
sess.query(
pa.name, pa.Engineer.primary_language, pa.Manager.manager_name
)
.filter(
or_(
pa.Engineer.primary_language == "java",
pa.Manager.manager_name == "dogbert",
)
)
.order_by(pa.Engineer.type)
.all(),
[("dilbert", "java", None), ("dogbert", None, "dogbert")],
)
def test_noorm(self):
"""test the control case"""
tests, options, categories = (
self.tables.tests,
self.tables.options,
self.tables.categories,
)
# I want to display a list of tests owned by owner 1
# if someoption is false or they haven't specified it yet (null)
# but not if they set it to true (example someoption is for hiding)
# desired output for owner 1
# test_id, cat_name
# 1 'Some Category'
# 3 "
# not orm style correct query
print("Obtaining correct results without orm")
result = (
sa.select(
[tests.c.id, categories.c.name],
sa.and_(
tests.c.owner_id == 1,
sa.or_(
options.c.someoption == None, # noqa
options.c.someoption == False,
),
),
order_by=[tests.c.id],
from_obj=[
tests.join(categories).outerjoin(
options,
sa.and_(
tests.c.id == options.c.test_id,
tests.c.owner_id == options.c.owner_id,
),
)
],
).execute().fetchall())
eq_(result, [(1, "Some Category"), (3, "Some Category")])
def test_exception_format_hide_parameters_nondbapi_round_trip(self):
foo = Table("foo", MetaData(), Column("data", String))
with self.no_param_engine.connect() as conn:
assert_raises_message(
tsa.exc.StatementError,
r"\(sqlalchemy_1_3.exc.InvalidRequestError\) A value is required "
r"for bind parameter 'the_data_2'\n"
r"\[SQL: SELECT foo.data \nFROM foo \nWHERE "
r"foo.data = \? OR foo.data = \?\]\n"
r"\[SQL parameters hidden due to hide_parameters=True\]",
conn.execute,
select([foo]).where(
or_(
foo.c.data == bindparam("the_data_1"),
foo.c.data == bindparam("the_data_2"),
)
),
{"the_data_1": "some data"},
)
def test_column_bind_labels_2(self):
table1 = self.table1
s = table1.select(
or_(
table1.c.this_is_the_primarykey_column == 4,
table1.c.this_is_the_primarykey_column == 2,
)
)
self.assert_compile(
s,
"SELECT some_large_named_table.this_is_the_primarykey_column, "
"some_large_named_table.this_is_the_data_column "
"FROM some_large_named_table WHERE "
"some_large_named_table.this_is_the_primarykey_column = "
":this_is_the_primarykey__1 OR "
"some_large_named_table.this_is_the_primarykey_column = "
":this_is_the_primarykey__2",
checkparams={
"this_is_the_primarykey__1": 4,
"this_is_the_primarykey__2": 2,
},
dialect=self._length_fixture(),
)
self.assert_compile(
s,
"SELECT some_large_named_table.this_is_the_primarykey_column, "
"some_large_named_table.this_is_the_data_column "
"FROM some_large_named_table WHERE "
"some_large_named_table.this_is_the_primarykey_column = "
"%s OR "
"some_large_named_table.this_is_the_primarykey_column = "
"%s",
checkparams={
"this_is_the_primarykey__1": 4,
"this_is_the_primarykey__2": 2,
},
checkpositional=(4, 2),
dialect=self._length_fixture(positional=True),
)
def test_dslish(self):
"""test the same as withjoinedload except using generative"""
Thing, tests, options = (
self.classes.Thing,
self.tables.tests,
self.tables.options,
)
s = create_session()
q = s.query(Thing).options(sa.orm.joinedload("category"))
result = q.filter(
sa.and_(
tests.c.owner_id == 1,
sa.or_(
options.c.someoption == None,
options.c.someoption == False, # noqa
),
)).outerjoin("owner_option")
result_str = ["%d %s" % (t.id, t.category.name) for t in result]
eq_(result_str, ["1 Some Category", "3 Some Category"])
def test_outer_join(self):
table1 = table(
"mytable",
column("myid", Integer),
column("name", String),
column("description", String),
)
table2 = table(
"myothertable",
column("otherid", Integer),
column("othername", String),
)
table3 = table(
"thirdtable",
column("userid", Integer),
column("otherstuff", String),
)
query = select(
[table1, table2],
or_(
table1.c.name == "fred",
table1.c.myid == 10,
table2.c.othername != "jack",
text("EXISTS (select yay from foo where boo = lar)"),
),
from_obj=[
outerjoin(table1, table2, table1.c.myid == table2.c.otherid)
],
)
self.assert_compile(
query,
"SELECT mytable.myid, mytable.name, "
"mytable.description, myothertable.otherid,"
" myothertable.othername FROM mytable, "
"myothertable WHERE (mytable.name = "
":name_1 OR mytable.myid = :myid_1 OR "
"myothertable.othername != :othername_1 OR "
"EXISTS (select yay from foo where boo = "
"lar)) AND mytable.myid = "
"myothertable.otherid(+)",
dialect=oracle.OracleDialect(use_ansi=False),
)
query = table1.outerjoin(table2,
table1.c.myid == table2.c.otherid).outerjoin(
table3,
table3.c.userid == table2.c.otherid)
self.assert_compile(
query.select(),
"SELECT mytable.myid, mytable.name, "
"mytable.description, myothertable.otherid,"
" myothertable.othername, "
"thirdtable.userid, thirdtable.otherstuff "
"FROM mytable LEFT OUTER JOIN myothertable "
"ON mytable.myid = myothertable.otherid "
"LEFT OUTER JOIN thirdtable ON "
"thirdtable.userid = myothertable.otherid",
)
self.assert_compile(
query.select(),
"SELECT mytable.myid, mytable.name, "
"mytable.description, myothertable.otherid,"
" myothertable.othername, "
"thirdtable.userid, thirdtable.otherstuff "
"FROM mytable, myothertable, thirdtable "
"WHERE thirdtable.userid(+) = "
"myothertable.otherid AND mytable.myid = "
"myothertable.otherid(+)",
dialect=oracle.dialect(use_ansi=False),
)
query = table1.join(table2, table1.c.myid == table2.c.otherid).join(
table3, table3.c.userid == table2.c.otherid)
self.assert_compile(
query.select(),
"SELECT mytable.myid, mytable.name, "
"mytable.description, myothertable.otherid,"
" myothertable.othername, "
"thirdtable.userid, thirdtable.otherstuff "
"FROM mytable, myothertable, thirdtable "
"WHERE thirdtable.userid = "
"myothertable.otherid AND mytable.myid = "
"myothertable.otherid",
dialect=oracle.dialect(use_ansi=False),
)
query = table1.join(table2,
table1.c.myid == table2.c.otherid).outerjoin(
table3, table3.c.userid == table2.c.otherid)
self.assert_compile(
query.select().order_by(table1.c.name).limit(10).offset(5),
"SELECT myid, name, description, otherid, "
"othername, userid, otherstuff FROM "
"(SELECT myid, name, description, otherid, "
"othername, userid, otherstuff, ROWNUM AS "
"ora_rn FROM (SELECT mytable.myid AS myid, "
"mytable.name AS name, mytable.description "
"AS description, myothertable.otherid AS "
"otherid, myothertable.othername AS "
"othername, thirdtable.userid AS userid, "
"thirdtable.otherstuff AS otherstuff FROM "
"mytable, myothertable, thirdtable WHERE "
"thirdtable.userid(+) = "
"myothertable.otherid AND mytable.myid = "
"myothertable.otherid ORDER BY mytable.name) "
"WHERE ROWNUM <= :param_1 + :param_2) "
"WHERE ora_rn > :param_2",
checkparams={
"param_1": 10,
"param_2": 5
},
dialect=oracle.dialect(use_ansi=False),
)
subq = (select([table1]).select_from(
table1.outerjoin(table2,
table1.c.myid == table2.c.otherid)).alias())
q = select([table3]).select_from(
table3.outerjoin(subq, table3.c.userid == subq.c.myid))
self.assert_compile(
q,
"SELECT thirdtable.userid, "
"thirdtable.otherstuff FROM thirdtable "
"LEFT OUTER JOIN (SELECT mytable.myid AS "
"myid, mytable.name AS name, "
"mytable.description AS description FROM "
"mytable LEFT OUTER JOIN myothertable ON "
"mytable.myid = myothertable.otherid) "
"anon_1 ON thirdtable.userid = anon_1.myid",
dialect=oracle.dialect(use_ansi=True),
)
self.assert_compile(
q,
"SELECT thirdtable.userid, "
"thirdtable.otherstuff FROM thirdtable, "
"(SELECT mytable.myid AS myid, "
"mytable.name AS name, mytable.description "
"AS description FROM mytable, myothertable "
"WHERE mytable.myid = myothertable.otherid("
"+)) anon_1 WHERE thirdtable.userid = "
"anon_1.myid(+)",
dialect=oracle.dialect(use_ansi=False),
)
q = select([table1.c.name]).where(table1.c.name == "foo")
self.assert_compile(
q,
"SELECT mytable.name FROM mytable WHERE "
"mytable.name = :name_1",
dialect=oracle.dialect(use_ansi=False),
)
subq = (select([
table3.c.otherstuff
]).where(table3.c.otherstuff == table1.c.name).label("bar"))
q = select([table1.c.name, subq])
self.assert_compile(
q,
"SELECT mytable.name, (SELECT "
"thirdtable.otherstuff FROM thirdtable "
"WHERE thirdtable.otherstuff = "
"mytable.name) AS bar FROM mytable",
dialect=oracle.dialect(use_ansi=False),
)