Compatibility for SQLAlchemy 0.9's new declarative syntax

[leavittx]

It seems like it's not possible to use recently introduced declarative table description syntax (http://docs.sqlalchemy.org/ru/latest/orm/extensions/declarative.html) with sqlalchemy-orm-tree extension!

My code goes like that:

from sqlalchemy import *
from sqlalchemy.orm import *
from sqlalchemy_tree import TreeManager

from sqlalchemy.pool import StaticPool
engine = create_engine('sqlite://',
                       connect_args={'check_same_thread':False},
                       poolclass=StaticPool,
                       echo=False)
session = Session(bind=engine)

from sqlalchemy.ext.declarative import declarative_base
Base = declarative_base()

class Node(Base):
    __tablename__ = 'node'
    id = Column(Integer, primary_key=True)
    parent_id = Column(Integer, ForeignKey('node.id'))
    name = Column(String(50), nullable=False)
    parent = relationship('Node',
                             backref     = backref('children'),
                             remote_side = [id])

Node.tree = TreeManager(Base.metadata.tables['node'])
Base.metadata.create_all(engine)
Node.tree.register()

root = Node(name='Root node')
session.add(root)
session.flush()

Which gives the following traceback:

Traceback (most recent call last):
  File "C:\Users\Lev\Downloads\projects\reabic\xls_importer\playground\sqlalchemy_tree_test.py", line 107, in <module>
    session.flush()
  File "C:\Python33\lib\site-packages\sqlalchemy-0.9.0dev-py3.3-win-amd64.egg\sqlalchemy\orm\session.py", line 1844, in flush
    self._flush(objects)
  File "C:\Python33\lib\site-packages\sqlalchemy-0.9.0dev-py3.3-win-amd64.egg\sqlalchemy\orm\session.py", line 1962, in _flush
    transaction.rollback(_capture_exception=True)
  File "C:\Python33\lib\site-packages\sqlalchemy-0.9.0dev-py3.3-win-amd64.egg\sqlalchemy\util\langhelpers.py", line 56, in __exit__
    compat.reraise(exc_type, exc_value, exc_tb)
  File "C:\Python33\lib\site-packages\sqlalchemy-0.9.0dev-py3.3-win-amd64.egg\sqlalchemy\util\compat.py", line 191, in reraise
    raise value
  File "C:\Python33\lib\site-packages\sqlalchemy-0.9.0dev-py3.3-win-amd64.egg\sqlalchemy\orm\session.py", line 1926, in _flush
    flush_context.execute()
  File "C:\Python33\lib\site-packages\sqlalchemy-0.9.0dev-py3.3-win-amd64.egg\sqlalchemy\orm\unitofwork.py", line 372, in execute
    rec.execute(self)
  File "C:\Python33\lib\site-packages\sqlalchemy-0.9.0dev-py3.3-win-amd64.egg\sqlalchemy\orm\unitofwork.py", line 525, in execute
    uow
  File "C:\Python33\lib\site-packages\sqlalchemy-0.9.0dev-py3.3-win-amd64.egg\sqlalchemy\orm\persistence.py", line 45, in save_obj
    uowtransaction)
  File "C:\Python33\lib\site-packages\sqlalchemy-0.9.0dev-py3.3-win-amd64.egg\sqlalchemy\orm\persistence.py", line 149, in _organize_states_for_save
    mapper.dispatch.before_insert(mapper, connection, state)
  File "C:\Python33\lib\site-packages\sqlalchemy-0.9.0dev-py3.3-win-amd64.egg\sqlalchemy\event\attr.py", line 236, in __call__
    fn(*args, **kw)
  File "C:\Python33\lib\site-packages\sqlalchemy-0.9.0dev-py3.3-win-amd64.egg\sqlalchemy\orm\events.py", line 506, in wrap
    wrapped_fn(*arg, **kw)
  File "C:\Python33\lib\site-packages\sqlalchemy_tree\orm.py", line 195, in before_insert
    tree_id = self._get_next_tree_id(connection, session_objs)
  File "C:\Python33\lib\site-packages\sqlalchemy_tree\orm.py", line 492, in _get_next_tree_id
    for n in session_objs] if tree_id is not None]
  File "C:\Python33\lib\site-packages\sqlalchemy_tree\orm.py", line 492, in <listcomp>
    for n in session_objs] if tree_id is not None]
AttributeError: 'Node' object has no attribute 'tree_id'

So I guess the problem might be that code executed by line

Node.tree = TreeManager(Base.metadata.tables['node'])

isn't working correctly.

[maaku]

Correct. This is known albeit not well documented issue. The problem is that TreeManager needs to be added to the class after the table is defined, but before the mapper. The declarative API's Base metaclass does all this in one step.

There is probably a way around this, but since we no longer user orm-tree in production (because we no longer use SQLAlchemy, not because of any problem with the code), I haven't had the time to properly look into this. A pull request would be welcome.

PS: I know this is just an example, but I would not recommend check_same_thread with sqlite.

[maaku]

Wait, I could have been more helpful. There is a solution: just add the following fields to your class.

tree_id = Column(TreeIdType, nullable=False)
left = Column(TreeLeftType, nullable=False)
right = Column(TreeRightType, nullable=False)
depth = Column(TreeDepthType, nullable=False)

These column types are found in sqlalchemy_tree.types. You can give the columns whatever names you like (their presence is auto-detected from the column type).

The original failure is that these columns were being added after the mapper was initialized, and therefore weren't mapped. Defining them explicitly obviously solves that, albeit by removing some of the magic of TreeManager.