Inheritance + Foreign Keys
I have been absolutely racking my brain over this, and can't seem to work out how to get around the issue. Please note that I have cut alot of irrelevant fields out of my models

I am in the middle of coding up my SQL-Alchemy models, and have encountered the following issue:

Due to multiple billing systems, each with completely different attributes, and due to the different types of Subscription attributes (i.e. usernames, node locations, etc), I have had to go down the polymorphic inheritance road.

    class Subscription(Base):
        id = db.Column(db.Integer, primary_key=True)
        name = db.Column(db.String(255))
        secret = db.Column(postgresql.BYTEA)
        type = db.Column(SubscriptionType.db_type())
        status = db.Column(StatusType.db_type())
        subscription_id = db.Column(db.Integer)
    
        __tablename__ = 'subscription'
        __table_args__ = {'schema':'public'}

        __mapper_args__ = {'polymorphic_on' : type}

        def __repr__(self):
            return '<Subscription: %r>' % self.id

    class Product1(Subscription):
        __mapper_args__ = {'polymorphic_identity' : SubscriptionType.product1}    
        id = db.Column(db.Integer, db.ForeignKey('public.subscription.id'), 
            primary_key = True)
        billing_system = db.Column(
            db.Integer, 
            db.ForeignKey('public.billing_system.id')
        )

    class BillingSystem(Base):
        id = db.Column(db.Integer, primary_key=True)
        name = db.Column(db.String(255))
        type = db.Column(BillingSystemType.db_type())

        __tablename__ = 'billing_system'
        __table_args__ = {'schema':'public'}

        __mapper_args__ = {'polymorphic_on' : type}

        def __repr__(self):
            return '<Subscription: %r>' % self.id

    class BillingSystem1(BillingSystem):
        __mapper_args__ = {'polymorphic_identity' : BillingSystemType.billingsystem1}    
        id = db.Column(db.Integer, db.ForeignKey('public.billing_system.id'), 
            primary_key = True)
        billing_system = db.Column(
            db.Integer, 
            db.ForeignKey('public.billing_system.id')
        )
        foo = db.Column(db.Integer)
        bar = db.Column(db.Integer)

     class BillingSystem2(BillingSystem):
        __mapper_args__ = {'polymorphic_identity' : BillingSystemType.billingsystem2}    
        id = db.Column(db.Integer, db.ForeignKey('public.billing_system.id'), 
            primary_key = True)
        billing_system = db.Column(
            db.Integer, 
            db.ForeignKey('public.billing_system.id')
        )
        bing = db.Column(db.Integer)
        boo = db.Column(db.Integer)
        

        __tablename__ = 'billing_system_product2'
        __table_args__ = {'schema':'public'}


It is all working fine, except for one thing.

Say that I run the following:

    >>> a = Product1()
    >>> b = BillingSystem.objects.get(1)
    >>> a.billing_system = b
    >>> session.add(a)
    >>> session.commit()

I will receive the following error.

    sqlalchemy.exc.ProgrammingError: (ProgrammingError) can't adapt type 'BillingSystem1' 'INSERT INTO

I understand what it is saying, and I have tried the following.

    >>> a.billing_system = b.id

This only stores the ID, and when I try to retreive the associated object, I instead receive an integer. This involves me having to do an additional query. I would expect that this isn't the way to go.

I have also tried adding foreign keys for all of the billing system IDs to the Product1 Model

    class BillingSystem1(BillingSystem):
        __mapper_args__ = {'polymorphic_identity' : BillingSystemType.billingsystem1}    
        id = db.Column(db.Integer, db.ForeignKey('public.billing_system.id'), 
            primary_key = True)
        billing_system = db.Column(
            db.Integer, 
            db.ForeignKey('public.billing_system.id'),
            db.ForeignKey('public.billing_system1.id'),
            db.ForeignKey('public.billing_system2.id'),
        )
        foo = db.Column(db.Integer)
        bar = db.Column(db.Integer)

This also didn't work at all, and I received the same ProgrammingError exception stating that the type can't adapt.

I have trawled the manual, and can't find how to do it, but I need some form of magical option to allow this to happen:

    >>> a = BillingSystem.query.get(1)
    >>> type(a)
    BillingSystem

Instead of:

    >>> a = BillingSystem.query.get(1)
    >>> type(a)
    BillingSystem1

Is anyone able to shed any light on how I can query my set of Polymorphic models for an ID, and only obtain the class of the base model? 

I feel that this will solve my problem, im just not sure of how to work it out.

Thanks for taking the time to read this, and I would really like to find out where I am going wrong (I have also been awake way too long, so that isn't helping).

Cheers,
Rhys
Inheritance + Foreign Keys
===

I have been absolutely racking my brain over this, and can't seem to work out how to get around the issue. Please note that I have cut alot of irrelevant fields out of my models

I am in the middle of coding up my SQL-Alchemy models, and have encountered the following issue:

Due to multiple billing systems, each with completely different attributes, and due to the different types of Subscription attributes (i.e. usernames, node locations, etc), I have had to go down the polymorphic inheritance road.

    class Subscription(Base):
        id = db.Column(db.Integer, primary_key=True)
        name = db.Column(db.String(255))
        secret = db.Column(postgresql.BYTEA)
        type = db.Column(SubscriptionType.db_type())
        status = db.Column(StatusType.db_type())
        subscription_id = db.Column(db.Integer)
    
        __tablename__ = 'subscription'
        __table_args__ = {'schema':'public'}

        __mapper_args__ = {'polymorphic_on' : type}

        def __repr__(self):
            return '<Subscription: %r>' % self.id

    class Product1(Subscription):
        __mapper_args__ = {'polymorphic_identity' : SubscriptionType.product1}    
        id = db.Column(db.Integer, db.ForeignKey('public.subscription.id'), 
            primary_key = True)
        billing_system = db.Column(
            db.Integer, 
            db.ForeignKey('public.billing_system.id')
        )

    class BillingSystem(Base):
        id = db.Column(db.Integer, primary_key=True)
        name = db.Column(db.String(255))
        type = db.Column(BillingSystemType.db_type())

        __tablename__ = 'billing_system'
        __table_args__ = {'schema':'public'}

        __mapper_args__ = {'polymorphic_on' : type}

        def __repr__(self):
            return '<Subscription: %r>' % self.id

    class BillingSystem1(BillingSystem):
        __mapper_args__ = {'polymorphic_identity' : BillingSystemType.billingsystem1}    
        id = db.Column(db.Integer, db.ForeignKey('public.billing_system.id'), 
            primary_key = True)
        billing_system = db.Column(
            db.Integer, 
            db.ForeignKey('public.billing_system.id')
        )
        foo = db.Column(db.Integer)
        bar = db.Column(db.Integer)

     class BillingSystem2(BillingSystem):
        __mapper_args__ = {'polymorphic_identity' : BillingSystemType.billingsystem2}    
        id = db.Column(db.Integer, db.ForeignKey('public.billing_system.id'), 
            primary_key = True)
        billing_system = db.Column(
            db.Integer, 
            db.ForeignKey('public.billing_system.id')
        )
        bing = db.Column(db.Integer)
        boo = db.Column(db.Integer)
        

        __tablename__ = 'billing_system_product2'
        __table_args__ = {'schema':'public'}


It is all working fine, except for one thing.

Say that I run the following:

    >>> a = Product1()
    >>> b = BillingSystem.objects.get(1)
    >>> a.billing_system = b
    >>> session.add(a)
    >>> session.commit()

I will receive the following error.

    sqlalchemy.exc.ProgrammingError: (ProgrammingError) can't adapt type 'BillingSystem1' 'INSERT INTO

I understand what it is saying, and I have tried the following.

    >>> a.billing_system = b.id

This only stores the ID, and when I try to retreive the associated object, I instead receive an integer. This involves me having to do an additional query. I would expect that this isn't the way to go.

I have also tried adding foreign keys for all of the billing system IDs to the Product1 Model

    class BillingSystem1(BillingSystem):
        __mapper_args__ = {'polymorphic_identity' : BillingSystemType.billingsystem1}    
        id = db.Column(db.Integer, db.ForeignKey('public.billing_system.id'), 
            primary_key = True)
        billing_system = db.Column(
            db.Integer, 
            db.ForeignKey('public.billing_system.id'),
            db.ForeignKey('public.billing_system1.id'),
            db.ForeignKey('public.billing_system2.id'),
        )
        foo = db.Column(db.Integer)
        bar = db.Column(db.Integer)

This also didn't work at all, and I received the same ProgrammingError exception stating that the type can't adapt.

I have trawled the manual, and can't find how to do it, but I need some form of magical option to allow this to happen:

    >>> a = BillingSystem.query.get(1)
    >>> type(a)
    BillingSystem

Instead of:

    >>> a = BillingSystem.query.get(1)
    >>> type(a)
    BillingSystem1

Is anyone able to shed any light on how I can query my set of Polymorphic models for an ID, and only obtain the class of the base model? 

I feel that this will solve my problem, im just not sure of how to work it out.

Thanks for taking the time to read this, and I would really like to find out where I am going wrong (I have also been awake way too long, so that isn't helping).

Cheers,
Rhys