tests whether sqlalchemy automatically uses relations loaded into session

already_in_session_test.py

from sqlalchemy import create_engine, Column, ForeignKey, Integer
from sqlalchemy.orm import relationship, 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)

    products = relationship('Product', backref='post')


class Product(Base):
    __tablename__ = 'products'
    id = Column(Integer, primary_key=True)
    post_id = Column(Integer, ForeignKey('posts.id'), index=True)


#Base.metadata.drop_all(engine)
Base.metadata.create_all(engine)

# create new rows if database is empty
first_result = Post.query.first()
if not first_result:
    for x in range(50):
        products = [Product() for y in range(12)]
        session.add(Post(products=products))
    session.commit()

# Example #1
# from child to parent, does not trigger lazy loading (uses session)
products = session.query(Product).all()
post_ids = {product.post_id for product in products}
posts = Post.query.filter(Post.id.in_(post_ids)).all()

for product in products:
    print(product.post.id)
session.commit()

# Example #2
# from parent to child, triggers lazy loading (ignores session)
posts = Post.query.limit(20).all()
post_ids = {post.id for post in posts}
products = Product.query.filter(Product.post_id.in_(post_ids)).all()

for post in posts:
    for product in post.products:
        print(product.id)