Geek Answers Handbook

Adding a join to an Alchemy SQL statement that already has select_from ()

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

SQL Alchemy is great 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 will get an exception:

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

sqlalchemy.exc.NoForeignKeysError: cannot find any foreign key relationship between 'Select object' and 'bmt_gene_name'

If you specify columns, it creates a subquery (which is still incomplete SQL):

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 I really want:

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

edit: adding a JOIN should be after creating the original q expression. The idea is that I create a basic request skeleton, then iterate over all the connections requested by the user and add them to the request.

SQL Alchemy?

+4
3

(NoForeignKeysError) , . , :

from sqlalchemy.types import Integer
from sqlalchemy.schema import MetaData, Table, Column, ForeignKey

meta = MetaData()

bmt_gene_name = Table(
    'bmt_gene_name', meta,
    Column('id', Integer, primary_key=True),
    Column('gene_id', Integer, ForeignKey('bmt_gene.id')),
    # ...
)

SQLAlchemy , . Join, Select:

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)

, , , Select (, , ), .

+6

sqlalchemy.

, , :

from sqlalchemy.sql import select
from sqlalchemy.sql.expression import table

u = table('user', column('user_id'), column('name'), column('email_id'))
e = table('email', column('email_id'), column('email'))

# SELECT "user".user_id, "user".name, "user".email_id \nFROM "user"
q = select([u])
if get_email:
    # SELECT "user".user_id, "user".name, "user".email_id, email.email_id, email.email \nFROM "user", email \nWHERE "user".email_id = email.email_id
    q = q.column(e).where(u.c.email_id == e.c.email_id)

, FROM ON WHERE.

0

select_from froms, select_from.

, select_from select FROM:

Join, , FROM, JOIN.

, :

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

More articles:


All Articles