sql - sqlalchemy: select specific columns from multiple join using aliases -

this has stumped more day , examples find have not worked. new sqlalchemy , find documentation not enlightening.

the query (so far):

prey = alias(ensembl_genes, name='prey') bait = alias(ensembl_genes, name='bait') query = db.session.query(tap,prey,bait).\     join(prey, tap.c.tap_prey_ensembl_gene_id==prey.c.ensembl_gene_id).\     join(bait, tap.c.tap_bait_ensembl_gene_id==bait.c.ensembl_gene_id).\     filter(\       or_(\         tap.c.tap_prey_ensembl_gene_id=='ensg00000100360',\         tap.c.tap_bait_ensembl_gene_id=='ensg00000100360'\       )\     ).\     order_by(desc(tap.c.tap_unique_peptide_count)) 

tap refers table of interacting genes. 1 interactor designated 'bait' , other 'prey'. prey , bait aliases same table holds additional information on these genes. objective select interactions given gene 'ensg00000100360' either bait or prey.

the problem:

this query returns 20 or columns, need 6 specific ones, 2 each original tables (i'd rename them well). examples found on interwebz thought should add:

  options(       load(tap).load_only('tap_unique_peptide_count','tap_sequence_coverage'),       load(prey).load_only('ensembl_gene_symbol','ensembl_gene_id'),       load(bait).load_only('ensembl_gene_symbol','ensembl_gene_id')     ) 

but gives me following error:

file "/users/jvandam/github/syscilia/tools/bdt/quest/blueprints/genereport.py", line 246, in createtapmsview load(tap).load_only('tap_unique_peptide_count','tap_sequence_coverage') file "/opt/local/library/frameworks/python.framework/versions/2.7/lib/python2.7/site-packages/sqlalchemy/orm/strategy_options.py", line 82, in init self.path = insp._path_registry attributeerror: 'table' object has no attribute '_path_registry'

i have not been able find on google about this. sqlalchemy table objects created database table metadata.

what trying emulate using sqlalchemy orm statements is:

select prey.ensembl_gene_symbol prey_ensembl_gene_symbol, prey.ensembl_gene_id prey_ensembl_gene_id, bait.ensembl_gene_symbol bait_ensembl_gene_symbol, bait.ensembl_gene_id bait_ensembl_gene_id, t.tap_unique_peptide_count unique_peptide_count, t.tap_sequence_coverage sequence_coverage tap t inner join ensembl_genes prey   on tap.tap_prey_ensembl_gene_id=prey.ensembl_gene_id inner join ensembl_genes bait   on t.tap_bait_ensembl_gene_id=bait.ensembl_gene_id   t.tap_prey_ensembl_gene_id='ensg00000100360'    or t.tap_bait_ensembl_gene_id='ensg00000100360' order t.tap_unique_peptide_count desc 

can me fix query? in advance! john

just change part db.session.query(tap,prey,bait).\ below:

db.session.query(\     prey.ensembl_gene_symbol.label("prey_ensembl_gene_symbol"),     prey.ensembl_gene_id.label("prey_ensembl_gene_id"),     bait.ensembl_gene_symbol.label("bait_ensembl_gene_symbol"),     bait.ensembl_gene_id.label("bait_ensembl_gene_id"),     tap.tap_unique_peptide_count.label("unique_peptide_count"),     tap.tap_sequence_coverage.label("sequence_coverage"), ).\ select_from(tap).\  # @note: need in , joins in desired order 

this select columns need.