Can't find any foreign key relationships between ...

[Diego Woitasen]

Hi,

 I have the following tables:

rcpt_group_asoc = Table("mailing_rcptgroup_rcpts", Base.metadata,

                Column('rcpt_id', Integer, ForeignKey('mailing_rcpt.id')),

                Column('rcptgroup_id', Integer, ForeignKey('mailing_rcptgroup.id')))

class Rcpt(Base):

    __tablename__ = 'mailing_rcpt'

    id = Column(Integer, primary_key=True)

    mail_addr = Column(String(128+256), nullable=False)

    rcpt_groups = relationship("RcptGroup", secondary=rcpt_group_asoc,

                                backref="rcpts")

class RcptGroup(Base):

    __tablename__ = 'mailing_rcptgroup'

    id = Column(Integer, primary_key=True)

    name = Column(String(64), nullable=False, unique=True)

I'm trying to do:

group = db.RcptGroup.__table__

rcpt = db.Rcpt.__table__

print group.join(rcpt)

And got:

ArgumentError: Can't find any foreign key relationships between 'mailing_rcptgroup' and 'mailing_rcpt'.

I can't find the error in my model definition.

Regards,

  Diego

[Michael Bayer]

the join() method of Table (which is what RcptGroup.__table__ is) is part of SQLAlchemy Core and has no awareness of the relationship() construct and therefore no implicit awareness of the rcpt_group_assoc table, if not provided explicitly. If using a pure Core approach, you'd need to join explicitly, group.join(rcpt_group_assoc).join(rcpt).

Alternatively, you can use the orm.join() construct and provide the relationship() bound attribute:

from sqlalchemy.orm import join

j = join(RcptGroup.rcpts)

though typically sticking with query.join() is the simplest approach. I never have a need to use the standalone join() function when querying with the ORM.

[Diego Woitasen]

Thanks.

I'm mixing ORM and Core in my app because I performance in critical in some parts of the code. Are there other problems related to this mix? 

[Michael Bayer]

On Dec 4, 2012, at 4:32 PM, Diego Woitasen wrote:

I'm mixing ORM and Core in my app because I performance in critical in some parts of the code. Are there other problems related to this mix? 

I wouldn't call them "problems", but to the degree that you're using plain Core functions you just won't get ORM awareness of that.  If for example you emitted a bunch of INSERT, UPDATE, DELETE statements, the objects loaded into your ORM session wouldn't be aware of those changes until a refresh.

I'd also say it's not a given that the ORM needs to be bypassed for "performance critical" features, the performance issue of the ORM Query object is that loading rows into full fledged Python objects is a slow process.  So if you need to run a query and get non-object results back, Query can run against individual columns, and you'll get SELECT performance that's more or less the same as you get from using Core directly, i.e.:

session.query(MyClass.x, MyClass.y)....

the Query has a little more overhead in constructing the underlying select() construct than doing it directly, so I guess it depends on what specific performance issues you were seeing.