Thanks, I ran a similar script without any problems. It seems there's a
modification somewhere to the Base we are using that causes the conflict.
On Thursday, June 12, 2014 12:26:45 PM UTC-4, Simon King wrote:
>
> For what it's worth, the following script works for me, but I only
> have access to sqlite at the moment, which doesn't support multiple
> schemas, so I had to comment those bits out. If you uncomment those
> and run it against your database, does it fail? If so, the problem
> would seem to be something to do with the tables being in different
> schemas.
>
> from sqlalchemy import (Column, ForeignKey, Enum, Integer,
> String, Text, CheckConstraint, Numeric,
> create_engine)
> from sqlalchemy.orm import relationship, sessionmaker, backref
> from sqlalchemy.ext.declarative import declarative_base
>
> Base = declarative_base()
>
> class ServiceInstance(Base):
> __tablename__ = 'services'
> #__table_args__ = (
> # {'schema': 'customer_inquiry'}
> # )
>
> service_id = Column(Integer, primary_key=True)
> service_instance_id = Column(String(12), unique=True)
> title = Column(String(255))
> definition_title = Column(String(255), nullable=False)
> description = Column(Text)
> #category = Column(ForeignKey(ServiceCategory.category),
> nullable=False)
> price = Column(Numeric, CheckConstraint('price >= 0'), nullable=False)
> units_included = Column(
> Numeric,
> CheckConstraint('units_included IS NULL OR units_included >= 0'))
> unit_price = Column(
> Numeric,
> CheckConstraint('unit_price IS NULL OR unit_price >= 0'))
>
> class Endpoint(Base):
> __tablename__ = 'endpoints'
> #__table_args__ = {'schema': 'nms'}
>
> service_id = Column(ForeignKey(ServiceInstance.service_id),
> primary_key=True)
> endpoint_type = Column(Enum('Service', 'Address'))
> service_instance = relationship(
> ServiceInstance,
> foreign_keys=service_id,
> backref=backref('endpoints', cascade='all, delete-orphan'))
>
> if __name__ == '__main__':
> engine = create_engine('sqlite://', echo='debug')
> Session = sessionmaker(bind=engine)
>
> Base.metadata.create_all(bind=engine)
>
>
> On Thu, Jun 12, 2014 at 5:19 PM, Simon King <[email protected]
> <javascript:>> wrote:
> > In that case, could you send a stripped-down single-file test case
> > with just those 2 classes that shows the problem?
> >
> > Thanks,
> >
> > Simon
> >
> > On Thu, Jun 12, 2014 at 5:13 PM, <[email protected] <javascript:>>
> wrote:
> >> I have tried calling relationship without foreign_keys or primaryjoin,
> in
> >> which case I get an error message suggesting I specify foreign_keys.
> Base is
> >> the same class for both model classes in the case where this does not
> work.
> >> When each model class has a different Base class things work fine. Also
> it
> >> is a 1-to-1 relationship, I just missed that s in the backref.
> >>
> >> On Thursday, June 12, 2014 12:03:29 PM UTC-4, Simon King wrote:
> >>>
> >>> On Thu, Jun 12, 2014 at 4:39 PM, <[email protected]> wrote:
> >>> > I can't seem to construct a relationship against this
> ServiceInstance
> >>> > class
> >>> > without having
> >>> > the Endpoint class inherit from a new declarative_base(). I've tried
> >>> > several
> >>> > different
> >>> > methods of calling relationship() and the error messages are fairly
> >>> > similar.
> >>> > Below I've
> >>> > shown the two classes as well as the various relationship() calls
> and
> >>> > the
> >>> > resulting
> >>> > errors. I'm new to SQLAlchemy so apologies if I'm overlooking
> something
> >>> > very
> >>> > basic, but I
> >>> > don't understand the errors given that I have service_id = Column(
> >>> > ForeignKey(ServiceInstance.service_id), primary_key=True)
> >>> >
> >>> > class Endpoint(Base):
> >>> > __tablename__ = 'endpoints'
> >>> > __table_args__ = {'schema': 'nms'}
> >>> >
> >>> > service_id = Column(ForeignKey(ServiceInstance.service_id),
> >>> > primary_key=True)
> >>> > endpoint_type = Column(Enum('Service', 'Address'))
> >>> > service_instance = relationship(
> >>> > ServiceInstance,
> >>> > foreign_keys=service_id,
> >>> > backref=backref('endpoints', cascade='all,
> delete-orphan'))
> >>> >
> >>> >
> >>> > class ServiceInstance(Base):
> >>> > __tablename__ = 'services'
> >>> > __table_args__ = (
> >>> > {'schema': 'customer_inquiry'}
> >>> > )
> >>> >
> >>> > service_id = Column(Integer, primary_key=True)
> >>> > service_instance_id = Column(String(12), unique=True)
> >>> > title = Column(String(255))
> >>> > definition_title = Column(String(255), nullable=False)
> >>> > description = Column(Text)
> >>> > category = Column(ForeignKey(ServiceCategory.category),
> >>> > nullable=False)
> >>> > price = Column(Numeric, CheckConstraint('price >= 0'),
> >>> > nullable=False)
> >>> > units_included = Column(
> >>> > Numeric,
> >>> > CheckConstraint('units_included IS NULL OR
> units_included >=
> >>> > 0'))
> >>> > unit_price = Column(
> >>> > Numeric,
> >>> > CheckConstraint('unit_price IS NULL OR unit_price >=
> 0'))
> >>> >
> >>> > sqlalchemy.exc.NoForeignKeysError: Could not determine join
> condition
> >>> > between parent/child
> >>> > tables on relationship Endpoint.service_instance - there are no
> foreign
> >>> > keys
> >>> > linking these
> >>> > tables. Ensure that referencing columns are associated with a
> >>> > ForeignKey or
> >>> > ForeignKeyConstraint, or specify a 'primaryjoin' expression.
> >>> >
> >>> >
> >>> > service_instance = relationship(
> >>> > ServiceInstance,
> >>> > primaryjoin=service_id == ServiceInstance.service_id,
> >>> > backref=backref('endpoints', cascade='all,
> delete-orphan'))
> >>> >
> >>> > sqlalchemy.exc.ArgumentError: Could not locate any simple equality
> >>> > expressions involving
> >>> > locally mapped foreign key columns for primary join condition
> >>> > 'nms.endpoints.service_id =
> >>> > customer_inquiry.services.service_id' on relationship
> >>> > Endpoint.service_instance.
> >>> > Ensure that referencing columns are associated with a ForeignKey or
> >>> > ForeignKeyConstraint,
> >>> > or are annotated in the join condition with the foreign()
> annotation. To
> >>> > allow comparison
> >>> > operators other than '==', the relationship can be marked as
> >>> > viewonly=True.
> >>> >
> >>> >
> >>> > service_instance = relationship(
> >>> > ServiceInstance,
> >>> > primaryjoin=service_id == ServiceInstance.service_id,
> >>> > viewonly=True,
> >>> > backref=backref('endpoints', cascade='all,
> delete-orphan'))
> >>> >
> >>> > sqlalchemy.exc.ArgumentError: Can't determine relationship direction
> for
> >>> > relationship
> >>> > 'Endpoint.service_instance' - foreign key columns are present in
> neither
> >>> > the
> >>> > parent nor
> >>> > the child's mapped tables
> >>> >
> >>>
> >>> With those column definitions, this should Just Work - you shouldn't
> >>> need foreign_keys or primaryjoin parameters in the relationship
> >>> definition, since there is only 1 foreign key and no ambiguity.
> >>>
> >>> (I'm slightly confused by your columns though - is service_id really
> >>> the only primary key of Endpoint? In which case, this would have to be
> >>> a 1-to-1 relationship, but your "endpoints" backref implies that you
> >>> expect there to be more than one endpoint per service)
> >>>
> >>> Did you say that "Base" is the same class in both cases? Foreign key
> >>> relationships are looked up in the Metadata instance which is attached
> >>> to the declarative_base class, so if you have 2 different Base
> >>> classes, SA won't be able to resolve the foreign keys. You can work
> >>> around this by getting the 2 Base classes to share the same Metadata
> >>> instance:
> >>>
> >>>
> >>>
> http://docs.sqlalchemy.org/en/rel_0_9/orm/extensions/declarative.html#accessing-the-metadata
>
> >>>
> >>> 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 [email protected] <javascript:>.
> >> To post to this group, send email to [email protected]
> <javascript:>.
> >> Visit this group at http://groups.google.com/group/sqlalchemy.
> >> For more options, visit https://groups.google.com/d/optout.
>
--
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 [email protected].
To post to this group, send email to [email protected].
Visit this group at http://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.
