Allow % literals when using named parameters

[adriangb]

I generally understand why this example doesn't work with psycopg:

import psycopg

conn = psycopg.connect('postgres://postgres:postgres@localhost:54320/postgres')
conn.autocommit = True
with conn.cursor() as cur:
    cur.execute('DROP TABLE IF EXISTS test')
    cur.execute('CREATE TABLE test (ts TIMESTAMPTZ NOT NULL, msg TEXT NOT NULL)')
    print(
        cur.execute(
        """
        SELECT *
        FROM test
        WHERE ts > %(since)s AND msg LIKE '%bar%'
        """,
        {'since': '2015-10-21T14:28:00'},
        ).fetchall()
    )

psycopg.ProgrammingError: positional and named placeholders cannot be mixed

And if you use positional parameters you get:

psycopg.ProgrammingError: only '%s', '%b', '%t' are allowed as placeholders, got '%''

Could something be done to make it work?
Two things I can see:

1. A % inside of a literal should always be ignored
2. In the case named placeholders are being used, more cases can be ignored (e.g. SELECT 'A' % 'a' for pgtrgm)

[dvarrazzo]

We will not parse the SQL statement in order to figure out if a % is in a string literal. Not gonna happen.

You can use %%, as documented, if you have a literal % in your SQL query. Alternatively %bar% can come from an external parameter. Alternatively you can use the ~ operator instead of LIKE.

[adriangb]

We will not parse the SQL statement in order to figure out if a % is in a string literal. Not gonna happen.

Ok. I didn't know if you were parsing them or not. It was just an observation.

You can use %%, as documented, if you have a literal % in your SQL query. Alternatively %bar% can come from an external parameter. Alternatively you can use the ~ operator instead of LIKE.

All of these options require editing the query. I'm running user-defined SQL so that's not an option. I worked around by not using parameters at all.

I'll note that although I much, much prefer psycopg's named parameter support this is not an issue with asyncpg's plain postgres parameter syntax:

import asyncio
import datetime
import asyncpg

async def main() -> None:
    conn = await asyncpg.connect('postgres://postgres:postgres@localhost:54320/postgres')
    await conn.execute('DROP TABLE IF EXISTS test')
    await conn.execute('CREATE TABLE test (ts TIMESTAMPTZ NOT NULL, msg TEXT NOT NULL)')
    print(
        await conn.fetch(
            """
            SELECT *
            FROM test
            WHERE ts > $1 AND msg LIKE '%bar%'
            """,
            datetime.datetime(2015, 10, 21, 14, 28, 0),
        )
    )

asyncio.run(main())

Is there a world where psycopg supports the $1 syntax for cases like this where parsing the % is complex and leaving it up to the database makes more sense?

[dvarrazzo]

All of these options require editing the query. I'm running user-defined SQL so that's not an option.

Not quite, you have already edited the query, because you added. %(since)s.

And yes, we are planning to release RawCursor objects to support postgres-style parameters and no query manipulation at all.

[adriangb]

I’m not sure I follow. I can change how %(since)s ends up there but assume the rest of the WHERE clause I cannot edit without parsing the SQL myself (which I don’t want to do for the same reasons psycopg probably avoids it)

[dvarrazzo]

If you have the original query, which was transformed by something that added your %(since)s, then you need to do no parsing, you just have to blindly replace % -> %% and then inject your placeholders.

[adriangb]

Makes sense. I assume this is what you mean:

import psycopg

conn = psycopg.connect('postgres://postgres:postgres@localhost:54320/postgres')
conn.autocommit = True
with conn.cursor() as cur:
    cur.execute('DROP TABLE IF EXISTS test')
    cur.execute('CREATE TABLE test (ts TIMESTAMPTZ NOT NULL, msg TEXT NOT NULL)')
    cur.execute('INSERT INTO test VALUES (%s, %s)', ('2020-09-21T14:28:00', 'bar'))
    where = "msg LIKE '%bar%'"
    print(
        cur.execute(
        f"""
        SELECT *
        FROM test
        WHERE ts > %(since)s AND {where.replace('%', '%%')}
        """,
        {'since': '2015-10-21T14:28:00'},
        ).fetchall()
    )

[bitner]

@adriangb have you tried using a Raw Cursor: https://www.psycopg.org/psycopg3/docs/advanced/cursors.html#raw-query-cursors which uses libpq/asyncpg "$1" notation?

[dvarrazzo]

raw cursors will be available from 3.2, still to be released, they are not available in 3.1.x

[adriangb]

Nope I had never heard of them but I suspect it solves this problem since asyncpg has no issues with the use case

[adriangb]

My solution for now was to just template the whole query into a string. I’m not worried about sql injection, since the query is being run by an isolated user with limited permissions, but it would be nice to get the prepared statement cache. Will raw cursors support the internal prepared statement cache?

[dvarrazzo]

Yes

[adriangb]

Nice that sounds like the right solution then once it’s released!