CODEWITHSUNDEEP
pythonsqlalchemy
djmac
djmac

Reputation: 895

SQLalchemy "load_only" doesn't load only the columns specified

I am trying to select a subset of columns from a table with sqlalchemy's load_only function. Unfortunately it doesn't seem to return only the columns specified in the functional call - specifically, it also seems to fetch the primary key (in my case, an auto_increment id field).

A simple example, if I use this statement to build a query,:

query = session.query(table).options(load_only('col_1', 'col_2'))

Then the query.statement looks like this:

SELECT "table".id, "table"."col_1", "table"."col_2" 
FROM "table"

Which is not what I would have expected - given I've specified the "only" columns to use...Where did the id come from - and is there a way to remove it?

Upvotes: 6

Views: 4882

Answers (1)

Ilja Everilä
Ilja Everilä

Reputation: 52929

Deferring the primary key would not make sense, if querying complete ORM entities, because an entity must have an identity so that a unique row can be identified in the database table. So the query includes the primary key though you have your load_only(). If you want the data only, you should query for that specifically:

session.query(table.col1, table.col2).all()

The results are keyed tuples that you can treat like you would the entities in many cases.

There actually was an issue where having load_only() did remove the primary key from the select list, and it was fixed in 0.9.5:

[orm] [bug] Modified the behavior of orm.load_only() such that primary key columns are always added to the list of columns to be “undeferred”; otherwise, the ORM can’t load the row’s identity. Apparently, one can defer the mapped primary keys and the ORM will fail, that hasn’t been changed. But as load_only is essentially saying “defer all but X”, it’s more critical that PK cols not be part of this deferral.

Upvotes: 3

Related Questions