在sqlalchemy中使用反射和声明性语法定义一对多关系时出现连接条件错误

Question

我正在尝试在一个已有的数据库上设置一对多的关系。

简化的数据库结构定义是：

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 代码：

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')

结果是

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.

错误。

我想到的解决方案有：

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')

不过这种方法需要我手动添加每个外键约束的列，这样会让反射变得没用。（因为我有很多列是引用其他表的。）

2

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

这种方法还有另一个后果（请查看我之前的问题）

那么我漏掉了什么呢？使用反射和声明式语法定义关系的最佳方法是什么？

编辑：

我发现如果子表只有一个引用到父表，SqlAlchemy会找到并建立关系。

但是如果子表有多个引用，比如：

create table slorder (
  code varchar(20) not null
  , def varchar(100)
  , dt date
  , c_accnt varchar(20) not null
  , c_accnt_ref  varchar(20) 
  , 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
);

就会出现上述错误。

所以，如果两个表之间有多个关系，SqlAlchemy给出错误是正常现象吗？

Answer 1

我觉得你需要在子表里添加一个 ForeignKey。

通过定义 ForeignKey，你可以给 c_accnt 赋值，同时也可以把父对象赋给 accnt。

在内部，sqlalchemy 会执行你在 primaryjoin 中写的查询。如果没有外键，模型就不知道该在哪个字段上执行查询。

你可以用这两种方式中的任何一种。但我个人更喜欢使用 ForeignKey 和 relation 来处理 ForeignKey。这样虽然需要写更多的代码，但会让你在赋值和直接使用对象时更灵活。

Answer 2

我觉得你的代码应该能自动识别ForeignKey，并且在关系中使用它，不需要做任何修改。
不过这里有一些建议可以帮助你解决问题：

1. 确保你没有对同一个父表定义多个ForeignKey，否则你需要用primaryjoin参数来指定连接条件，因为SQLAlchemy（简称SA）无法自动决定用哪个。
2. 确保在slorder表中确实定义了ForeignKey（就像代码示例中那样）。
3. 检查一下是否有某些表定义了非默认的schema，也许你需要在你的表中定义一个，比如_table_args__ = {'schema': 'my_schema'}（我只是猜测，因为我对firebird不太了解，所以不清楚那里的schema支持情况）。
4. 检查/调试反射步骤：sqlalchemy/dialects/firebird/base.py中有get_foreign_keys。检查SQL语句fkqry，并直接在你的数据库中执行它，看看是否能反映出你的ForeignKey。如果没有，试着找出原因。