I'm currently designing the business and persistence layer that is going to 
be used in various frontend applications (Web and standalone). To that end 
I've been trying to reconcile ORM entities with a tight session scope but 
I'm constantly running into the same issues. For web I haven't had a big 
problem as sessions scoped to a single request work just fine, but for 
standalone applications I've been having architectural problems. To 
illustrate this, I'm using a simplified example.

*Setup - Model and database definition*


class _Base(object):
    def __tablename__(cls):    # This just generates table name from the class 
        name = cls.__name__
        table_name = name[0]
        for c in name[1:]:
            if c.isupper():
                table_name += '_'
            table_name += c
        return table_name.lower()

    id = Column(Integer, primary_key=True)

Base = declarative_base(cls=_Base)

class Tree(Base):
    type = Column(String(200))

    branches = relationship("Branch", back_populates="tree")

    def __repr__(self):
        return "<Tree(id='{}', type='{}', branches='{}')>".format(self.id, 
self.type, self.branches)

class Branch(Base):
name = Column(String(200))

    tree_id = Column(Integer, ForeignKey('tree.id'), nullable=False)
    tree = relationship("Tree", back_populates='branches')

leaves = relationship("Leaf", back_populates='branch')

def __repr__(self):
return "<Branch(id='{}', name='{}', leaves='{}')>".format(self.id, self.name, 

class Leaf(Base):
size = Column(Integer)

branch_id = Column(Integer, ForeignKey('branch.id'), nullable=False)
branch = relationship("Branch", back_populates='leaves')

def __repr__(self):
return "<Leaf(id='{}', size='{}')>".format(self.id, self.size)

# Database setup

db_conn_string = "sqlite://"
self.engine = create_engine(db_conn_string)
Base.metadata.bind = self.engine
self.DBSession = sessionmaker(bind=self.engine)


Since this layer will be interacted with by many other developers in the 
future I wanted to abstract away session management. This in turn means I 
want to prevent potential side effects from occurring.

As an example: In a scenario where someone modifies a Leaf object (but 
doesn't want to save it yet) and then proceeds to modify a Branch object 
elsewhere and saves it. The expected behaviour here is that the Branch 
modification is persisted, but not the Leaf changes. So clearly having an 
application wide session is not a good idea, but how do I properly separate 
this out?

*Attempted solutions*

*1. Detached entities with eager loading.*

Initially I simply closed each session once a context was closed and tried 
to use the detached objects.

class RepositoryContext(object):
    def __enter__(self):
        self.session = get_session()
        return CrudRepository(self.session)    # Provides simple crud methods 
like add(entity), retrieve_all(entity_class), etc...

    def __exit__(self, exc_type, exc_val, exc_tb):
        except Exception:

I mark all relationships I want as eager loaded relations using 
`lazy='subquery'` and remove relationship definitions where that is not the 
case. My new model looks like this:

class Tree(Base):
    type = Column(String(200))

    branches = relationship("Branch", lazy="subquery")

    def __repr__(self):
        return "<Tree(id='{}', type='{}', branches='{}')>".format(self.id, 
self.type, self.branches)

class Branch(Base):
name = Column(String(200))

    tree_id = Column(Integer, ForeignKey('tree.id'), nullable=False)

def __repr__(self):
return "<Branch(id='{}', name='{}')>".format(self.id, self.name)

class Leaf(Base):
size = Column(Integer)

branch_id = Column(Integer, ForeignKey('branch.id'), nullable=False)

def __repr__(self):
return "<Leaf(id='{}', size='{}')>".format(self.id, self.size)

So if I want to get a relationship that was previously lazy loaded I'd have 
to load it within a RepositoryContext, which I could live with.

The problem happens when I start updating entries. Because of the detached 
nature I'm forced to manually refresh entities each time they are updated. 
This means instead of a simple update statement I now have to perform this 
*merge-commit-add-refresh* cycle for every entity. It technically works but 
it's performing a lot more database requests than it should and I fear this 
will not scale properly.

*2. Separate commit session*

Another solution I've tried is to have two sessions, one that is 
application wide and another that is newly created within a new context. 
The idea is to have a "link_session" to which entities keep attached to so 
they can load/refresh attributes and have a "merge_session" which perform 
insert/updates/removals. Whilst seeming like a good idea at first I seem to 
be having trouble actually transferring objects to the link_session after 
adding them. My current solution (which is only called within a context):

def add(self, entity):
    except FlushError:

*3. Complete separation of domain and entity models*

I initially went down this route but as I went on it basically looked like 
I was essentially not using the ORM at all and constantly mapping entities 
to domain objects and back again, at which point I might as well write the 
SQL directly in the repository functions.


Any thoughts on these or better approaches would be much appreciated. I may 
have been staring at this problem for too long and lost sight of some 
simple solution.

Kind Regards,

Kevin CYT

SQLAlchemy - 
The Python SQL Toolkit and Object Relational Mapper


To post example code, please provide an MCVE: Minimal, Complete, and Verifiable 
Example.  See  http://stackoverflow.com/help/mcve for a full description.
You received this message because you are subscribed to the Google Groups 
"sqlalchemy" group.
To unsubscribe from this group and stop receiving emails from it, send an email 
to sqlalchemy+unsubscr...@googlegroups.com.
To post to this group, send email to sqlalchemy@googlegroups.com.
Visit this group at https://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.

Reply via email to