CODEWITHSUNDEEP
pythonsqlalchemy
Cristhian Boujon
Cristhian Boujon

Reputation: 4190

Performance one-to-many relationship in SQLAlchemy

I'm trying to define a one-to-many relationship with SqlAlchemy where I have Parent has many Child

class Parent(Base):
    __tablename__ = "parent"

    id = Column(String, primary_key = True)
    children = relationship("Child")


class Child(Base):
    __tablename__ = "child"

    id = Column(Integer, primary_key = True) 
    feed_type_id = Column(String, ForeignKey("parent.id"))

From business rules, Parent has no much Child (between 10 and 30) and most of the time I will need access to all of them so I think that it's good idea that relationship() retrieve all children in memory in order to increase performance (First question: am I right?) but Few times I need to get a particular child but I won't do something like:

def search_bar_attr(some_value)
    for bar in foo.bars:
        if(bar.attr == some_value)
            return bar

lazy="dynamic" returns a list that allows queries but I think it's slow against "eagerly" loaded because dynamic relationship always queries the database.

Second question: Is there some configuration that covers all my needs?

Upvotes: 1

Views: 2250

Answers (3)

Sam Hartman
Sam Hartman

Reputation: 6499

No one strategy will give you everything. However, you can choose a default strategy and then override it. My recommendation would be to:

  • Add lazy = "joined" to your relationship so that by default, you will get all the parents.

  • In cases where you want to query for a set of children dependent on properties of their parents but don't need the parent objects, use the join function on the query and filters referring both to the parent and child

  • In cases where you need to construct a query similar to what lazy = "dynamic" would do, use the sqlalchemy.orm.defer operator to turn off your lazy = "joined" eager loading and the loading interface( to override eager loading and then use with_parent to construct query. a query like you would have gotten with lazy = "dynamic"

Upvotes: 0

univerio
univerio

Reputation: 20518

You can construct the same query that lazy="dynamic" does by using .with_parent.

class Parent(Base):
    ...
    @property
    def children_dynamic(self):
        return object_session(self).query(Child).with_parent(self, Parent.children)

You can even add a function to reduce boilerplate if you have to write a lot of these:

def dynamicize(rel):
    @property
    def _getter(self):
        return object_session(self).query(rel.parent).with_parent(self, rel)
    return _getter

class Parent(Base):
    ...
    children = relationship("Child")
    children_dynamic = dynamicize(children)

Upvotes: 3

oliver2213
oliver2213

Reputation: 141

You don't need to use a function like that one, you don't even need to load all of the child objects in memory.
When you want to search for a child with a certain attribute, you can do:

# get a session object, usually with sessionmaker() configured to bind to your engine instance
c = session.query(Child).filter_by(some_attribute="some value here").all() # returns a list of all child objects that match the filter
# or: to get a child who belongs to a certain parrent with a certain attribute:
# get the parent object (p)
c = session.query(Child).filter_by(feed_type_id=p.id).filter_by(some_attr="some attribute that belongs to children of the p parrent object")

Upvotes: 1

Related Questions