将CTE查询转换为SQLAlchemy对象模型

from sqlalchemy import create_engine, select, text
from sqlalchemy.orm import sessionmaker
from sqlalchemy.ext.declarative import declarative_base

engine = create_engine('mysql+mysqlconnector://username:password@localhost/database')
Session = sessionmaker(bind=engine)
session = Session()

Base = declarative_base()

class MyModel(Base):
    __tablename__ = 'my_table'
    
    id = Column(Integer, primary_key=True)
    name = Column(String(50))
    # 定义其他属性...

cte_query = text('WITH cte AS (SELECT * FROM my_table) SELECT * FROM cte')
sqlalchemy_query = session.query(MyModel).from_statement(cte_query)

results = sqlalchemy_query.all()
for row in results:
    print(row.id, row.name)