I have got 2 tables, one is department and another one is supplier. For every department, there could be a supplier or it could be none. So I use the outerjoin and the result is correct. I need to know how add these results in to a list

@department.route("/department/getDepartmentDetails/<int:id>", methods=['GET', 'POST'])
def getDepartmentDetails(id):
     department = db.session.query(Department, Supplier) \
        .outerjoin(Department, Department.default_supplier_id == Supplier.id) \
        .filter(Department.id == id)

If I query single table like below, I can use getattr to get the desired result

department = Department.query.filter(Department.id==id).first()
dept={c.name: str(getattr(department, c.name)) for c in department.__table__.columns}
return jsonify(dept)

How do I get the same for the multi-table query result?

I have got 2 tables, one is department and another one is supplier. For every department, there could be a supplier or it could be none. So I use the outerjoin and the result is correct. I need to know how add these results in to a list

@department.route("/department/getDepartmentDetails/<int:id>", methods=['GET', 'POST'])
def getDepartmentDetails(id):
     department = db.session.query(Department, Supplier) \
        .outerjoin(Department, Department.default_supplier_id == Supplier.id) \
        .filter(Department.id == id)

If I query single table like below, I can use getattr to get the desired result

department = Department.query.filter(Department.id==id).first()
dept={c.name: str(getattr(department, c.name)) for c in department.__table__.columns}
return jsonify(dept)

How do I get the same for the multi-table query result?

• python
• flask
• flask-sqlalchemy

1 Answer 1

Assuming that you want to return a single dict (or JSON object) for each row in the resultset, all that is required is generate a dict for each entity in each (Department, Supplier) row, and merge them. However we need to consider that the models may share some attribute names, which must be disambiguated.

The disambiguation can be handled by creating a function, based on your code, which accepts a prefix string and an entity, and returns a dict of prefixed attribute names and values:

def to_prefix_dict(prefix, entity):
    if not entity:
        return {}
    return {
        f'{prefix.lower()}_{c.name}': getattr(entity, c.name) for c in entity.__table__.columns
    }

It can be used like this:

import functools
import sqlalchemy as sa
...

# This code is vanilla SQLAlchemy, but Flask-SQLAlchemy code will be 
# essentially the same: substitute `db.session.query` for `sa.select` and
# `db.session` for `s`.
with Session() as s:
    q = sa.select(Department, Supplier).outerjoin(
        Supplier, Department.default_supplier_id == Supplier.id
    )
    rs = s.execute(q)
    result = [
        functools.reduce(
            dict.__or__,
            [to_prefix_dict(key, entity) for key, entity in zip(rs.keys(), row)],
        )
        for row in rs
    ]

Resulting in a structure like this when passed through jsonify, assuming one department with a supplier, and one without:

[
  {
    "department_id": 1,
    "department_name": "D1",
    "department_default_supplier_id": 1,
    "supplier_id": 1,
    "supplier_name": "S1"
  },
  {
    "department_id": 2,
    "department_name": "S2",
    "department_default_supplier_id": null
  }
]

Since the ORM entities returned by the query aren't really required for the output, you could consider specifying the required columns and their labels directly:

    q = sa.select(
        Department.name.label('department'), Supplier.name.label('supplier')
    ).outerjoin(Supplier, Department.default_supplier_id == Supplier.id)
    rs = s.execute(q)
    result = [dict(r) for r in rs.mappings()]

Output:

[
  {
    "department": "D1",
    "supplier": "S1"
  },
  {
    "department": "S2",
    "supplier": null
  }
]

Manual labelling can be avoided by configuring the query to automatically label each column as <table name + column name>. If your table names are not siutable for exposure you can use an alias:

    dept = orm.aliased(Department, name='dep')
    supp = orm.aliased(Supplier, name='sup')
    q = (
        sa.select(dept.name, supp.name)
        .outerjoin(supp, dept.default_supplier_id == supp.id)
        .set_label_style(sa.LABEL_STYLE_TABLENAME_PLUS_COL)
    )
    rs = s.execute(q)
    result = [dict(r) for r in rs.mappings()]

Output:

[
  {
    "department": "D1",
    "supplier": "S1"
  },
  {
    "department": "S2",
    "supplier": null
  }
]

If all the column in both tables are required, you can unpack both tables' columns attributes.

    dept = sa.alias(Department.__table__, 'dep')
    supp = sa.alias(Supplier.__table__, 'sup')
    q = (
        sa.select(*dept.columns, *supp.columns)
        .outerjoin(supp, dept.c.default_supplier_id == supp.c.id)
        .set_label_style(sa.LABEL_STYLE_TABLENAME_PLUS_COL)
    )
    rs = s.execute(q)
    result = [dict(r) for r in rs.mappings()]

Output:

[
  {
    "dep_id": 1,
    "dep_name": "D1",
    "dep_default_supplier_id": 1,
    "sup_id": 1,
    "sup_name": "S1"
  },
  {
    "dep_id": 2,
    "dep_name": "S2",
    "dep_default_supplier_id": null,
    "sup_id": null,
    "sup_name": null
  }
]

Alternatively, this function can used to return a nested row structure, which may be useful if the consumer of the JSON needs to handle departments and suppliers separately:

def to_dict(entity):
    if not entity:
        return {}
    return {c.name: getattr(entity, c.name) for c in entity.__table__.columns}

like this:

...
    result = [
        {key_name: to_dict(entity) for key_name, entity in zip(rs.keys(), row)}
        for row in rs
    ]

With this result:

[                   
  { 
    "Department": {
      "id": 1,         
      "name": "D1", 
      "default_supplier_id": 1
    },
    "Supplier": {                                                                                        
      "id": 1,                                                                                           
      "name": "S1"                                                                                       
    }
  },
  {                  
    "Department": { 
      "id": 2,
      "name": "S2",
      "default_supplier_id": null
    },              
    "Supplier": {}
  }
]