Iterate List of tables for specific column

Question

I have a list of tables which I would like to iterate and find a specific row based on a foreign key column, then delete it.

This is what my list of tables look like:

subrep_tables = [   TCableResistance.__table__,
                    TCapacitorBankTest.__table__,
                    TCapAndDf.__table__,
                    TMeasuredData.__table__,
                    TMultiDeviceData.__table__,
                    TStepVoltage.__table__,
                    TTemperatureData.__table__,
                    TTransformerResistance.__table__,
                    TTransformerTurnsRatio.__table__,
                    TTripUnit.__table__,
                    TVectorDiagram.__table__,
                    TWithstandTest.__table__,
                ]

I called the list subrep_tables because all of those tables contains a foreign key called ixSubReport.

What I'm trying to do is iterate the list and find all the rows that have a certain sub report and delete those rows instead of going to each table and running the query to delete them(very tedious)

This is what I've come up with thus far.

for report in DBSession.query(TReport).filter(TReport.ixDevice == device_id).all():
    for sub_report in DBSession.query(TSubReport).filter(TSubReport.ixReport == report.ixReport).all():
        for table in subrep_tables:
            for item in DBSession.query(table).filter(table.ixSubReport == sub_report.ixSubReport).all():
                print "item: " + str(item)
                #DBSession.delete(item)

I'm having some difficulty accessing the table's ixSubReport column for my WHERE clause. The code I have right now gives me an error saying: 'Table' Object has no attribute 'ixSubReport'.

How can I access my iterated table's ixSubReport column to use in my WHERE clause to find the specific row so I can delete it?

Answer 1

If you really want to query the tables, the columns are under the c attribute, use table.c.ixSubReport.

There's no reason to create a list of the __table__ attributes though, just query the models directly. Also, you can avoid a ton of overhead by not performing the first two queries; you can do all this in a single query per model. (This example assumes there are relationships set up between te models).

from sqlalchemy.orm import contains_eager

has_subrep_models = [TCableResistance, TCapacitorBankTest, ...]
# assuming each has_subrep model has a relationship "subrep"
# assuming TSubReport has a relationship "report"

for has_subrep_model in has_subrep_models:
    for item in DBSession.query(has_subrep_model).join(has_subrep_model.subrep, TSubReport.report).filter(TReport.ixDevice == device_id).options(contains_eager(has_subrep_model.subrep), contains_eager(TSubReport.report)):
        DBSession.delete(item)

This simply joins the related sub report and report when querying each model that has a sub report, and does the filtering on the report's device there. So you end up doing one query per model, rather than 1 + <num reports> + (<num reports> * <num models with sub reports>) = a lot.