使用 Python 中的 API 查询在 SQL Alchemy 中插入关系数据 [英] Insert relational data in SQL Alchemy with API queries in Python

问题描述

也许我之前的问题太长而且没办法回答，抱歉……我会尽量缩短我之前的问题的具体时间

我可以从 API 查询(json 格式作为输出)中提取以下信息:

GENE1

Experiment1实验2实验3实验4

基因2

Experiment5实验2实验3实验8实验9

<块引用>

[...]

所以我得到了基因及其研究的相关实验...一个基因可以有多个实验，1个实验可以有多个基因(多对多)

我在 SQL Alchemy 中有这个架构:

from sqlalchemy import create_engine, Column, Integer, String, Date, ForeignKey, Table, Float从 sqlalchemy.orm 导入 sessionmaker、关系、backref从 sqlalchemy.ext.declarative 导入 declarative_base进口请求Base = declarative_base()Genes2experiments = Table('genes2experiments',Base.metadata,Column('gene_id', String, ForeignKey('genes.id')),列('experiment_id', String, ForeignKey('experiments.id')))类基因(基础):__tablename__ = '基因'id = 列(字符串(45)，primary_key=True)实验 = 关系(实验"，secondary=Genes2experiments，backref =基因")def __init__(self, id=""):self.id=iddef __repr__(self):return "<genes(id:'%s')>"% (self.id)课堂实验(基础):__tablename__ = '实验'id = 列(字符串(45)，primary_key=True)def __init__(self, id=""):self.id=iddef __repr__(self):return ""% (self.id)定义设置():全球会议engine=create_engine('mysql://root:password@localhost/db_name?charset=utf8', pool_recycle=3600,echo=False)会话=会话制造者(绑定=引擎)def add_data():会话=会话()对于 i 在范围内(0,1000,200):request= requests.get('http://www.ebi.ac.uk/gxa/api/v1',params={"updownInOrganism_part":"brain","rows":200,"start":i})结果 = request.json对于结果中的项目['结果']:gene_to_add = item['gene']['ensemblGeneId']session.commit()session.close()设置()添加数据()

使用此代码，我只需将 API 查询中的所有基因添加到我的数据库中，并添加到基因表中...

第一个问题:我应该如何以及何时添加实验信息以保持它们之间的关系???

第二个问题:我应该在实验类中添加一个新的次要关系，就像在基因类中一样，还是只添加一个就足够了?

谢谢

(更多上下文/信息:我之前的问题)

解决方案

1. 每当您记录实验结果时，甚至在您计划实验时，您都已经可以将实例添加到数据库和关系中.

   立>

2. 拥有 backref 将有效地添加关系的另一面，因此拥有 Experiments 的实例，您可以通过 my_experiment.genes

注意:我会从实体名称中删除复数:class Gene, class Experiment 而不是 class Genes, class Experiments.

Maybe my previous question was too much long and endless to answer, sorry for that... I will try to be more specific shortening my previous question

I can extract from an API query (json format as output) the following information:

GENE1

Experiment1     
Experiment2     
Experiment3     
Experiment4     

GENE2

Experiment5     
Experiment2     
Experiment3     
Experiment8     
Experiment9     

[...]

So I obtain genes and their related experiments in which they have been studied... One gene can have more than one experiment, and 1 experiment can have more than one gene (many to many)

I have this schema in SQL Alchemy:

from sqlalchemy import create_engine, Column, Integer, String, Date, ForeignKey, Table, Float
from sqlalchemy.orm import sessionmaker, relationship, backref
from sqlalchemy.ext.declarative import declarative_base
import requests

Base = declarative_base()

Genes2experiments = Table('genes2experiments',Base.metadata,                                                                                                                                                                                                     
  Column('gene_id', String, ForeignKey('genes.id')),                                                                                                                                                                                                       
  Column('experiment_id', String, ForeignKey('experiments.id'))
)

class Genes(Base):
    __tablename__ = 'genes'
    id = Column(String(45), primary_key=True)
    experiments = relationship("Experiments", secondary=Genes2experiments, backref="genes")
    def __init__(self, id=""):
        self.id= id
    def __repr__(self):
        return "<genes(id:'%s')>" % (self.id)

class Experiments(Base):
    __tablename__ = 'experiments'
    id = Column(String(45), primary_key=True)
    def __init__(self, id=""):
        self.id= id
    def __repr__(self):
        return "<experiments(id:'%s')>" % (self.id)

def setUp():
    global Session
    engine=create_engine('mysql://root:password@localhost/db_name?charset=utf8', pool_recycle=3600,echo=False)
    Session=sessionmaker(bind=engine)

def add_data():   
    session=Session()
    for i in range(0,1000,200):
        request= requests.get('http://www.ebi.ac.uk/gxa/api/v1',params={"updownInOrganism_part":"brain","rows":200,"start":i})
        result = request.json
        for item in result['results']:
            gene_to_add = item['gene']['ensemblGeneId']            
    session.commit()
    session.close()       


setUp()
add_data()

With this code I just add to my database all the genes from the API query to the Genes table...

1st question: how and when should I add the experiments information to keep their relationship someway???

2nd question: should I add a new secondary relationship in the Experiments class, as in the Genes class, or is it enough putting just one?

Thank you

(for more context/info: my previous question)

解决方案

1. Whenever you records the results of an experiment, or even when you plan an experiment, you can already add instances to the database and the relationships as well.

2. having backref will effectively add the other side of the relationship, so that having an instance of Experiments, you can get the Genes[] via my_experiment.genes

Note: I would remove plural from the names of your entities: class Gene, class Experiment instead of class Genes, class Experiments.

这篇关于使用 Python 中的 API 查询在 SQL Alchemy 中插入关系数据的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！