RubenSandwich RubenSandwich - 7 days ago 11
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

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.

Comments