Re: [sqlalchemy] Re: Limiting Relationship Depths

[Michael P. McDonnell]

Awesome, thank you Jonathan.

I know I've read that at least 3-4 times, but I think I've been staring at
the screen too much these days to actually read. I'll give it a go and let
you know how it goes!

On Wed, Jun 26, 2019 at 10:58 AM Jonathan Vanasco 
wrote:

> This section of the narrative dogs will help you configure the
> relationship as you want.
>
>https://docs.sqlalchemy.org/en/13/orm/loading_relationships.html
> 
>
> In terms of API docs,
>
>  https://docs.sqlalchemy.org/en/13/orm/relationship_api.html
>
> look for the `lazy` keyword
>
> --
> 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/e4b5b44a-2764-4d89-8fa1-e529fad821d5%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/CAHmCLHqocAZ_P-RiuQ4yGo_eJmSh%2BSLZ%3DNOR0EhOM3xqQBDYDw%40mail.gmail.com.
For more options, visit https://groups.google.com/d/optout.

Re: [sqlalchemy] Listing all the tables that a query uses

[Brian Maissy]

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

[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+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.

Re: [sqlalchemy] How to inspect filters

[Andrew Martin]

That's very interesting, Jonathan. Could you show me a quick example of
that approach? I'm not sure I *need* to do that, but I think I would learn
about SQLAlchemy from such an example and trying to understand it.

On Wed, Jun 26, 2019 at 11:00 AM Jonathan Vanasco 
wrote:

> FWIW, I found a better approach to a similar problem was to create a
> dict/object I used to log metadata about the query I wanted... then build
> the query or analyze it based on that metadata.  All the information is in
> the sqlalchemy query, but the execution performance a development time was
> much faster when I stopped analyzing the query and just consulted the dict.
>
> --
> 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/chGVkNwmKyQ/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/18bed722-c8c3-42a3-97de-a5f58987fdcf%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/CAOVGraJ1QapxWARpnS86pN3ez5fHykA60bRRRgboRXertBXSxA%40mail.gmail.com.
For more options, visit https://groups.google.com/d/optout.

[sqlalchemy] 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.

-- 
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.

Re: [sqlalchemy] How to inspect filters

[Jonathan Vanasco]

FWIW, I found a better approach to a similar problem was to create a 
dict/object I used to log metadata about the query I wanted... then build 
the query or analyze it based on that metadata.  All the information is in 
the sqlalchemy query, but the execution performance a development time was 
much faster when I stopped analyzing the query and just consulted the dict.

-- 
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/18bed722-c8c3-42a3-97de-a5f58987fdcf%40googlegroups.com.
For more options, visit https://groups.google.com/d/optout.

[sqlalchemy] Re: Limiting Relationship Depths

[Jonathan Vanasco]

This section of the narrative dogs will help you configure the relationship 
as you want.

   https://docs.sqlalchemy.org/en/13/orm/loading_relationships.html 


In terms of API docs, 

 https://docs.sqlalchemy.org/en/13/orm/relationship_api.html

look for the `lazy` keyword

-- 
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/e4b5b44a-2764-4d89-8fa1-e529fad821d5%40googlegroups.com.
For more options, visit https://groups.google.com/d/optout.