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.