回答此问题可获得 20 贡献值,回答如果被采纳可获得 50 分。
<p>我试图在现有数据库上设置一对多关系。在</p>
<p>简化的DDL为:</p>
<pre><code>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;
</code></pre>
<p>SqlAlchemy代码:</p>
^{pr2}$
<p>给予</p>
<pre><code>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.
</code></pre>
<p>错误。在</p>
<p>我对这个问题的可能解决办法是:</p>
<p><strong>1</strong></p>
<pre><code>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')
</code></pre>
<p>但是这种方法需要我手动添加每一个外键约束列,这将导致反射可用。(因为我有很多列引用了其他表。)</p>
<p><strong>2</strong></p>
<pre><code>class SlOrder(Base):
__table__ = Table('accnt', metadata, autoload = True, autoload_with=engine)
accnt = relationship('Accnt', backref='slorders', primaryjoin=(__table__.c_accnt==Accnt.code))
</code></pre>
<p>这种方法还有另一个后果(请参见<a href="https://stackoverflow.com/questions/7679893/how-to-override-a-column-name-in-sqlalchemy-using-reflection-and-descriptive-synt">my previous question</a>)</p>
<p>那我还缺什么呢?使用反射和声明性语法定义关系的最佳方法是什么?在</p>
<p>编辑:</p>
<p>我认为,如果子表只有一个对父表的引用,SqlAlchemy会查找并构建关系。在</p>
<p>但如果子表有多个引用,则为:</p>
<pre><code>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
);
</code></pre>
<p>出现上述错误。在</p>
<p>那么,如果两个表之间存在多个关系,SqlAlchemy的预期行为是否会出错?在</p>