ValueError: 9 is not valid, must be one of set() or {'ascending', 'asc', 'descending', 'desc'}

[euri10]

greetings, idk if i'm doing something wrong here, so I passed 10 forparams["rsi_period"] and get this error ValueError: 9 is not valid, must be one of set() or {'ascending', 'asc', 'descending', 'desc'} while it should replace it :

Traceback (most recent call last):
  File "/home/lotso/PycharmProjects/damiers/.venv/lib/python3.13/site-packages/litestar/middleware/_internal/exceptions/middleware.py", line 158, in __call__
    await self.app(scope, receive, capture_response_started)
  File "/home/lotso/PycharmProjects/damiers/.venv/lib/python3.13/site-packages/litestar/routes/http.py", line 81, in handle
    response = await self._get_response_for_request(
               ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
        scope=scope, request=request, route_handler=route_handler, parameter_model=parameter_model
        ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
    )
    ^
  File "/home/lotso/PycharmProjects/damiers/.venv/lib/python3.13/site-packages/litestar/routes/http.py", line 133, in _get_response_for_request
    return await self._call_handler_function(
           ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
        scope=scope, request=request, parameter_model=parameter_model, route_handler=route_handler
        ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
    )
    ^
  File "/home/lotso/PycharmProjects/damiers/.venv/lib/python3.13/site-packages/litestar/_kwargs/cleanup.py", line 125, in _throw
    await gen.athrow(exc)
  File "/home/lotso/PycharmProjects/damiers/src/damiers/dependencies.py", line 72, in get_instrument_service
    yield InstrumentService(InstrumentGateway(connection=connection))
  File "/home/lotso/PycharmProjects/damiers/.venv/lib/python3.13/site-packages/litestar/_kwargs/cleanup.py", line 125, in _throw
    await gen.athrow(exc)
  File "/home/lotso/PycharmProjects/damiers/src/damiers/dependencies.py", line 30, in get_connection
    yield connection
  File "/home/lotso/PycharmProjects/damiers/.venv/lib/python3.13/site-packages/litestar/routes/http.py", line 181, in _call_handler_function
    else await route_handler.fn(**parsed_kwargs)
         ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "/home/lotso/PycharmProjects/damiers/src/damiers/controllers/instrument.py", line 28, in index
    plot = await instrument_service.plot(symbol, params)
           ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "/home/lotso/PycharmProjects/damiers/src/damiers/services.py", line 166, in plot
    return await self._instrument_gateway.plot(symbol, params)
           ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "/home/lotso/PycharmProjects/damiers/src/damiers/infra/gateways.py", line 475, in plot
        query_value, values = sql(
                              ~~~^
            """WITH daily_calcs AS (
            ^^^^^^^^^^^^^^^^^^^^^^^^
    ...<78 lines>...
            locals()
            ^^^^^^^^
        )
        ^
  File "/home/lotso/PycharmProjects/damiers/.venv/lib/python3.13/site-packages/sql_tstring/__init__.py", line 104, in sql
    new_values = _replace_placeholders(parsed_query, 0)
  File "/home/lotso/PycharmProjects/damiers/.venv/lib/python3.13/site-packages/sql_tstring/__init__.py", line 215, in _replace_placeholders
    result.extend(_replace_placeholders(clause_, 0))
                  ~~~~~~~~~~~~~~~~~~~~~^^^^^^^^^^^^
  File "/home/lotso/PycharmProjects/damiers/.venv/lib/python3.13/site-packages/sql_tstring/__init__.py", line 218, in _replace_placeholders
    result.extend(_replace_placeholders(expression_, index))
                  ~~~~~~~~~~~~~~~~~~~~~^^^^^^^^^^^^^^^^^^^^
  File "/home/lotso/PycharmProjects/damiers/.venv/lib/python3.13/site-packages/sql_tstring/__init__.py", line 221, in _replace_placeholders
    result.extend(_replace_placeholders(part, index))
                  ~~~~~~~~~~~~~~~~~~~~~^^^^^^^^^^^^^
  File "/home/lotso/PycharmProjects/damiers/.venv/lib/python3.13/site-packages/sql_tstring/__init__.py", line 221, in _replace_placeholders
    result.extend(_replace_placeholders(part, index))
                  ~~~~~~~~~~~~~~~~~~~~~^^^^^^^^^^^^^
  File "/home/lotso/PycharmProjects/damiers/.venv/lib/python3.13/site-packages/sql_tstring/__init__.py", line 215, in _replace_placeholders
    result.extend(_replace_placeholders(clause_, 0))
                  ~~~~~~~~~~~~~~~~~~~~~^^^^^^^^^^^^
  File "/home/lotso/PycharmProjects/damiers/.venv/lib/python3.13/site-packages/sql_tstring/__init__.py", line 218, in _replace_placeholders
    result.extend(_replace_placeholders(expression_, index))
                  ~~~~~~~~~~~~~~~~~~~~~^^^^^^^^^^^^^^^^^^^^
  File "/home/lotso/PycharmProjects/damiers/.venv/lib/python3.13/site-packages/sql_tstring/__init__.py", line 221, in _replace_placeholders
    result.extend(_replace_placeholders(part, index))
                  ~~~~~~~~~~~~~~~~~~~~~^^^^^^^^^^^^^
  File "/home/lotso/PycharmProjects/damiers/.venv/lib/python3.13/site-packages/sql_tstring/__init__.py", line 221, in _replace_placeholders
    result.extend(_replace_placeholders(part, index))
                  ~~~~~~~~~~~~~~~~~~~~~^^^^^^^^^^^^^
  File "/home/lotso/PycharmProjects/damiers/.venv/lib/python3.13/site-packages/sql_tstring/__init__.py", line 215, in _replace_placeholders
    result.extend(_replace_placeholders(clause_, 0))
                  ~~~~~~~~~~~~~~~~~~~~~^^^^^^^^^^^^
  File "/home/lotso/PycharmProjects/damiers/.venv/lib/python3.13/site-packages/sql_tstring/__init__.py", line 218, in _replace_placeholders
    result.extend(_replace_placeholders(expression_, index))
                  ~~~~~~~~~~~~~~~~~~~~~^^^^^^^^^^^^^^^^^^^^
  File "/home/lotso/PycharmProjects/damiers/.venv/lib/python3.13/site-packages/sql_tstring/__init__.py", line 221, in _replace_placeholders
    result.extend(_replace_placeholders(part, index))
                  ~~~~~~~~~~~~~~~~~~~~~^^^^^^^^^^^^^
  File "/home/lotso/PycharmProjects/damiers/.venv/lib/python3.13/site-packages/sql_tstring/__init__.py", line 223, in _replace_placeholders
    result.extend(_replace_placeholder(node, index))
                  ~~~~~~~~~~~~~~~~~~~~^^^^^^^^^^^^^
  File "/home/lotso/PycharmProjects/damiers/.venv/lib/python3.13/site-packages/sql_tstring/__init__.py", line 278, in _replace_placeholder
    _check_valid(
    ~~~~~~~~~~~~^
        value,
        ^^^^^^
        case_sensitive=ctx.columns,
        ^^^^^^^^^^^^^^^^^^^^^^^^^^^
        case_insensitive={"asc", "ascending", "desc", "descending"},
        ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
    )
    ^
  File "/home/lotso/PycharmProjects/damiers/.venv/lib/python3.13/site-packages/sql_tstring/__init__.py", line 142, in _check_valid
    raise ValueError(
        f"{value} is not valid, must be one of {case_sensitive} or {case_insensitive}"
    )
ValueError: 9 is not valid, must be one of set() or {'ascending', 'asc', 'descending', 'desc'}

the corresponding code is:

    async def plot(self, symbol: str, params) -> str:
        """Get a plot for a specific asset by its symbol."""
        symbol = params["symbol"]
        volatility_period_1 = params["volatility_period"] - 1
        risk_free_rate = params["risk_free_rate"]
        rsi_period_1 = params["rsi_period"] - 1
        macd_short_period_1 = params["macd_short_period"] - 1
        macd_long_period_1 = params["macd_long_period"] - 1
        mayer_period_1 = params["mayer_period"] - 1
        query_value, values = sql(
            """WITH daily_calcs AS (
        SELECT
            instrument_id,
            close_time,
            close_price,
            open_price,
            open_time,
            low_price,
            high_price,
            volume,
            LAG(close_price, 1) OVER (PARTITION BY instrument_id ORDER BY close_time) AS prev_close
        FROM
            portfolio_schema.instrument_daily_prices
        WHERE
            instrument_id = (SELECT instrument_id FROM portfolio_schema.instruments WHERE symbol = {symbol})
    ),

    indicator_components AS (
        SELECT
            *,
            close_price - prev_close AS price_change,
            (close_price / NULLIF(prev_close, 0)) - 1 AS daily_return
        FROM
            daily_calcs
    ),
    final_indicators AS (
        SELECT
            *,
            AVG(CASE WHEN price_change > 0 THEN price_change ELSE 0 END) OVER w_rsi AS avg_gain,
            AVG(CASE WHEN price_change < 0 THEN ABS(price_change) ELSE 0 END) OVER w_rsi AS avg_loss,

            STDDEV(daily_return) OVER w_volatility AS stddev_return,
            
            MAX(close_price) OVER w_unbounded AS running_max_price,

            EXP(AVG(LN(close_price)) OVER w_macd_short) AS gma_short,
            EXP(AVG(LN(close_price)) OVER w_macd_long) AS gma_long,

            AVG(close_price) OVER w_mayer AS sma_mayer

        FROM
            indicator_components
        WINDOW
            w_rsi AS (PARTITION BY instrument_id ORDER BY close_time ROWS BETWEEN {rsi_period_1} PRECEDING AND CURRENT ROW),
            w_macd_short AS (PARTITION BY instrument_id ORDER BY close_time ROWS BETWEEN {macd_short_period_1} PRECEDING AND CURRENT ROW),
            w_macd_long AS (PARTITION BY instrument_id ORDER BY close_time ROWS BETWEEN {macd_long_period_1} PRECEDING AND CURRENT ROW),
            w_volatility AS (PARTITION BY instrument_id ORDER BY close_time ROWS BETWEEN {volatility_period_1} PRECEDING AND CURRENT ROW),
            w_mayer AS (PARTITION BY instrument_id ORDER BY close_time ROWS BETWEEN {mayer_period_1} PRECEDING AND CURRENT ROW),
            w_unbounded AS (PARTITION BY instrument_id ORDER BY close_time ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW)
    )

    SELECT
        instrument_id,
        close_time,
        close_price,
        open_price,
        open_time,
        low_price,
        high_price,
        volume,

        100 - (100 / (1 + (avg_gain / NULLIF(avg_loss, 0)))) AS rsi,

        gma_short - gma_long AS macd_line,

        stddev_return * SQRT(365) AS volatility,

        (AVG(daily_return) OVER w_volatility - ({risk_free_rate} / 365)) / NULLIF(stddev_return, 0) AS sharpe_ratio,

        close_price / NULLIF(sma_mayer, 0) AS mayer_multiple,

        MIN((close_price / NULLIF(running_max_price, 0)) - 1) OVER (PARTITION BY instrument_id) AS max_drawdown
    FROM
        final_indicators
    WINDOW
        w_volatility AS (PARTITION BY instrument_id ORDER BY close_time ROWS BETWEEN {volatility_period_1} PRECEDING AND CURRENT ROW)
    ORDER BY
        close_time ASC;
            """,
            locals()
        )

[euri10]

it's as if it was unable to replace in w_rsi AS (PARTITION BY instrument_id ORDER BY close_time ROWS BETWEEN {rsi_period_1} PRECEDING AND CURRENT ROW),

[euri10]

a simpler reproducer:

rsi_period_1 = 14
d="""SELECT
    instrument_id,
    close_time,
    close_price,
    AVG(close_price) OVER w_rsi AS avg_close_price
FROM
    portfolio_schema.instrument_daily_prices
WINDOW
    w_rsi AS (
        PARTITION BY instrument_id
        ORDER BY close_time
        ROWS BETWEEN {rsi_period_1} PRECEDING AND CURRENT ROW
    )"""
sql(d, locals())

[pgjones]

I think c6db309 fixes this. Can you give it a go?

[euri10]

there is progress ie it doesnt crash on not getting the window function like before but now i get :

  File "/home/lotso/PycharmProjects/damiers/src/damiers/infra/gateways.py", line 476, in plot
        query_value, values = sql(
                              ~~~^
            """WITH daily_calcs AS (
            ^^^^^^^^^^^^^^^^^^^^^^^^
    ...<77 lines>...
            locals(),
            ^^^^^^^^^
        )
        ^
  File "/home/lotso/PycharmProjects/damiers/.venv/lib/python3.13/site-packages/sql_tstring/__init__.py", line 104, in sql
    new_values = _replace_placeholders(parsed_query, 0)
  File "/home/lotso/PycharmProjects/damiers/.venv/lib/python3.13/site-packages/sql_tstring/__init__.py", line 218, in _replace_placeholders
    result.extend(_replace_placeholders(clause_, 0))
                  ~~~~~~~~~~~~~~~~~~~~~^^^^^^^^^^^^
  File "/home/lotso/PycharmProjects/damiers/.venv/lib/python3.13/site-packages/sql_tstring/__init__.py", line 221, in _replace_placeholders
    result.extend(_replace_placeholders(expression_, index))
                  ~~~~~~~~~~~~~~~~~~~~~^^^^^^^^^^^^^^^^^^^^
  File "/home/lotso/PycharmProjects/damiers/.venv/lib/python3.13/site-packages/sql_tstring/__init__.py", line 224, in _replace_placeholders
    result.extend(_replace_placeholders(part, index))
                  ~~~~~~~~~~~~~~~~~~~~~^^^^^^^^^^^^^
  File "/home/lotso/PycharmProjects/damiers/.venv/lib/python3.13/site-packages/sql_tstring/__init__.py", line 221, in _replace_placeholders
    result.extend(_replace_placeholders(expression_, index))
                  ~~~~~~~~~~~~~~~~~~~~~^^^^^^^^^^^^^^^^^^^^
  File "/home/lotso/PycharmProjects/damiers/.venv/lib/python3.13/site-packages/sql_tstring/__init__.py", line 224, in _replace_placeholders
    result.extend(_replace_placeholders(part, index))
                  ~~~~~~~~~~~~~~~~~~~~~^^^^^^^^^^^^^
  File "/home/lotso/PycharmProjects/damiers/.venv/lib/python3.13/site-packages/sql_tstring/__init__.py", line 224, in _replace_placeholders
    result.extend(_replace_placeholders(part, index))
                  ~~~~~~~~~~~~~~~~~~~~~^^^^^^^^^^^^^
  File "/home/lotso/PycharmProjects/damiers/.venv/lib/python3.13/site-packages/sql_tstring/__init__.py", line 226, in _replace_placeholders
    result.extend(_replace_placeholder(node, index))
                  ~~~~~~~~~~~~~~~~~~~~^^^^^^^^^^^^^
  File "/home/lotso/PycharmProjects/damiers/.venv/lib/python3.13/site-packages/sql_tstring/__init__.py", line 282, in _replace_placeholder
    _check_valid(value, case_sensitive=ctx.columns)
    ~~~~~~~~~~~~^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "/home/lotso/PycharmProjects/damiers/.venv/lib/python3.13/site-packages/sql_tstring/__init__.py", line 141, in _check_valid
    raise ValueError(f"{value} is not valid, must be {value_type}")
ValueError: 0.05 is not valid, must be <class 'str'>

this value pass would correspond to risk_free_rate in my 1st query posted, but I kinda suppose now this is my mistake trying to divide it in the query template like so : (AVG(daily_return) OVER w_volatility - ({risk_free_rate} / 365)) / NULLIF(stddev_return, 0) AS sharpe_ratio,

edit I tried to pass 0.05/365 and remove the divide 365 fro within the tempate but I get the same error in fact now

[pgjones]

I'm wondering if numbers should always be allowed unlike strings as I think it is safe.

As a workaround given you know risk_free_rate is safe I'd do,

from sql_tstring import t

avg = t(f"(AVG(daily_return) OVER w_volatility - ({risk_free_rate} / 365)) / NULLIF(stddev_return, 0)")
query_value, values = sql(
    """
    ...
    stddev_return * SQRT(365) AS volatility,
    {avg},
    ...""",
    locals(),
)