On 10/03/2016 10:31 AM, [email protected] 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, [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 <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 <[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>
> <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 [email protected]
<javascript:>
> <mailto:[email protected]
<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 [email protected]
<mailto:[email protected]>.
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.
