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