CODEWITHSUNDEEP
pythonpython-3.xdb2pyodbc
Pintang
Pintang

Reputation: 438

pyodbc not committing changes to db2 database

I am trying to update my db2 database using pyodbc in python. The sql statement runs normally without errors on the database directly. when I run the code below, I get no errors and the code executes successfully but when I query the database, the changes did not save.

try:
    conn2 = pyodbc.connect("DRIVER={iSeries Access ODBC Driver};SYSTEM="+ Config_Main.iseriesServer +";DATABASE="+ Config_Main.iseriesDB +";UID="+ Config_Main.iseriesUser +";PWD=" + Config_Main.iseriesPass)
    db2 = conn2.cursor()

    for row in encludeData:
        count = len(str(row[2]))
        srvid = row[2]
        if count < 10:
            sql3 = "UPDATE SVCEN2DEV.SRVMAST SET svbrch = ? WHERE svtype != '*DCS-' AND svacct = ? AND svcid LIKE '%?' and svbrch = ?"
            db2.execute(sql3, (row[4],row[1],"%" + str(srvid),row[5]))
        else:
            sql3 = "UPDATE SVCEN2DEV.SRVMAST SET svbrch = ? WHERE svtype != '*DCS-' AND svacct = ? AND svcid = ? and svbrch = ?"
            db2.execute(sql3, (row[4],row[1],srvid,row[5]))
        conn2.commit()
except pyodbc.Error as e:
    logging.error(e)

I have tried setting conn2.autocommit = True. and I have also tried moving the conn2.commit() inside of the for loop to commit after each iteration. I also tried a different driver {IBM i Access ODBC Driver}

EDIT:

Sample of encludeData

['4567890001','4567890001','1234567890','1234567890','foo','bar']

After changing the except statement to grab general errors, the code above now produces this error:

IntegrityError('23000', '[23000] [IBM][System i Access ODBC Driver][DB2 for i5/OS]SQL0803 - Duplicate key value specified. (-803) (SQLExecDirectW)')

Upvotes: 2

Views: 1806

Answers (1)

Parfait
Parfait

Reputation: 107567

As OP found out, the application layer language, Python, may not raise specific database exceptions such as duplicate index or foreign key issues and hence will silently fail or will be logged on server side. Usually errors that affect actual SQL queries to run like incorrect identifiers and syntax errors will raise an error on client side.

Therefore, as best practice in programming it is necessary to use exception handling like Python's try/except/finally or the equivalent in other general purpose languages that interface with any external API like database connections in order to catch and properly handle runtime issues.

Below will print any exception on statements raised in the try block including connection and query execution. And regardless of success or fail will run the finally statements.

try:
    conn2 = pyodbc.connect(...)
    db2 = conn2.cursor()

    sql = "..."
    db2.execute(sql, params)
    conn2.commit()

except Exception as e:
    print(e)

finally:
    db2.close()
    conn2.close()

Upvotes: 3

Related Questions