On Aug 11, 2012, at 10:08 AM, David McKeone wrote:
> so with this helper I can context sensitively build up a result object with
> just the stuff I need (but without losing the benefits of the associated
> methods):
>
> deferred = User.get_deferred_except('id', 'password') # Get list of defer()
> instances for all columns, but those specified
> user = User.query.options(*deferred).first()
>
> # SQL Emitted -- SELECT id, password FROM user
>
> if user.is_valid(the_password):
> # Valid stuff
> else:
> # Invalid stuff
>
> Ok, well that worked great, but now I need to get the patrons name for some
> runtime specific reason. So I do this:
>
> full_name = " ".join([user.title, user.first_name, user.last_name])
>
> I now emit:
>
> SELECT title FROM user
> SELECT first_name FROM user
> SELECT last_name FROM user
>
> When what I really want at this point, and can predictably know in this case,
> is:
>
> SELECT title, first_name, last_name FROM user
>
> So, the question is, what is the best way to back-fill an object in a way
> that you keep the number of SQL queries low, while also getting the
> advantages of using a declarative instance?
think about just from a python level how that would necessarily have to work.
the requirements are:
1. object is loaded with most attributes deferred.
2. code executes against loaded object, which asks for three attributes. the
three attributes should load at once. however, the request for those
attributes are separate.
Right off, your code is going to be more verbose - "user.title, user.firstname,
user.lastname" are three separate instructions.
so no matter what, to follow the requested behavior, it *has* to be:
user = User.query.options(..).first()
load_more_attributes(user, ["title", "first_name", "last_name"])
full_name = " ".join([user.title, user.first_name, user.last_name])
that is, there must be an explicit instruction of some kind that tells it to
load these three at once.
So for load_more_attributes, two choices. One is use the function you have:
query(User).options(User.get_deferred_except("title", "first_name",
"last_name")).filter(...).first()
because, the identity map will have the effect that the same User instance is
the target.
Or use session.refresh(), probably more direct:
session.refresh(user, ["title", "first_name", "last_name"])
also, if the columns you're actually using are along these lines, that is, they
aren't 10K text files, I'd strongly encourage you to do some actual profiling
to determine if all this complexity is necessary and not actually hurting
performance much more. To pull over 3K of text over the wire in a single
result is probably a lot less overhead than to pull 1000K of text in two or
three queries.
> Additionally, I'd also wonder if it would be possible to make a 'faulting'
> type of object; one where requesting any of the deferred columns would cause
> all of the deferred columns in that instance to be loaded.
well this is what the "group" feature does, if any columns in the group are
touched, the whole group is loaded. My advice would be to do some profiling,
come up with groups that are tailored close enough to the groups of attributes
that tend to be called together, and to not overthink it.
--
You received this message because you are subscribed to the Google Groups
"sqlalchemy" group.
To post to this group, send email to sqlalchemy@googlegroups.com.
To unsubscribe from this group, send email to
sqlalchemy+unsubscr...@googlegroups.com.
For more options, visit this group at
http://groups.google.com/group/sqlalchemy?hl=en.
