Re: [sqlalchemy] Listing all the tables that a query uses
You and sqlalchemy never cease to impress. Thank you very much!
On Wed, Jun 26, 2019, 22:14 Mike Bayer 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
>
> .
> 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.
Re: [sqlalchemy] Listing all the tables that a query uses
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.
[sqlalchemy] Listing all the tables that a query uses
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. -- 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.
