在SQLAlchemy中按关系计数过滤

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

Base = declarative_base()

class Author(Base):
    __tablename__ = 'author'
    id = Column(Integer, primary_key=True)
    name = Column(String)
    books = relationship('Book', back_populates='author')

class Book(Base):
    __tablename__ = 'book'
    id = Column(Integer, primary_key=True)
    title = Column(String)
    author_id = Column(Integer, ForeignKey('author.id'))
    author = relationship('Author', back_populates='books')

# 创建数据库引擎和会话
engine = create_engine('sqlite:///books.db')
Session = sessionmaker(bind=engine)
session = Session()

# 按关系计数过滤
authors_with_books = session.query(Author).join(Author.books).group_by(Author).having(func.count(Book.id) > 1).all()