How to do a sqlalchemy query using a string variable as table name?

I needed a way to query by referring to the model's tablename. This is what I came up with:

class Model1(db.Model): __tablename__ = 'table1' # or whatever, doesn't matter class Model2(db.Model): __tablename__ = 'table2' tables_dict = {table.__tablename__: table for table in db.Model.__subclasses__()} def table_object(table_name): return tables_dict.get(table_name) 

Then, use it like this:

model_list = ['table1', 'table2'] for model in model_list: some_var = db.session.query(table_object(table_name=model)).filter_by(name='something').all() 

The important bit is db.Model.__subclasses__() - gives a list of the model classes (objects?).

Hope it's not too late. I think you can try this:

class Model1: pass class Model2: pass model_list = [Model1, Model2] for model in model_list: db.session.query(model) 

Model1 and Model2 are classes like Users, Roles ,etc.

Expounding on the answer that @timothyh gave because I don't have enough reputation points to comment and believe it will be useful for someone. I use abstract tables and rely on class inheritance. It is necessary to include:

if hasattr(table,'__tablename__') 

to the end of the dictionary comprehension, otherwise an error will occur. Then, ensure you inherit both your abstract table as well as a Base Model.

For example:

class MasterTable(db.Model): __abstract__ = True column_1 = some_column_schema column_2 = some_column_schema column_x = some_column_schema class SimilarTable(MasterTable,db.Model): __tablename__ = 'SubTab_1' class OtherSimilarTable(MasterTable,db.Model): __tablename__ = 'SubTab_2" 

And my function:

def get_table_class(tablename): '''Uses a dictionary that will contain table names as key and Model object as value. Returns the db.Model object that can be queried.''' my_tables_dict = {table.__tablename__: table for table in db.Model.__subclasses__() if hasattr(table,'__tablename__')} return my_tables_dict.get(tablename) 

I think importlib may help you here if you are using python > 2.7

importlib.import_module('module to import') 

For your case: If your project structure looks like below and models.py defines all the OR mapped classes.

a | + - __init__.py - b | + - __init__.py - models.py 

The code should be like:

model_list = ['Model1', 'Model2', 'Model3'] the_module = importlib.import_module('module to import') # eg. importlib.import_module('a.b.models') for model in model_list: the_class = getattr(the_module, model) some_var = the_class.query.filter_by(name='something').all() 

Try this:

mapping_str_to_object = {} for model in BaseModel._decl_class_registry.values(): if hasattr(model, '__tablename__'): mapping_str_to_object[model.__tablename__] = model model_list = ['Model1', 'Model2', 'Model3'] for model in model_list: some_var = mapping_str_to_object[model].query.filter_by(name='something').all()