Sqlalchemy + sqlite3 |使用date()创建日期，时间增量取自CTE列的天数

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

# 创建Sqlalchemy引擎和会话
engine = create_engine('sqlite:///mydatabase.db')
Session = sessionmaker(bind=engine)
session = Session()

# 创建基本模型
Base = declarative_base()

# 定义模型类
class MyModel(Base):
    __tablename__ = 'mytable'
    id = Column(Integer, primary_key=True)
    date_column = Column(Date)

# 创建CTE查询
cte_query = session.query(func.date("2022-01-01").label("start_date"),
                          func.date("2022-01-01", "+10 days").label("end_date"))

# 执行CTE查询并获取结果
cte_result = cte_query.all()

# 使用CTE结果进行插入操作
for row in cte_result:
    new_entry = MyModel(date_column=row.start_date + (row.end_date - row.start_date).days)
    session.add(new_entry)

# 提交事务
session.commit()