Re: [sqlalchemy] querying Class Inheritance Hierarchies

[Mike Bayer]

On 6/2/15 12:30 PM, g wrote:
Hi all

Is there a way to get the "classname"  of thepolymorphic class result in a 
single query on the base class ?
soemthing like this

well that's lookup / conditional logic, I'd just do it as an in-Python 
filter on the results you get:

for result in query:
    classname = get_the_classname(result.type)

if that is unappealing, then you'd need to build a conditional 
expression in SQL using CASE: 
http://docs.sqlalchemy.org/en/rel_1_0/core/sqlelement.html?highlight=case#sqlalchemy.sql.expression.case

You can build out this "case" construct dynamically using the 
keys/values in the polymorphic_map.    It would just be a chunky SQL 
expression.



session.query(cast(Employee.__mapper__.polymorphic_map[Employee.type].class_.__name__, 
String)).filter(Employee.name=='Employee_Name').one()
*Details *
*=======*
from sqlalchemy import *
from sqlalchemy.orm import *
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.orm import aliased
from sqlalchemy.ext.hybrid import hybrid_property

Base = declarative_base()

class Employee(Base):
    __tablename__ = 'employee'
    id = Column(Integer, primary_key=True)
    name = Column(String(50))
    type = Column(String(50))
    __mapper_args__ = {
        'polymorphic_identity':'employee',
        'polymorphic_on':type
    }
class Engineer(Employee):
    __tablename__ = 'engineer'
    id = Column(Integer, ForeignKey('employee.id'), primary_key=True)
    engineer_name = Column(String(30))

    __mapper_args__ = {
        'polymorphic_identity':'engineer',
    }

class Manager(Employee):
    __tablename__ = 'manager'
    id = Column(Integer, ForeignKey('employee.id'), primary_key=True)
    manager_name = Column(String(30))

    __mapper_args__ = {
        'polymorphic_identity':'manager',
    }
e = create_engine('postgresql+psycopg2://uuuu:****@host/test', echo=False)
Base.metadata.create_all(e)

session = Session(e)

*WITH THIS DATA*
*===============*

eng = Engineer(name= 'Employee_Name', engineer_name= 'Engineer_name')
session.add(eng)
session.commit()

*SIMPLE QUERY *
*==============*
empl = 
session.query(Employee).filter(Employee.name=='Employee_Name').one()
print empl <__main__.Engineer at 0x6573c50>
BUT WHAT I WANT IS ONLY THE CLASS NAME OF POLYMORPHIC CLASS SO I TRIED 
THIS
employee = 
session.query(cast(Employee.__mapper__.polymorphic_map['employee'].class_.__name__, 
String)).filter(Employee.name=='Employee_Name').one() print(employee) 
=> (u'Employee',)
and with
e = 
session.query(cast(Employee.__mapper__.polymorphic_map['engineer'].class_.__name__, 
String)).filter(Employee.name=='Employee_Name').one()
print(e)
AND finally to generalize i tried:
e = 
session.query(cast(Employee.__mapper__.polymorphic_map[Employee.type].class_.__name__, 
String)).filter(Employee.name=='Employee_Name').one() print(e)

---------------------------------------------------------------------------
KeyError                                   Traceback (most recent call last)
<ipython-input-31-45f6c96837bf>  in<module>() 1 #generalize----> 2e = 
session.query(cast(Employee.__mapper__.polymorphic_map[Employee.type].class_.__name__, 
String)).filter(Employee.name=='EN').one()3 print(e)KeyError: 
<sqlalchemy.orm.attributes.InstrumentedAttribute object at 0x0654FD80>
I think i am doing  something logically wrong .
QUESTION
========
Is there a way to get the classname  of thepolymorphic class result in a single 
query ?
I tried with hybrid_property but i had the same error
Some hints ?
Regards
G



--
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 
<mailto:sqlalchemy+unsubscr...@googlegroups.com>.
To post to this group, send email to sqlalchemy@googlegroups.com 
<mailto:sqlalchemy@googlegroups.com>.
Visit this group at http://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.

--
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/d/optout.