CODEWITHSUNDEEP
pythonsqlsqlalchemy
Niel
Niel

Reputation: 2006

Return query with columns from multiple tables in SQLAlchemy

I haven't been able to find an answer to this, but I'm sure it must be somewhere. My question is similar to this question: sqlalchemy: how to join several tables by one query?

But I need a query result, not a tuple. I don't have access to the models, so I can't change it, and I can't modify the functions to use a tuple.

I have two tables, UserInformation and MemberInformation, both with a foreign key and relationship to Principal, but not to each other.

How can I get all the records and columns from both tables in one query? I've tried:

query = DBSession.query(MemberInformation).join(UserInformation, MemberInformation.pId == UserInformation.pId)

but it only returns the columns of MemberInformation

and:

query = DBSession.query(MemberInformation, UserInformation).join(UserInformation, MemberInformation.pId == UserInformation.pId)

but that returns a tuple.

What am I missing here?

Upvotes: 11

Views: 11182

Answers (2)

Matt Graham
Matt Graham

Reputation: 232

Old question, but worth answering because i see it's got a lot of view activity.

You need to create a relationship and then tell SQLAlchemy how to load the related data. Not sure what your tables / relationship looks like, but it might look something like this:

# Create relationship
MemberInformation.user = relationship(
    "UserInformation",
    foreign_keys=[MemberInformation.pId],
    lazy="joined",
)

# Execute query
query = DBSession.query(MemberInformation) \
    .options(joinedload(MemberInformation.user)) \
    .all()

# All objects are in memory. Evaluating the following will NOT result in additional
# database interaction
for member in query:
    print(f'Member: {member} User: {member.user}')

# member is a MemberInformation object, member.user is a UserInformation object

Ideally, the relationship would be defined in your models. If can, however, be defined at run time list the example above.

Upvotes: 6

Sindhu Kothe
Sindhu Kothe

Reputation: 51

Only way I found to do this is to use statement instead of query:

stmt = select([table1, table2.col.label('table2_col')]).select_from(join(table1, table2, table1.t1_id == table2.t2_id))
obj = session.execute(stmt).fetchall()

Upvotes: 5

Related Questions