SQLAlchemy db.session.query() vs model.query

Question

For a simple return all results query should one method be preferred over the other? I can find uses of both online but can't really find anything describing the differences.

db.session.query([my model name]).all()  [my model name].query.all() 

I feel that [my model name].query.all() is more descriptive.

Answer 1

It is hard to give a clear answer, as there is a high degree of preference subjectivity in answering this question.

From one perspective, the db.session is desired, because the second approach requires it to be incorporated in your model as an added step - it is not there by default as part of the Base class. For instance:

Base = declarative_base() DBSession = scoped_session(sessionmaker()) class User(Base):    __tablename__ = 'users'     id = Column(Integer, primary_key=True)    name = Column(String)    fullname = Column(String)    password = Column(String) session = Session() print(User.query) 

That code fails with the following error:

AttributeError: type object 'User' has no attribute 'query'

You need to do something like this:

class User(Base):    __tablename__ = 'users'     id = Column(Integer, primary_key=True)    name = Column(String)    fullname = Column(String)    password = Column(String)    query = DBSession.query_property() 

However, it could also be argued that just because it is not enabled by default, that doesn't invalidate it as a reasonable way to launch queries. Furthermore, in the flask-sqlalchemy package (which simplifies sqlalchemy integration into the flask web framework) this is already done for you as part of the Model class (doc). Adding the query property to a model can also be seen in the sqlalchemy tutorial (doc):

class User(object):    query = db_session.query_property()    .... 

Thus, people could argue either approach.

I personally have a preference for the second method when I am selecting from a single table. For example:

serv = Service.query.join(Supplier, SupplierUsr).filter(SupplierUsr.username == usr).all() 

This is because it is of smaller line length and still easily readable.

If am selecting from more than one table or specifying columns, then I would use the model query method as it extracting information from more than one model.

deliverables = db.session.query(Deliverable.column1, BatchInstance.column2).\     join(BatchInstance, Service, Supplier, SupplierUser). \     filter(SupplierUser.username == str(current_user)).\     order_by(Deliverable.created_time.desc()).all() 

That said, a counter argument could be made in always using the session.query method as it makes the code more consistent, and when reading left to right, the reader immediately knows that the sqlalchemy directive they are going to read will be query, before mentally absorbing what tables and columns are involved.

At the end of the day, the answer to your question is subjective and there is no correct answer, and any code readability benefits either way are tiny. The only thing where I see a strong benefit is not to use model query if you are selecting from many tables and instead use the session.query method.