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

Question

I apologize in advance if I am not clear, English is not my native language. Feel free to tell me if I make too many mistakes :)

I am a newbie in using flask_sqlalchemy and get frustrated after spending hours on Internet searching for an answer.

What I want is doing a query like that one :

ModelName.query.filter_by(name='something').all()

But instead of using ModelName I want to do something like that :

model_list = ['Model1', 'Model2', 'Model3']
for model in model_list:
    some_var = model.query.filter_by(name='something').all()
    # do some stuff with the variable

The error is :

'str' object has no attribute 'query'

I have of course the same kind of problem when trying to use db.session.add() with a string variable.

I understand that the query requires some kind of _BoundDeclarativeMeta object instead of a string but then how to get that object from a list to pass it to a for loop ?

I tryed using 'db.metadata.tables[my_variable]' but I get a Table object instead of a _BoundDeclarativeMeta object and it doesn't work the same.

I manage to do a query using

db.session.query(db.metadata.tables[my_variable])

instead of

my_variable.query

but I can't use that method for the 'db.session.add()' (or is it possible ?)

Is there any way to 'cast' the string variable into the right type of sqlalchemy object ?

timothyh · Accepted Answer · 2019-09-19 00:38:19Z

5

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?).

answered Sep 19, 2019 at 0:38

timothyh

1692 silver badges8 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

请叫我小马哥 · Accepted Answer · 2019-05-23 11:48:46Z

3

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.

answered May 23, 2019 at 11:48

请叫我小马哥

1,7291 gold badge16 silver badges11 bronze badges

1 Comment

m3nda Over a year ago

Although this works and should suffice, OP asked "using strings as table names", and used Models objects.

Randy Stegner Sr. · Accepted Answer · 2022-03-08 19:55:25Z

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)

ichbinblau · Accepted Answer · 2016-12-18 06:25:24Z

2

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()

edited Dec 18, 2016 at 6:25

answered Dec 16, 2016 at 2:29

ichbinblau

4,8395 gold badges26 silver badges38 bronze badges

1 Comment

cleve21 Over a year ago

Thank you for responding :)but I dont really know which module use for 'the_module' to get the right type of object.

Kanchan Srivastava · Accepted Answer · 2017-02-13 15:53:06Z

2

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()

answered Feb 13, 2017 at 15:53

Kanchan Srivastava

3993 silver badges7 bronze badges

Comments

cleve21 · Accepted Answer · 2016-12-16 19:11:44Z

-5

I sort of managed to get what I wanted using

eval(my_variable)

It seems that eval() allow me to get a db object named by the variable value rather than a plain string object.

I saw security warnings about the use of eval() but I gess it's OK since the evaluated variable doesn't come from user input.

Of course if anyone sees a better way to do the same thing I would be glad know it.

answered Dec 16, 2016 at 19:11

cleve21

1691 gold badge2 silver badges14 bronze badges

1 Comment

llazzaro Over a year ago

eval is insecure

Collectives™ on Stack Overflow

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

6 Answers 6

Comments

1 Comment

Comments

1 Comment

Comments

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

6 Answers 6

Comments

1 Comment

Comments

1 Comment

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related