SQL UPDATE statement error in python code

Question

I am trying to write a python function that updates a postgres database. The table to be updated is given (department) Below is the function I wrote:

def modify_dept_budget(deptName, newBudget):
  connection = None
  try:
    connection  = connector() # This is a function I wrote to connect so I can hide my credentials.
    cursor1 = connection.cursor()
    query1 = f"select dept_name from department"
    cursor1.execute(query1)
    results1 = cursor1.fetchall()
    results1 = [result[0] for result in results1]
    if deptName in results1:
        idx = results1.index(deptName)
        print(results1[idx])
        cursor2 = connection.cursor()
        query = f"UPDATE department SET budget = %s WHERE dept_name == {deptName}"
        cursor2.execute(query, [newBudget])
        cursor3 = connection.cursor()
        cursor3.execute(f'SELECT * FROM department')
        results2 = cursor3.fetchall()
        headers = [item[0] for item in cursor3.description]
        df = pd.DataFrame(data = results2, columns = headers)
        print(df)
  except(Exception, psycopg2.DatabaseError) as error:
    print(error)
  finally:
    if connection:
      cursor1.close()
      cursor2.close()
      cursor3.close()
      connection.close()

When I run modify_dept_budget('Music', 85000), I get the following error:

Music
column "music" does not exist
LINE 1: ...PDATE department SET budget = 85000 WHERE dept_name == Music
                                                                  ^

---------------------------------------------------------------------------
UnboundLocalError                         Traceback (most recent call last)
<ipython-input-77-16e7278e3358> in <module>
----> 1 modify_dept_budget('Music', 85000)

<ipython-input-76-80361b6ddf35> in modify_dept_budget(deptName, newBudget)
     26       cursor1.close()
     27       cursor2.close()
---> 28       cursor3.close()
     29       connection.close()

UnboundLocalError: local variable 'cursor3' referenced before assignment

Can anyone help me figure out what is going on?

Use the proper parameter passing as shown here Parameters. The issue is deptName not being passed in as a properly quoted string e.g. 'Music'. While you are there read the note that starts Warning Never, never, NEVER ... for why you don't want to do what you did. — Adrian Klaver
– Adrian Klaver, Commented Mar 30, 2022 at 22:08

Tim Roberts · Accepted Answer · 2022-03-30 22:06:55Z

0

You have two problems. First, SQL does not use the double == for the equality test. Just plain =. Second, that string literal needs to be quoted. Unless you need a table name or a field name, you won't be using f-strings with SQL. So, do:

        query = "UPDATE department SET budget = %s WHERE dept_name = %s"
        cursor2.execute(query, [newBudget, deptName])

answered Mar 30, 2022 at 22:06

Tim Roberts

55.2k4 gold badges28 silver badges41 bronze badges

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

Collectives™ on Stack Overflow

SQL UPDATE statement error in python code

1 Answer 1

Comments

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Related