One To Many Relationship definition with reflection and declarative syntax in sqlalchemy gives join condition error

I am trying to set One-To-Many relationship on an existing database.

Simplified DDL is :

create table accnt (
  code varchar(20) not null
  , def varchar(100)
  , constraint pk_accnt primary key (code)
);

commit;

create table slorder (
  code varchar(20) not null
  , def varchar(100)
  , dt date
  , c_accnt varchar(20) not null
  , constraint pk_slorder primary key (code)
  , constraint fk_slorder_accnt foreign key (c_accnt)
     references accnt (code)
     on update cascade on delete cascade
);

commit;

SqlAlchemy Code :

from sqlalchemy import *
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.orm import *

engine = create_engine('firebird://sysdba:masterkey@127.0.0.1/d:\\prj\\db2\\makki.fdb?charset=WIN1254', echo=False)
Base = declarative_base()
Base.metadata.bind = engine

class Accnt(Base):
    __tablename__ = 'accnt'
    __table_args__ = {'autoload': True}

    defi = Column('def', String(100))

class SlOrder(Base):
    __tablename__ = 'slorder'
    __table_args__ = {'autoload': True}

    defi = Column("def", String(100))
    accnt = relationship('Accnt', backref='slorders')

gives

sqlalchemy.exc.ArgumentError: Could not determine join condition between parent/child tables on relationship SlOrder.accnt.  Specify a 'primaryjoin' expression.  If 'secondary' is present, 'secondaryjoin' is needed as well.

error.

My possible solutions to this problem are :

1

class SlOrder(Base):
    __tablename__ = 'slorder'
    __table_args__ = {'autoload': True}

    defi = Column("def", String(100))
    c_accnt = Column("c_accnt", String(20), ForeignKey('accnt.code'))

    accnt = relationship('Accnt', backref='slorders')

But this approach needs that I have to add every foreign key constraint column manually, which leads to making reflection useles. (Because I've got many columns references to other tables.)

2

class SlOrder(Base):
    __table__ = Table('accnt', metadata, autoload = True, autoload_with=engine)
    accnt = relationship('Accnt', backref='slorders', primaryjoin=(__table__.c_accnt==Accnt.code))

This approach has an another consequence (please see my previous question)

So what am I missing? What is the best way to define a relationship both using reflection and declarative syntax?

EDIT :

I've figured that SqlAlchemy finds and builds relationships if child table has only one reference to parent table.

But if child table has more than one reference as :

create table slorder (
  code varchar(20) not null
  , def varchar(100)
  , dt date
  , c_accnt varchar(20) not null
  , c_accnt_ref  varchar(20) 
  , 开发者_StackOverflow社区constraint pk_slorder primary key (code)
  , constraint fk_slorder_accnt foreign key (c_accnt)
     references accnt (code)
     on update cascade on delete cascade

  , constraint fk_slorder_accnt_ref foreign key (c_accnt_ref)
     references accnt (code)
     on update cascade on delete no action
);

the above error occurs.

So is it expected behavior of SqlAlchemy to give error if there is more than one relation between two tables?

I think you have to add ForeignKey in child table.

By the way of define ForeignKey you can assign value to c_accnt and also assign object of parent to accnt.

Internally sqlalchemy fire the query which you wrote in primaryjoin. If there is no foreign key then model can't understand on which field it has to run a query.

You can use any both way. But I personally prefer ForeignKey and relation to ForeignKey. This way you have to write some more code but, it will give flexibility to assign value plus object directly.

I think your code should automatically reflect ForeignKey and use for the relationship without any changes.
Just some ideas to explore the issue though:

1. Make sure you do not have multiple ForeignKey's to the same parent table or else you must specify the join condition using primaryjoin parameter, because SA cannot automatically decide which one to use.
2. Make sure you actually have a ForeignKey defined in the slorder table (as shown in the code example)
3. Check if maybe there is a non-default schema defined for some tables and maybe you need to define one in your tables _table_args__ = {'schema': 'my_schema'} (Just guessing, as I do not know firebird so no idea about schema support there really)
4. Check/Debug the reflection step: sqlalchemy/dialects/firebird/base.py has get_foreign_keys. Check the SQL statement fkqry and execute it directly on your database to see if it reflects your ForeignKey. If not, try to find out why.