将关系插入到一个表中，该表使用 SQLALchemy 连接 3 个具有多对多关系的表 - python

2022-01-09 00:00:00 python sqlalchemy many-to-many insert foreign-key-relationship

问题描述

您可以在下面看到我的数据库中的一些表是如何关联的.

Below you can see how some tables of my database are related.

如您所见，中间的表格是连接 3 个具有多对多关系的表格...

As you can see the table in the middle is connecting 3 tables with many-to-many relationships...

  1. 一个基因可以在多个器官中表达并在多个实验中进行研究
  2. 在一个器官中，可以表达多个基因，并且可以在多个实验中研究一个器官.
  3. 在一项实验中，可以研究多个基因和器官

我正在使用 SQLAlchemy 插入数据.我知道如何与连接 2 个表的表添加多对多关系，我或多或少是这样做的:

I am using SQLAlchemy to insert data. I know how to add a many-to-many relationships with a table that is connecting 2 tables, and more or less I do it like this:

def add_data():
    session=Session()
    gene = "BRCA2"
    gene_to_add = Gene(gene_name = gene)
    session.add(gene_to_add)

    experiment = "experiment1"
    experiment_to_add = Experiment(experimentAccession = experiment)
    gene_to_add.experiment_rel.append(experiment_to_add)

    organ = "brain"
    organ_to_add = Organ(organName = organ)

    session.commit()
    session.close()

但我不知道如何添加新关系(本例中为器官表).我尝试使用扩展而不是附加，但它不起作用...

But I don't know how to add a new relationship (organs table in this case). I tried with extend instead of append, but it doesn't works...

有谁知道如何解决这种情况?也许数据库的结构应该改变......任何帮助将不胜感激

Does anyone know how to solve this kind of situation? Maybe the structure of the database should change... any help would be much appreciated

解决方案

您始终可以使用 关联对象:

class Genes2Experiments2Organs(Base):
    __tablename__ = 'genes2experiments2organs'
    gene_id = Column(Integer, ForeignKey('genes.id'), primary_key=True)
    experiment_id = Column(Integer, ForeignKey('experiments.id'), primary_key=True)
    organ_id = Column(Integer, ForeignKey('organs.id'), primary_key=True)
    # relationships
    gene = relationship("Gene", backref="map") 
    experiment = relationship("Experiment", backref="map") 
    organ = relationship("Organ", backref="map") 


class Gene(Base):
    __tablename__ = 'genes'
    id = Column(Integer, primary_key=True)
    gene_name = Column(String)

class Experiment(Base):
    __tablename__ = 'experiments'
    id = Column(Integer, primary_key=True)
    experimentAccession = Column(String)

class Organ(Base):
    __tablename__ = 'organs'
    id = Column(Integer, primary_key=True)
    organName = Column(String)

###########################################
def add_data():
    session = Session()
    gene = "BRCA2"
    gene_to_add = Gene(gene_name = gene)
    #session.add(gene_to_add)

    experiment = "experiment1"
    experiment_to_add = Experiment(experimentAccession = experiment)
    #session.add(experiment_to_add)

    organ = "brain"
    organ_to_add = Organ(organName = organ)
    #session.add(organ_to_add)

    #gene_to_add.experiment_rel.append(experiment_to_add)
    assoc_obj_to_add = Genes2Experiments2Organs(
        gene = gene_to_add,
        experiment = experiment_to_add,
        organ = organ_to_add,
        )

    session.add(assoc_obj_to_add)

    session.commit()
    session.close()

add_data()

相关文章