Cannot override create() and drop()

[‮rekcäH nitraM‮]

Although there is Table.create() and Table.drop(), they don't seem to actually be called by other parts of the system.

I've had to fall back to this workaround to get what I wanted:

class ReflectedTable(Table):
    "Never create or drop this table type, as it represents something the ORM does not manage."

from sqlalchemy.engine.ddl import SchemaDropper, SchemaGenerator
@monkey_patch(SchemaDropper, 'visit_table')
def replacement_visit_table_drop(original, self, table, drop_ok=False):
    if isinstance(table, ReflectedTable):
        return
    else:
        return original(self, table, drop_ok=drop_ok)

@monkey_patch(SchemaGenerator, 'visit_table')
def replacement_visit_table_create(original, self, table, create_ok=False):
    if isinstance(table, ReflectedTable):
        return
    else:
        return original(self, table, create_ok=create_ok)

Still this is something that should be possible to do - or I am missing something big time.

[Mike Bayer]

there are several approaches to this use case.

Simplest is to just send a list to create_all/drop_all:

metadata.drop_all(engine, tables=[for t in metadata.tables.values() if not isinstance(t, ReflectedTable)](t))

the next, which is what I'd probably do, would be just use two MetaData objects:

metadata = MetaData()
readonly_metadata = MetaData()

another, is to override the compilation to CreateTable DropTable, though there's no hook to actually prevent the execution here:

from sqlalchemy.ext.compiler import compiles
from sqlalchemy.schema import DropTable

@compiles(DropTable)
def _override_drop(element, compiler, **kw):
    if isinstance(element, element, ReflectedTable):
        return "SELECT 1"
    else:
        return compiler.visit_drop_table(element, **kw)

table.create and table.drop themselves have logic that doesn't apply to a mass create/drop all, so overriding them would not be feasible.

[‮rekcäH nitraM‮]

Well, indeed that second aproach is pure gold. Had I just understood that before delving deep into this. :/

I think I may end up overriding the ReflectedTable.new to ensure the right metadata instance is always used.

Many thanks for this documentation!

On a related note: What I'm doing with this is trying to provide some nice syntax to define and manage views in SQLAlchemy - do you think it makes sense to put that up somewhere, as the examples I've found floating around to be a bit lacking, yet you stated (or at least I seem to remember) that there's lots of problems which is why you didn't want to pull this into the framework.

Regards and Thanks!

[Mike Bayer]

take a look at the recipe we have for views at http://www.sqlalchemy.org/trac/wiki/UsageRecipes/Views. One aspect to this recipe is that the "table" we use is a lower-case-t table(), which doesn't actually get associated with the MetaData object at all. The recipe here could be enhanced to support reflection as well using inspector.get_columns() (http://docs.sqlalchemy.org/en/rel_0_8/core/schema.html?highlight=get_columns#sqlalchemy.engine.reflection.Inspector.get_columns).

[‮rekcäH nitraM‮]

Interesting technike the lowercase t tables. Good to know.

My solution currently looks like this: https://gist.github.com/dwt/5310037

Of course it's less sophisticated in that it doesn't reflect on the query to build the table, but I didn't know how to do that. :-)

[Mike Bayer]

Removing milestone: 0.8.xx (automated comment)