SQLAlchemy - in_() and notin_() with an empty list

18 Mar 2017

Updated 2026-07-16: clarified the current behavior and cited the SQLAlchemy docs.

Before SQLAlchemy 1.2.0, an empty list passed to in_() emitted SQL that queried your entire table. Since 1.2.0 this is handled: an empty in_() renders a backend-specific empty-set expression that matches no rows, and an empty notin_() one that matches all rows, so neither scans the table. If you are stuck on an older version, upgrading is the fix.

The gist below shows the old behavior and the query it produced:

# demonstrates the issue fixed in: https://bitbucket.org/zzzeek/sqlalchemy/issues/3907 from sqlalchemy import create_engine, Column, Integer from sqlalchemy.orm import scoped_session, sessionmaker from sqlalchemy.ext.declarative import declarative_base engine = create_engine('mysql://root@localhost/test?charset=utf8mb4', convert_unicode=True, echo=True) session = scoped_session(sessionmaker(autocommit=False, autoflush=False, bind=engine)) Base = declarative_base() Base.query = session.query_property() class Post(Base): __tablename__ = 'posts' id = Column(Integer, primary_key=True) #Base.metadata.drop_all(engine) Base.metadata.create_all(engine) # create new rows if database is empty for x in range(10): session.add(Post()) session.commit() # empty lists trigger insane queries before 1.2.0 Post.query.filter(Post.id.in_([])).all() """ SELECT posts.id AS posts_id FROM posts WHERE posts.id != posts.id """