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 <[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, 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 [email protected].
For more options, visit https://groups.google.com/d/optout.
