You and sqlalchemy never cease to impress. Thank you very much!
On Wed, Jun 26, 2019, 22:14 Mike Bayer <mike...@zzzcomputing.com> wrote:
>
>
> On Wed, Jun 26, 2019, at 1:51 PM, Brian Maissy wrote:
>
> Background: I have a bunch of materialized views (postgres) that are
> dependent on each other. When I want to change one of them, I drop cascade
> it (postgres does not provide a way to modify the query of an existing
> materialized view) and recreate it and all of its dependents (which are
> dropped from the cascade).
>
> I have corresponding sqlalchemy core tables and queries which represent
> the views, and a script which generates the necessary DDL to drop and
> recreate the views, but I got stuck trying to solve the common case of the
> problem of automatically detecting the dependencies between the views.
>
> So my question is this: given an sqlalchemy core query (a Select object),
> is there a simple way to list all of the tables that it uses?
>
>
> yes, use the visitors package:
>
> from sqlalchemy.sql import visitors
>
> tables = []
> for obj in visitors.iterate(my_stmt, {}):
> if isinstance(obj, Table):
> tables.add(obj)
>
> there's some other ways to use this API as well as some utility functions
> that find tables such as
> https://github.com/sqlalchemy/sqlalchemy/blob/master/lib/sqlalchemy/sql/util.py#L231
> which you can take a look at for examples.
>
>
>
>
>
> By simple I mean without traversing the entire object tree of the query
> and dealing with each type of element individually. I started out by
> implementing something along those lines only for the FROM clauses of the
> query (which was pretty simple, just need to expect tables, aliases, joins,
> compound selects, and maybe another case or two). But when I realized that
> the query might refer to a table in a subquery anywhere in the SELECT or
> WHERE clauses, the number of possible elements I would have to deal with
> jumped dramatically.
>
> I should note that the obvious answer of "define the dependencies
> manually" is sufficient in practice for my use case, but for interest and
> the challenge I wanted to see if there was a better way.
>
> --
> 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 sqlalchemy+unsubscr...@googlegroups.com.
> To post to this group, send email to sqlalchemy@googlegroups.com.
> Visit this group at https://groups.google.com/group/sqlalchemy.
> To view this discussion on the web visit
> https://groups.google.com/d/msgid/sqlalchemy/4580a1cb-adcd-4acf-bf81-bddf3577ee81%40googlegroups.com
> .
> 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 a topic in the
> Google Groups "sqlalchemy" group.
> To unsubscribe from this topic, visit
> https://groups.google.com/d/topic/sqlalchemy/y1bS_5UxenQ/unsubscribe.
> To unsubscribe from this group and all its topics, send an email to
> sqlalchemy+unsubscr...@googlegroups.com.
> To post to this group, send email to sqlalchemy@googlegroups.com.
> Visit this group at https://groups.google.com/group/sqlalchemy.
> To view this discussion on the web visit
> https://groups.google.com/d/msgid/sqlalchemy/e7a7c00b-36ca-4998-8632-8e7910f4e67d%40www.fastmail.com
> <https://groups.google.com/d/msgid/sqlalchemy/e7a7c00b-36ca-4998-8632-8e7910f4e67d%40www.fastmail.com?utm_medium=email&utm_source=footer>
> .
> 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 sqlalchemy+unsubscr...@googlegroups.com.
To post to this group, send email to sqlalchemy@googlegroups.com.
Visit this group at https://groups.google.com/group/sqlalchemy.
To view this discussion on the web visit
https://groups.google.com/d/msgid/sqlalchemy/CAHhXk4htt9kpH9hvCQtLiWgBWQeJ6oDOg6PX0uepfQOBgc7%2B3g%40mail.gmail.com.
For more options, visit https://groups.google.com/d/optout.
