Inserting multiple rows with PostGIS points with Psycopg3

Question

I'm using psycopg3 to insert multiple rows into a table using the following function:

async def execute_values(self, command_with_placeholders, values) -> None:
    placeholders = sql.SQL(', ').join(sql.Placeholder() * len(values[0]))
    command = sql.SQL(command_with_placeholders).format(placeholders=placeholders)
    async with (await self.conn).cursor() as cursor:
        await cursor.executemany(command, values)

Calling it with the following parameters fails when I include a non empty location:

insert_sessions_command = """
        INSERT INTO sessions (session_id, start_time, last_activity_time, end_time, ip,
            user_agent, country_code, region_code, location)
        VALUES ({placeholders});
    """
    await sql_db.execute_values(insert_sessions_command, [
        ('s1', datetime(2023, 1, 1), datetime(2023, 1, 2), None, '1.2.3.4', None,
         'NZ', 'NZ-CAN', 'ST_SetSRID(ST_MakePoint(172.6306, 43.5320), 4326)'),
    ]

Doing the following using a Postgres console works:

INSERT INTO sessions (session_id, start_time, last_activity_time, end_time, ip,
                user_agent, country_code, region_code, location)
            VALUES ('s1', '2023-01-01', '2023-01-02', null, '1.2.3.4', null,
             'NZ', 'NZ-CAN', ST_SetSRID(ST_MakePoint(172.6306, 43.5320), 4326));

So I guess the psycopg code fails because it encloses the point with quotations, like the following wrong Postgres command:

INSERT INTO sessions (session_id, start_time, last_activity_time, end_time, ip,
                user_agent, country_code, region_code, location)
            VALUES ('s1', '2023-01-01', '2023-01-02', null, '1.2.3.4', null,
             'NZ', 'NZ-CAN', 'ST_SetSRID(ST_MakePoint(172.6306, 43.5320), 4326)');

In both cases the error is

parse error - invalid geometry
HINT:  "ST" <-- parse error at position 2 within geometry
CONTEXT:  unnamed portal parameter $9 = '...'

How do I make the Psycopg code not enclose the point string with quotations to fix it?

Answer 1

As simple example to show what I am getting at:

import psycopg
from psycopg import sql

con = psycopg.connect('dbname=test host=localhost user=postgres')
cur = con.cursor()

sql_str = sql.SQL('select {round}({random}()::numeric, %s)')
cur.execute(sql_str.format(round=sql.Identifier('round'), random=sql.Identifier('random')), [3]).fetchone()
(Decimal('0.357'),)

cur.execute(sql_str.format(round=sql.Identifier('round'), random=sql.Identifier('random')), [2]).fetchone()
(Decimal('0.50'),)