Listing all the tables that a query uses

[Brian Maissy]

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?

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.

[Mike Bayer]

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+...@googlegroups.com.

To post to this group, send email to sqlal...@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.

[Brian Maissy]

You and sqlalchemy never cease to impress. Thank you very much!

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+...@googlegroups.com.

To post to this group, send email to sqlal...@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.