Create materialized view with reflected metadata

[Stefan Urbanek]

Hi,

We are trying to create a materialized view (PostgreSQL). We have this simple statement compiler:

class CreateMaterializedView(Executable, ClauseElement):

    def __init__(self, name, select):

        self.name = name

        self.select = select

@compiles(CreateMaterializedView)

def visit_create_materialized_view(element, compiler, **kw):

    

    return "CREATE MATERIALIZED VIEW {} AS {}".format(

         element.name,

         compiler.process(element.select, literal_binds=True)

         )

What is the clean way to get the actual reflected metadata (Table object) to construct the `select` element for the CreateMaterializedView?

Thanks,

Stefan

[Michael Bayer]

not sure if i understand the question.    where’s the reflected table here?   are you trying to reflect the underlying SELECT from it, or make a new SELECT out of an existing view ?

if the question is, “I want to reflect a materialized view in Postgresql”, we just added that in 1.0, so is not released yet: http://docs.sqlalchemy.org/en/latest/changelog/migration_10.html#postgresql-dialect-reflects-materialized-views-foreign-tables

--
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-alem...@googlegroups.com.
For more options, visit https://groups.google.com/d/optout.

[Stefan Urbanek]

I want to construct SELECT from the underlying table(s) to create the view during the migration. The materialized VIEW is the target object to be created by the migration.

Stefan

To unsubscribe from this group and stop receiving emails from it, send an email to sqlalchemy-alembic+unsub...@googlegroups.com.

[Michael Bayer]

we have a recipe for that at:  https://bitbucket.org/zzzeek/sqlalchemy/wiki/UsageRecipes/Views

To unsubscribe from this group and stop receiving emails from it, send an email to sqlalchemy-alem...@googlegroups.com.

[Stefan Urbanek]

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.

s.

[Michael Bayer]

On Oct 2, 2014, at 11:41 AM, Stefan Urbanek <stefan....@gmail.com> 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.

[Stefan Urbanek]

Thank you Michael, the argument "autoload_with=op.get_bind()” was what I missed.

I apologize for asking about something that was in the docs,

Stefan

> On 02 Oct 2014, at 18:15, Michael Bayer <mik...@zzzcomputing.com> wrote:
>
>
> On Oct 2, 2014, at 11:41 AM, Stefan Urbanek <stefan....@gmail.com> 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 a topic in the Google Groups "sqlalchemy-alembic" group.
> To unsubscribe from this topic, visit https://groups.google.com/d/topic/sqlalchemy-alembic/eKqQWPAdunM/unsubscribe.
> To unsubscribe from this group and all its topics, send an email to sqlalchemy-alem...@googlegroups.com.

[wor...@gmail.com]

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__)

[Mike Bayer]

On 10/02/2016 11:21 PM, wor...@gmail.com 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?

> <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 sqlalchemy-alem...@googlegroups.com
> <mailto:sqlalchemy-alem...@googlegroups.com>.

[wor...@gmail.com]

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?

> an email to sqlalchemy-alembic+unsub...@googlegroups.com
> <mailto:sqlalchemy-alembic+unsub...@googlegroups.com>.

[Mike Bayer]

On 10/03/2016 10:31 AM, wor...@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.

> > 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>)

> > an email to sqlalchemy-alem...@googlegroups.com
> <javascript:>
> > <mailto:sqlalchemy-alem...@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-alem...@googlegroups.com
> <mailto:sqlalchemy-alem...@googlegroups.com>.

[wor...@gmail.com]

Hi Mike,

Thanks.  If I read you correctly, I have 2 options:

1.) autogenerate --> proof-read migration script --> manually add the views to upgrade() and downgrade()

2.) Write custom extensions and invoke them in env.py --> autogenerate --> proof-read migration script

Say I want to explore (1).  Is it as simple as adding the following to my upgrade()?

meta = Base.metadata 
vgbd_view = Table(“v_gbd”, meta, autoload=True, autoload_with=op.get_bind()) 

What would be in downgrade() then?

Your opinion on version control makes sense.  On our end we expect people of varying degrees of python experience to touch this schema migration pipeline, some even more noobs than me.  Which is why there's incentive to remove as much manual work as possible.

Much obliged.

[Mike Bayer]

On 10/03/2016 11:31 AM, wor...@gmail.com wrote:
> Hi Mike,
>
> Thanks. If I read you correctly, I have 2 options:
>
> 1.) autogenerate --> proof-read migration script --> manually add the
> views to upgrade() and downgrade()
> 2.) Write custom extensions and invoke them in env.py --> autogenerate
> --> proof-read migration script

yeah I think those are the options

>
> Say I want to explore (1). Is it as simple as adding the following to
> my upgrade()?
> meta = Base.metadata
> vgbd_view = Table(“v_gbd”, meta, autoload=True,
> autoload_with=op.get_bind())

you'd want to say something like op.execute(CreateView(my_view))

>
> What would be in downgrade() then?

something like op.execute(DropView(my_view))

doing the autoload in the migration script is not really a thing,
because the view would not exist yet.

>
> Your opinion on version control makes sense. On our end we expect
> people of varying degrees of python experience to touch this schema
> migration pipeline, some even more noobs than me. Which is why there's
> incentive to remove as much manual work as possible.
>
> Much obliged.
>