[sqlalchemy] Re: How to instantiate objects of a class mapped against multiple tables?
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 athttp://www.sqlalchemy.org/docs/05/mappers.html#mapping-a-class-agains... You're right. Sorry, it just wasn't clear to me that you need to specify the equivalent columns throughout the whole inheritance hierarchy. I thoguht that SQLA would somehow automagically know that they are equivalent. Thanks a lot for your effort Mike, this was really of great help! --~--~-~--~~~---~--~~ 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 -~--~~~~--~~--~--~---
[sqlalchemy] Re: How to instantiate objects of a class mapped against multiple tables?
bojanb wrote: 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 athttp://www.sqlalchemy.org/docs/05/mappers.html#mapping-a-class-agains... You're right. Sorry, it just wasn't clear to me that you need to specify the equivalent columns throughout the whole inheritance hierarchy. I thoguht that SQLA would somehow automagically know that they are equivalent. it figures that out when you use inherits, but mapping to the join without using inherits it doesn't make any assumptions (since you could be mapping to something more complicated and the equivalent logic would start losing stability) Thanks a lot for your effort Mike, this was really of great help! we're done, hooray ! --~--~-~--~~~---~--~~ 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 -~--~~~~--~~--~--~---
[sqlalchemy] Re: How to instantiate objects of a class mapped against multiple tables?
with_polymorphic can be set against any subset of classes, not just '*'. Yes, but in the first case I can't use with_polymorphic() on the query, because the query class is not the problem - I want the polymorphic load on an attribute (relation) of the queried class in order for the eagerload to work. Therefore I must set with_polymorphic in the mapper of that other class. But since mappers are global for the whole application, I can't just set it to a subset of the classes (then some other queries in the application won't work correctly). I don't have the time most of today to get into it so I can't confirm what's going on. Any chance you could map to a straight join of all four tables instead of a join to two sub-joins ? I'll try using single-table inheritance first, then I'll try that. It would be great if you could look up into this when you have time. Just run the last code example and SQL echo and error message should be self-explanatory. --~--~-~--~~~---~--~~ 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 -~--~~~~--~~--~--~---
[sqlalchemy] Re: How to instantiate objects of a class mapped against multiple tables?
I don't have the time most of today to get into it so I can't confirm what's going on. Any chance you could map to a straight join of all four tables instead of a join to two sub-joins ? I tried the following join in the mapper for Subordinate: join(Employee, Person).join(Relation, onclause=Person.id==Relation.person_from_id).join(SupervisorRelation) I also tried using single-table inheritance in order to avoid sub- joins. The results were the same - trying to select from a non-existing sequnce. This time it's the sequence for the other object, so I guess both are firing wrong, the exact error depends on which one is firing first. I could use MapperExtension.before_insert() as you suggested. However, since rows are inserted first in parent table then in child table, I would need to know that next primary key value and set the id to it. But selecting from a sequnce causes it to increment, and the parent and child primary keys will fail to be in sync. I would need to somehow set the primary key after the parent object has been inserted but before the child is inserted, and I don't know how to accomplish that. --~--~-~--~~~---~--~~ 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 -~--~~~~--~~--~--~---
[sqlalchemy] Re: How to instantiate objects of a class mapped against multiple tables?
bojanb wrote: with_polymorphic can be set against any subset of classes, not just '*'. Yes, but in the first case I can't use with_polymorphic() on the query, because the query class is not the problem - I want the polymorphic load on an attribute (relation) of the queried class in order for the eagerload to work. Therefore I must set with_polymorphic in the mapper of that other class. But since mappers are global for the whole application, I can't just set it to a subset of the classes (then some other queries in the application won't work correctly). not like I'll have time to get to this soon, but there *is* a ticket to address this, #1106. I'll try using single-table inheritance first, then I'll try that. It would be great if you could look up into this when you have time. Just run the last code example and SQL echo and error message should be self-explanatory. will do ! --~--~-~--~~~---~--~~ 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 -~--~~~~--~~--~--~---
[sqlalchemy] Re: How to instantiate objects of a class mapped against multiple tables?
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'
[sqlalchemy] Re: How to instantiate objects of a class mapped against multiple tables?
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 =
[sqlalchemy] Re: How to instantiate objects of a class mapped against multiple tables?
bojanb wrote:
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).
let me get this straight. you want to map to a JOIN, which itself is
JOINed against two joined-table inheritance subclasses.
That is
+--- join --+
||
joinjoin
and each call to Query() would emit a JOIN against two sub-JOINs.
is this correct ? is there a reason this need be so complex ? (and its
efficient ? really ? a query like that would bring any DB to a halt on a
large dataset, I would think...)
If the issue is just a sequence not firing off, an immediate workaround
would be to fire the sequence off yourself. you can even do this in a
MapperExtension.before_insert() (good place for your polymorphic identity
setting too). I don't as yet understand why the normal sequence firing
wouldn't be working here, is one firing off and the other not ?
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,
[sqlalchemy] Re: How to instantiate objects of a class mapped against multiple tables?
Yes, I want to map to a join between two classes which are parts of
joined table inheritance. I don't think it's complex - it fits very
naturally with the problem I am modeling.
When I said it's efficient, I meant that the generated SQL is optimal,
ie. the same as I would write if I were doing it by hand. eagerload
and with_polymorphic result in SQL that also queries on fields of
sibling classes (ie. which inherit from the same superclass but are
not in the inheritance path of the final class) which are unnecessary,
and contains a subquery (which I believe is not optimal).
My understanding is that performing joins on indexed fields is what
RDBMS do well. However, if the query turned out to be too slow I can
always switch to single-table inheritance - whether I use joined-table
or single-table inheritance is just an implementation detail (as I
understand it).
The problem is not that sequence is not firing off, it's that it's
firing for a sequence that doesn't exist. In the code above, it's
trying to get the next value from supervisor_relations_id sequence,
but that sequence doesn't exist because of inheritance. It should be
trying to get from relations_id_sequence but for some reason it
isn't. If you run the code you can see what's going on exactly in the
SQL echo.
I will play around with MapperExtension and single-table inheritance
and see what I get. However, I just thought that since selects and
updates work so nicely in this setup, create should also work in the
same way.
On Sep 15, 4:32 pm, Michael Bayer mike...@zzzcomputing.com wrote:
bojanb wrote:
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).
let me get this straight. you want to map to a JOIN, which itself is
JOINed against two joined-table inheritance subclasses.
That is
+--- join --+
| |
join join
and each call to Query() would emit a JOIN against two sub-JOINs.
is this correct ? is there a reason this need be so complex ? (and its
efficient ? really ? a query like that would bring any DB to a halt on a
large dataset, I would think...)
If the issue is just a sequence not firing off, an immediate workaround
would be to fire the sequence off yourself. you can even do this in a
MapperExtension.before_insert() (good place for your polymorphic identity
setting too). I don't as yet understand why the normal sequence firing
wouldn't be working here, is one firing off and the other not ?
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):
[sqlalchemy] Re: How to instantiate objects of a class mapped against multiple tables?
bojanb wrote:
Yes, I want to map to a join between two classes which are parts of
joined table inheritance. I don't think it's complex - it fits very
naturally with the problem I am modeling.
When I said it's efficient, I meant that the generated SQL is optimal,
ie. the same as I would write if I were doing it by hand. eagerload
and with_polymorphic result in SQL that also queries on fields of
sibling classes (ie. which inherit from the same superclass but are
not in the inheritance path of the final class) which are unnecessary,
and contains a subquery (which I believe is not optimal).
with_polymorphic can be set against any subset of classes, not just '*'.
My understanding is that performing joins on indexed fields is what
RDBMS do well. However, if the query turned out to be too slow I can
always switch to single-table inheritance - whether I use joined-table
or single-table inheritance is just an implementation detail (as I
understand it).
oh that query is going to be pretty slow for sure (though slow is a
relative term).
The problem is not that sequence is not firing off, it's that it's
firing for a sequence that doesn't exist. In the code above, it's
trying to get the next value from supervisor_relations_id sequence,
but that sequence doesn't exist because of inheritance. It should be
trying to get from relations_id_sequence but for some reason it
isn't. If you run the code you can see what's going on exactly in the
SQL echo.
I don't have the time most of today to get into it so I can't confirm
what's going on. Any chance you could map to a straight join of all four
tables instead of a join to two sub-joins ?
I will play around with MapperExtension and single-table inheritance
and see what I get. However, I just thought that since selects and
updates work so nicely in this setup, create should also work in the
same way.
On Sep 15, 4:32 pm, Michael Bayer mike...@zzzcomputing.com wrote:
bojanb wrote:
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).
let me get this straight. you want to map to a JOIN, which itself is
JOINed against two joined-table inheritance subclasses.
That is
+--- join --+
| |
join join
and each call to Query() would emit a JOIN against two sub-JOINs.
is this correct ? is there a reason this need be so complex ? (and its
efficient ? really ? a query like that would bring any DB to a halt on
a
large dataset, I would think...)
If the issue is just a sequence not firing off, an immediate workaround
would be to fire the sequence off yourself. you can even do this in a
MapperExtension.before_insert() (good place for your polymorphic
identity
setting too). I don't as yet understand why the normal sequence firing
wouldn't be working here, is one firing off and the other not ?
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,
[sqlalchemy] Re: How to instantiate objects of a class mapped against multiple tables?
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:
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 lazily, which is fine most of the time but grossly
inefficient in this case (as there can be thousands of records in the
first table).
Aha, I'll use eagerload! I thought. Alas, it doesn't seem to work
for inherited classes. A message
[sqlalchemy] Re: How to instantiate objects of a class mapped against multiple tables?
bojanb wrote:
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, at
http://www.sqlalchemy.org/docs/05/mappers.html#mapping-a-class-against-multiple-tables
. 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:
bojanb wrote:
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
[sqlalchemy] Re: How to instantiate objects of a class mapped against multiple tables?
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...
On Sep 14, 4:31 pm, Michael Bayer mike...@zzzcomputing.com wrote:
bojanb wrote:
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:
[sqlalchemy] Re: How to instantiate objects of a class mapped against multiple tables?
bojanb wrote:
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-tables-are-queried
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
[sqlalchemy] Re: How to instantiate objects of a class mapped against multiple tables?
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(). bojanb wrote: 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 lazily, which is fine most of the time but grossly inefficient in this case (as there can be thousands of records in the first table). Aha, I'll use eagerload! I thought. Alas, it doesn't seem to work for inherited classes. A message in this group suggests that it doesn't work for self-referential inherited classes, but in this case it didn't work for plain inherited classes that don't contain references to self. I'll write a test case that shows this later. OK, I then decided to create a new class mapped against the two tables, using join() in a mapper. This worked great regarding the emitted SQL - session.query on new object generates the correct SQL even with the (deep) inheritance tree that we have. Modifying the attributes on of this object also writes them to correct respective tables on commit. Great! The new class even conceptually fits nicely with the rest of the application (e.g. I realized it's more of a business object while the two underlying classes/tables are more of an implementation detail; I'm not sure I even need the other two classes, just the tables may be enough). Fantastic! However, I can't figure how to create new instances of this (composite as I call it) class. Since it contains fields both for autogenerated primary key from the first class and foreign key from the second class, I cannot set the foreign key because I don't know the primary key before I commit, and commit fails because the foreign key is still null. Am I just missing something or am I attempting a no-no? I would think that since I've defined the attributes to be identical in the oncluase, SQLAlchemy would now that the two are dependent and would not complain about the null value. Of course, I can always create the two other objects, but being able to do it this way fits much more nicely with the rest of the application. --~--~-~--~~~---~--~~ 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 -~--~~~~--~~--~--~---
