On Feb 19, 2011, at 9:23 AM, farcat wrote:
> Hi,
>
> I am trying to implement tables to store a basic multiple inheritance
> scheme via SA. I am pretty new to SA and could use some help with the
> following code:
>
> <code>
>
> from sqlalchemy import *
> from sqlalchemy.ext.declarative import declarative_base
> from sqlalchemy.orm import relationship, sessionmaker
> from sqlalchemy.ext.associationproxy import association_proxy
>
> Base = declarative_base()
>
> def _create_inheritance(supertype, subtype):
> return Inheritance(supertype, subtype)
>
> class Inheritance(Base):
> __tablename__ = 'Inheritance'
> sub_name = Column(String(50), ForeignKey('Types.name'),
> primary_key=True)
> super_name = Column(String(50), ForeignKey('Types.name'),
> primary_key=True)
> def __init__(self, supertype, subtype):
> self.supertype = supertype
> self.subtype = subtype
>
> class Types(Base):
> __tablename__ = 'Types'
> name = Column(String(50), primary_key=True)
> abstract = Column(Boolean)
> subtypes = association_proxy('Inheritance', 'subtypes', creator =
> _create_inheritance)
> supertypes = association_proxy('Inheritance', 'supertypes',
> creator = _create_inheritance)
> def __init__(self, name, abstract = False):
> self.name = name
> self.abstract = abstract
> def __repr__(self):
> return "Type(%r)" % (self.name)
>
>
> class Members(Base):
> __tablename__ = 'Members'
> name = Column(String(50), primary_key=True)
> type_name = Column(String(50),ForeignKey('Types.name'))
> type = relationship("Types", backref= "of_members", uselist=False)
> of_type_name = Column(String(50),ForeignKey('Types.name'),
> primary_key=True)
> of_type = relationship("Types", backref='members')
> def __init__(self, name, tp):
> self.name = name
> self.type = tp
> def __repr__(self):
> return "Member(%r, %r)" % (self.name, type.name)
>
> engine = create_engine('sqlite:///:memory:', echo=False)
> Base.metadata.create_all(engine)
>
>
> if __name__ == "__main__":
> Session = sessionmaker(bind=engine)
> session = Session()
>
> c1 = Types("A") #<= error below
> c2 = Types("B")
> c3 = Types("C")
> c1.members.append(Members("m1", c2))
> </code>
>
> I have the following 2 questions:
>
> 1) did i get the relationship between Types and Inheritance right? I
> want to be able to call supertypes and subtypes from any type
> directly.
I haven't thought about the problem of multiple inheritance in this context so
I don't understand every aspect of this structure - I get that Types are
associated with each other in a many-to-many pattern via Inheritance. Most
elements appear to be here though I don't see any relationship() that deals
with Inheritance. Inheritance would appear to model an m2m between Types so
is more succinctly placed as the "secondary" element of a relationship() (or
two such relationships if each Type has links in both directions, the rationale
of that relatively rare use case is beyond my current understanding of what you
are modeling though), if you don't otherwise need to reference Inheritance
explicitly. Given that, I don't really get what "Members" does, which also
appears to be modeling its own m2m relationship between Types. And you have
some join conditions missing since the join used by a relationship can't be
assumed when there is more than one path.
> 2) loose question: how does sqlalchemy handle type errors, e.g. when i
> have an A.attr = Column(String), a = A(), a.attr = 15? (i use sqlite
> now, which accepts putting string in integer columns, but later i will
> use another DB )
sqlite has on the fly types so you can pass anything to anything and you
normally won't get an error - only if you use a SQLalchemy type that features
in-python coercion such as DateTime or Numeric would you get a type error on
that platform. Using a more stringent backend such as Postgresql or SQL
Server will lead to errors emitted by the DBAPI as it attempts to prepare data
for the backend. Different DBAPIs on the same platform have different behavior
as well. SQLAlchemy defers to the backend as often as possible both for
performance reasons as well as allowing full compatibility with the backend (at
the cost of identical behavior across backends).
User-defined in-Python type coercion and validation can be handled at the ORM
level using the @validates decorator, or by using attribute extensions, and at
the SQL expression level using TypeDecorator.
> 3) I get an error for the above code related to the Members class
> which i don't understand, can anyone explain/help with a fix:
the pattern here is:
class Parent(object):
fk_one = Column(Integer, ForeignKey('child.id'))
fk_two = Column(Integer, ForeignKey('child.id'))
child = relationship(Child)
how to load the "Child" for a certain Parent ? Use "fk_one" or "fk_two" ?
When ambiguity is present:
class Parent(object):
fk_one = Column(Integer, ForeignKey('child.id'))
fk_two = Column(Integer, ForeignKey('child.id'))
child = relationship(Child, primaryjoin=Child.id==fk_two)
docs:
http://www.sqlalchemy.org/docs/orm/relationships.html#specifying-alternate-join-conditions-to-relationship
http://www.sqlalchemy.org/docs/orm/extensions/declarative.html#configuring-relationships
>
>
> Traceback (most recent call last):
> File "D:\Documents\Code\NetBeans\test\Alchemy\src\alchemy.py", line
> 60, in <module>
> c1 = Types("A")
> File "<string>", line 4, in __init__
> File "C:\Python27\lib\site-packages\sqlalchemy\orm\state.py", line
> 100, in initialize_instance
> fn(self, instance, args, kwargs)
> File "C:\Python27\lib\site-packages\sqlalchemy\orm\mapper.py", line
> 2413, in _event_on_init
> instrumenting_mapper.compile()
> File "C:\Python27\lib\site-packages\sqlalchemy\orm\mapper.py", line
> 807, in compile
> mapper._post_configure_properties()
> File "C:\Python27\lib\site-packages\sqlalchemy\orm\mapper.py", line
> 837, in _post_configure_properties
> prop.init()
> File "C:\Python27\lib\site-packages\sqlalchemy\orm\interfaces.py",
> line 475, in init
> self.do_init()
> File "C:\Python27\lib\site-packages\sqlalchemy\orm\properties.py",
> line 899, in do_init
> self._determine_joins()
> File "C:\Python27\lib\site-packages\sqlalchemy\orm\properties.py",
> line 1028, in _determine_joins
> % self)
> sqlalchemy.exc.ArgumentError: Could not determine join condition
> between parent/child tables on relationship Members.type. Specify a
> 'primaryjoin' expression. If 'secondary' is present, 'secondaryjoin'
> is needed as well.
>
> Any help and clarifications are welcome!
>
> --
> 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.
>
--
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.
