I work with SQL Alchemy a lot and, as a "lazy programmer", I enjoy the convenience it offers. But the "diligent programmer" in me often worries about optimisation and the performance of the queries and more the performance of my application in respect to those queries.
A major pitfall that programmers face when using an ORM is doing work in your application that should be done on the database side.
So here I am, wondering when to sacrifice performance for convenience. For example, in my application I might often need to do something of the sort:
Convenient
users = User.query.filter_by(some_column=True).all() # list of User objects (all columns)
refined_users = []
for user in users: # do something with a couple of columns
refined_users.append((user.name, user.age))
Optimised
users = session.query(User.name, User.age).filter(User.some_column==True).all() # list of tuples
So this is a pretty simple and contrived example where you would clearly opt for the optimised option. But in cases where I have to query many columns, it becomes tedious. I also like the convenience of accessing columns as user.name whereas the optimised example just returns the data in a list of a tuples (i.e.: no column names).
So whilst querying the entire object (i.e.: every column) is convenient and most likely doesn't have that big an impact on my database, it still feels a little dirty knowing that I can be nicer to my database.
