psycopg2.errors.SyntaxError: syntax error at or near

Question

Psycopg2 has been giving me this error whenever I try to update a row:

Traceback (most recent call last):
  File "p:/Projects/Freelance/Optometry_Inventory/code/tester.py", line 39, in <module>
    new_user.updateUser(*(fname, lname, username, role, hashed_pwd, user_id))
  File "p:\Projects\Freelance\Optometry_Inventory\code\UserDC.py", line 64, in updateUser
    cursor.execute(update_query, args)
psycopg2.errors.SyntaxError: syntax error at or near "lname"
LINE 1: UPDATE users SET fname= ('a'), SET lname= ('b'),

When I run the following function:

def updateUser(self, user_id):
        self.user_id = user_id
        update_query = """UPDATE users SET fname= %s, SET lname= (%s),
         SET username= (%s), SET user_role= (%s), SET h_pwd= (%s), WHERE user_id= (%s)"""
        with pool() as cursor:
            cursor.execute(update_query, (self.fname, self.lname, self.user_name,
                           self.user_role, self.user_pwd, self.user_id))

I have tried just updating a single field and I still get the same error. Is there something more I am missing? The error code isn't giving me much information.

You only need one SET at the beginning and you have to remove the comma before the WHERE. And you can get rid of the parentheses in your SQL statement. — Matthias
– Matthias, Commented Aug 3, 2021 at 19:40

Henry Harutyunyan · Accepted Answer · 2021-08-03 19:40:08Z

2

The issue is that you redundantly write SET for every modified column, one SET is more than enough :)

def updateUser(self, user_id):
    self.user_id = user_id
    update_query = """
        UPDATE users 
        SET fname= %s, 
            lname= (%s),
            username= (%s), 
            user_role= (%s),
            h_pwd= (%s) 
        WHERE user_id= (%s)"""
        with pool() as cursor:
            cursor.execute(update_query, (self.fname, self.lname, self.user_name,
                           self.user_role, self.user_pwd, self.user_id))

answered Aug 3, 2021 at 19:40

Henry Harutyunyan

2,4351 gold badge19 silver badges23 bronze badges

Sign up to request clarification or add additional context in comments.

6 Comments

Henry Harutyunyan Over a year ago

@le-travie let me know if this works fine

le_travie Over a year ago

Yes that worked. Thank you, I realized I had way too many sets #facepalm

Henry Harutyunyan Over a year ago

hehe yeah that happens

Henry Harutyunyan Over a year ago

@le_travie and now you can :)

Henry Harutyunyan Over a year ago

@le_travie hehe no worries at all

|

Adrian Klaver · Accepted Answer · 2021-08-03 21:31:22Z

If you want to save yourself some typing there is also:

import psycopg2

con = psycopg2.connect(...)
cur = con.cursor()
update_query = """
        UPDATE users 
        SET 
         (fname, lname, username,user_role, h_pwd)
          =
         (%s, %s, %s, %s, %s)
        WHERE user_id= (%s)"""
cursor.execute(update_query, (self.fname, self.lname, self.user_name,
                           self.user_role, self.user_pwd, self.user_id))

If you are doing multiple updates at a time you might also want to take a look at execute_values. There is an UPDATE example in the execute_values section of the link.

Collectives™ on Stack Overflow

psycopg2.errors.SyntaxError: syntax error at or near

2 Answers 2

6 Comments

Comments

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

6 Comments

Comments

Related