SQLAlchemy Core - Add a Where condition to a preexisting select(...)

user_1391717 0
Viewed 110

I want to create a function that takes in a generic select, which assumes a particular column exists in the select, and then add a where condition on this column. Here is the setup:

from sqlalchemy import Table, Column, Integer, String, MetaData
from sqlalchemy import select

metadata = MetaData()

Foo = Table(
    'foos', metadata,
    Column('id', Integer),
    Column('foo', String),
    Column('status', String),
)

Bar = Table(
    'bars', metadata,
    Column('id', Integer),
    Column('bar', String),
    Column('status', String),
)

The following is along the lines of what I would like, however it results in a natural join:

def add_common_where_conditions(sel):
    return sel.where(sel.c.status == 'ACTIVE')

def get_foos():
    sel = select([Foo])
    sel = add_common_where_conditions(sel)
    print(sel)

def get_bars():
    sel = select([Bar])
    sel = add_common_where_conditions(sel)
    print(sel)

The output of get_foos() is:

SELECT foos.id, foos.foo, foos.status 
FROM foos, (SELECT foos.id AS id, foos.foo AS foo, foos.status AS status 
FROM foos) AS anon_1 
WHERE anon_1.status = :status_1

I'm not sure how I can get a reference to the original table. For example I tried this, but it only gets a reference to the subquery:

>>> sel = select([Foo])
>>> sel.c[0].table
<sqlalchemy.sql.selectable.Subquery at 0x1d360b7dba8; anon_1>
sqlalchemy python-3.x python
2 Answers

I think you want to use the select's froms attribute:

sel = select([Foo])
sel = sel.where(sel.froms[0].c.status == 'Active')
print(sel)

Produces

SELECT foos.id, foos.foo, foos.status 
FROM foos 
WHERE foos.status = :status_1
user_53209060

In SQLAlchemy 1.4 you can use the new selected_columns attribute, which the documentation says can be used for this very thing:

def add_common_where_conditions(sel):
    return sel.where(sel.selected_columns.status == 'ACTIVE')

sel = select([Foo])

print(add_common_where_conditions(sel))

This outputs the expected:

SELECT foos.id, foos.foo, foos.status 
FROM foos 
WHERE foos.status = :status_1

However, with this approach you cannot apply the where condition to columns that were not specified in the selected columns. For example, this would fail:

sel = select([Foo.c.id])
sel = sel.where(sel.selected_columns.foo = 'hello')
user_13917170
Related