CODEWITHSUNDEEP
sqlalchemy
ChaimKut
ChaimKut

Reputation: 2809

Avoiding object creation with SQL Alchemy

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.

Upvotes: 0

Views: 309

Answers (2)

zzzeek
zzzeek

Reputation: 75217

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.

Upvotes: 1

Victor Shelepen
Victor Shelepen

Reputation: 2256

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

....

Upvotes: 1

Related Questions