5

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?

djmac
  • 827
  • 5
  • 11
  • 27

1 Answers1

2

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.

Ilja Everilä
  • 50,538
  • 7
  • 126
  • 127
  • Thanks - but when I do this I only get the first col (i.e. `table.col1`) - not both. I think I tried something similar earlier on. – djmac Jan 12 '19 at 10:42
  • Probably should point out - was originally trying the `load_only` with a `joinedload`. That's when I realized that the the primary key was being return (saw id from both tables being joined being fetched). ... Question was a sort of "minimum example" showing the `load_only` function returning primary key. – djmac Jan 12 '19 at 10:46
  • You might've made your question a bit too minimal in relation to your original issue, but it is a good question on its own. As to "when I do this I only get the first col", what exactly did you do? – Ilja Everilä Jan 12 '19 at 12:25
  • Note that, generally, the notation in the answer does not reliably work, one may need to defer columns still to prevent SQLAlchemy querying unnecessary columns, which is what OP was trying to do – bytepusher Dec 05 '22 at 14:07
  • Please provide an example of such. The "notation" works reliably. It queries tuples of exactly the columns requested, nothing more. It is not loading ORM objects, so no deferring is necessary. – Ilja Everilä Dec 07 '22 at 00:18