RubenSandwich RubenSandwich - 1 year ago 126
Python Question

SQLAlchemy load_only and defer

I have tried using the SQLAlchemy methods load_only and defer to send only certain columns from the database, but I can't seem to get them to work.

Here is the code I am using:

query = db.session.query(Vendor).options(defer('ownerName', 'boxSize', 'boxes', 'lastPickUp', 'zone', 'code'))
return response( query )


*response is a custom jsonify method I wrote because of the specific needs of my application.

I'm expecting the response:


{

"Response": [

{

"address": "10th and SW Alder St",

"city": "Portland, OR",

"id": 1,

"keywords": "Tacos, Mexican",

"latitude": 45.5206464,

"longitude": -122.68157559999997,

"name": "Taco Ted",

"phone": null,

"website": null,

},


...


But instead I'm getting:


{

"Response": [

{

"address": "10th and SW Alder St",

"boxSize": null,

"boxes": 10,

"city": "Portland, OR",

"id": 1,

"keywords": "Tacos, Mexican",

"lastPickUp": "01/12/2014",

"latitude": 45.5206464,

"longitude": -122.68157559999997,

"name": "Taco Ted",

"ownerName": null,

"phone": null,

"website": null,

"zone": 1

},


...


Which means my defer is being ignored. I have tried using load_only to solve this problem as well but both of them seem to make no difference. What am I missing here?

(If it makes any difference I'm using a sqlite database for development.)

Answer Source

that's not the correct usage of deferred() and the use case you have here is why load_only() was added. each call to deferred() only does one attribute at a time; the fact that it accepts many strings has to do with "paths" in the query, which is not what you're dealing with here.

load_only() will prevent those attributes from being loaded in the initial query:

load_only('ownerName', 'boxSize', 'boxes', 'lastPickUp', 'zone', 'code')

however, if your jsonify method still calls upon those attributes, like somevendor.boxSize, it will emit the SQL at that point. so it depends on how you are pulling the attributes as well.