使用子查询连接立即加载关系

SELECT u.*, o.*
FROM users u
LEFT JOIN orders o ON u.user_id = o.user_id;

from sqlalchemy import create_engine, Column, Integer, String, ForeignKey
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.orm import sessionmaker, relationship

Base = declarative_base()

class User(Base):
    __tablename__ = 'users'
    id = Column(Integer, primary_key=True)
    name = Column(String)
    orders = relationship("Order", back_populates="user")

class Order(Base):
    __tablename__ = 'orders'
    id = Column(Integer, primary_key=True)
    user_id = Column(Integer, ForeignKey('users.id'))
    product = Column(String)
    user = relationship("User", back_populates="orders")

engine = create_engine('sqlite:///example.db')
Session = sessionmaker(bind=engine)
session = Session()

users_with_orders = session.query(User).options(joinedload(User.orders)).all()