Preserve Key Naming When Creating A Joinedload Sql_query In SQLAlchemy

I am extracting a table row and the corresponding row from all referenced tables via SQLAlchemy. Given the following object structure: class DNAExtractionProtocol(Base): __tabl

Solution 1:

This is not what joinedload is supposed to be used for. You want to do an explicit join in this case:

session.query(DNAExtractionProtocol.id.label("id"),
              ...,
              MeasurementUnit.id.label("mass_unit_id"),
              ...) \
       .join(DNAExtractionProtocol.mass_unit) \
       .join(DNAExtractionProtocol.digestion_buffer) \
       ... \
       .filter(...)

If you don't want to type out all those names, you can inspect the DNAExtractionProtocol class to find all relationships and dynamically construct the query and labels. An example:

cols = []
joins = []
insp = inspect(DNAExtractionProtocol)
for name, col in insp.columns.items():
    cols.append(col.label(name))
for name, rel in insp.relationships.items():
    alias = aliased(rel.mapper.class_, name=name)
    for col_name, col in inspect(rel.mapper).columns.items():
        aliased_col = getattr(alias, col.key)
        cols.append(aliased_col.label("{}_{}".format(name, col_name)))
    joins.append((alias, rel.class_attribute))

query = session.query(*cols).select_from(DNAExtractionProtocol)
for join in joins:
    query = query.join(*join)

EDIT: Depending on your data structure you might need to use outerjoin instead of join on the last line.

You'll probably need to tweak this to your liking. For example, this doesn't take into account potential naming conflicts, e.g. for mass_unit_id, is it DNAExtractionProtocol.mass_unit_id or is it MeasurementUnit.id?

In addition, you'll probably want to execute sql_query.statement instead of str(sql_query). str(sql_query) is for printing purposes, not for execution. I believe you don't need to pass params=[code] if you use sql_query.statement because code will already have been bound to the appropriate parameter in the query.