title: SQLAlchemy Recursion
body: <p>I'm having a great time learning Python, but I've just gotten a bit stuck on trying to incorporate a recursive function into SQLAlchemy.  </p>
  
  <p>Essentially, have a function which creates a instance of a class to be put in the database.  Inside this function, I get user input on whether the instance has a parent class (defined using a self-referential adjacency table).  If it does, the function is then called again, recursively. This function seems to work if no parent class is needed, but whenever the recursive element is activated it crashes.</p>
  
  <p>My code is like so:</p>
  
  <pre><code>engine = create_engine('sqlite:///recDB.db')
  Session = sessionmaker(bind=engine)
  session = Session()
  Base = declarative_base()
  
  class IngList(Base):
      __tablename__ = "ingList"
  
      id = Column(Integer, primary_key = True)
      ingredient = Column(String, nullable=False)
      parentIng = Column(Integer, ForeignKey('ingList.id'))
      children = relationship("IngList",backref=backref('parent', remote_side=[id]))
  
      def __init__(self, ingredient):
          self.ingredient = ingredient
  
  def addIngredient(ingredient):
      global newIngList
      newIng = IngList(ingName) #create new class instance
      parentIng = raw_input("To add parent ingredient, type it.  Otherwise press enter")
      if parentIng != '':
          parentIngObj = addIngredient(parentIng) # Recursion!
          newIng.parentIng = parentIngObj
      newIngList.append(newIng)
  
  if __name__ == '__main__':
      newIngList = []
      ingredient = raw_input("Enter new ingredient")
      addIngredient(ingredient)
      for ing in newIngList
          session.add(ing)
      session.commit()
  </code></pre>
  
  <p>I've kept this sample simple to keep it readable, but if I'm missing some important information, please let me know.  </p>
  
  <p>I assumed that my problem was that the class instances were losing scope as I recursed, but adding the global to the list variable did not seem to fix that.  I also thought that the fact that a session acts as sort of a buffer would handle any scope problems. </p>
  
  <p>Does this have something to do with <a href="http://www.sqlalchemy.org/docs/orm/relationships.html?highlight=adjacency%2520list#configuring-self-referential-eager-loading" rel="nofollow">eager loading</a>?  I saw that in the documentation, but don't really understand it. </p>
  
  <p>The error I'm getting is:</p>
  
  
  
  <pre><code>Traceback (most recent call last):
    File "C:\workspace\recipes\langProc.py", line 102, in &lt;module&gt;
      session.commit()
    File "c:\Python27\lib\site-packages\sqlalchemy\orm\session.py", line 645, in commit
      self.transaction.commit()
    File "c:\Python27\lib\site-packages\sqlalchemy\orm\session.py", line 313, in commit
      self._prepare_impl()
    File "c:\Python27\lib\site-packages\sqlalchemy\orm\session.py", line 297, in _prepare_impl
      self.session.flush()
    File "c:\Python27\lib\site-packages\sqlalchemy\orm\session.py", line 1547, in flush
      self._flush(objects)
    File "c:\Python27\lib\site-packages\sqlalchemy\orm\session.py", line 1616, in _flush
      flush_context.execute()
    File "c:\Python27\lib\site-packages\sqlalchemy\orm\unitofwork.py", line 328, in execute
      rec.execute(self)
    File "c:\Python27\lib\site-packages\sqlalchemy\orm\unitofwork.py", line 472, in execute
      uow
    File "c:\Python27\lib\site-packages\sqlalchemy\orm\mapper.py", line 2153, in _save_obj
      execute(statement, params)
    File "c:\Python27\lib\site-packages\sqlalchemy\engine\base.py", line 1399, in execute
      params)
    File "c:\Python27\lib\site-packages\sqlalchemy\engine\base.py", line 1532, in _execute_clauseelement
      compiled_sql, distilled_params
    File "c:\Python27\lib\site-packages\sqlalchemy\engine\base.py", line 1640, in _execute_context
      context)
    File "c:\Python27\lib\site-packages\sqlalchemy\engine\base.py", line 1633, in _execute_context
      context)
    File "c:\Python27\lib\site-packages\sqlalchemy\engine\default.py", line 330, in do_execute
      cursor.execute(statement, parameters)
  sqlalchemy.exc.InterfaceError: (InterfaceError) Error binding parameter 0 
  - probably unsupported type.
  u'UPDATE "ingList" SET "parentIng"=? WHERE "ingList".id = ?' 
  (&lt;assignDB.IngList object at 0x00000000096969E8&gt;, 4)
  </code></pre>