How to drop a view which is represented by Table instance?

[Stefan Urbanek]

Hi,

Problem: My code generates a table or a view, based on user's preference. Table("something") accepts both table and view name and works properly for most of the tasks, including Table.exists(). When I use Table.delete() (postgresql) it works only on tables, when I try to use it on a view I get:

sqlalchemy.exc.ProgrammingError: (ProgrammingError) "contracts_denorm" is not a table

HINT:  Use DROP VIEW to remove a view.

This seems to be a little bit inconsistent or maybe I am missing something here and not doing it right. How to properly drop a view using SQLAlchemy? As for workaround, is there a way how I can at least determine whether the Table object is a view and issue manual DROP VIEW statement by myself?

Stefan Urbanek

[Michael Bayer]

there's a recipe for views, including DDL,  here:

http://www.sqlalchemy.org/trac/wiki/UsageRecipes/Views

another way to go would be to alter the compilation of sqlalchemy.schema.DropTable which would somehow have to check those Table objects you have which are actually views, maybe if you put a flag on them table.isview=True or something like that.

--
You received this message because you are subscribed to the Google Groups "sqlalchemy" group.
To view this discussion on the web visit https://groups.google.com/d/msg/sqlalchemy/-/0aZj0MOWgagJ.
To post to this group, send email to sqlal...@googlegroups.com.
To unsubscribe from this group, send email to sqlalchemy+...@googlegroups.com.
For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en.

[Stefan Urbanek]

Thanks, this is partially helpful. However, in the example there is:

stuff_view = view("stuff_view"...

and not:

stuff_view = Table("stuff_view"...

My problem is, that I get just a name and expect TABLE or VIEW and I am replacing TABLE or VIEW with TABLE or VIEW. To be able to replace something, I need to drop the something. To be able to drop the something, I need to know how to drop it, therefore I need to know whether it is a table (DROP TABLE) or a view (DROP VIEW). All I have is just a name. I was expecting Table() to know whether it is table or a view. I have no problems with using the solution in the wiki, however I need to know a way, how to determine whether the something named "stuff_view" is real view or a table (primitive straightforward materialization of a view).

Is there a way how to know whether the Table() object represents view or table? Something like my_table.is_view?

Stefan

To unsubscribe from this group, send email to sqlalchemy+unsubscribe@googlegroups.com.

[Michael Bayer]

On May 7, 2012, at 2:03 PM, Stefan Urbanek wrote:

Thanks, this is partially helpful. However, in the example there is:

stuff_view = view("stuff_view"...

and not:

stuff_view = Table("stuff_view"...

My problem is, that I get just a name and expect TABLE or VIEW and I am replacing TABLE or VIEW with TABLE or VIEW. To be able to replace something, I need to drop the something. To be able to drop the something, I need to know how to drop it, therefore I need to know whether it is a table (DROP TABLE) or a view (DROP VIEW). All I have is just a name.

normally, whenever you got the name "stuff_view", it would be defined at that moment if that name represents a table or view.

is this reflection you're talking about ?    yeah I was never a huge fan of how view reflection got implemented.   you'd need run inspector.get_view_names() for now to get those names that represent views:

from sqlalchemy.engine.reflection import Inspector

view_names = Inspector.from_engine(someengine).get_view_names()

[Stefan Urbanek]

My fault: Yes, I apologize for not mentioning that. I was thinking about reflection.

Thank you, the inspector (from description and docs) seems to be sufficient for the time being. I will try that.

Stefan

p.s.: On the other hand, Table.is_view might be a good flag - to maintain consistency with the fact that Table() can reflect a view. However, I am not sure how does that fit into overall design of the library.

[Michael Bayer]

On May 7, 2012, at 6:09 PM, Stefan Urbanek wrote:

p.s.: On the other hand, Table.is_view might be a good flag - to maintain consistency with the fact that Table() can reflect a view. However, I am not sure how does that fit into overall design of the library.

the reason is that it's a partial, broken API:

t = Table("myview", metadata, 

Column(...),

                Column(...),

                # ...

is_view=True

)

metadata.create_all()

...to which we get, what exactly ?    a view is not just a bunch of columns, you need the whole view definition.

similarly:

mytable = Table("someview", metadata, autoload=True)

assert mytable.is_view

mytable_2 = mytable.tometadata(othermetadata)

othermetadata.create_all()

-> same thing ! how do we do a CREATE ?   is the whole view definition present ?  do we have round trip tests for all that ?  can I reflect a view from Oracle and recreate on SQLite ?    the answer is: absolutely not.  It would require that we can fully parse SQL, intelligently enough even to translate it into another platform, which is not just out of scope, it would be a vastly complicated process.

Hence the whole thing stays as a recipe, and also why you're finding it awkward that you'd like to emit DROP for views that you've reflected; the "view reflection" use case was at most intended as a read only use case.

I'm not 100% sure how "view reflection" even got into the library, to be honest, as it really doesn't belong in Table.  a Table is not a view.

[Stefan Urbanek]

On 8.5.2012, at 0:18, Michael Bayer wrote:

On May 7, 2012, at 6:09 PM, Stefan Urbanek wrote:

p.s.: On the other hand, Table.is_view might be a good flag - to maintain consistency with the fact that Table() can reflect a view. However, I am not sure how does that fit into overall design of the library.

the reason is that it's a partial, broken API:

t = Table("myview", metadata, 

Column(...),

                Column(...),

                # ...

is_view=True

)

metadata.create_all()

...to which we get, what exactly ?    a view is not just a bunch of columns, you need the whole view definition.

similarly:

mytable = Table("someview", metadata, autoload=True)

assert mytable.is_view

mytable_2 = mytable.tometadata(othermetadata)

othermetadata.create_all()

-> same thing ! how do we do a CREATE ?   is the whole view definition present ?  do we have round trip tests for all that ?  can I reflect a view from Oracle and recreate on SQLite ?    the answer is: absolutely not.  It would require that we can fully parse SQL, intelligently enough even to translate it into another platform, which is not just out of scope, it would be a vastly complicated process.

You are right, I agree with that. That would create more confusion and even more inconsistencies.

Hence the whole thing stays as a recipe, and also why you're finding it awkward that you'd like to emit DROP for views that you've reflected; the "view reflection" use case was at most intended as a read only use case.

Creating views (and therefore dropping them as well) should not belong to Table and I see no problem with the way described in the recipe. The read-only flag might be really helpful though - once the table/view is reflected, it might be known what the source object type was (if it is possible to get it from DB engine/backend).

Btw. this what I did finally and seems to work:

# we already have: view_name, metadata, schema, statement (selectable)

table = sqlalchemy.Table(view_name, metadata,

                         autoload=False, schema=schema)

full_name = "%s.%s" % (schema, view_name) if schema else view_name

if table.exists():

    inspector = sqlalchemy.engine.reflection.Inspector.from_engine(engine)

    view_names = inspector.get_view_names(schema=schema)

    if view_name in view_names:

        # Table reflects a view

        drop_statement = "DROP VIEW %s" % full_name

        engine.execute(drop_statement)

    else:

        # Table reflects a table

        table.drop(checkfirst=False)

if materialize:

    create_stat = "CREATE TABLE"

else:

    create_stat = "CREATE OR REPLACE VIEW"

statement = "%s %s AS %s" % (create_stat, full_name, str(statement))

engine.execute(statement)

I'm not 100% sure how "view reflection" even got into the library, to be honest, as it really doesn't belong in Table.  a Table is not a view.

Table is not a view, right. On the other hand, views are quite useful and in analytical domain they are being used quite a lot. The analytical data are mostly read-only during their use, therefore I see no problem with treating VIEWs as read-only TABLEs in this case. I would definitely not drop view reflection from the library, as it is makes building another kinds of abstractions/tools (besides ORM) much more easier.

Stefan

[Michael Bayer]

On May 7, 2012, at 7:06 PM, Stefan Urbanek wrote:

>
>
>
>> I'm not 100% sure how "view reflection" even got into the library, to be honest, as it really doesn't belong in Table. a Table is not a view.
>
> Table is not a view, right. On the other hand, views are quite useful and in analytical domain they are being used quite a lot. The analytical data are mostly read-only during their use, therefore I see no problem with treating VIEWs as read-only TABLEs in this case. I would definitely not drop view reflection from the library, as it is makes building another kinds of abstractions/tools (besides ORM) much more easier.

oh no argument there, SQLA should support views better, with their own object called a View. We have all the reflection logic, we have a recipe, nailing down a full feature spec is straightforward. Someone needs to express an interest in establishing a behavioral spec that makes sense, given how creating a view works, reflecting, etc.