CODEWITHSUNDEEP
pythonpostgresqlpsycopg2
le_travie
le_travie

Reputation: 19

psycopg2.errors.SyntaxError: syntax error at or near

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.

Upvotes: 1

Views: 5269

Answers (2)

Adrian Klaver
Adrian Klaver

Reputation: 19570

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.

Upvotes: 1

Henry Harutyunyan
Henry Harutyunyan

Reputation: 2415

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))

Upvotes: 2

Related Questions