from sqlalchemy import *
db = create_engine( 'sqlite:///:memory:')
meta = BoundMetaData( db)
import sys
meta.engine.echo= 'echo' in sys.argv

table_Address = Table( 'Address', meta,
    Column( 'home1_id', Integer, ForeignKey( 'Home.db_id', ), ),
    Column( 'street1',   type= String, ),
    Column( 'kvartal',   type= String, ),
    Column( 'street',   type= String, ),
    Column( 'owner_id', Integer, ForeignKey( 'Person.db_id', ), ),
    Column( 'home_id', Integer, ForeignKey( 'Home.db_id', ), ),
    Column( 'db_id',   primary_key= True,   type= Integer, ),
)
table_Home = Table( 'Home', meta,
    Column( 'num',   type= String, ),
    Column( 'db_id',   primary_key= True,   type= Integer, ),
)
table_Person = Table( 'Person', meta,
    Column( 'alias',   type= String, ),
    Column( 'age',   type= Integer, ),
    Column( 'name',   type= String, ),
    Column( 'friend_id', Integer, ForeignKey( 'Person.db_id',     name= 'friend_id_fk',     use_alter= True, ), ),
    Column( 'address_id', Integer, ForeignKey( 'Address.db_id',     name= 'address_id_fk',     use_alter= True, ), ),
    Column( 'db_id',   primary_key= True,   type= Integer, ),
)

meta.create_all()

class Base( object):
    def __str__( self):
        r = self.__class__.__name__ + '('
        for k in self.props:
            v = getattr( self, k, '<notset>')
            if isinstance( v, Base):
                v = '>'+str(v.db_id)+'/'+str(v.name)
            r+= ' '+k+'='+str(v)
        return r+' )'

class Address( Base):
    props = ['db_id', 'home1', 'street1', 'kvartal', 'street', 'owner', 'home']
class Home( Base):
    props = ['db_id', 'num']
class Person( Base):
    props = ['db_id', 'age', 'name', 'friend', ]
    def __init__( me, **kargs):
        for k in me.props:
            try:
                setattr( me, k, kargs[k])
            except KeyError: pass
if 0:
    mapper_Address = mapper( Address, table_Address,
                polymorphic_identity= 'Address',
                )
    mapper_Address.add_property( 'owner', relation( Person,
                foreignkey= table_Address.c.owner_id,
                lazy= False,
                primaryjoin= table_Address.c.owner_id == table_Person.c.db_id,
                remote_side= table_Person.c.db_id,
                uselist= False,
                ) )
    mapper_Address.add_property( 'home', relation( Home,
                foreignkey= table_Address.c.home_id,
                lazy= False,
                primaryjoin= table_Address.c.home_id == table_Home.c.db_id,
                remote_side= table_Home.c.db_id,
                uselist= False,
                ) )
    mapper_Address.add_property( 'home1', relation( Home,
                foreignkey= table_Address.c.home1_id,
                lazy= False,
                primaryjoin= table_Address.c.home1_id == table_Home.c.db_id,
                remote_side= table_Home.c.db_id,
                uselist= False,
                ) )

mapper_Home = mapper( Home, table_Home,
            polymorphic_identity= 'Home',
            )

mapper_Person = mapper( Person, table_Person,
            polymorphic_identity= 'Person',
            )
mapper_Person.add_property( 'friend', relation( Person,
            foreignkey= table_Person.c.friend_id,
            lazy= True,
            post_update= True,
            primaryjoin= table_Person.c.friend_id == table_Person.c.db_id,
            remote_side= table_Person.c.db_id,
            uselist= False,
            ) )
if 0:
    mapper_Person.add_property( 'address', relation( Address,
            foreignkey= table_Person.c.address_id,
            lazy= True,
            post_update= True,
            primaryjoin= table_Person.c.address_id == table_Address.c.db_id,
            remote_side= table_Address.c.db_id,
            uselist= False,
            ) )

p1 = Person( age=87, name='aa')
p2 = Person( age=12, name='bb')
p3 = Person( age=65, name='cc')
p4 = Person( age=34, name='dd')
p5 = Person( age=78, name='ee')
p6 = Person( age=56, name='ff')
p7 = Person( age=66, name='gg')

p1.friend=None
p2.friend=p2#bb
p3.friend=p3#cc
p4.friend=p2#bb
p5.friend=p6#ff
p6.friend=p4#dd
p7.friend=p2#bb
all = [p1,p2,p3,p4,p5,p6,p7]

session = create_session()
for p in all:#locals().values():
    if isinstance( p,Person):
        session.save(p)
session.flush()
expected = [ (p.db_id,str(p)) for p in all]
session.clear()

print '========objects:'
for q in all: print q

print '========db:'
for q in session.query(Person).select():
    print q

for (pid,pstr) in expected:
    p = session.query(Person).get_by_db_id(pid)
    assert str(p) == pstr, 'expect:%(pstr)s, result:%(p)s' % locals()