sqlalchemy.outerjoin - python examples

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 7

2 View Complete Implementation : workbench_dao.py
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
    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
    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
    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),
        )