Re: [sqlalchemy] Polymorphic joined-table inheritance

[Michael Bayer]

On Jan 10, 2014, at 7:08 AM, david.ceresu...@gmail.com wrote: Problem: Querying client and clientvip ------------------------------------------------------- With this code I try to query all the clients [ clients = DBSession.query(Client) ] and this is where the problems start, because the query it is issuing is: ## SELECT client.id AS client_id, client.ctype AS client_ctype, client.refinternal AS client_refinternal, ... client.notestx AS client_notestx, client.notesclient AS client_notesclient FROM client WHERE client.ctype IN (NULL) ## Why is there a where clause at all? Should not the query finish with the "FROM client" line? And besides, why does it think that ctype must be NULL (instead of "basic" or "vip", for instance)? that’s a very strange output, the IN is applied to single-table inheritance mappings in order to limit the types of returned classes to those which are queried.  The mapping you’ve illustrated should not have this issue and in fact as Client is at the base, there shouldn’t be any IN at all. I’ve copied your mappings verbatim to a test case, a query against Client produces this: SELECT client.id AS client_id, client.ctype AS client_ctype, client.parentid AS client_parentid, client.refinternal AS client_refinternal, client.notestx AS client_notestx, client.notesclient AS client_notesclient  FROM client so more specifics would be needed to determine why this obviously erroneous output is being produced.  The constructor of Client is not involved with this issue as a Query only works against the class, not an instance. Appendix: Inserting client ------------------------------------- You might have seen that in the __init__ methods I have commented out the " self.ctype = 'basic' " When this line is commented out (as it should) and I try to insert a Client, I get the following error: ## IntegrityError: (IntegrityError) null value in column "ctype" violates not-null constraint ## which leads me to think that the polymorphism I am trying to get is not working properly, becasue I shouldn't need to force a value on the "ctype" column. you absolutely don’t need to worry about “ctype”, nor do you need to worry about calling super().  the polymorphic identity is set on the instance using an on-init event.   I can again use your mappings as is, then run code like this: c1 = Client(refinternal=5) sess.add(c1) sess.commit() and the output: INSERT INTO client (ctype, parentid, refinternal, notestx, notesclient) VALUES (?, ?, ?, ?, ?) 2014-01-10 12:39:14,949 INFO sqlalchemy.engine.base.Engine ('basic', None, 5, None, None) 2014-01-10 12:39:14,949 INFO sqlalchemy.engine.base.Engine COMMIT so no problems there either. Both issues seem to focus around the polymorphic_identity configuration of your setup but the code examples you’ve sent don’t indicate any such issue. If you can attach a simple “test.py” similar to the one attached which reproduces the issue, we can fix your problem immediately.

from sqlalchemy import *
from sqlalchemy.orm import *
from sqlalchemy.ext.declarative import declarative_base

Base = declarative_base()

class Client(Base):
    __tablename__ = 'client'
    id = Column(Integer, primary_key=True)
    ctype = Column(String(16), nullable=False)
    #webaddid = Column(Integer, ForeignKey('web.id'), nullable=False)
    parentid = Column(Integer, ForeignKey('client.id'), nullable=True)
    refinternal = Column(Integer, unique=True, nullable=False)
    notestx = Column(String(256), nullable=True)
    notesclient = Column(String(256), nullable=True)

    __mapper_args__ = {
        'polymorphic_identity':'basic',
        'polymorphic_on': ctype
    }

    def __init__(self, webaddid=None, parentid=None, refinternal=None,
                notestx=None, notesclient=None):
        self.webaddid = webaddid
        self.parentid = parentid
        self.refinternal = refinternal
        self.notesclient = notesclient


class ClientVip(Client):
    __tablename__ = 'clientvip'
    id = Column(Integer, ForeignKey('client.id'), primary_key=True)
    legalname = Column(String(128), nullable=True)
    commercialname = Column(String(64), nullable=True)
    isclienttop = Column(Boolean, nullable=False)
    numlisttop = Column(Integer, nullable=True)

    # Fill the column 'ctype' with the value 'vip'
    __mapper_args__ = {
        'polymorphic_identity':'vip',
    }

    def __init__(self, clientid=None, legalname=None, commercialname=None,
                 isclienttop=False, numlisttop=None, **kwargs):

        # First initialize the basic client
        super(ClientVip, self).__init__(**kwargs)

        self.clientid = clientid
        self.legalname = legalname
        self.commercialname = commercialname
        self.isclienttop = isclienttop
        self.numlisttop = numlisttop


e = create_engine("sqlite://", echo=True)
Base.metadata.create_all(e)
sess = Session(e)


c1 = Client(refinternal=5)
c2 = ClientVip(refinternal=6)

sess.add(c1)
sess.add(c2)
sess.commit()


clients = set(sess.query(Client).all())

assert clients == set([c1, c2])

signature.asc
Description: Message signed with OpenPGP using GPGMail