SQLAlchemy Query - Dynamic class selection

Question

I can find many examples of how to build these queries where the 'filter_by' part is dynamic, but I cant seem to find anything where the class name is the dynamic bit. I'm sure an answer must be out there, just not sure what to search for.

My use case it this: I need to build a dynamic SQLAlchemy query. The twist is that its the class name changing rather than the filter variables. The query type will always be a '.get()' so I'm good there. I should also say that simply plugging a variable in where the class name should be doesnt work.

db_model = request.values.get("db_model_class")
item_id = request.values.get("item_id")

result = db.session.query(db_model).get(int(item_id))

How do I go about making this work?

Related: https://stackoverflow.com/questions/1373164/how-do-i-create-a-variable-number-of-variables — Ilja Everilä, Jul 09 '18 at 11:55
You can treat `db_model` like any other variable in python and pass the model class reference to it in order to form a generic query as answered by IIjia. — mad_, Jul 09 '18 at 13:30

score 1 · Answer 1 · answered Jul 06 '18 at 20:32

1

Create a lookup of relevant classes:

models = {"Foo": Foo,
          "Bar": Bar,
          "...": ...}

and get the class:

db_model = models[request.values.get("db_model_class")]

answered Jul 06 '18 at 20:32

Ilja Everilä

50,538
7
126
127

Unfortunately, this doesnt work. You get the error "sqlalchemy.exc.InvalidRequestError: get() can only be used against a single mapped class." whenever you try and plug a variable in place of the class name. – user3723688 Jul 09 '18 at 11:32
So you're passing something odd, but there's no way to help you based on the error message alone. A simple mapping of class name to class does not cause that. – Ilja Everilä Jul 09 '18 at 11:53
This dictionary can be created with the oneliner `classes = dict(inspect.getmembers(sys.modules[__name__], inspect.isclass))`. Be sure to define it _after_ you define your classes and be sure to import both `inspect` and `sys`. – mas Nov 30 '18 at 18:44

score -1 · Answer 2 · answered Jul 09 '18 at 12:58

db_model = request.values.get("db_model_class") item_id = request.values.get("item_id")

Maybe the problem is here. you need to set a debug point.

the answer by Ilja Everilä was great. so you need to check the way you get the parameters.

if you are using a pycharm, configure a debug setting.

if you are using Macbook, click the run->edit configurations->

then you click the left margin of the code, and restart your app with the debug button on the top right corner. when you request the url again, you will see what your view function has get in the request object.

SQLAlchemy Query - Dynamic class selection

2 Answers2