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

Question

我正在尝试使用以下方法创建一个简单的日历：

with Session(DB) as session:
    
    ints = union_all(
        select(literal(1).label('num')),
        select(2),
        select(3),
        select(4),
        select(5),
        select(6),
        select(7),
        select(8),
        select(9),
        select(10),
    ).cte()

    calendar = session.execute(
        select(
            ints.c.num,
            func.DATE(func.current_date(), f'+{ints.c.num} days')
        ).select_from(ints)
    )

    for row in calendar:
        print(row)

如何从ints.c.num中提取原始数据类型来创建interval字符串？应该类似于DATE(CURRENT_DATE，'+1天‘)。我真希望Sqlite能让这一切变得更容易。

标准输出：

2021-10-10 16:57:50,882 INFO sqlalchemy.engine.Engine BEGIN (implicit)
2021-10-10 16:57:50,883 INFO sqlalchemy.engine.Engine WITH anon_1 AS 
(SELECT ? AS num UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9 UNION ALL SELECT 10)
 SELECT anon_1.num, DATE(CURRENT_DATE, ?) AS "DATE_1" 
FROM anon_1
2021-10-10 16:57:50,883 INFO sqlalchemy.engine.Engine [generated in 0.00007s] (1, '+anon_1.num days')
(1, None)
(2, None)
(3, None)
(4, None)
(5, None)
(6, None)
(7, None)
(8, None)
(9, None)
(10, None)
2021-10-10 16:57:50,883 INFO sqlalchemy.engine.Engine ROLLBACK

Answer 1

诀窍是将列转换为String，并使用+连接各个部分，这将被转换为sqlite的||连接操作符。

    import sqlalchemy as sa
    ...

    calendar = select(
        ints.c.num,
        func.DATE(func.current_date(), '+' + sa.cast(ints.c.num, sa.String) + ' days')
    ).select_from(ints)

生成的SQL为

WITH anon_1 AS 
(SELECT :param_3 AS num UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9 UNION ALL SELECT 10)
 SELECT anon_1.num, DATE(CURRENT_DATE, :param_1 || CAST(anon_1.num AS VARCHAR) || :param_2) AS "DATE_1" 
FROM anon_1