CODEWITHSUNDEEP
pythonmysqlsqlmysql-workbenchmysql-connector-python
Radiant379
Radiant379

Reputation: 9

Why doesn't this update query work when I run it through Python?

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.

Upvotes: 0

Views: 356

Answers (2)

Elina
Elina

Reputation: 23

UPD.: this worked:

if table:
            print(table)
            with pyodbc.connect('DRIVER={SQL Server Native Client 11.0};SERVER='+server+';DATABASE='+database+';UID='+username+';PWD='+password) as cnxn:
                with cnxn.cursor() as cursor:
                    ctr = 0
                    for i in table:
                        t = (rentId, area, rental, fap, pap, startDate, endDate, agreement, area, agreementId, supplementaryId, rentId, area,rentId, building, floor, startDate, endDate, agreement, lesseId, typeId, dealId, stageNumber, rental, 0, 0, signDate, file, lf0, lf1, None, agreementId,supplementaryId,)

                        try:
                            query = """
                            IF EXISTS (SELECT * FROM [dbo].[rent] WHERE rentId = ? AND (btiId = ? OR btiId IS NULL))
                            BEGIN
                                UPDATE [dbo].[rent] 
                                SET rental = ?, fixed = convert(decimal(8,2),?), variable = convert(decimal(8,2),?), 
                                    startDate = ?, endDate = ?, agreement = ?, btiId = ?, 
                                    agreementId = ?, supplementaryId = ? 
                                WHERE rentId = ? AND (btiId = ? OR btiId IS NULL)
                            END
                            ELSE
                            BEGIN
                                INSERT INTO [dbo].[rent] ([rentId],[building],[floor],[startDate],[endDate],
                                                          [agreement],[lesseeId],[typeId],[dealId],[stageNumber],
                                                          [rental],[fixed],[variable],[signDate],[fileId],
                                                          [lff1],[lf0],[btiId],[agreementId],[supplementaryId]) 
                                VALUES(?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,convert(decimal(18,2),?),convert(decimal(18,2),?),?,?,?)
                            END
                            """
                            cursor.execute(query, t)
                            cnxn.commit()
                            ctr += 1
                        except pyodbc.Error as e:
                            print("Error:", e)
                            print("Query:", query)
                            print("Data:", t)

                    print(f"Total rows processed: {ctr}")

I have problem with committing the update query within a loop, can it be somehow cured?(

for i in table:
                    t = (rentId, area, rental, fap, pap, startDate, endDate, agreement, area, agreementId, supplementaryId, rentId, area,rentId, building, floor, startDate, endDate, agreement, lesseId, typeId, dealId, stageNumber, rental, 0, 0, signDate, file, lf0, lf1, None, agreementId,supplementaryId,)
                    
                    cnxn = pyodbc.connect('DRIVER={SQL Server Native Client 11.0};SERVER='+server+';DATABASE='+database+';UID='+username+';PWD='+ password)
                    cursor = cnxn.cursor()
                    query = "IF EXISTS (SELECT * FROM [dbo].[rent] WHERE rentId = ? and btiId = ?) BEGIN \
                    UPDATE [dbo].[rent] SET rental =  ?, fixed = convert(decimal(8,2),?), variable = convert(decimal(8,2),?), startDate = ?, endDate = ?, agreement = ?, btiId = ?, agreementId = ?, supplementaryId =? WHERE rentId = ? and btiId = ?  END \
                    ELSE BEGIN INSERT INTO [dbo].[rent] ([rentId],[building], [floor], [startDate], [endDate] \
                    ,[agreement],[lesseeId],[typeId],[dealId],[stageNumber],[rental],[fixed],[variable] \
                    ,[signDate],[fileId],[lff1],[lf0],[btiId],[agreementId],[supplementaryId]) \
                    VALUES(?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,convert(decimal(18,2),?),convert(decimal(18,2),?),?,?,?) END"
    
                    cursor.execute(query,t)
                    cnxn.commit()
                    cursor.close()

Upvotes: 0

Phoenix
Phoenix

Reputation: 93

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

Upvotes: 2

Related Questions