Sqlalchemy sqlite3 | create date using date() with timedelta days taken from CTE column-CodePudding

I'm trying to create a simple calendar using the following method:

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)

How do i extract the primitive data type from ints.c.num to create the interval string? should be something like DATE(CURRENT_DATE, ' 1 days'). I really wish Sqlite made this easier.

Stdout:

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

CodePudding user response：

The trick is to cast the column to String and concatenate the parts using , which will be converted to sqlite's || concatenation operator.

    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)

The generated SQL is

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