Avoiding object creation with SQL Alchemy

Question

Short question: How do I avoid object creation when fetching from an SQL Alchemy-backed object?

Situation: SQL Alchemy: I have an ORM object (declarative base) Objects backed by a table with columns uid, name, etc. Now I would like to create a separate table ObjectProps which uses uid as a foreign, primary key , and then an additional Text field. Additionally, it is setup as a relationship such that ObjectProps has a relationship called props with ObjectProps (uselist=False, backref="object").

Long question: How can I retrieve ObjectProps's Text attribute/column via props without generating the ObjectProps ORM object? I would ideally like to avoid the object creation overhead since ideally I just want to retrieve the column's value without creating an entire ObjectProps.

Presumably the solution will require mixing the ORM Layer with the SQL Layer. Any help appreciated.

zzzeek · Accepted Answer · 2014-08-30 21:27:50Z

Everything the ORM loads when you use relationship() is going to be an object. It depends here on if the issue is the performance and memory overhead of loading ObjectProps, or if it's just that you want to access the columns in a nice way without "seeing" ObjectProps. For the latter, this is easy using the association proxy and we have examples of this. For the former, yes you'd need to use core if you just want a raw SELECT. You can perhaps use a descriptor. Below is one that does a SELECT from a table when first accessed:

class MyObject(Base):
    # ...

    _properties = None

    @property
    def my_properties(self):
        if self._properties:
            return self._properties
        self._properties = dict(
               (row.key, row.value) for row in 
               object_session(self).execute(
                     object_table.select().
                     where(object_table.c.parent_id == self.id)
               )
        )
        return self._properties

There's some rudimental memoization going on above. If you wanted to do that, and link _properties to the lifecycle of the object, you can link it to events like expire so that you'd know when to clear it out.

Victor Shelepen · Accepted Answer · 2014-08-29 19:14:26Z

1

I divide querying into: guering of objects and quering of data like SQL does. Try look at http://docs.sqlalchemy.org/en/rel_0_9/core/tutorial.html#selecting http://docs.sqlalchemy.org/en/latest/core/selectable.html

http://docs.sqlalchemy.org/en/rel_0_9/orm/query.html Using quering:

.session.query(
        UserModel.id,
        UserModel.username,
        UserModel.email,
        UserModel.best_friend_id,
        FUserModel.username.label('friend_username'),
        func.count().label('friends')
      )\
      .select_from(UserModel)\

....

answered Aug 29, 2014 at 19:14

Victor Shelepen

2,3663 gold badges24 silver badges54 bronze badges

Collectives™ on Stack Overflow

Avoiding object creation with SQL Alchemy

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related