Sqlalchemy two filters vs one and

Question

This is a straight forward question. What is better to use, two filters or one and_? Is there any difference?

session.query(Test).filter(Test.id == X).filter(Test.test == Y).all()

vs

session.query(Test).filter(and_(Test.id == X, Test.test == Y)).all()

They will give me the same result but is there any difference i speed or anything else?

Answer 1

Both queries in your question have the same performance. You can test this easily by inspecting the resulting SQL query.

from sqlalchemy import Column, Integer, String, and_
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.orm.query import Query

Base = declarative_base()

class Test(Base):
    __tablename__ = 'test'
    id = Column(Integer, primary_key=True)
    test = Column(String(50))


print(Query([Test]).filter(Test.id == 1).filter(Test.test == 'foo'))

# SELECT test.id AS test_id, test.test AS test_test
# FROM test
# WHERE test.id = :id_1 AND test.test = :test_1

print(Query([Test]).filter(and_(Test.id == 1, Test.test == 'foo')))

# SELECT test.id AS test_id, test.test AS test_test
# FROM test
# WHERE test.id = :id_1 AND test.test = :test_1

Both queries produce the same SQL expression.

and_ is commonly used when you're using the SQL expression to query directly against tables, or when nesting multiple predicates within or_.