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 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/e7a7c00b-36ca-4998-8632-8e7910f4e67d%40www.fastmail.com. For more options, visit https://groups.google.com/d/optout.