SQLAlchemy - empty lists + in_() causing crazy queries (before 1.2.0) 18 Mar 2017 Before SQLAlchemy 1.2.0, if you use an empty list with in_(), it will emit some crazy SQL that will query your entire table. The best solution is probably to upgrade to SQLAlchemy 1.2.0.
# 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
"""