Return single resultset with SQLAlchemy join

Question

In my Flask app, I would like to create a join-query and return a single (flat) resultset using SQLAlchemy.

Simplified, I created two tables in my models.py file:

class User(db.Model):
    __tablename__ = 'user'

    id = db.Column(db.Integer, primary_key=True, autoincrement=True)
    first_name = db.Column(db.String(120), nullable=False)
    last_name = db.Column(db.String(120), nullable=False)
    department_id = db.Column(db.Integer)
    date_added = db.Column(db.DateTime(timezone=True))

class Department(db.Model):
    __tablename__ = 'department'

    id = db.Column(db.Integer, primary_key=True, autoincrement=True)
    name = db.Column(db.String(120), nullable=False)
    date_added = db.Column(db.DateTime(timezone=True))

In my app, I create the following query:

from app import app, db
from app.models import User, Department
...
@app.route("/index")
def index():
    results = db.session.query(User, Department).join(Department, Department.id == User.department_id, isouter=True)

The results are returned in a tuple with 2 objects: (<User>, <Department>).

(In case I would join on 1 more object, such as Company, the results will be returned in 3 tuples (<User>, <Department>, <Company>).)

Is there any way I can return the results in a single object without the use of tuples: ('user_id1', 'first_name1', 'last_name1', 'department_name1'), ('user_id2', 'first_name2', 'last_name2', 'department_name2').

Answer 1

It appears that the solution was very simple.

I modified the query to specifically select the required fields instead of the entire objects:

db.session.query(User.id, User.first_name, User.last_name,  Department.name).join(Department, Department.id == User.department_id, isouter=True)