In SQLAlchemy, why must I alias the select construct when mapping against arbitrary selects?

Question

I tried to replicate the code from the docs regarding mapping models to arbitrary tables, but I get the following error:

sqlalchemy.exc.InvalidRequestError: When mapping against a select() construct, map against an alias() of the construct instead.This because several databases don't allow a SELECT from a subquery that does not have an alias.

Here is how I implemented the code example.

from sqlalchemy import ( select, func, Table, Column, Integer, ForeignKey, MetaData, ) from sqlalchemy.ext.declarative import declarative_base metadata = MetaData() Base = declarative_base() customers = Table('customer', metadata, Column('id', Integer, primary_key=True), ) orders = Table('order', metadata, Column('id', Integer, primary_key=True), Column('price', Integer), Column('customer_id', Integer, ForeignKey('customer.id')), ) subq = select([ func.count(orders.c.id).label('order_count'), func.max(orders.c.price).label('highest_order'), orders.c.customer_id ]).group_by(orders.c.customer_id).alias() customer_select = select([customers,subq]).\ where(customers.c.id==subq.c.customer_id) class Customer(Base): __table__ = customer_select

I can make this work by using the following:

class Customer(Base): __table__ = customer_select.alias()

Unfortunately, that creates all the queries in a subselect, which is prohibitively slow.

Is there a way to map a model against an arbitrary select? Is this a documentation error--the code sample from the docs doesn't work for me (in sqlalchemy==0.8.0b2 or 0.7.10)

Community · Accepted Answer · 2017-05-23 12:24:58Z

If a subquery is turning out to be "prohibitively slow" this probably means you're using MySQL, which as I've noted elsewhere has really unacceptable subquery performance.

The mapping of a select needs that statement to be parenthesized, basically, so that it's internal constructs don't leak outwards into the context in which it is used and so that the select() construct itself doesn't need to be mutated.

Consider if you were to map to a SELECT like this:

SELECT id, name FROM table WHERE id = 5

now suppose you were to select from a mapping of this:

ma = aliased(Mapping) query(Mapping).join(ma, Mapping.name == ma.name)

without your mapped select being an alias, SQLAlchemy has to modify your select() in place, which means it has to dig into it and fully understand its geometry, which can be quite complex (consider if it has LIMIT, ORDER BY, GROUP BY, aggregates, etc.):

SELECT id, name FROM table JOIN (SELECT id, name FROM table) as anon_1 ON table.name = anon_1.name WHERE table.id = 5

whereas, when it's self-contained, SQLAlchemy can remain ignorant of the structure of your select() and use it just like any other table:

SELECT id, name FROM (SELECT id, name FROM table WHERE id = 5) as a1 JOIN (SELECT id, name FROM table WHERE id = 5) as a2 ON a1.name = a2.name

In practice, mapping to a SELECT statement is a rare use case these days as the Query construct typically plenty flexible enough to do whatever pattern you need. Very early versions of SQLAlchemy featured the mapper() as the primary querying object, and internally we do make use of mapper()'s flexibility in this way (especially in joined inheritance), but the explicit approach isn't needed too often.

Is this a documentation error--the code sample from the docs doesn't work for me (in sqlalchemy==0.8.0b2 or 0.7.10)

Thanks for pointing this out. I've corrected the example and also added a huge note discouraging the practice overall, as it doesn't have a lot of use (I know this because I never use it).

"whereas, when it's self-contained, SQLAlchemy can remain ignorant of the structure of your select() and use it just like any other table" That makes sense. Thanks

Collectives™ on Stack Overflow

In SQLAlchemy, why must I alias the select construct when mapping against arbitrary selects?

1 Answer 1

1 Comment

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Related