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.kla...@aklaver.com
