bojanb wrote: > > > I hope this makes sense. Here's the code. When run, it throws > "ProgrammingError: (ProgrammingError) relation > "supervisor_relations_id_seq" does not exist"
the solution is the same as that I illustrated in a previous email, that when you map to a JOIN you must place all "equivalent" columns which you would like populated identically in groups. This is described at http://www.sqlalchemy.org/docs/05/mappers.html#mapping-a-class-against-multiple-tables . Here is a working mapper: mapper(Subordinate, join(Employee, SupervisorRelation, onclause=SupervisorRelation.person_from_id==Employee.id), properties={ 'relation_id': [relations.c.id, supervisor_relations.c.id], #Need to rename asthere's also Employee.id 'relation_type': relations.c.type, #Also need to rename 'person_from_id': [relations.c.person_from_id, persons.c.id, employees.c.id], the effect of the error you're getting, the "sequence not found", is a separate issue which is ticket #1516, and that issue is fixed in 0.6. If you were to run your program with the broken mapping in 0.6, you'd instead get "null value in column "id" violates not-null constraint" which is more directly descriptive of the issue. > > <code> > > from sqlalchemy import create_engine, Table, Column, Integer, String, > MetaData, ForeignKey > from sqlalchemy.orm import mapper, relation, sessionmaker, eagerload, > join > from sqlalchemy.orm.mapper import validates > > DB_URI='postgres://postg...@localhost/postgres' #Replace this > accordingly > db_engine=create_engine(DB_URI, echo=False) > metadata = MetaData() > > class Person(object): > > def __init__(self, name): > self.name = name > > persons = Table('persons', > metadata, > Column('id', Integer, primary_key=True), > Column('type', String(1), nullable=False), > Column('name', String(100), nullable=False)) > > class Employee(Person): > > def __init__(self, name, position): > Person.__init__(self, name) > self.position = position > > employees = Table('employees', > metadata, > Column('id', Integer, ForeignKey('persons.id'), > primary_key=True), > Column('position', String(50), nullable=False)) > > class Relation(object): > > def __init__(self, person_from, person_to): > self.person_from = person_from > self.person_to = person_to > > relations = Table('relations', > metadata, > Column('id', Integer, primary_key=True), > Column('type', String(1), nullable=False), > Column('person_from_id', Integer, ForeignKey > ('persons.id'), nullable=False), > Column('person_to_id', Integer, ForeignKey > ('persons.id'), nullable=False)) > > class SupervisorRelation(Relation): > def __init__(self, person_from, person_to, additional_info): > Relation.__ini__(person_from, person_to) > self.additional_info = additional_info > > supervisor_relations = Table('supervisor_relations', > metadata, > Column('id', Integer, ForeignKey > ('relations.id'), primary_key=True), > Column('additional_info', String(100), > nullable=False)) > > class Subordinate(object): #This class represents the business object > that we work with > pass > > mapper(Person, persons, polymorphic_on=persons.c.type, > polymorphic_identity='P') > mapper(Employee, employees, inherits=Person, polymorphic_identity='E') > mapper(Relation, relations, polymorphic_on=relations.c.type, > polymorphic_identity='R', properties={ > 'person_from': relation(Person, primaryjoin= > (relations.c.person_from_id==persons.c.id)), > 'person_to': relation(Person, primaryjoin= > (relations.c.person_to_id==persons.c.id)), > }) > mapper(SupervisorRelation, supervisor_relations, inherits=Relation, > polymorphic_identity='S') > mapper(Subordinate, join(Employee, SupervisorRelation, > onclause=SupervisorRelation.person_from_id==Employee.id), properties={ > 'relation_id': supervisor_relations.c.id, #Need to rename as > there's also Employee.id > 'relation_type': relations.c.type, #Also need to rename > 'person_from_id': [relations.c.person_from_id, persons.c.id], > #Need to declare them synonymous > 'person_from': relation(Person, primaryjoin= > (relations.c.person_from_id==persons.c.id)), > 'person_to': relation(Person, primaryjoin= > (relations.c.person_to_id==persons.c.id)), > }) > > if __name__ == '__main__': > metadata.create_all(db_engine) > s=sessionmaker(bind=db_engine)() > try: > jack = Employee('Jack', 'manager') > s.add(jack) > s.commit() > #Here we try to create a Subordinate object which should > automatically create dependant objects > db_engine.echo = True > subordinate = Subordinate() > subordinate.person_to = jack > subordinate.name = 'Peter' > subordinate.position = 'clerk' > subordinate.additional_info = 'Works for Jack since 2007' > subordinate.type = 'E' > subordinate.relation_type='S' > s.add(subordinate) > s.commit() #Fails > > finally: > db_engine.echo = False > s.close() > metadata.drop_all(db_engine) > > </code> > > > Here's the code that demonstrates the problem from my original > question (the previous code was in response to your reply which > > On Sep 14, 6:52 pm, "Michael Bayer" <mike...@zzzcomputing.com> wrote: >> bojanbwrote: >> >> > Actually you can't use with_polymorphic() in the query because Meeting >> > is not an inherited object (one would get an InvalidRequestError if >> > one tried). But plugging: >> >> > with_polymorphic='*' >> >> > in the mapper for Person makes the eagerload work in the code above. >> >> > However, we're off on a tangent. I still don't know how to instantiate >> > objects of a class mapped against two tables when they contain both an >> > autogenerated primary key from the first table and a mandatory foreign >> > key from the second... >> >> Just to clarify, the mapper on Employee with the "with_polymorphic='*'" >> *is* a mapper that is mapped against two tables, in pretty much the same >> way as a map against a plain join is represented. So I'm assuming this >> is >> unsuitable only because it's your observation that the joined tables in >> your particular system are more of an "implementation detail" and you >> don't really need to represent inheritance. >> >> So, as far as synchronizing the foreign key with the primary key of the >> two tables in a mapper that is mapped to a plain join, you just map two >> columns to one attribute. This is also in the docs, >> athttp://www.sqlalchemy.org/docs/05/mappers.html#mapping-a-class-agains... >> . The tables are populated in order of foreign key dependency, and >> after >> each primary key generation the value is synchronized to the mapped >> attribute, where its then available for the insert into the second >> table. >> >> Since I didn't read your initial (very long) email carefully enough, >> here >> is your sample program using that style. >> >> from sqlalchemy import create_engine, Table, Column, Integer, >> String,MetaData, ForeignKey >> from sqlalchemy.orm import mapper, relation, sessionmaker, eagerload >> from sqlalchemy.orm.mapper import validates >> >> db_engine=create_engine('sqlite://', echo=True) >> metadata = MetaData() >> >> persons = Table('persons', >> metadata, >> Column('id', Integer, primary_key=True), >> Column('name', String(100), nullable=False)) >> >> class Employee(object): >> >> def __init__(self, name, position): >> self.name = name >> self.position = position >> >> employees = Table('employees', >> metadata, >> Column('id', Integer, >> ForeignKey('persons.id'),primary_key=True), >> Column('position', String(50), nullable=False)) >> >> class Meeting(object): >> >> def __init__(self, date, person_from, person_to): >> self.date = date >> self.person_from = person_from >> self.person_to = person_to >> >> meetings = Table('meetings', >> metadata, >> Column('id', Integer, primary_key=True), >> Column('date', String(8), nullable=False), >> Column('person_from_id', Integer, >> ForeignKey('persons.id'), nullable=False), >> Column('person_to_id', Integer, >> ForeignKey('persons.id'), >> nullable=False)) >> >> mapper(Employee, employees.join(persons), properties={ >> 'id':[persons.c.id, employees.c.id] >> >> }) >> >> mapper(Meeting, meetings, properties={ >> 'person_from': relation(Employee, >> primaryjoin=(meetings.c.person_from_id==persons.c.id)), >> 'person_to': relation(Employee, >> primaryjoin=(meetings.c.person_to_id==persons.c.id)), >> }) >> >> if __name__ == '__main__': >> metadata.create_all(db_engine) >> s=sessionmaker(bind=db_engine)() >> >> john = Employee('John', 'person') >> peter = Employee('Peter', 'clerk') >> jack = Employee('Jack', 'manager') >> m1 = Meeting('20090914', peter, john) >> m2 = Meeting('20090915', peter, jack) >> s.add_all([john, peter, jack, m1, m2]) >> s.commit() >> >> #We now want to print the names and positions of everyonePeter has >> ever met with >> peters_meetings = >> s.query(Meeting).options(eagerload('person_to')).filter_by(person_from=peter).all() >> for meeting in peters_meetings: >> print meeting.date, >> meeting.person_to.name,meeting.person_to.position >> >> >> >> >> >> > On Sep 14, 4:31 pm, "Michael Bayer" <mike...@zzzcomputing.com> wrote: >> >>bojanbwrote: >> >> >> > The root of the problem is inheritance. Let's say that I have a >> Person >> >> > class and an Employee class that inherits from it. I also have a >> >> > Meeting class that records meetings between two persons. >> >> >> > A query on Meeting will always lazy load Employee's attributes, >> >> > regardless of any lazy/eagerload settings. E.g. if I want to print >> the >> >> > list of names of all persons somebody had meetings with and also >> their >> >> > position if they are employees (null if they're not), it will >> always >> >> > be done lazily. This is bad when I have, let's say, 100.000 >> Meetings. >> >> >> > I guess I can build a custom join and work from that, but if I have >> >> > two levels of inheritance on one side and three levels on the other >> >> > side, I will have to write a six-way join, and this, I'm sure >> you'll >> >> > agree, sort of defeats the purpose of an object-relational mapper. >> >> >> > Using classes mapped against multiple tables would elegantly solve >> >> > this problem, if I could only instantiate them (see my original >> post). >> >> >> > Here's the code that shows attributes of inherited objects are >> loaded >> >> > lazily: >> >> >> oh. you want with_polymorphic() for this. >> >> >>http://www.sqlalchemy.org/docs/05/mappers.html#controlling-which-tabl... >> >> >> > <code> >> >> >> > from sqlalchemy import create_engine, Table, Column, Integer, >> String, >> >> > MetaData, ForeignKey >> >> > from sqlalchemy.orm import mapper, relation, sessionmaker, >> eagerload >> >> > from sqlalchemy.orm.mapper import validates >> >> >> > DB_URI='postgres://postg...@localhost/postgres' #Replace this >> >> > accordingly >> >> > db_engine=create_engine(DB_URI, echo=False) >> >> > metadata = MetaData() >> >> >> > class Person(object): >> >> >> > def __init__(self, name): >> >> > self.name = name >> >> >> > persons = Table('persons', >> >> > metadata, >> >> > Column('id', Integer, primary_key=True), >> >> > Column('type', String(1), nullable=False), >> >> > Column('name', String(100), nullable=False)) >> >> >> > class Employee(Person): >> >> >> > def __init__(self, name, position): >> >> > Person.__init__(self, name) >> >> > self.position = position >> >> >> > employees = Table('employees', >> >> > metadata, >> >> > Column('id', Integer, ForeignKey('persons.id'), >> >> > primary_key=True), >> >> > Column('position', String(50), nullable=False)) >> >> >> > class Meeting(object): >> >> >> > def __init__(self, date, person_from, person_to): >> >> > self.date = date >> >> > self.person_from = person_from >> >> > self.person_to = person_to >> >> >> > meetings = Table('meetings', >> >> > metadata, >> >> > Column('id', Integer, primary_key=True), >> >> > Column('date', String(8), nullable=False), >> >> > Column('person_from_id', Integer, ForeignKey >> >> > ('persons.id'), nullable=False), >> >> > Column('person_to_id', Integer, ForeignKey >> >> > ('persons.id'), nullable=False)) >> >> >> > mapper(Person, persons, polymorphic_on=persons.c.type, >> >> > polymorphic_identity='P') >> >> > mapper(Employee, employees, inherits=Person, >> polymorphic_identity='E') >> >> > mapper(Meeting, meetings, properties={ >> >> > 'person_from': relation(Person, primaryjoin= >> >> > (meetings.c.person_from_id==persons.c.id)), >> >> > 'person_to': relation(Person, primaryjoin= >> >> > (meetings.c.person_to_id==persons.c.id)), >> >> > }) >> >> >> > if __name__ == '__main__': >> >> > metadata.create_all(db_engine) >> >> > s=sessionmaker(bind=db_engine)() >> >> > try: >> >> > john = Person('John') >> >> > peter = Employee('Peter', 'clerk') >> >> > jack = Employee('Jack', 'manager') >> >> > m1 = Meeting('20090914', peter, john) >> >> > m2 = Meeting('20090915', peter, jack) >> >> > s.add_all([john, peter, jack, m1, m2]) >> >> > s.commit() >> >> >> > db_engine.echo = True >> >> > #We now want to print the names and positions of everyone >> >> > Peter has ever met with >> >> > peters_meetings = s.query(Meeting).options(eagerload >> >> > ('person_to')).filter_by(person_from=peter).all() >> >> > for meeting in peters_meetings: >> >> > if meeting.person_to.type == 'P': >> >> > print meeting.date, meeting.person_to.name, None >> >> > else: >> >> > #Each print statement here will emit an SQL SELECT >> on >> >> > the employees table >> >> > print meeting.date, meeting.person_to.name, >> >> > meeting.person_to.position >> >> >> > finally: >> >> > db_engine.echo = False >> >> > s.close() >> >> > metadata.drop_all(db_engine) >> >> >> > </code> >> >> >> > On Sep 11, 7:52 pm, "Michael Bayer" <mike...@zzzcomputing.com> >> wrote: >> >> >> have you tried using query + join() + contains_eager() ? any >> query >> >> you >> >> >> like can be used to build the object graph of your choice along >> >> >> relations(). >> >> >> >> bojanbwrote: >> >> >> >> > Here's something I've been struggling with recently. I'll >> include >> >> the >> >> >> > description of steps that got me here, as I believe the context >> >> will >> >> >> > make the question clearer. >> >> >> >> > It all started because I needed to show data (eg. in a list >> form) >> >> from >> >> >> > two related tables (classes). However, SQLAlchemy would emit one >> >> SQL >> >> >> > query for getting the objects of the first class, then one query >> >> each >> >> >> > for each access to attributes of the other class. It obviously >> >> loads >> >> >> > the attributes >> >> ... >> >> read more » > > > --~--~---------~--~----~------------~-------~--~----~ You received this message because you are subscribed to the Google Groups "sqlalchemy" group. To post to this group, send email to sqlalchemy@googlegroups.com To unsubscribe from this group, send email to sqlalchemy+unsubscr...@googlegroups.com For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en -~----------~----~----~----~------~----~------~--~---
