1. Michael Bayer
  2. sqlalchemy
  3. Issues

Issues

Issue #812 new

Views as schema objects that can be created and dropped

Anonymous created an issue

I'd like to ask for ability to define views as schema items, and that they be included in metadata.create_all and drop_all.

So i could do something like this:

v = View(name, metadata, selectable) or
v = View(name, metadata, autoload=True). and then use v as any other selectable.

I think what's needed would be a visit_view in DDLSchemaGenerator and dropper, but i'm not proficient enough at SQLAlchemy internals to implement this myself.

Comments (7)

  1. Michael Bayer repo owner

    autoload=True implies reflection support for views as well. This ticket would be a significant undertaking from an implementation and testing point of view, particularly for reflection. table reflection takes up a huge amount of support time as it is, for a feature which is essentially an unnecessary "convenience" feature... so I'm hesitant to add more support burden in that department. its not trivial to add it on the create side either since the View object would represent a new addition to the schema package along the lines of complexity of Table itself; a lot of tests would be needed for to support a new schema object at that level.

    DDL/reflection support of views has historically been out of SA's scope, since we are not a DDL management tool and views are not a supported feature on many of the databases we support.

    the only key problem we have right now with views is that defining a Table with a view name means create_all() will attempt to create that view as a table, so i think a simple flag to table "creatable=False" should probably be added to address that use case.

  2. tyler barrus

    I am using the view recipe in postgres with an assigned schema. It seems that the recipe does not take into account the schema information. The view was created in the public schema and I have not been able to figure out how to get it to read and utilize the metadata.schema information. Any help would be much appreciated.

    This is on SQLAlchemy 1.0.13

  3. Michael Bayer repo owner

    it doesn't, but this should be an easy add, stick the "schema" you care about like this:

    def view(name, metadata, selectable):
        t = table(name)
    
        for c in selectable.c:
            c._make_proxy(t)
    
        CreateView(name, metadata.schema, selectable).execute_at('after-create', metadata)
        DropView(name, metadata.schema).execute_at('before-drop', metadata)
        return t
    

    then add a "schema" argument to CreateView and DropView and use it in the Mark Espinoza recipe.

  4. tyler barrus

    Thank you for your help! I added it into my local version of the recipe. Thank you for pointing me in the right direction!

    Will the view recipe be added directly to SQLAlchemy in the future?

  5. Log in to comment