Turning a complex query into a view for SQLAlchemy?

[Dan Stromberg]

Hi.

In https://pajhome.org.uk/blog/10_reasons_to_love_sqlalchemy.html it says:

When performing highly complex queries, it is possible to define these
with SQLAlchemy syntax. However, I find there's a certain level of
complexity where it becomes easier to write SQL directly. In that
case, you can define a database view that encompasses the complex
query, and SQLAlchemy can map the view to Python objects.

I spoke with my team lead about this practice, and he was open to
trying it but said he had heard that SQLAlchemy may have problems
using views.

Does anyone on the list have anything to add here?

Thanks!

[Mike Bayer]

SQLAlchemy has no intrinsic issue with views but the thing that goes wrong with views is that they perform very poorly when used with composition. that is, if you take your view and use in a subquery, do some GROUP BY on it, do some LEFT OUTER JOIN onto it, the performance plummets, because the view itself is already pre-optimized to the complex query which it represents.

so if you are using SQLAlchemy and then adding lots of joinedload() to a query, you would get a lot of LEFT OUTER JOIN onto your view and it might not be very efficient from a DB server perspective. solution, dont use joinedload(), we have other eager loaders namely selectinload() which put much less stress on the query planner.

> --
> 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 view this discussion on the web visit
> https://groups.google.com/d/msgid/sqlalchemy/CAOvKW54y462yR4m5pOV5ukACA7v0CgCDUcrDztRBogtuE9t_GA%40mail.gmail.com.