多用户关系上的SQLalchemy连接

Question

当我试图使用SQLAlchemy加入一对多关系时，我完全被卡住了。 我的模型是这样的：

class Protein(Base):
    __tablename__ = 'protein'

    protein_id = Column(Integer, primary_key=True)
    gene_name = Column(String(45))

    spectrum_hit_spectrum_hits = relationship(u'SpectrumHit', secondary='spectrum_protein_map')


class SpectrumHit(Base):
    __tablename__ = 'spectrum_hit'

    spectrum_hit_id = Column(Integer, primary_key=True)    
    sequence = Column(String(60, u'latin1_german1_ci'), index=True)

以及映射表：

^{pr2}$

我的疑问是：

query = DBSession.query(Protein.gene_name, SpectrumHit.sequence)
        query = query.join(SpectrumHit)
        result = query.all()

我也试过另一种方法

query = DBSession.query(SpectrumHit.sequence, Protein.gene_name)
        query = query.join(Protein)
        result = query.all()

如果有帮助，我也可以添加MySQL表。在

我总是得到一个错误：

InvalidRequestError: Could not find a FROM clause to join from.  Tried joining to <class 'ligando.models.Protein'>, but got: Can't find any foreign key relationships between 'spectrum_hit' and 'protein'.

这就解释了。。。在

如果我尝试在纯MySQL中执行此查询，则如下所示：

SELECT spectrum_hit.sequence, protein.gene_name
From spectrum_hit
join spectrum_protein_map on spectrum_hit.spectrum_hit_id = spectrum_protein_map.spectrum_hit_spectrum_hit_id
join protein on protein.protein_id = spectrum_protein_map.protein_protein_id

它确实有效

Answer 1

我不知道您的metadata对象来自何处，但是如果您将映射表代码更改为使用Base.metadata，那么您的代码将起作用：

from sqlalchemy.engine import create_engine
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.orm import relationship
from sqlalchemy.orm.session import sessionmaker
from sqlalchemy.sql.schema import Column, Table, ForeignKey
from sqlalchemy.sql.sqltypes import Integer, String

Base = declarative_base()

class Protein(Base):
    __tablename__ = 'protein'

    id = Column(Integer, primary_key=True)
    gene_name = Column(String(45))

    spectrum_hit_spectrum_hits = relationship('SpectrumHit', secondary='spectrum_protein_map')


class SpectrumHit(Base):
    __tablename__ = 'spectrum_hit'

    id = Column(Integer, primary_key=True)
    sequence = Column(String(60, u'latin1_german1_ci'), index=True)



spectrum_protein_map = Table(
    'spectrum_protein_map', Base.metadata,
    Column('spectrum_hit_id', ForeignKey('spectrum_hit.id')),
    Column('protein_id', ForeignKey('protein.id'))
)

eng = create_engine("mysql://<your connection string>", echo=False, pool_recycle=1800)
Base.metadata.create_all(eng)
session_maker = sessionmaker(bind=eng, autocommit=False,autoflush=False)
session = session_maker()

res = session.query(Protein.gene_name, SpectrumHit.sequence).join(SpectrumHit).query.all()