On Fri, Oct 26, 2018 at 11:51 AM Philip Martin
<[email protected]> wrote:
>
> 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?
no, the dependencies are taken into account, here is a demo:
from sqlalchemy import *
m = MetaData()
t1, t2, t3 = Table('t1', m), Table('t2', m), Table('t3', m)
t1.add_is_dependent_on(t2)
t3.add_is_dependent_on(t1)
print([t.name for t in m.sorted_tables])
dependencies are consumed in
https://github.com/zzzeek/sqlalchemy/blob/master/lib/sqlalchemy/sql/ddl.py#L1105,
use pdb and make sure what's expected is present in there.
>
>
> 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.
--
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.
