How to generate view columns in mapped class with async postgresql

[dcs3spp]

Hi,

I am using alembic to apply a migration for a postgresql view using an async engine.

I can see this successfully applied in the database.

I have the following declarative mapped class to  the view, defined as:

class MailingListView(Base): 
    """View for mailing labels.
    After metata reflection from db -> model expecting columns to
    be available on this class. 
    """

    __tablename__ = "mailing_list_view"

    # Specify the column override from the underlying view that is the primary key
    id = Column(UUID(as_uuid=True), primary_key=True)
    

    # Expecting  these columns  below to be mapped in this class after
    # metadata reflection. Currently have to uncomment these
    # to manually synchronise with view!
    #
    # addressee = Column(String)
    # street = Column(String)
    # town = Column(String)
    # county = Column(String)
    # postcode = Column(String)
    # cursor = Column(String)

I am reflecting the views using the following:

      def use_inspector(conn):
            inspector = inspect(conn)
            return inspector.get_view_names()

        views = await connection.run_sync(use_inspector)

        # I can see the table columns in __table__.c.keys()
        # after the reflection below has run

        await connection.run_sync(
            target_metadata.reflect,
            only=views,
            views=True,
            extend_existing=True,
        )

After performing the above reflection I can see that my mapped model has the underlyingtable columns updated with those defined in the underlying view.

obj = MailingListView()
obj.__table__.c.keys()

However, the properties of my mapped class are not updated after reflection, raising an exception:

obj = MailingListView()
obj.town = "town" # this raises an exception with unavailable property

How is it possible for a postgresql db (asyncpg) + async sqlalchemy to:

Synchronise the columns of a declarative model with its underlying table after metadata reflection. Currently, I have to manually specify the columns in the declarative model.

[Michael Bayer]

the async aspect here is not that important, for Declarative + reflection you likely should look at DeferredReflection: https://docs.sqlalchemy.org/en/14/orm/declarative_tables.html#using-deferredreflection

for async, where it has in the example:

Reflected.prepare(engine)

you would instead say:

await async_connection.run_sync(Reflected.prepare)