Insert relational data in SQL Alchemy with API queries in Python

Question

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)

van · Accepted Answer · 2013-04-09 20:09:17Z

3

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.
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.

answered Apr 9, 2013 at 20:09

van

77.6k13 gold badges174 silver badges179 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Insert relational data in SQL Alchemy with API queries in Python

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related