title: What is an efficient SqlAlchemy design pattern to assemble complex objects?
body: <p><strong>Short Description of my issue:</strong>
  The short version of this problem is that I need an elegant and efficient way to retrieve a customer with all its related data from the Database as an object.</p>
  <p><strong>Details:</strong>
  I've created the following design to interact with my db using sqlAlchemy, where each table is a Model that retrieves rows and converts them to objects. However the Customer Model has this method called <em>GetWithChildren</em> that seems to me is inefficient since it instantiates other models (Names, Address, etc...) and each of those models make db calls to get its own data. (see implementation below)</p>
  <p><strong>Questions:</strong>
  Is there a better design pattern I can use with SqlAlchemy to interact with my db?</p>
  <hr />
  <p>Here are the details of my current implementation:</p>
  <p>I have a class called Entity that has most common methods. Each table will have its own class, which is extends this Entity class.</p>
  <pre><code>    Class Entity(object):
        def __init__(self, tablename, username, session):
          self.acting_username = username
          self.session = session
          self.table = db.TablesMetadata()[tablename]
          self.session = session
  
        def ConvertRowToObject(self,row):
          &quot;&quot;&quot; Returns a Python Dic representing the row.
          each method model needs to define how to implement.
          &quot;&quot;&quot;
          ...
    
        ## Most models will be related to Customer, so making this generic method here
        def QueryByCustomerId(self, customer_id):
          kwargs = {customer_id; customer_id}
          rows = self.session.query(self.table).filter_by(**kwargs) 
          return [self.ConvertRowToObject(row) for row in rows]
          
  </code></pre>
  <p>Then each table model extends the Entity Class</p>
  <pre><code>    Class Address(Entity)
        def __init__(self, session, username):
          table_name = 'Address'
          super(Customer, self).__init__(tablebname, username, session)
          self.table = db.TablesMetadata()[tablename]
          self.session = session
          self.entity_msg = {
            &quot;id&quot;: null,
            &quot;customer_id&quot;: null,
            &quot;address&quot;: null
          }
  
        def ConvertRowToObject(self, row):
          &quot;&quot;&quot; converts the row to a dic as defined by self.entity_msg&quot;&quot;&quot;
          ...
          return self.entity_msg
  
        def Get(self, id):
          &quot;&quot;&quot; Returns the row containing the record id &quot;&quot;&quot;
          ...
          returns row
  
  
      Class Name(Entity)
        def __init__(self, session, username):
          table_name = 'Names'
          super(Customer, self).__init__(tablebname, username, session)
          self.table = db.TablesMetadata()[tablename]
          self.session = session
          self.entity_msg = {
            &quot;id&quot;: null,
            &quot;customer_id&quot;: null,
            &quot;name&quot;: null
          }
  
        def ConvertRowToObject(self, row):
          &quot;&quot;&quot; converts the row to a dic as defined by self.entity_msg&quot;&quot;&quot;
          ...
          return self.entity_msg
  </code></pre>
  <p>The Customer Object will have all other tables, so I have a GetWithChildren method that gets the customer and all the related data as such:</p>
  <pre><code>
      Class Customer(Entity)
        def __init__(self, session, username):
          table_name = 'Customers'
          super(Customer, self).__init__(tablebname, username, session)
          self.table = db.TablesMetadata()[tablename]
          self.session = session
          self.entity_msg = {
            &quot;id&quot;: null,
            &quot;addresses&quot;: null, # &lt;-This is a list of Addresses
            &quot;names&quot;: null # &lt;-This is a list of names
          }
  
        def ConvertRowToObject(self, row):
          &quot;&quot;&quot; converts the row to a dic as defined by self.entity_msg&quot;&quot;&quot;
          ...
          return self.entity_msg
  
        def GetWithChildren(self, customer_id):
  
          customer_obj = self.ConvertRowToObject(self.Get(customer_id))
          customer_children = [(Names, customer_obj.names), (Address, customer_obj.addresses)]
  
          for child in customer_children:
            child_model = child[0]
            customer_child = child[1]
            entity = child_model.(self.username, session)
            customer_child = entity.QueryByCustomerId(customer_id)            
          return customer_obj
  </code></pre>