SQLAlchemy ORM has some features that would simplify your task. It looks like you need to reinvent some wheels already present at the ORM level: "I have a method called getuser that searches for a user in db (by name or id), retrieves the user string and encapsulates it with the user class" - this is what does ORM do.
With ORM, you have a session that, among other things, serves as a cache for ORM objects, so you can avoid loading the same model more than once per transaction. You will find that you need to load the User object to authenticate the request anyway, so not querying the table at all is probably not an option.
You can also configure some attributes lazily loaded , so some rarely required or bulky properties are loaded only when accessing them
You can also set up high-load relationships in a single request, which can save you from hundreds of small individual requests. I mean, in your current design, how many requests will be triggered below:
for user in get_all_users(): print user.get_avatar_uri() print user.get_name() print user.get_about()
from your description, it looks like you might need a 1 + request (num_users * 3). With SQLMalchemy ORM, you can load everything in one query.
Conclusion: selecting one object from the database by its primary key is a reasonably cheap operation, you should not worry about it if you do not create something the size of facebook. What you need to worry about is making hundreds of small individual queries where one larger query will suffice. This is an area where SQLAlchemy ORM is very, very good.
Now, regarding "whether you should spend time creating a user object and looking for a row of users when they don’t even use the extracted data, but just want to make changes to a subset of the columns" - I understand that you are thinking of something like
class ChangePasswordForm(...): def _change_password(self, user_id, new_password): session.execute("UPDATE users ...", user_id, new_password) def save(self, request): self._change_password(request['user_id'], request['password'])
vs
class ChangePasswordForm(...): def save(self, request): user = getuser(request['user_id']) user.change_password(request['password'])
In the previous example, only one query will be issued, the latter will have to issue a SELECT and build a User object, and then issue UPDATE. The latter may seem to be “more efficient,” but in a real application the difference may be negligible. In addition, often you will need to get an object from the database in any case, either for verification (the new password cannot be the same as the old password), verification of rights (user Molly allowed to edit the description of photo No. 12343?) Or logging.
If you think that the difference in the additional request will be important (millions of users constantly edit their profile images), then you probably need to do some profiling and see where the bottlenecks are.