Re: [sqlalchemy] creating indexes from inherited fields
On 6/2/15 12:59 PM, Richard Gerd Kuesters wrote: thanks again Mike! almost there. the problem now are inherited tables ... taken from my example code, let's say I have a table that inherits SomeOtherClass; then, the error is something like: sqlalchemy.exc.ArgumentError: Index 'ix_adapter_created_on' is against table 'adapter', and cannot be associated with table 'http_adapter'. in the example above, http_error inherits from adapter. take a look at using has_inherited_table(), the section in http://docs.sqlalchemy.org/en/rel_1_0/orm/extensions/declarative/mixins.html#controlling-table-inheritance-with-mixins has clues best regards, richard. On 06/02/2015 01:45 PM, Mike Bayer wrote: On 6/2/15 12:05 PM, Richard Gerd Kuesters wrote: oh, sorry Mike, I forgot to mention that I use TimestampMixin in other classes aswell, so I got an existent index error (something like that). Is there a way I don't need to declare the index name? (IMHO this appears to be the problem). indexes have to be named something so you'd have to figure out a naming convention based on tables/column names. There should be an existing convention for indexes so if you set None as the name it should make one up for you like ix_tablename_columnname thanks a lot! richard. On 06/02/2015 12:28 PM, Mike Bayer wrote: On 6/2/15 10:16 AM, Richard Gerd Kuesters wrote: hi all, again :) how can I create an index in fields inherited by other classes? example: class TimestampMixin(object): updated_on = Column(DateTime) # i wanted to create three indexes in this field, updated_on, updated_on.asc() and updated_on.desc() class SomeOtherClass(Base, TimestampMixin): __tablename__ = 'some_table' id = Column(Integer) Index('ix_some_other_class_01', SomeOtherClass.updated_on) Index('ix_some_other_class_02', SomeOtherClass.updated_on.asc()) Index('ix_some_other_class_03', SomeOtherClass.updated_on.desc()) I just want to avoid repetition, if possible :) you could use a __table_args__() callable with @declared_attr. Or use a mapping event like __declare_first__() or __declare_last__() (these are hooked into the before_configured / after_configured events which you could also use directly, e.g. http://docs.sqlalchemy.org/en/rel_1_0/orm/events.html?highlight=before_configure#sqlalchemy.orm.events.MapperEvents.before_configured) best regards, richard. -- 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. 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 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 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 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 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
Re: [sqlalchemy] creating indexes from inherited fields
thanks a lot, Mike! good catch of yours. the answer was in front of me all the time and i couldn't see it ... my mistake, thanks for pointing the right direction ;) best regards, richard. On 06/02/2015 03:17 PM, Mike Bayer wrote: On 6/2/15 12:59 PM, Richard Gerd Kuesters wrote: thanks again Mike! almost there. the problem now are inherited tables ... taken from my example code, let's say I have a table that inherits SomeOtherClass; then, the error is something like: sqlalchemy.exc.ArgumentError: Index 'ix_adapter_created_on' is against table 'adapter', and cannot be associated with table 'http_adapter'. in the example above, http_error inherits from adapter. take a look at using has_inherited_table(), the section in http://docs.sqlalchemy.org/en/rel_1_0/orm/extensions/declarative/mixins.html#controlling-table-inheritance-with-mixins has clues best regards, richard. On 06/02/2015 01:45 PM, Mike Bayer wrote: On 6/2/15 12:05 PM, Richard Gerd Kuesters wrote: oh, sorry Mike, I forgot to mention that I use TimestampMixin in other classes aswell, so I got an existent index error (something like that). Is there a way I don't need to declare the index name? (IMHO this appears to be the problem). indexes have to be named something so you'd have to figure out a naming convention based on tables/column names. There should be an existing convention for indexes so if you set None as the name it should make one up for you like ix_tablename_columnname thanks a lot! richard. On 06/02/2015 12:28 PM, Mike Bayer wrote: On 6/2/15 10:16 AM, Richard Gerd Kuesters wrote: hi all, again :) how can I create an index in fields inherited by other classes? example: class TimestampMixin(object): updated_on = Column(DateTime) # i wanted to create three indexes in this field, updated_on, updated_on.asc() and updated_on.desc() class SomeOtherClass(Base, TimestampMixin): __tablename__ = 'some_table' id = Column(Integer) Index('ix_some_other_class_01', SomeOtherClass.updated_on) Index('ix_some_other_class_02', SomeOtherClass.updated_on.asc()) Index('ix_some_other_class_03', SomeOtherClass.updated_on.desc()) I just want to avoid repetition, if possible :) you could use a __table_args__() callable with @declared_attr. Or use a mapping event like __declare_first__() or __declare_last__() (these are hooked into the before_configured / after_configured events which you could also use directly, e.g. http://docs.sqlalchemy.org/en/rel_1_0/orm/events.html?highlight=before_configure#sqlalchemy.orm.events.MapperEvents.before_configured) best regards, richard. -- 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. 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 mailto: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. -- 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. 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 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 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,
[sqlalchemy] Re: getting 'No database selected' randomly from session
BTW is there a way in sqlalchemy to append the name of the database to the table name like SELECT * from dbname.table_name -- 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.
[sqlalchemy] creating indexes from inherited fields
hi all, again :) how can I create an index in fields inherited by other classes? example: class TimestampMixin(object): updated_on = Column(DateTime) # i wanted to create three indexes in this field, updated_on, updated_on.asc() and updated_on.desc() class SomeOtherClass(Base, TimestampMixin): __tablename__ = 'some_table' id = Column(Integer) Index('ix_some_other_class_01', SomeOtherClass.updated_on) Index('ix_some_other_class_02', SomeOtherClass.updated_on.asc()) Index('ix_some_other_class_03', SomeOtherClass.updated_on.desc()) I just want to avoid repetition, if possible :) best regards, richard. -- 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. attachment: richard.vcf
[sqlalchemy] getting 'No database selected' randomly from session
Hi, I have a very strange behavior, I have a program that uses bulk insertions to the DB. for some reason the process which iterate on one table and create bulk and insert into another table fails with the error of 'No database selected'. this is how I bind the session: #--- # use_db: #this method will return a session reference # using this DB #--- def use_db(self, new_db_name): self.connection.execute(USE %s % (new_db_name)) session_ref = sessionmaker(bind = self.connection, expire_on_commit=False) self.session_cover_tst = session_ref() return self.session_cover_tst engine = connection_engine.use_db(db_name) count_sig = engine.query(func.count(distinct(signals_table.Signals.sig_value)).label('count_sig')).\ filter(signals_table.Signals.message_id == msg_row.id).\ filter(signals_table.Signals.signal_id == sig_id).\ group_by(signals_table.Signals.signal_id).\ one()[0] now the table Signals is huge (500M records), but the table is indexed and everything is working great for a few rounds and then I'm getting the error 'No database selected' and it fails... -- 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.
Re: [sqlalchemy] getting 'No database selected' randomly from session
On 6/2/15 5:52 AM, eli rashlin wrote: Hi, I have a very strange behavior, I have a program that uses bulk insertions to the DB. for some reason the process which iterate on one table and create bulk and insert into another table fails with the error of 'No database selected'. this is how I bind the session: #--- # use_db: #this method will return a session reference # using this DB #--- def use_db(self, new_db_name): self.connection.execute(USE %s % (new_db_name)) session_ref = sessionmaker(bind = self.connection, expire_on_commit=False) self.session_cover_tst = session_ref() return self.session_cover_tst engine = connection_engine.use_db(db_name) count_sig = engine.query(func.count(distinct(signals_table.Signals.sig_value)).label('count_sig')).\ filter(signals_table.Signals.message_id == msg_row.id).\ filter(signals_table.Signals.signal_id == sig_id).\ group_by(signals_table.Signals.signal_id).\ one()[0] now the table Signals is huge (500M records), but the table is indexed and everything is working great for a few rounds and then I'm getting the error 'No database selected' and it fails... it sounds like at some point your program is using self.connection without emitting that USE statement, or you have some kind of routine that is undoing the USE and perhaps multiple callers and/or threads are accessing self.connection at the same time. -- 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.
Re: [sqlalchemy] Re: getting 'No database selected' randomly from session
On 6/2/15 5:54 AM, eli rashlin wrote: BTW is there a way in sqlalchemy to append the name of the database to the table name like SELECT * from dbname.table_name this is the schema argument documented at http://docs.sqlalchemy.org/en/rel_1_0/core/metadata.html?highlight=schema#sqlalchemy.schema.Table.params.schema. -- 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.
Re: [sqlalchemy] creating indexes from inherited fields
oh, sorry Mike, I forgot to mention that I use TimestampMixin in other classes aswell, so I got an existent index error (something like that). Is there a way I don't need to declare the index name? (IMHO this appears to be the problem). thanks a lot! richard. On 06/02/2015 12:28 PM, Mike Bayer wrote: On 6/2/15 10:16 AM, Richard Gerd Kuesters wrote: hi all, again :) how can I create an index in fields inherited by other classes? example: class TimestampMixin(object): updated_on = Column(DateTime) # i wanted to create three indexes in this field, updated_on, updated_on.asc() and updated_on.desc() class SomeOtherClass(Base, TimestampMixin): __tablename__ = 'some_table' id = Column(Integer) Index('ix_some_other_class_01', SomeOtherClass.updated_on) Index('ix_some_other_class_02', SomeOtherClass.updated_on.asc()) Index('ix_some_other_class_03', SomeOtherClass.updated_on.desc()) I just want to avoid repetition, if possible :) you could use a __table_args__() callable with @declared_attr. Or use a mapping event like __declare_first__() or __declare_last__() (these are hooked into the before_configured / after_configured events which you could also use directly, e.g. http://docs.sqlalchemy.org/en/rel_1_0/orm/events.html?highlight=before_configure#sqlalchemy.orm.events.MapperEvents.before_configured) best regards, richard. -- 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 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. attachment: richard.vcf
[sqlalchemy] querying Class Inheritance Hierarchies
Hi all Is there a way to get the classname of the polymorphic class result in a single query on the base class ? soemthing like this 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://:@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 2 e = 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 the polymorphic 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. 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.
Re: [sqlalchemy] creating indexes from inherited fields
thanks again Mike! almost there. the problem now are inherited tables ... taken from my example code, let's say I have a table that inherits SomeOtherClass; then, the error is something like: sqlalchemy.exc.ArgumentError: Index 'ix_adapter_created_on' is against table 'adapter', and cannot be associated with table 'http_adapter'. in the example above, http_error inherits from adapter. best regards, richard. On 06/02/2015 01:45 PM, Mike Bayer wrote: On 6/2/15 12:05 PM, Richard Gerd Kuesters wrote: oh, sorry Mike, I forgot to mention that I use TimestampMixin in other classes aswell, so I got an existent index error (something like that). Is there a way I don't need to declare the index name? (IMHO this appears to be the problem). indexes have to be named something so you'd have to figure out a naming convention based on tables/column names. There should be an existing convention for indexes so if you set None as the name it should make one up for you like ix_tablename_columnname thanks a lot! richard. On 06/02/2015 12:28 PM, Mike Bayer wrote: On 6/2/15 10:16 AM, Richard Gerd Kuesters wrote: hi all, again :) how can I create an index in fields inherited by other classes? example: class TimestampMixin(object): updated_on = Column(DateTime) # i wanted to create three indexes in this field, updated_on, updated_on.asc() and updated_on.desc() class SomeOtherClass(Base, TimestampMixin): __tablename__ = 'some_table' id = Column(Integer) Index('ix_some_other_class_01', SomeOtherClass.updated_on) Index('ix_some_other_class_02', SomeOtherClass.updated_on.asc()) Index('ix_some_other_class_03', SomeOtherClass.updated_on.desc()) I just want to avoid repetition, if possible :) you could use a __table_args__() callable with @declared_attr. Or use a mapping event like __declare_first__() or __declare_last__() (these are hooked into the before_configured / after_configured events which you could also use directly, e.g. http://docs.sqlalchemy.org/en/rel_1_0/orm/events.html?highlight=before_configure#sqlalchemy.orm.events.MapperEvents.before_configured) best regards, richard. -- 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. 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 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 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 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. attachment: richard.vcf
Re: [sqlalchemy] querying Class Inheritance Hierarchies
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://:@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 inmodule() 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.