On 10/03/2016 10:31 AM, worl...@gmail.com wrote:
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.
Unfortunately, Alembic does not support that use case. It is dangerous to not manually verify that the scripts are correct, because they very often will not be. You may have noticed that the included templates themselves also encourage the user to "please adjust!". This is documented here: http://alembic.zzzcomputing.com/en/latest/autogenerate.html#what-does-autogenerate-detect-and-what-does-it-not-detect
For your view, since SQLAlchemy does not have a first class view object, neither does Alembic. The recipe you've built for CreateView / DropView is great, and if you want to take that all the way through Alembic you'd have to add more extensions per the docs at http://alembic.zzzcomputing.com/en/latest/api/autogenerate.html#autogenerating-custom-operation-directives .
Easier to implement control and documentation that way.
once you get a migration script that works correctly, you commit it to version control, and it's there. I don't see how whether autogeneration did the file 100% or only 85% affects that goal.
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, wor...@gmail.com <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 <http://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 <http://self.name> = name > > > @compiler.compiles(CreateView) # compiles CreateView > def compile(element, compiler, **kw): > return "CREATE VIEW {} AS {}".format(element.name <http://element.name>, > compiler.sql_compiler.process(element.selectable)) > > > @compiler.compiles(DropView) # compiles DropView > def compile(element, compiler, **kw): > return "DROP VIEW {}".format(element.name <http://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 <http://docs.sqlalchemy.org/en/rel_0_9/core/reflection.html> > <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> > <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 sqlalchemy-alembic+unsubscr...@googlegroups.com <javascript:> > <mailto:sqlalchemy-alembic+unsubscr...@googlegroups.com <javascript:>>. > For more options, visit https://groups.google.com/d/optout <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 sqlalchemy-alembic+unsubscr...@googlegroups.com <mailto:sqlalchemy-alembic+unsubscr...@googlegroups.com>. 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 sqlalchemy-alembic+unsubscr...@googlegroups.com. For more options, visit https://groups.google.com/d/optout.