Query a PostgreSQL view created by SQLAcodegen

Question

I have a useful PostgreSQL view, generated by a JOIN query:

\d very_useful_view;
 View "public.very_useful_view"
   Column    |    Type     | Modifiers
-------------+-------------+-----------
 tree_id     | dom_treeid  |
 title       | text        |
 grass_id    | dom_grass   |
 name        | text        |
 street_uuid | uuid        |

The view was modeled by SQLAcodegen to the following object:

t_very_useful_view = Table(
    'very_useful_view', metadata,
    Column('tree_id', String),
    Column('title', Text),
    Column('grass_id', Text),
    Column('name', Text),
    Column('street_uuid', UUID)
)

Normal query works as expected:

print session.query(t_very_useful_view).all()

However, filtering -

print session.query(t_very_useful_view).\
    filter(t_very_useful_view.tree_id == some_tree_id).\
    all()

Raises:

AttributeError: 'Table' object has no attribute 'tree_id'

Any idea what am I missing with the view mapping?

Answer 1

The columns of a Table object should be accessed with the columns property:

print session.query(t_very_useful_view).\
    filter(t_very_useful_view.columns.tree_id == some_tree_id).\
    all()

This contrasts with subclasses of declarative_base(), which expose columns as root-level object properties.