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
	"""

view raw empty_in.py hosted with ❤ by GitHub