Sending SQL into a placeholder?

[jvanasco]

I could not find any documentation on this.

I have a statement with a placeholder: INSERT INTO foo (id, timestamp_updated) VALUES (%(id)s, %(timestamp_updated)s);

Instead of a datetime object, I would like to submit the sql statement NOW() AT TIME ZONE 'UTC'. I can't figure out how to set this value to not be escaped.

[dvarrazzo]

You can the psycopg2.sql objects for it. Untested:

sql.SQL("INSERT INTO foo (id, timestamp_updated) VALUES ({id}, {timestamp_updated})") \
    .format(id=sql.Literal(my_id), timestamp_updated=sql.SQL("NOW() AT TIME ZONE 'UTC'")))

Look for these objects in the docs.

[jvanasco]

Thank you for the reply. I had already tried all of the psycopg2.sql objects (for both that value, and the sql query). I guess the API doesn't support the adaptation through .execute?

    _changes = {"id": 3, "timestamp_last_updated": psycopg2.sql.SQL("(CURRENT_TIMESTAMP AT TIME ZONE `UTC`)")}
    _sql = "UPDATE external_rss_feed SET timestamp_last_updated = %(timestamp_last_updated)s WHERE id = %(id)s;"
    txn.execute(_sql, _changes)

It triggers:

<class 'psycopg2.ProgrammingError'>
psycopg2.ProgrammingError: can't adapt type 'SQL'

in:

psycopg2/extras.py", line 146, in execute
   return super().execute(query, vars)

I ended up using a preprocessing step to swap the placeholder with the actual SQL command.

[dvarrazzo]

Correct: with execute you can pass only values, not snippets of SQL expression.

[jvanasco]

Thank you! I didn't see that in the documentation, and every object/api method I tried failed, so I got very confused for a bit.

My preprocessing approach will remain.