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 <si...@simonking.org.uk> 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, <ty...@beanfield.com> 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, <ty...@beanfield.com> 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 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/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 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/d/optout.
