sqlalchemy joined subquery example

joined_subquery.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)
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',
        lazy='joined',
    )


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)

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()

print(Post.query.limit(20))