SQLAlchemy: Getting a single object from joining multiple tables

Question

DB - SQLAlchemy setting

Consider a classic setting of two tables - user and api_key, represented by SQLAlchemy objects as:

class User(Base):
    __tablename__ = 'user'
    user_id = Column(String)
    user_name = Column(String)
    vioozer_api_key = Column(String, ForeignKey("api_key.api_key"))

class ApiKey(Base):
    __tablename__ = 'api_key'    
    api_key = Column(String(37), primary_key=True)

Other fields omitted for clarity.

My Query

Suppose that I want to get the user name and the api key for a specific user id:

user, api_key = database.db_session.query(User, ApiKey)\
    .join(ApiKey, User.vioozer_api_key==ApiKey.api_key)\
    .filter(User.user_id=='user_00000000000000000000000000000000').first()

I get two objects - user and api_key, from which I can fetch user.name and api_key.api_key.

The problem

I would like to wrap this call with a function, which would return a single objects whose fields would be the union of the user fields and the api_key fields - the same way a SQL join returns a table with the columns of both tables being joined. Is there a wayo to automatically get a object whose fields are the union of the fields of both tables?

Wha have I tried

I can define a mapper class for each Join operation, but I wonder if the mapping could be done automatically.

Answer 1

Instead of querying objects, query for list of fields instead, in which case SQLAlchemy returns instances of KeyedTuple, which offers KeyedTuple._asdict() method you can use to return arbitrary dictionary:

def my_function(user_id):
    row =  database.db_session.query(User.name, ApiKey.api_key)\
        .join(ApiKey, User.vioozer_api_key==ApiKey.api_key)\
        .filter(User.user_id==user_id).first()
    return row._asdict()

my_data = my_function('user_00000000000000000000000000000000')

But for your particular query, you do not need even to join on ApiKey as the api_key field is present on the User table:

row = database.db_session.query(User.name, User.api_key)\
    .filter(User.user_id==user_id).first()