Note: this is a question about SQL Alchemy's expression language not the ORM

SQL Alchemy is fine for adding WHERE or HAVING clauses to an existing query:

q = select([bmt_gene.c.id]).select_from(bmt_gene)
q = q.where(bmt_gene.c.ensembl_id == "ENSG00000000457")
print q

SELECT bmt_gene.id 
FROM bmt_gene 
WHERE bmt_gene.ensembl_id = %s

However if you try to add a JOIN in the same way you'll get an exception:

q = select([bmt_gene.c.id]).select_from(bmt_gene)
q = q.join(bmt_gene_name)

sqlalchemy.exc.NoForeignKeysError: Can't find any foreign key relationships between 'Select object' and 'bmt_gene_name'

If you specify the columns it creates a subquery (which is incomplete SQL anyway):

q = select([bmt_gene.c.id]).select_from(bmt_gene)
q = q.join(bmt_gene_name, q.c.id == bmt_gene_name.c.gene_id)

(SELECT bmt_gene.id AS id FROM bmt_gene)
JOIN bmt_gene_name ON id = bmt_gene_name.gene_id

But what I actually want is this:

SELECTbmt_gene.id AS id 
FROMbmt_geneJOIN bmt_gene_name ON id = bmt_gene_name.gene_id

edit: Adding the JOIN has to be after the creation of the initial query expression q. The idea is that I make a basic query skeleton then I iterate over all the joins requested by the user and add them to the query.

Can this be done in SQL Alchemy?

The first error (NoForeignKeysError) means that your table lacks foreign key definition. Fix this if you don't want to write join clauses by hand:

from sqlalchemy.types import Integer
from sqlalchemy.schema import MetaData, Table, Column, ForeignKeymeta = MetaData()bmt_gene_name = Table('bmt_gene_name', meta,Column('id', Integer, primary_key=True),Column('gene_id', Integer, ForeignKey('bmt_gene.id')),# ...
)

The joins in SQLAlchemy expression language work a little bit different from what you expect. You need to create Join object where you join all the tables and only then provide it to Select object:

q = select([bmt_gene.c.id])
q = q.where(bmt_gene.c.ensembl_id == 'ENSG00000000457')j = bmt_gene  # Initial table to join.
table_list = [bmt_gene_name, some_other_table, ...]
for table in table_list:j = j.join(table)
q = q.select_from(j)

The reason why you see the subquery in your join is that Select object is treated like a table (which essentially it is) which you asked to join to another table.