[sqlalchemy] Re: relation that works in .48 but fails in .53

[Michael Bayer]

Bobby Impollonia wrote:

 Is there a video or slides from that pycon talk available online? I
 see the overview on the pycon site
 (http://us.pycon.org/2009/tutorials/schedule/2PM4/) and it looks very
 interesting.

 I looked more at what you said about the parent id column taking
 precedence and that does seem to be what happens. In particular, I see
 that the sql generated for
 session.query(Child1).filter( Child1.id.in_( range(5))) .count()
 uses the IN condition on parent.id in .53, whereas it was on the
 child1.id in .48. The .48 behavior seems better here. I can't think of
 a situation where I would say Child1.id and want it to use the column
 from the parent table instead of the child.

 Regardless, now that I understand what is happening, I will be able to
 get my application working on .53.

I dont recall the exact use cases that still require this.  the gist is if
you said :

session.query(Child.id, Child.name)

you'd probably want

select id, name from parent

instead of

select child.id, parent.name from parent join child on 

that's not exactly the case where it comes into play, but its along those
lines.  Child.id really represents the id column of your joined table,
which is most prominently parent.id.  Child is not the same as the
child table.I can try changing the order of columns and see what the
unit tests say these days.



--~--~-~--~~~---~--~~
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: relation that works in .48 but fails in .53

[Michael Bayer]

 Bobby Impollonia wrote:

 Is there a video or slides from that pycon talk available online? I
 see the overview on the pycon site
 (http://us.pycon.org/2009/tutorials/schedule/2PM4/) and it looks very
 interesting.

we haven't put anything on line as of yet.  I also just remembered that
the section where I talked about the foo_id issue was probably not in
the final slides we used.


 I looked more at what you said about the parent id column taking
 precedence and that does seem to be what happens. In particular, I see
 that the sql generated for
 session.query(Child1).filter( Child1.id.in_( range(5))) .count()
 uses the IN condition on parent.id in .53, whereas it was on the
 child1.id in .48. The .48 behavior seems better here. I can't think of
 a situation where I would say Child1.id and want it to use the column
 from the parent table instead of the child.

I ran the tests with the order reversed and not too much failed.  but
here's one of the surprises you'll get:

p = session.query(Parent).filter(...some criteiron...).one()

suppose you get a Child1 object from the above.

Then say:

print p.id

id is now child1.id.   the above load will *force a load of the child
table*, even though parent.id is right there.

If the guts of column attributes were really overhauled to adjust for
every scenario, meaning Child.id gives you child1.id, but the instance
somechild.id knows to give you the value bound to parent.id, I suppose it
may be possible for there to be no surprises.

but at the core of it, the mapper maps columns to your class.   if you're
mapping a single class to two tables, the docs/mapper should make it
apparent that the attribute is shared between two columns and if you want
direct access to both, you should split them up.

--~--~-~--~~~---~--~~
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: relation that works in .48 but fails in .53

[Michael Bayer]

this is a side effect of declarative which I covered in my pycon
tutorial.  Child1.id is a map of both child1.id and parent.id, since
they are mapped under the same name.  you can see this if you say
Child1.id.property.columns.   the non-foreign key parent.id takes
precedence.

the easy solution is to split them up:

class Child1(Parent):
 __tablename__ = 'child1'
 __mapper_args__ = dict(polymorphic_identity = 'child1')
 child_id = Column(Integer,  ForeignKey('parent.id'), primary_key=True)


 class Other(Base):
 __tablename__ = 'other'
 id = Column(Integer, primary_key=True)
 child1_id = Column(Integer, ForeignKey('child1.id'))
 child1_object = relation('Child1', backref='others', primaryjoin =
 child1_id == Child1.child_id)



Bobby Impollonia wrote:

 I am porting some code from SQLA .48 to .53 . I have a relation that
 works in .48, but not in .53.

 At the end of this post is a test program demonstrating the problem.
 The program works in .48 but fails in .53 trying to understand the
 child1_object relation . The error message says to add foreign_keys to
 the relation, but that doesn't seem to actually help.

 It does however work if I change the relation to
 child1_object = relation('Child1', backref='others', primaryjoin =
 child1_id == Child1.__table__.c.id)
 Is making this change the recommended solution? Is the behavior I am
 seeing here expected? Is the message telling me to use foreign_keys
 bogus?

 Here is the code:

 #!/usr/bin/python -u
 from sqlalchemy import Column, Integer, create_engine, String, ForeignKey
 from sqlalchemy.orm import sessionmaker, relation
 from sqlalchemy.ext.declarative import declarative_base

 Base = declarative_base()

 class Parent(Base):
 __tablename__ = 'parent'
 id = Column(Integer, primary_key=True)
 _cls = Column('cls', String(50))
 __mapper_args__ = dict(polymorphic_on = _cls )

 class Child1(Parent):
 __tablename__ = 'child1'
 __mapper_args__ = dict(polymorphic_identity = 'child1')
 id = Column(Integer,  ForeignKey('parent.id'), primary_key=True)

 class Child2(Parent):
 __tablename__ = 'child2'
 __mapper_args__ = dict(polymorphic_identity = 'child2')
 id = Column(Integer,  ForeignKey('parent.id'), primary_key=True)

 class Other(Base):
 __tablename__ = 'other'
 id = Column(Integer, primary_key=True)
 child1_id = Column(Integer, ForeignKey('child1.id'))
 child1_object = relation('Child1', backref='others', primaryjoin =
 child1_id == Child1.id)

 engine = create_engine('sqlite://')
 Base.metadata.create_all(engine)
 session = sessionmaker(engine)()

 def main():
 child1 = Child1()
 child2 = Child2()
 other = Other()
 child1.others = [other]
 session.add(child1)
 session.add(child2)
 session.add(other)
 session.flush()
 assert 2 == session.query(Parent).count()
 assert child1 == session.query(Other).one().child1_object

 if __name__ == '__main__':
 main()

 



--~--~-~--~~~---~--~~
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: relation that works in .48 but fails in .53

[Bobby Impollonia]

Is there a video or slides from that pycon talk available online? I
see the overview on the pycon site
(http://us.pycon.org/2009/tutorials/schedule/2PM4/) and it looks very
interesting.

I looked more at what you said about the parent id column taking
precedence and that does seem to be what happens. In particular, I see
that the sql generated for
session.query(Child1).filter( Child1.id.in_( range(5))) .count()
uses the IN condition on parent.id in .53, whereas it was on the
child1.id in .48. The .48 behavior seems better here. I can't think of
a situation where I would say Child1.id and want it to use the column
from the parent table instead of the child.

Regardless, now that I understand what is happening, I will be able to
get my application working on .53.

Thanks for your help.

On Wed, Apr 8, 2009 at 6:04 PM, Michael Bayer mike...@zzzcomputing.com wrote:

 this is a side effect of declarative which I covered in my pycon
 tutorial.  Child1.id is a map of both child1.id and parent.id, since
 they are mapped under the same name.  you can see this if you say
 Child1.id.property.columns.   the non-foreign key parent.id takes
 precedence.

 the easy solution is to split them up:

 class Child1(Parent):
     __tablename__ = 'child1'
     __mapper_args__ = dict(polymorphic_identity = 'child1')
     child_id = Column(Integer,  ForeignKey('parent.id'), primary_key=True)


  class Other(Base):
     __tablename__ = 'other'
     id = Column(Integer, primary_key=True)
     child1_id = Column(Integer, ForeignKey('child1.id'))
     child1_object = relation('Child1', backref='others', primaryjoin =
  child1_id == Child1.child_id)



 Bobby Impollonia wrote:

 I am porting some code from SQLA .48 to .53 . I have a relation that
 works in .48, but not in .53.

 At the end of this post is a test program demonstrating the problem.
 The program works in .48 but fails in .53 trying to understand the
 child1_object relation . The error message says to add foreign_keys to
 the relation, but that doesn't seem to actually help.

 It does however work if I change the relation to
     child1_object = relation('Child1', backref='others', primaryjoin =
 child1_id == Child1.__table__.c.id)
 Is making this change the recommended solution? Is the behavior I am
 seeing here expected? Is the message telling me to use foreign_keys
 bogus?

 Here is the code:

 #!/usr/bin/python -u
 from sqlalchemy import Column, Integer, create_engine, String, ForeignKey
 from sqlalchemy.orm import sessionmaker, relation
 from sqlalchemy.ext.declarative import declarative_base

 Base = declarative_base()

 class Parent(Base):
     __tablename__ = 'parent'
     id = Column(Integer, primary_key=True)
     _cls = Column('cls', String(50))
     __mapper_args__ = dict(polymorphic_on = _cls )

 class Child1(Parent):
     __tablename__ = 'child1'
     __mapper_args__ = dict(polymorphic_identity = 'child1')
     id = Column(Integer,  ForeignKey('parent.id'), primary_key=True)

 class Child2(Parent):
     __tablename__ = 'child2'
     __mapper_args__ = dict(polymorphic_identity = 'child2')
     id = Column(Integer,  ForeignKey('parent.id'), primary_key=True)

 class Other(Base):
     __tablename__ = 'other'
     id = Column(Integer, primary_key=True)
     child1_id = Column(Integer, ForeignKey('child1.id'))
     child1_object = relation('Child1', backref='others', primaryjoin =
 child1_id == Child1.id)

 engine = create_engine('sqlite://')
 Base.metadata.create_all(engine)
 session = sessionmaker(engine)()

 def main():
     child1 = Child1()
     child2 = Child2()
     other = Other()
     child1.others = [other]
     session.add(child1)
     session.add(child2)
     session.add(other)
     session.flush()
     assert 2 == session.query(Parent).count()
     assert child1 == session.query(Other).one().child1_object

 if __name__ == '__main__':
     main()

 



 


--~--~-~--~~~---~--~~
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
-~--~~~~--~~--~--~---