from sqlalchemy import Column, ForeignKey, Integer, String, create_engine
from sqlalchemy.orm import relationship, backref, scoped_session, sessionmaker
from sqlalchemy.ext.declarative import declarative_base, declared_attr

Base = declarative_base()

class Content(Base):
    id = Column(Integer, primary_key=True)
    type = Column(String(250))
    
    __tablename__ = 'content'
    __mapper_args__ = { 'polymorphic_identity' : 'content',
                        'polymorphic_on' : type
                      }

class User(Content):
    __tablename__ = 'users'
    __mapper_args__ = { 'polymorphic_identity' : 'user' }

    id = Column(Integer, ForeignKey('content.id'), primary_key=True)
    name = Column(String(50))

class Address(Content):
    __tablename__ = 'addresses'
    __mapper_args__ = { 'polymorphic_identity' : 'address' }

    id = Column(Integer, ForeignKey('content.id'), primary_key=True)
    email = Column(String(50))


class UserAddresses(Content):
    __tablename__ = 'useraddresses'
    __mapper_args__ = { 'polymorphic_identity' : 'useraddress' }

    id = Column(Integer, ForeignKey('content.id'), primary_key=True)
    user_id = Column(Integer, ForeignKey('users.id'))
    user = relationship(User)
    address_id = Column(Integer, ForeignKey('addresses.id'))
    address = relationship(Address)

engine = create_engine('sqlite://')
Base.metadata.create_all(engine)

Session = scoped_session(sessionmaker(autocommit=False,
                                      autoflush=False,
                                      bind=engine))

session = Session()

u1 = User(name='Paul')
a1 = Address(email = 'paul@smith.com')
a2 = Address(email = 'work@somewhere.com')

u1a1 = UserAddresses(user = u1, address = a1)
u1a2 = UserAddresses(user = u1, address = a2)

for obj in [u1, a1, a2, u1a1, u1a2]:
    session.add( obj )
session.commit()

for assignment in session.query(UserAddresses).filter(UserAddresses.user == u1).all():
    print assignment.user.name, assignment.address.email