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 [email protected].
> To post to this group, send email to [email protected].
> 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 [email protected].
To post to this group, send email to [email protected].
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.
