Hi Mike, Thanks for the swift reply. In fact I run
alembic revision --autogenerate -m 'some message' followed by alembic upgrade head and I don't add anything to the auto-generated scripts inside alembic/versions/. The goal is to keep this whole process as automated as possible. Easier to implement control and documentation that way. Is that a possibility? On Monday, October 3, 2016 at 10:01:36 PM UTC+8, Mike Bayer wrote: > > > > On 10/02/2016 11:21 PM, [email protected] <javascript:> wrote: > > 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. > > when you say "upgrading" to you mean "alembic upgrade" ? what are you > putting in your alembic migration scripts? > > > > > > > > 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 <[email protected] > > <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 > > <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 > > > < > 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 [email protected] > <javascript:> > > <mailto:[email protected] <javascript:>>. > > For more options, visit https://groups.google.com/d/optout. > -- 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 [email protected]. For more options, visit https://groups.google.com/d/optout.
