How do I set the attributes of a SQLAlchemy ORM class before query?

Question

For example, using Flask-SQLAlchemy and jsontools to serialize to JSON like shown -here-, and given a model like this:

class Engine(db.Model):
    __tablename__ = "engines"

    id      = db.Column(db.Integer, primary_key=True)
    this    = db.Column(db.String(10))
    that    = db.Column(db.String(10))
    parts   = db.relationship("Part")

    schema = ["id"
        ,   "this"
        ,   "that"
        ,       "parts"
        ]

    def __json__(self):
        return self.schema

class Part(db.Model):
    __tablename__ = "parts"

    id          = db.Column(db.Integer, primary_key=True)
    engine_id   = db.Column(db.Integer, db.ForeignKey("engines.id"))
    code        = db.Column(db.String(10))

    def __json__(self):
        return ["id", "code"]

How do I change the schema attribute before query so that it takes effect on the return data?

enginelist = db.session.query(Engine).all()
return enginelist

So far, I have succeeded with subclassing and single-table inheritance like so:

class Engine_smallschema(Engine):
    __mapper_args__ = {'polymorphic_identity': 'smallschema'}

    schema = ["id"
        ,   "this"
        ,   "that"
        ]

and

enginelist = db.session.query(Engine_smallschema).all()
return enginelist

...but it seems there should be a better way without needing to subclass (I'm not sure if this is wise). I've tried various things such as setting an attribute or calling a method to set an internal variable. Problem is, when trying such things, the query doesn't like the instance object given it and I don't know SQLAlchemy well enough yet to know if queries can be executed on pre-made instances of these classes.

I can also loop through the returned objects, setting a new schema, and get the wanted JSON, but this isn't a solution for me because it launches new queries (I usually request the small dataset first).

Any other ideas?

score 0 · Answer 1 · answered Mar 01 '16 at 08:17

0

The JSON serialization takes place in flask, not in SQLAlchemy. Thus, the __json__ function is not consulted until after you return from your view function. This has therefore nothing to do with SQLAlchemy, and instead it has to do with the custom encoding function, which presumably you can change.

I would actually suggest not attempting to do it this way if you have different sets of attributes you want to serialize for a model. Setting a magic attribute on an instance that affects how it's serialized violates the principle of least surprise. Instead, you can, for example, make a Serializer class that you can initialize with the list of fields you want to be serialized, then pass your Engine to it to produce a dict that can be readily converted to JSON.

If you insist on doing it your way, you can probably just do this:

for e in enginelist:
    e.__json__ = lambda: ["id", "this", "that"]

Of course, you can change __json__ to be a property instead if you want to avoid the lambda.

answered Mar 01 '16 at 08:17

univerio

19,548
3
66
68

In general, I agree with what you say, which is why I asked. I had thought what I returned with `__json__` affected the query that SQLAlchemy writes but I looked at it closer and see it doesn't. I'm just a month into learning all of this (Python, Flask, SQLAlchemy, etc) and it is a lot to grasp all at once. Thus, I don't yet understand the serializer I'm using well enough to see how to tweak it. I could switch to a tool like `marshmallow` but I'm trying to avoid a lot of DSL (more to grok). The last part of your response with the loop is what I was referring to in the last part of my question. – juanitogan Mar 02 '16 at 00:34
So, I've been reading the Query API doc the last couple days looking to do things different if I can. I've discovered I can get the "small schema" from the large schema with `enginelist = db.session.query(Engine).options(noload(Engine.parts)).all()`. This doesn't meet my implied original request to change the JSON return however I want but it does meet my need to eliminate larger queries where I don't need them. – juanitogan Mar 02 '16 at 03:54
@juanitogan By default, SQLAlchemy will not load `Engine.parts` if you don't access `.parts` on an `Engine` instance. Doing `noload` will just make SQLAlchemy not return anything when you do `.parts` on an `Engine` instance. If you do the serialization correctly, `.parts` won't be loaded anyway. – univerio Mar 02 '16 at 03:56
By default, yes, but not by how jsontools is working with the various lazy settings on the relationship. Doing the serialization "correctly" to not load `parts` is not the point. That is easy to do. The point is to allow for flexible serialization without great departure from baseline code. For example: to build up an efficient RESTful API that returns only what you need for a given route. Thus far, I have a subclass solution and an `.options` solution, and I'm shopping for something better. You can tell me to build a better serializer but I don't have time for such an adventure. – juanitogan Mar 03 '16 at 06:52

How do I set the attributes of a SQLAlchemy ORM class before query?

1 Answers1