Hi, First of all, thank you guys for this thread. It's the close to what I need, but unfortunately I couldn't quite get things to work.
What I'm trying to do now is setting up a sqlalchemy ORM to version control our mysql db schema, with Alembic executing the actual migration. We have quite a few views in our db, and the migration is not tied to any Flask app. Our Alembic version is 0.8.7, with sqlalchemy 1.0.13. I bastardized the aforementioned UsageRecipes code to set up the following view, and I set target_metadata = Base.metadata in my alembic/env.py. However, when upgrading head on my local machine, I only see my tables in the db, not the views. None of my numerous trials-and-errors, including adding 'autoload':True to __table_args__, have solved the problem. I'd be grateful for any pointer to the right direction. class CreateView(DDLElement): """ A part of sqlalchemy definition of view. Compiled and used in the view() function below. """ def __init__(self, name, selectable): self.name = name self.selectable = selectable def write_ddl(self): comp = sqlcompiler.SQLCompiler(mysql.dialect(), self.selectable) comp.compile() enc = mysql.dialect().encoding params = [] for k,v in comp.params.items(): if isinstance(v, unicode): v = v.encode(enc) if isinstance(v, str): v = '"{}"'.format(v) params.append(v) return comp.string.encode(enc) % tuple(params) class DropView(DDLElement): """ A part of sqlalchemy definition of view. Compiled and used in the view function below. """ def __init__(self, name): self.name = name @compiler.compiles(CreateView) # compiles CreateView def compile(element, compiler, **kw): return "CREATE VIEW {} AS {}".format(element.name, compiler.sql_compiler.process(element.selectable)) @compiler.compiles(DropView) # compiles DropView def compile(element, compiler, **kw): return "DROP VIEW {}".format(element.name) def view(name, metadata, selectable): """ Defines a view. :param str name: must be the name of the table in the db :param MetaData metadata: usually comes from Base.metadata :param sqlalchemy.orm.query.Query.selectable selectable: defined by a sqlalchemy query. See use case. """ t = table(name) for c in selectable.c: c._make_proxy(t) CreateView(name, selectable).execute_at('after-create', metadata) DropView(name).execute_at('before-drop', metadata) return t class VGbd(Base): """ A view of the raw v_gbd table. """ __tabletype__ = "view" __table_args__ = ({'mysql_engine': 'InnoDB'}) __selectable__ = select([VGbdRaw]) # VGbdRaw is some table in the db __table__ = view('v_gbd', Base.metadata, __selectable__) On Friday, October 3, 2014 at 6:15:59 AM UTC+8, Michael Bayer wrote: > > > On Oct 2, 2014, at 11:41 AM, Stefan Urbanek <stefan....@gmail.com > <javascript:>> wrote: > > > Thanks, but that is for SQLAlchemy and we are already using > constructions like that. That is not a problem at all. Problem is in > Alembic migration – how to correctly get a reflected Table object from the > database just being migrated? Something like: > > > > table = Table("some_table", md) > > > > where md is the actual reflected metadata of the existing schema in the > database that the migration will be also upgrading. > > OK, I’m pretty sure you know this already, so forgive me if I’m stating > things you already know and the question is still something I’m not > getting. Table reflection with a Table is via the “autoload=True” > argument, where “autoload_with=<some bind>” allows you to send along the > engine or connection you want to reflect with ( > http://docs.sqlalchemy.org/en/rel_0_9/core/reflection.html, which I’m > sure you’ve seen). In an Alembic migration, the connection is available > via “get_bind()”: > http://alembic.readthedocs.org/en/latest/ops.html#alembic.operations.Operations.get_bind. > > So put those together and you get: > > def upgrade(): > meta = MetaData() > table = Table(“some_table”, meta, autoload=True, > autoload_with=op.get_bind()) > > the above, since it emits SELECT statements isn’t compatible with > “offline” mode. > > > > > > -- You received this message because you are subscribed to the Google Groups "sqlalchemy-alembic" group. To unsubscribe from this group and stop receiving emails from it, send an email to sqlalchemy-alembic+unsubscr...@googlegroups.com. For more options, visit https://groups.google.com/d/optout.