Short: ------- Is there a way to backfill multiple deferred columns in a declarative object result instance in a dynamic way when groups can't be predicted in the model?
Long: -------- First, let me just say thanks for SQLAlchemy. This is my first post to this list and after working with it for quite a while I've found it to be an excellent tool for working with the database. My previous work (non-Python) was done with a database abstraction layer that was more relational and less object-oriented and I've found SQLAlchemy to be amazing for letting me have my cake (objects) and eat it too (hand-crafted sql optimizations). Alright, so a few caveats for background: 1) I'm fairly new to Python (~4 months), but not to programming (~10 years) 2) This is the first time I've used an ORM, so my question may be more about the object-relational mismatch handling, rather than SQLAlchemy directly. 3) I'm using SQLAlchemy with Flask's plug-in flask-sqlalchemy. That may not have much do with my question, but just in case there is some subtle difference between declarative's base model and Flask's db.Model 4) The current project is to use Flask and SQLAlchemy to create a web site with an existing database in a deployed client-server application (96 Tables, can be anywhere between ~200MB and 30GB) 5) Assumptions abound... this is a fairly complicated/specific case (I think) so there may be underlying assumptions about how I'm doing things that are incorrect. If I'm wrong in those underlying assumptions, then feel free to challenge them. 6) SQLAlchemy 0.7.8 Cool. So, I see that using declarative objects has a quite a few advantages; you can easily add attributes(columns, relationships, etc...), validators, and methods -- all great stuff for keeping things logically grouped. Then when you get to performance optimizations there is a significant benefit with larger models to not fetch all the columns for every request (this is a web app after all, so lower response times are a goal). Great, so deferred looks like the ticket to be able to handle this particular mis-match in a good enough way. I can defer any non-essential columns and if I need one or two other columns down the line then they'll be lazy-loaded as required. Contrived example: class User(db.Model, HelperMixin): __tablename__ = 'user' id = db.Column(db.Integer, primary_key=True) password = db.Column(db.String) type = db.Column(db.Integer) first_name = db.Column(db.String) last_name = db.Column(db.String) title = db.Column(db.String) birthday = db.Column(db.Date) height = db.Column(db.Numeric) width = db.Column(db.Numeric) # etc... def is_valid(self, check_password): # check password in a horribly insecure, but easy way return True if check_password == self.password else False So with this model I want to validate a users password on login, but not load all the other unnecessary stuff, because login probably doesn't need all the rest of those columns. Because I also want to keep things simple on the model, I don't use deferred directly, but rather I created a couple helper methods in a mixin. (Note that other parts of the application may need more columns or less columns or different columns, depending on context, so putting deferreds directly in the model would also be impractical) The mixin looks like this: from sqlalchemy.orm import defer from sqlalchemy.orm.properties import ColumnProperty from sqlalchemy.orm.util import class_mapper class HelperMixin(object): @classmethod def itercolumns(cls): for prop in class_mapper(cls).iterate_properties: if isinstance(prop, ColumnProperty): yield prop.key @classmethod def get_deferred_except(cls, *attributes): attribute_set = set(attributes) ret = list() for name in cls.itercolumns(): if name not in attribute_set: ret.append(defer(name)) return ret 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? 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. Thanks for your time! -Dave -- You received this message because you are subscribed to the Google Groups "sqlalchemy" group. To view this discussion on the web visit https://groups.google.com/d/msg/sqlalchemy/-/BslpM49_4G0J. 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.