sqlalchemy from_statement dynamic attributes for python objects

Question

I have such model:

class Test(db.Model, UnicodeMixin):
    __tablename__ = 'test'

    id = db.Column(db.Integer, primary_key=True)

    subject = db.Column(db.String(512), nullable=False)

    additional = None

    def __unicode__(self):
        return u'<Test {0}>'.format(self.id)

Some code generate RAW SQL for very difficult SELECT from db with additional dynamic data.

For example it like:

db.session.query(Test).from_statement("SELECT test.id AS test_id, test.subject AS test_subject, 99 AS additional FROM test").all()

It generate list of python objects Test, but how I can fill attribute additional for these python objects?

I don't want store NULL column additional in database.

I want create dynamic additional data with SQL and add it to python objects.

Help please.

Thanks.

Arad · Accepted Answer · 2016-11-08 19:52:38Z

1

Do this:

db.session.query(Test, "additional").from_statement(
    """
    SELECT test.id AS test_id, test.subject AS test_subject, 99 AS additional
    FROM test
    """
).all()

It will return a list of tuples in the (Test object,additional) structure.

answered Nov 8, 2016 at 19:52

Arad

6337 silver badges15 bronze badges

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

1 Comment

emerix Over a year ago

This is pretty cool, thanks for sharing! I use it to get total count in one query: count(1) OVER() AS total_count

Collectives™ on Stack Overflow

sqlalchemy from_statement dynamic attributes for python objects

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related