Feedback-ID: i76984098:Fastmail
Message-ID: <c9023cca-a193-4430-912d-5cebe6ce7c4c@aklaver.com>
Date: Thu, 5 Dec 2024 18:00:00 -0800
MIME-Version: 1.0
User-Agent: Mozilla Thunderbird
Subject: Re: Parameter in SQL query being misinterpreted
From: Adrian Klaver <adrian.klaver@aklaver.com>
To: Daniel Johnson <djohnson@progman.us>, psycopg@lists.postgresql.org
References: <dfc11d8e-dc27-4c72-a2aa-87b2150dc331@progman.us>
 <cc13d120-44c1-4d13-b478-57015d54fc1e@aklaver.com>
Content-Language: en-US
In-Reply-To: <cc13d120-44c1-4d13-b478-57015d54fc1e@aklaver.com>
Content-Type: text/plain; charset=UTF-8; format=flowed
Content-Transfer-Encoding: 8bit
Archived-At: 
 <https://www.postgresql.org/message-id/c9023cca-a193-4430-912d-5cebe6ce7c4c%40aklaver.com>
Precedence: bulk

On 12/5/24 17:37, Adrian Klaver wrote:
> On 12/5/24 17:27, Daniel Johnson wrote:
>> Good day!  I've run into a quirk executing an SQL statement and am not 
>> sure if it's a fundamental issue with my query or a bug in psycopg3.
>>
>> In short, I'm inserting/updating a row using both NOW() and 'NOW() 
>> plus a varying value'.  The NOW() value is fine, but what ends up in 
>> the database for the second one is just "NOW() + 4 seconds".  I 
>> finally realized this is because of how the command is being 
>> processed, the variable becomes "$4" and apparently the dollar sign is 
>> lost.
>>
>> I'm guessing this is an example of why one of the bullet points at
>> https://www.psycopg.org/psycopg3/docs/basic/params.html says "The 
>> placeholder must not be quoted".  :-/  I would appreciate any advice 
>> on how to work around this while still passing in the value safely
> 
> Pretty sure the below does not work because the placeholder is becoming 
> part of the literal string:
> 
> NOW() + INTERVAL '%(NextDBLog)s SECOND'
> 
> You might try something like:
> 
> NOW() + INTERVAL || ' ' ||  %(NextDBLog)s || ' SECOND'
> 
> If that does work then you will need to use the psycopg.sql module to 
> build that part of the query
>>


What I ended getting to work:

from psycopg import sql

cur.execute(sql.SQL("select now(), now() + ({} || ' seconds')::interval 
").format(sql.Literal(4)))

cur.fetchone()
(datetime.datetime(2024, 12, 5, 17, 57, 55, 670218, 
tzinfo=zoneinfo.ZoneInfo(key='US/Pacific')),
  datetime.datetime(2024, 12, 5, 17, 57, 59, 670218, 
tzinfo=zoneinfo.ZoneInfo(key='US/Pacific')))


-- 
Adrian Klaver
adrian.klaver@aklaver.com