我正在嘗試使用以下方法創建一個簡單的日歷：
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 中提取原始資料型別以創建間隔字串？應該類似于 DATE(CURRENT_DATE, ' 1 days')。我真的希望 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
uj5u.com熱心網友回復：
訣竅是將列強制轉換為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
轉載請註明出處，本文鏈接：https://www.uj5u.com/qita/311407.html