[sqlalchemy] self-referential many-to-many relationships and mixins

2013-08-13 Thread till.plewe 
I am using python 3.3 and sqlalchemy 0.8.2

I am trying to define a self-referential many-to-many relationship for a 
class where the primary key is provided by a mixin. Defining the primary
directly in the class works. Using the mixin does not. 

I would be grateful for any suggestions or pointers to relevant 
documentation.

Below is an example showing my problem.  As given the example works.
Uncommenting the line #id = ... in 'Base' and commenting out the 
corresponding line in 'A' breaks the example. Is there any way to define
the primary key in Base and getting the 'requires' relation to work?

---

from sqlalchemy import Column, Integer, String, DateTime, Table, 
ForeignKey,create_engine
from sqlalchemy.ext.declarative import declarative_base,declared_attr
from sqlalchemy.orm import sessionmaker, relationship, backref

class Base(object):
#id = Column(Integer, primary_key=True)
pass

Base = declarative_base(cls=Base)

association_table = Table('association', 
  Base.metadata,
  Column('prerequisite', Integer, 
ForeignKey('a.id')),
  Column('dependency', Integer, ForeignKey('a.id')))

class A(Base):
__tablename__ = a
id = Column(Integer, primary_key=True)
requires   = relationship(A, 
  secondary = association_table,
  
primaryjoin=(id==association_table.c.prerequisite),
  
secondaryjoin=(id==association_table.c.dependency),
  backref = backref(required_by))

if __name__ == __main__:
engine = create_engine('sqlite:///:memory:', echo=False)
Session = sessionmaker(bind=engine)
session = Session()
Base.metadata.create_all(engine)

T=A()
U=A()
session.add(T)
session.add(U)
T.requires.append(U)
session.commit()
print(T,T.id,T.requires,T.required_by)
print(U,U.id,U.requires,U.required_by)

-- 
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/groups/opt_out.

Re: [sqlalchemy] self-referential many-to-many relationships and mixins

2013-08-13 Thread Simon King 
On Tue, Aug 13, 2013 at 2:07 PM, till.plewe till.pl...@gmail.com wrote:
 I am using python 3.3 and sqlalchemy 0.8.2

 I am trying to define a self-referential many-to-many relationship for a
 class where the primary key is provided by a mixin. Defining the primary
 directly in the class works. Using the mixin does not.

 I would be grateful for any suggestions or pointers to relevant
 documentation.

 Below is an example showing my problem.  As given the example works.
 Uncommenting the line #id = ... in 'Base' and commenting out the
 corresponding line in 'A' breaks the example. Is there any way to define
 the primary key in Base and getting the 'requires' relation to work?

 ---

 from sqlalchemy import Column, Integer, String, DateTime, Table,
 ForeignKey,create_engine
 from sqlalchemy.ext.declarative import declarative_base,declared_attr
 from sqlalchemy.orm import sessionmaker, relationship, backref

 class Base(object):
 #id = Column(Integer, primary_key=True)
 pass

 Base = declarative_base(cls=Base)

 association_table = Table('association',
   Base.metadata,
   Column('prerequisite', Integer,
 ForeignKey('a.id')),
   Column('dependency', Integer, ForeignKey('a.id')))

 class A(Base):
 __tablename__ = a
 id = Column(Integer, primary_key=True)
 requires   = relationship(A,
   secondary = association_table,

 primaryjoin=(id==association_table.c.prerequisite),

 secondaryjoin=(id==association_table.c.dependency),
   backref = backref(required_by))

 if __name__ == __main__:
 engine = create_engine('sqlite:///:memory:', echo=False)
 Session = sessionmaker(bind=engine)
 session = Session()
 Base.metadata.create_all(engine)

 T=A()
 U=A()
 session.add(T)
 session.add(U)
 T.requires.append(U)
 session.commit()
 print(T,T.id,T.requires,T.required_by)
 print(U,U.id,U.requires,U.required_by)


You can make it work by using strings as the primaryjoin and
secondaryjoin parameters and referring to A.id rather than just id:

class A(Base):
__tablename__ = a
requires   = relationship(A,
  secondary = association_table,

primaryjoin=A.id==association.c.prerequisite,

secondaryjoin=A.id==association.c.dependency,
  backref = backref(required_by))

This technique is described in the Configuring Relationships section
of the declarative documentation:

http://docs.sqlalchemy.org/en/rel_0_8/orm/extensions/declarative.html#configuring-relationships

Hope that helps,

Simon

-- 
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/groups/opt_out.

Re: [sqlalchemy] self-referential many-to-many relationships and mixins

2013-08-13 Thread till plewe 
Thank you. That does the trick.

Till

On Tue, Aug 13, 2013 at 10:54 PM, Simon King si...@simonking.org.uk wrote:
 On Tue, Aug 13, 2013 at 2:07 PM, till.plewe till.pl...@gmail.com wrote:
 I am using python 3.3 and sqlalchemy 0.8.2

 I am trying to define a self-referential many-to-many relationship for a
 class where the primary key is provided by a mixin. Defining the primary
 directly in the class works. Using the mixin does not.

 I would be grateful for any suggestions or pointers to relevant
 documentation.

 Below is an example showing my problem.  As given the example works.
 Uncommenting the line #id = ... in 'Base' and commenting out the
 corresponding line in 'A' breaks the example. Is there any way to define
 the primary key in Base and getting the 'requires' relation to work?

 ---

 from sqlalchemy import Column, Integer, String, DateTime, Table,
 ForeignKey,create_engine
 from sqlalchemy.ext.declarative import declarative_base,declared_attr
 from sqlalchemy.orm import sessionmaker, relationship, backref

 class Base(object):
 #id = Column(Integer, primary_key=True)
 pass

 Base = declarative_base(cls=Base)

 association_table = Table('association',
   Base.metadata,
   Column('prerequisite', Integer,
 ForeignKey('a.id')),
   Column('dependency', Integer, ForeignKey('a.id')))

 class A(Base):
 __tablename__ = a
 id = Column(Integer, primary_key=True)
 requires   = relationship(A,
   secondary = association_table,

 primaryjoin=(id==association_table.c.prerequisite),

 secondaryjoin=(id==association_table.c.dependency),
   backref = backref(required_by))

 if __name__ == __main__:
 engine = create_engine('sqlite:///:memory:', echo=False)
 Session = sessionmaker(bind=engine)
 session = Session()
 Base.metadata.create_all(engine)

 T=A()
 U=A()
 session.add(T)
 session.add(U)
 T.requires.append(U)
 session.commit()
 print(T,T.id,T.requires,T.required_by)
 print(U,U.id,U.requires,U.required_by)


 You can make it work by using strings as the primaryjoin and
 secondaryjoin parameters and referring to A.id rather than just id:

 class A(Base):
 __tablename__ = a
 requires   = relationship(A,
   secondary = association_table,

 primaryjoin=A.id==association.c.prerequisite,

 secondaryjoin=A.id==association.c.dependency,
   backref = backref(required_by))

 This technique is described in the Configuring Relationships section
 of the declarative documentation:

 http://docs.sqlalchemy.org/en/rel_0_8/orm/extensions/declarative.html#configuring-relationships

 Hope that helps,

 Simon

 --
 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/groups/opt_out.



-- 
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/groups/opt_out.