The problem is when I have an object mapped against two tables, both
of which are part of an inheritance hierarchy. I managed to
synchronize the foreign key with the primary key (per the
documentation link you provided). However, SQLAlchemy doesn't (or I
can't instruct it how to) set the polymorphic discrimintaor fields
appropriately. I can set them manually, but then insert fails because
it looks for the sequence object on the inherited table, which doesn't
exist (it exist only on the root table of the inheritance hierarchy).
Here's example code. In brief, I have a Person->Employee and Relation-
>SupervisorRelation as two independent inheritance hierarchies.
Relation is defined between two Persons, and SupervisorRelation
between two Employees. I want to hide this as an implementation and
have a Subordinate class that the programmer down the line will work
with. Subordinate contains fields from Employee and
SupervisorRelation. Querying on Subordinate works (efficiently, too),
and so does attribute modification. I would like to be able to create
it also (after populating the required fields and commit, the
underlying engine should create both a new Employee and a new
SupervisorRelation).
I hope this makes sense. Here's the code. When run, it throws
"ProgrammingError: (ProgrammingError) relation
"supervisor_relations_id_seq" does not exist"
<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" <[email protected]> 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" <[email protected]> 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" <[email protected]> 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 [email protected]
To unsubscribe from this group, send email to
[email protected]
For more options, visit this group at
http://groups.google.com/group/sqlalchemy?hl=en
-~----------~----~----~----~------~----~------~--~---
