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

问题描述

也许我的previous问题是太多长期和无休止的回答，对不起...我会尝试更具体缩短我的previous问题

我可以从API查询（JSON格式作为输出）以下信息中提取

基因1

  Experiment1
Experiment2
Experiment3
Experiment4
 

基因2

  Experiment5
Experiment2
Experiment3
Experiment8
Experiment9
 

  

[...]

因此​​，我获得其中它们已被研究基因及其相关实验...一个基因可以有一个以上的试验，和1实验可以有一个以上的基因（多对多）

我有这种模式在SQL炼金：

 从SQLAlchem​​y的进口create_engine，列，整数，字符串，日期，ForeignKey的，表，浮动
从sqlalchem​​y.orm进口sessionmaker，关系，backref
从sqlalchem​​y.ext.declarative进口declarative_base
进口要求基地= declarative_base（）Genes2experiments =表（'genes2experiments'，Base.metadata，
  列（'gene_id，弦乐，ForeignKey的（'genes.id'））
  列（'EXPERIMENT_ID，弦乐，ForeignKey的（'experiments.id'））
）类基因（基本）：
    __tablename__ ='基因'
    ID =柱（字符串（45），primary_key =真）
    实验=关系（实验，次要= Genes2experiments，backref =基因）
    高清__init __（自我，ID =）：
        self.id = ID
    高清__repr __（个体经营）：
        回归＆LT;基因（ID：'％s'的）GT; ％（self.id）实验班（基础）：
    __tablename__ ='实验'
    ID =柱（字符串（45），primary_key =真）
    高清__init __（自我，ID =）：
        self.id = ID
    高清__repr __（个体经营）：
        回归＆LT;实验（ID：'％s'的）GT; ％（self.id）DEF设置（）：
    全球会议
    发动机= create_engine（'的mysql：//根：密码@本地/ DB_NAME的charset = UTF8'，pool_recycle = 3600，回声= FALSE）
    会话= sessionmaker（绑定=引擎）高清add_data工具（）：
    会话=会话（）
    在范围（0,1000,200）我：
        请求= requests.get('http://www.ebi.ac.uk/gxa/api/v1',params={\"updownInOrganism_part\":\"brain\",\"rows\":200,\"start\":i})
        结果= request.json
        在结果['结果']项目：
            gene_to_add =项目['基因'] ['ensemblGeneId']
    session.commit（）
    session.close（）
建立（）
add_data工具（）
 

有了这个code我刚刚从API查询添加到我的数据库中的所有基因的基因表...

1的问题：如何以及何时我应该补充的实验信息，以保持他们的关系好歹？

第二个问题：我要补充的实验类新的辅助关系，如基因类，或者是它不够投入只是一个

？

感谢您

（更多的上下文/信息：<一href=\"http://stackoverflow.com/questions/15899565/how-to-insert-relational-data-many-to-many-in-sql-alchemy-by-means-of-api-quer\">my previous问题）

解决方案

1. 当你记录实验的结果，甚至当你的计划的一个实验，你已经可以添加到数据库以及实例和关系。




2. 有 backref 将有效补充的关系的另一面，使得具有实验实例，您可以通过 my_experiment.genes

的注：类基因，类实验而不是类基因，一流的实验：我想从你的实体的名称中删除复数 的

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.

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