Home psycopg2 "%s" variable with " LIKE 'fake_%' " triggers "IndexError: tuple index out of range"

Questions

psycopg2 "%s" variable with " LIKE 'fake_%' " triggers "IndexError: tuple index out of range"

January 15, 2024

I run the following query in Django using its postgres connection (pyscopg2 lib):

SELECT a.trade_date, a.ticker, a.company_name, a.cusip, a.shares_held, a.nominal, 
        a.weighting, b.weighting "previous_weighting", ABS(a.weighting - b.weighting) "weighting_change"
FROM t_ark_holdings a LEFT JOIN t_ark_holdings b 
    ON a.etf_ticker=b.etf_ticker AND a.ticker=b.ticker
        AND b.trade_date=(SELECT MAX(trade_date) FROM t_ark_holdings WHERE trade_date<a.trade_date)
-- THIS MIX is causing the error
WHERE a.etf_ticker = %s AND LOWER(a.ticker) NOT LIKE 'fake_%'
--
    AND a.weighting<>b.weighting
    AND a.trade_date = (SELECT MAX(trade_date) FROM t_ark_holdings)
ORDER BY a.trade_date DESC, "weighting_change" DESC, a.ticker

When I use "a.etf_ticker = %s" And "NOT LIKE ‘fake_%’", an "IndexError: tuple index out of range" is raised, if I use one or the other, the query works fine.
It seems like the driver is looking for another variable corresponging to ‘%’ in "LIKE ‘fake_%’".
I am curious on how to format/write correctly my query so that it accepts variables and a fixed LIKE reference.
Thank you

Using python 3.10, psycocp2 latest and django 4

>Solution :

psycopg thinks every % is a variable. To escape them, double the percents:

SELECT a.trade_date, a.ticker, a.company_name, a.cusip, a.shares_held, a.nominal, 
        a.weighting, b.weighting "previous_weighting", ABS(a.weighting - b.weighting) "weighting_change"
FROM t_ark_holdings a LEFT JOIN t_ark_holdings b 
    ON a.etf_ticker=b.etf_ticker AND a.ticker=b.ticker
        AND b.trade_date=(SELECT MAX(trade_date) FROM t_ark_holdings WHERE trade_date<a.trade_date)
WHERE a.etf_ticker = %s AND LOWER(a.ticker) NOT LIKE 'fake_%%'
    AND a.weighting<>b.weighting
    AND a.trade_date = (SELECT MAX(trade_date) FROM t_ark_holdings)
ORDER BY a.trade_date DESC, "weighting_change" DESC, a.ticker

Or make 'fake_%' another parameter to your query (eg query: NOT LIKE %s, args: ('fake_%',).