psycopg2 syntax error at or near "UPDATE"

Question

I've read some q&a about "syntax error at or near" but none could solve my issue.

A sample of the error:

Traceback (most recent call last):
  File "<stdin>", line 1, in <module>
psycopg2.ProgrammingError: syntax error at or near "UPDATE"
LINE 1: DECLARE "teste" CURSOR WITHOUT HOLD FOR UPDATE applicant SET...
                                                ^

Please, note the ^ right on the UPDATE. I've tested the update script on pgadmin 4 and everything worked fine.

The script is really simple:

UPDATE applicant
SET cv_entities = %s
WHERE id = %s

My code is basically:

def _connect_database():
    return psy.connect(
        dbname=settings.DATABASE['DBNAME'],
        host=settings.DATABASE['HOST'],
        port=settings.DATABASE['PORT'],
        user=settings.DATABASE['USER'],
        password=settings.DATABASE['PASSWORD'],
        application_name=settings.env
    )

# Connects to database
conn = _connect_database()
# Creats a named cursor
cur = conn.cursor('test')

# Execute
cur.execute(update_script, ('{"json": "test"}', '11125ba0-748f-11e8-b1d0-d8108ee3at21'))

I've explicitly written the script on the execute method as well as the parameters, but I still get the same error:

cur.execute("UPDATE applicant SET cv_entities = 'json here' WHERE id = '11125ba0-748f-11e8-b1d0-d8108ee3at21'")

Note that I even removed the double quotes from the first parameter ('json here')

Am I missing something?!

Answer 1

With

cur = conn.cursor('test')

you are trying to open a server side cursor. Per the documentation:

cursor(name=None, cursor_factory=None, scrollable=None, withhold=False)

Return a new cursor object using the connection.

If name is specified, the returned cursor will be a server side cursor (also known as named cursor). Otherwise it will be a regular client side cursor.

Use just

cur = conn.cursor()

Server side (named) cursors can be used only for SELECT or VALUES queries. They implement Postgres cursors:

query

A SELECT or VALUES command which will provide the rows to be returned by the cursor.

With named cursors the resulting data is gathered on the server and sent (maybe partialy) to the client when needed (fetched). The data is stored only during the transaction in which the cursor was opened, so commit or rollback releases it.

Client side (unnamed) cursors give the possibility to execute any valid SQL query. A possible resultset is sent to the client immediately after executing the query.

It's possible to use named and unnamed cursors utilizing a single connection but if you want to do this concurrently, you should use two separate connections.