Hello all,
I'm trying to use SQLAlchemy inheritance in a (company's) project and I
cannot seem to make it work.
First, the versions:
- Ubuntu: 12.10
- Python: 2.7.3
- SQLAlchemy: 0.9.1
- PostgreSQL: 9.1
- sqlite: 3.7.13
- Pyramid: 1.4.2
All of the problems happen in both PostgreSQL and sqlite.
I have a ClientVip class that inherits from Client. This is the trimmed
code:
##
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.ctype = "basic" ### This is actually commented, but might
be important
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)
# And then the VIP client
# self.ctype = "vip" ### This is actually commented, but might
be important
self.clientid = clientid
self.legalname = legalname
self.commercialname = commercialname
...
self.isclienttop = isclienttop
self.numlisttop = numlisttop
##
I have checked the code in
-
http://docs.sqlalchemy.org/en/rel_0_9/orm/examples.html?highlight=inheritance#module-examples.inheritance
-
http://docs.sqlalchemy.org/en/rel_0_9/_modules/examples/inheritance/joined.html
and I can't find anything different, but maybe are my eyes.
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)?
If a force to query with a filter [ clients =
DBSession.query(Client).filter(Client.ctype == "basic") ]
this is the query I get:
##
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 = %(ctype_1)s AND client.ctype IN (NULL)
##
which also looks for NULL values in the where clause.
And what happens if I query the ClientVip?
Well, there are no where clauses, so we are good:
##
SELECT clientvip.id AS clientvip_id, client.id AS client_id, client.ctype
AS client_ctype, client.refinternal AS client_refinternal,
...
clientvip.isclienttop AS clientvip_isclienttop, clientvip.numlisttop AS
clientvip_numlisttop
FROM client JOIN clientvip ON client.id = clientvip.id
##
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.
A plea for help
---------------------
I have been looking at this code for quite some time and I can't figure out
what I am missing.
If any of you have any idea of what I could be possibly doing wrong, or any
ideas that I could
try, I will be very very happy to hear them, because I have run out of
ideas to try right now.
Thank you very much.
--
You received this message because you are subscribed to the Google Groups
"sqlalchemy" group.
To unsubscribe from this group and stop receiving emails from it, send an email
to sqlalchemy+unsubscr...@googlegroups.com.
To post to this group, send email to sqlalchemy@googlegroups.com.
Visit this group at http://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/groups/opt_out.
