How to have a declarative model of a (MySQL) VIEW?

[cnicodeme]

Hi!

Using the declarative model, I have a class that represents a VIEW in my database.

The issue I'm having is that when I call Alembic to generate migration, it always tries to create me this table because it can't find it (since it doesn't exist, it's a view).

I tried to set the __abstract__ = True property, but when doing this, the MyModelClass.query is then not defined, and I need it to query the database.

Here's how I declare my Models:

class SelectWrapper(Select):
    inherit_cache = True

    async def execute(self):
        return await db.read.execute(self)

    async def first(self):
        return (await self.execute()).scalars().first()

    async def first_or_404(self, description: str = None):
        result = await self.first()
        if not result:
            abort(404, description)

        return result

    async def all(self):
        return (await self.execute()).scalars().all()

    async def count(self) -> int:
        return await select(func.count()).select_from(self.subquery()).first()


select = public_factory(SelectWrapper._create_future_select, ".future.select")

class DBMeta(DeclarativeMeta):
    @property
    def query(cls):
        return select(cls)

    async def execute(cls, stmt, **params):
        if isinstance(stmt, str):
            stmt = text(stmt)

        if str(stmt)[0:6].upper() == 'SELECT':
            return await db.read.execute(stmt, params)

        return await db.write.execute(stmt, params)


BaseModel = declarative_base(metaclass=DBMeta)


class ORModel(BaseModel):
    __abstract__ = True

    async def save(self):
            db.write.add(self)
            await db.write.flush()
            await db.write.refresh(self)

    async def delete(self):
        await db.write.delete(self)
        await db.write.commit()

And then my Model representing the View

class MyView(ORModel):
    __tablename__ = 'the_mysql_view'
    # __abstract__  = True  # this causes the cls.query to be empty!

    id = ...

I wonder if having __abstract__ twice in the hierarchy is not the cause of the problem or how I can set __abstract__ and still have query.

Thanks for your input!

[zzzeek]

I would not use __abstract__. In your alembic env.py , set up an include_object rule to skip Table objects that are actually views, such as by using a naming convention or custom flag inside of Table.info.

[cnicodeme]

Thank you, this seems interesting.

I wasn't able to add info to the table. The "info" parameter isn't caught by Alembic ... I searched on the doc but couldn't find how I can set a info property to my model as a class. How can I do ?
Thanks :)

[zzzeek]

in a declarative mapping you could use __table_args__:

class MyClass(Base):
    # ...
    __table_args__ = ((), {"info": {"is_view": True}})