Thanks Mike. It looks like *util.find_tables* is exactly what I was looking
for.
On a related topic, it seems *Base.metadata.sorted_tables* is splitting out
a different (and not correct) dependency order when a view depends on
another view while *schema.sort_tables([ view_depend_on_view_one,
view_one]) *is returning the views in proper order and the order of the DDL
statements appears correct. Is that to be expected?
from sqlalchemy.schema import CreateColumn
from sqlalchemy.sql.ddl import _CreateDropBase
from sqlalchemy.sql.util import find_tables
from sqlalchemy.ext.compiler import compiles
from sqlalchemy import Column, event, MetaData, Table
def find_selectable_dependencies(selectable):
"""Find the tables used in a select query."""
return list(set(find_tables(selectable)))
class _View(Table):
__visit_name__ = 'view'
is_view = True
def View(name, metadata, selectable, replace=True, cascade=False):
v = _View(name, metadata)
_meta = MetaData(schema=metadata.schema,
naming_convention=metadata.naming_convention)
table = Table(name, _meta)
for c in selectable.c:
table.append_column(Column(c.name, c.type))
event.listen(
metadata, 'after_create', CreateView(v, selectable, replace=replace)
)
event.listen(
metadata, 'before_drop', DropView(v, if_exists=True, cascade=cascade
)
)
for dependency in find_selectable_dependencies(selectable):
table.add_is_dependent_on(dependency)
v.add_is_dependent_on(dependency)
return table
class CreateView(_CreateDropBase):
__visit_name__ = 'create_view'
def __init__(self, element, selectable, on=None,
bind=None, replace=True):
super(CreateView, self).__init__(element, on=on, bind=bind)
self.columns = [CreateColumn(column) for column in element.columns]
self.selectable = selectable
self.replace = replace
@compiles(CreateView)
def visit_create_view(create, compiler, **kw):
view = create.element
preparer = compiler.dialect.identifier_preparer
text = '\nCREATE '
if create.replace:
text += 'OR REPLACE '
text += 'VIEW %s ' % preparer.format_table(view)
if create.columns:
column_names = [preparer.format_column(col.element)
for col in create.columns]
text += '(%s)' % ', '.join(column_names)
text += 'AS %s\n\n' % compiler.sql_compiler.process(create.selectable,
literal_binds=True)
return text
class DropView(_CreateDropBase):
__visit_name__ = 'drop_view'
def __init__(self, element, on=None, bind=None, cascade=False, if_exists
=False):
super(DropView, self).__init__(element, on=on, bind=bind)
self.cascade = cascade
self.if_exists = if_exists
@compiles(DropView)
def compile(drop, compiler, **kw):
text = "\nDROP VIEW "
if drop.if_exists:
text += "IF EXISTS "
text += compiler.preparer.format_table(drop.element)
if drop.cascade:
text += " CASCADE"
return text
On Thursday, October 25, 2018 at 2:58:59 PM UTC-5, Philip Martin wrote:
>
> I am trying to create a function that finds the dependent tables used in a
> select-able statement. My plan is to use this function in conjunction with
> *t.add_is_dependent_on* inside a modified version of the recipe to create
> views. Hopefully, this allows *sorted_tables* to use these dependency
> relationships. The function definitely seems to work when the selectable
> only involves base tables, but breaks when a view uses another view. I
> think if I modify the function to recursively cycle through elements that
> are Selectables it should do the trick, but I was wondering if there is a
> public method like *_from_objects */ already a function to do something
> similar to this in the library?
>
>
> def find_selectable_dependents(selectable):
> dependents = set()
> for part in selectable.froms:
> for obj in part._from_objects:
> if hasattr(obj, 'element'):
> table_ = obj.element
> dependents.add(table_)
>
> return list(dependents)
>
> def view(name, metadata, selectable):
> 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)
>
>
> dependents = find_selectable_dependents(selectable)
> for dependent in dependents:
> t.add_is_dependent_on(dependent)
>
> return t
>
>
>
--
SQLAlchemy -
The Python SQL Toolkit and Object Relational Mapper
http://www.sqlalchemy.org/
To post example code, please provide an MCVE: Minimal, Complete, and Verifiable
Example. See http://stackoverflow.com/help/mcve for a full description.
---
You received this message because you are subscribed to the Google Groups
"sqlalchemy" group.
To unsubscribe from this group and stop receiving emails from it, send an email
to [email protected].
To post to this group, send email to [email protected].
Visit this group at https://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.
