Creating an ORM Table mapping on a view will generate a migration to create a table

[jack-michaud]

Related to the tail end of discussion here: https://github.com/olirice/alembic_utils/issues/14

If I had created a view and a model to interface with this view:

from alembic_utils.pg_view import PGView

from sqlalchemy import Column, Integer, String, select
from sqlalchemy.ext.declarative import declarative_base

Base = declarative_base()

class MyView(Base):
    __tablename__ = "my_view"
   id = Column(Integer)
   name = Column(String)

MyViewAlembic = PGView(
    schema="public",
    signature="my_view",
    definition="select id, name from user"
)

...I'm able to successfully query the view using the MyView class.

However, when I generate a new revision with alembic, it attempts to create a table from that view.

op.create_table('my_view',
    sa.Column('id', postgresql.Integer(), nullable=False),
    sa.Column('name', sa.String(), nullable=True),
)

The view is already created an managed through alembic_utils and attempting to create this table will claim that (psycopg2.errors.DuplicateTable) relation "my_view" already exists.

Is there a way to interface with the view through MyView while not needing to delete the create_table call in each migration?

[olirice]

Yeah, theres an entry for this in the alembic cookbook describing how to ignore tables

https://alembic.sqlalchemy.org/en/latest/cookbook.html#don-t-emit-create-table-statements-for-views

[jack-michaud]

For the life of me, I can't find how to add this info object to a declarative table. Do you have any insight @olirice?

[olirice]

sure

from sqlalchemy import Column, Integer, String, select
from sqlalchemy.ext.declarative import declarative_base

Base = declarative_base()

class MyView(Base):
    __tablename__ = "my_view"

    __table_args__ = {
        "info": {"is_view": True},
    }

    id = Column(Integer, primary_key=True)
    name = Column(String)

print(MyView.__table__.info) # {'is_view': True}

I also wasn't able to find it in their docs. Might be worth opening an issue

[jack-michaud]

Great, thank you. I had to customize the include_object function in the cookbook since PGView doesn't have an info attribute; my function looks like this and gets the desired result:

def include_object(object, name, type_, reflected, compare_to) -> bool:
    """
    Exclude views from Alembic's consideration (but includes alembic_utils' views)
    """
    if isinstance(object, ReplaceableEntity):
        return True
    return not object.info.get("is_view", False)

Thanks again for the help!