SQlAlchemy: Could not determine join condition between parent/child tables on relationship error message

Question

I've recently used sqlacodegen

When I try and run against the generated code it get this message that I've been unable to fix:

Could not determine join condition between parent/child tables on relationship Workgrp.usrmst - there are multiple foreign key paths linking the tables. Specify the 'foreign_keys' argument, providing a list of those columns which should be counted as containing a foreign key reference to the parent table.

I'm wondering if someone can shed some light:

Here is the generated code for the relevant tables causing the issue:

class Workgrp(Owner):
    __tablename__ = 'workgrp'

    workgrp_id = Column(ForeignKey('owner.owner_id'), primary_key=True)
    workgrp_prntid = Column(Numeric(scale=0, asdecimal=False))
    workgrp_name = Column(String(256))
    workgrp_desc = Column(String(4000))
    workgrp_owner = Column(ForeignKey('usrmst.usrmst_id'))
    workgrp_lstchgtm = Column(DateTime, index=True)
    workgrp_externid = Column(String(20))
    workgrp_profile = Column(Text)
    workgrp_usrmodtm = Column(DateTime)

    usrmst = relationship('Usrmst')


class Usrmst(Owner):
    __tablename__ = 'usrmst'
    __table_args__ = (
        Index('usrmst_ak1', 'usrmst_domain', 'usrmst_name'),
    )

    usrmst_id = Column(ForeignKey('owner.owner_id'), primary_key=True)
    usrmst_domain = Column(String(256))
    usrmst_name = Column(String(256), nullable=False)
    usrmst_fullname = Column(String(1024))
    usrmst_desc = Column(String(4000))
    usrmst_phoneno = Column(String(40))
    usrmst_pagerno = Column(String(40))
    usrmst_email = Column(String(1024))
    usrmst_emailtype = Column(Numeric(scale=0, asdecimal=False))
    secmst_id = Column(ForeignKey('secmst.secmst_id'))
    lngmst_id = Column(ForeignKey('lngmst.lngmst_id'))
    usrmst_password = Column(String(1024))
    usrmst_externid = Column(String(20))
    usrmst_suser = Column(String(1))
    usrmst_lstchgtm = Column(DateTime, index=True)
    usrmst_orapassword = Column(String(144))
    usrmst_wingroup = Column(String(1))
    usrmst_tmpacct = Column(String(1))
    usrmst_profile = Column(Text)
    usrmst_usrmodtm = Column(DateTime)
    usrmst_principal = Column(String(256))
    usrmst_keytab = Column(String(4000))

    lngmst = relationship('Lngmst')
    secmst = relationship('Secmst')

I've looked at the SQLAlchemy docs, and tried things with

But ended up with messages like:

'Table' object has no attribute 'usrmst_id'
AttributeError: 'Table' object has no attribute 'workgrp_owner'

Here are some screenshots relevant raw Tables in DBeaver, the Eclipse based SQL Explorer type plugin:

I don't think it should make much difference but I'm developing on:

Windows Server 2012 RC2
using Oracle 11g
With Python 3.6.4
sqlacodegen 1.1.6

There is a snippet I've added after the generated code, with the relevant imports..

engine = create_engine("oracle://<dbuser>:<dbpwd>@<host>:<port>/<db>")
Session = sessionmaker(bind=engine)
session = Session()
firstjm = session.query(Jobmst).first()
print(firstjm)

Other annoying things about the sqlacodegen generated code:

Didn't generate string implementations, of __repr__ and __str__, so printing out a table didn't generically list, table name, followed by key, value pair list for columns and their values.
Purports to be PEP compliant code, lines often blew the character limit, and so you'd have to go and reformat long comparison strings 'x x' into 'x' ' x' etc ,

JGFMK · Accepted Answer · 2018-02-08T14:36:53.833

I finally tracked down the syntactic sugar doing a Google advanced search on the exact term:

"Could not determine join condition between parent/child tables on relationship"
within the domain : http://docs.sqlalchemy.org/
This yielded this: http://docs.sqlalchemy.org/en/latest/orm/join_conditions.html#handling-multiple-join-paths
From that, this seemed to fit the bill:

usrmst = relationship('Usrmst', foreign_keys=[workgrp_owner])

I also overrode the default implementation of __repr__ for the Base class defined by:

Base = declarative_base()

to this:

import json
def tablerepr(self):
    return "<{}: ({})>".format(self.__table__,
                             json.dumps(
                                dict(
                                    list(
                                        self.__dict__.items()
                                        )[1:]
                                 ), default=str)
                             )
Base.__repr__ = tablerepr

Useful links I found when getting to grips with the objects SQLAlchemy spits out:

So I end up adding the tablerepr method to my main generated code..

Then in a separate python script I import the generated code with my tweaks, and have code like this:

engine = create_engine("oracle://<user>:<password>@<host>:<port>/<db>")
inspector = inspect(engine)
Session = sessionmaker(bind=engine)
session = Session()
print(session.query(Jobmst).first())

Jobmst is a class representing a table I've imported. This represents kind of content that print then generates, with the customer data replaced by .... The table name, then a tuple containing each column name and it's data as key/value pairs:

<jobmst: ({"jobmst_id": 1590.0, "jobmst_alias": "1590", "jobmst_owner": 67.0, "jobmst_type": 1.0, "jobmst_dirty": "0", "jobmst_desc": null, "jobmst_vars": null, "jobmst_prntid": 1589.0, "jobmst_crttm": "2001-06-18 10:38:19", "jobmst_active": "N", "jobmst_usrmodtm": "2001-06-29 20:27:06", "jobdtl_id": 1591.0, "evntmst_id": null, "bizunit_id": null, "jobmst_lstchgtm": "2001-06-29 20:27:06", "jobmst_evntoffset": null, "jobmst_desc_type": null, "jobmst_runbook": null, "jobmst_name": "....", "jobmst_runbook_type": null, "jobcls_id": 32.0, "jobmst_mode": null, "jobmst_prntname": "...."})>

SQlAlchemy: Could not determine join condition between parent/child tables on relationship error message

1 Answers1