0

UPDATE: Nevermind folks, I got it working. Turns out you need to commit your actions using connection.commit() each time. Big up my mate user56700.

I'm quite new to coding, I made a program using Python and MySQL that is able to create, read, update and delete tables. For simplicity's sake, updating the table only adds pre-specified elements. The following code is responsible for the process:

mySql_Update_CV_Query = """INSERT INTO valuesTable (CVCode) VALUE (1);""" 

#database = valuesBase

if userRequestUpdate == 1: 
                                  cursor
                                  result4 = cursor.execute(mySql_Update_CV_Query)
                                  cursor.close()
                                  print("CVCode updated successfully.")

mySql_Update_Values_Query = """UPDATE valuesTable
                                            SET factoryValue = 3, customValue = 0
                                            WHERE CVCode = 1;"""

elif userRequestUpdate == 2:
                                  cursor
                                  result4 = cursor.execute(mySql_Update_Values_Query)
                                  cursor.close()
                                  print("userValue updated successfully.")

I ran the exact same queries in MySQL Workbench and they ran just fine, but when I attempted to run them this way, the table wasn't actually getting updated despite the "successful" statement printing.

Radiant379
  • 9
  • 2
  • 1
    You need to commit your changes after executing them. `cursor.commit()` – Cow Apr 04 '23 at 05:51
  • *`INSERT INTO valuesTable (CVCode) VALUE (1);`* is insert and not update. – Akina Apr 04 '23 at 06:04
  • Doesn't work, returns the following error: AttributeError: 'CMySQLCursor' object has no attribute 'commit' – Radiant379 Apr 04 '23 at 06:05
  • Format your code correctly - in python the correctness of indentation is critical. – Akina Apr 04 '23 at 06:07
  • Try to use VALUES instead of VALUE. and add conn.commit() #conn = connection object For ref: https://stackoverflow.com/questions/30842031/attributeerror-mysqlcursor-object-has-no-attribute-commit – dp808139 Apr 04 '23 at 06:52

1 Answers1

1

I am glad you made your code work, I'm writing this so other users with your problem can have an example on how to use this:

make sure your commit happens before you close the cursor and, of course, don't forget to manage the rollback as well if an error triggers

mySql_Update_CV_Query = """INSERT INTO valuesTable (CVCode) VALUE (1);""" 

#database = valuesBase

if userRequestUpdate == 1: 
    cursor
    try:
        result4 = cursor.execute(mySql_Update_CV_Query)
        cursor.commit() # <--- this
        print("CVCode updated successfully.")
    except Exception as e:
        print(f"Error triggered: {e}")
        cursor.rollback() # <--- if something goes wrong
    cursor.close()

mySql_Update_Values_Query = """UPDATE valuesTable
    SET factoryValue = 3, customValue = 0
    WHERE CVCode = 1;"""

elif userRequestUpdate == 2:
    cursor
    try:
        result4 = cursor.execute(mySql_Update_Values_Query)
        cursor.commit() # <--- this
        print("userValue updated successfully.")
    except Exception as e:
        print(f"Error triggered: {e}")
        cursor.rollback() # <--- if something goes wrong
    cursor.close()
Phoenix
  • 63
  • 7