Here are the examples of the python api sqlalchemy.outerjoin taken from open source projects. By voting up you can indicate which examples are most useful and appropriate.
4 Examples
2
View Complete Implementation : workbench_dao.py
Copyright BSD 3-Clause "New" or "Revised" License
Author : all-of-us
Copyright BSD 3-Clause "New" or "Revised" License
Author : all-of-us
def get_workspaces_with_user_detail(self, status):
query = sqlalchemy.select(
[
WorkbenchWorkspace.workspaceSourceId.label('workspaceId'),
WorkbenchWorkspace.name.label('name'),
WorkbenchWorkspace.status.label('status'),
WorkbenchWorkspace.creationTime.label('creationTime'),
WorkbenchWorkspace.modifiedTime.label('modifiedTime'),
WorkbenchWorkspace.excludeFromPublicDirectory.label('excludeFromPublicDirectory'),
WorkbenchWorkspace.diseaseFocusedResearch.label('diseaseFocusedResearch'),
WorkbenchWorkspace.diseaseFocusedResearchName.label('diseaseFocusedResearchName'),
WorkbenchWorkspace.otherPurposeDetails.label('otherPurposeDetails'),
WorkbenchWorkspace.methodsDevelopment.label('methodsDevelopment'),
WorkbenchWorkspace.controlSet.label('controlSet'),
WorkbenchWorkspace.ancestry.label('ancestry'),
WorkbenchWorkspace.socialBehavioral.label('socialBehavioral'),
WorkbenchWorkspace.populationHealth.label('populationHealth'),
WorkbenchWorkspace.drugDevelopment.label('drugDevelopment'),
WorkbenchWorkspace.commercialPurpose.label('commercialPurpose'),
WorkbenchWorkspace.educational.label('educational'),
WorkbenchWorkspace.otherPurpose.label('otherPurpose'),
WorkbenchWorkspaceUser.userId.label('userId'),
WorkbenchWorkspaceUser.role.label('role'),
WorkbenchResearcher.givenName.label('givenName'),
WorkbenchResearcher.familyName.label('familyName'),
WorkbenchInssatutionalAffiliations.inssatution.label('inssatution'),
WorkbenchInssatutionalAffiliations.role.label('inssatutionRole'),
WorkbenchInssatutionalAffiliations.nonAcademicAffiliation.label('nonAcademicAffiliation')
]
).select_from(
sqlalchemy.outerjoin(
sqlalchemy.outerjoin(WorkbenchWorkspace, WorkbenchWorkspaceUser,
WorkbenchWorkspace.id == WorkbenchWorkspaceUser.workspaceId),
sqlalchemy.outerjoin(WorkbenchResearcher, WorkbenchInssatutionalAffiliations,
WorkbenchResearcher.id == WorkbenchInssatutionalAffiliations.researcherId),
WorkbenchResearcher.id == WorkbenchWorkspaceUser.researcherId
)
).where(WorkbenchWorkspaceUser.role == WorkbenchWorkspaceUserRole.OWNER)
if status is not None:
query = query.where(WorkbenchWorkspace.status == status)
results = []
with self.session() as session:
cursor = session.execute(query)
for row in cursor:
record = {
'workspaceId': row.workspaceId,
'name': row.name,
'creationTime': row.creationTime,
'modifiedTime': row.modifiedTime,
'status': str(WorkbenchWorkspaceStatus(row.status)),
'workspaceOwner': [
{
'userId': row.userId,
'userName': row.givenName + ' ' + row.familyName,
'affiliations': [
{
"inssatution": row.inssatution,
"role": row.inssatutionRole,
"nonAcademicAffiliation": str(WorkbenchInssatutionNoAcademic(
row.nonAcademicAffiliation if row.nonAcademicAffiliation is not None
else 'UNSET'))
}
]
}
],
"excludeFromPublicDirectory": row.excludeFromPublicDirectory,
"diseaseFocusedResearch": row.diseaseFocusedResearch,
"diseaseFocusedResearchName": row.diseaseFocusedResearchName,
"otherPurposeDetails": row.otherPurposeDetails,
"methodsDevelopment": row.methodsDevelopment,
"controlSet": row.controlSet,
"ancestry": row.ancestry,
"socialBehavioral": row.socialBehavioral,
"populationHealth": row.populationHealth,
"drugDevelopment": row.drugDevelopment,
"commercialPurpose": row.commercialPurpose,
"educational": row.educational,
"otherPurpose": row.otherPurpose
}
is_exist_workspace = False
for item in results:
if item['workspaceId'] == record['workspaceId']:
is_exist_user = False
for user in item['workspaceOwner']:
if user['userId'] == record['workspaceOwner'][0]['userId']:
user['affiliations'] = user['affiliations'] + \
record['workspaceOwner'][0]['affiliations']
is_exist_user = True
break
if not is_exist_user:
item['workspaceOwner'] = item['workspaceOwner'] + record['workspaceOwner']
is_exist_workspace = True
break
if not is_exist_workspace:
results.append(record)
return results
0
View Complete Implementation : test_zoomark.py
Copyright MIT License
Author : sqlalchemy
Copyright MIT License
Author : sqlalchemy
def _baseline_7_multiview(self):
Zoo = self.metadata.tables["Zoo"]
Animal = self.metadata.tables["Animal"]
engine = self.metadata.bind
def fulltable(select):
"""Iterate over the full result table."""
return [list(row) for row in engine.execute(select).fetchall()]
for x in range(ITERATIONS):
fulltable(
select(
[Zoo.c.ID] + list(Animal.c),
Zoo.c.Name == "San Diego Zoo",
from_obj=[join(Zoo, Animal)],
)
)
Zoo.select(Zoo.c.Name == "San Diego Zoo")
fulltable(
select(
[Zoo.c.ID, Animal.c.ID],
and_(
Zoo.c.Name == "San Diego Zoo",
Animal.c.Species == "Leopard",
),
from_obj=[join(Zoo, Animal)],
)
)
# Now try the same query with INNER, LEFT, and RIGHT JOINs.
fulltable(
select(
[Zoo.c.Name, Animal.c.Species],
from_obj=[join(Zoo, Animal)],
)
)
fulltable(
select(
[Zoo.c.Name, Animal.c.Species],
from_obj=[outerjoin(Zoo, Animal)],
)
)
fulltable(
select(
[Zoo.c.Name, Animal.c.Species],
from_obj=[outerjoin(Animal, Zoo)],
)
)
0
View Complete Implementation : test_compiler.py
Copyright MIT License
Author : sqlalchemy
Copyright MIT License
Author : sqlalchemy
def test_outer_join_one(self):
table1, table2, table3 = self._test_outer_join_fixture()
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.astert_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),
)
0
View Complete Implementation : test_assorted_eager.py
Copyright MIT License
Author : sqlalchemy
Copyright MIT License
Author : sqlalchemy
def test_nested_joins(self):
task, Task_Type, Joined, task_type, msg = (
self.tables.task,
self.clastes.Task_Type,
self.clastes.Joined,
self.tables.task_type,
self.tables.msg,
)
# this is testing some subtle column resolution stuff,
# concerning corresponding_column() being extremely accurate
# as well as how mapper sets up its column properties
mapper(Task_Type, task_type)
j = sa.outerjoin(task, msg, task.c.id == msg.c.task_id)
jj = sa.select(
[
task.c.id.label("task_id"),
sa.func.count(msg.c.id).label("props_cnt"),
],
from_obj=[j],
group_by=[task.c.id],
).alias("prop_c_s")
jjj = sa.join(task, jj, task.c.id == jj.c.task_id)
mapper(
Joined,
jjj,
properties=dict(type=relationship(Task_Type, lazy="joined")),
)
session = create_session()
eq_(
session.query(Joined).limit(10).offset(0).one(),
Joined(id=1, satle="task 1", props_cnt=0),
)