Accessing relational data as (Python) objects

from sqlalchemy import and_
from seabass_model import *


session = get_session('postgres:///seabass_db')
species = session.query(Species).filter(
                          Species.common_name == 'seabass').one()
query = (session.query(Protein.name, 
                       GoTerm.name, 
                       GoTerm.namespace
                      ).join(Protein.goterms).
                        filter(Protein.species == species).limit(10))
      

A Flask web application

@app.route('/search', methods=['GET', 'POST'])
@dbsession
def search(session):
    try:
        if request.method == 'POST':
            search_term = request.form['search_term']
            genes = session.query(Gene).filter(
                and_(Gene.name.like("%" + search_term + "%"), Gene.species_id == 16)).all()
            proteins = dict()
            transcripts = None
            for gene in genes:
                transcripts = session.query(Transcript).filter(Transcript.gene_id == gene.id).all()
                for transcript in transcripts:
                    protein = session.query(Protein).filter(Protein.transcript_id == transcript.id).one()
                    proteins[gene.id] = protein
    except NoResultFound:
        return abort(404)
    else:
        return render_template('search_results.html', genes=genes, transcripts=transcripts, proteins=proteins)