Updating Oracle DB via pyodbc hangs

Question

I'm having issues with pyodbc used with an Oracle DB. I can do select statements with results coming back, however when I try to update a record, the program seems to hang during the "conn.execute(query)" line with no error messages. I have to force quit the application.

Running a select statement retrieves the expected result. Updating that same record will hang. I've also used this definition on other applications with no issues on a MSSQL DB with selects and updates working properly. I've also checked that the user / pass do have read and write permissions

import pyodbc

def connect_odbc(conn_str, query, commit=False):
    """
    Returns result set from connection string.
    If commit is set to true, it will write to DB as well
    """
    if commit:
        conn = pyodbc.connect(conn_str)
        cursor = conn.cursor()
        cursor.execute(query)  ### <--- Program hangs here
        cursor.commit()
        cursor.close()
        return True
    else:
        conn = pyodbc.connect(conn_str)
        cursor = conn.cursor()
        cursor.execute(query)
        results = cursor.fetchall()
        cursor.close()

        return results

## Main Program ##
query = "select * from multishipdtl where cartonid = '100000000060506';"
queryupdate = "update multishipdtl set trackid='88888888' where cartonid = '100000000060506';"

conn_str = '''DSN=syntest;UID=userid;PWD=userpass'''
results = connect_odbc(conn_str, query)  ### This connects ok with expected results

connect_odbc(conn_str, queryupdate, commit=True)  ### Update statement hangs

score 0 · Accepted Answer · answered Aug 23 '19 at 16:30

If the program hangs when executing cursor.execute("UPDATE ..."), the first thing I'd check is if it's waiting for the database to respond: How do you show SQL executing on an Oracle database?

If the DB shows your session running the UPDATE, most likely it's because another transaction has locked the rows you're trying to update - see Oracle database is hanging infinitly in UPDATE queries; otherwise check V$SESSION's status and wait_time, and V$SESSION_WAIT's event. You might also want to try to reproduce this by running the UPDATE from an SQL client (sans Python).
If it's not the DB, check the Python's call stack for hints. It should be printed when you abort the program by pressing CTRL+C, if not - try Showing the stack trace from a running Python application

An ODBC trace log might be helpful too.

Updating Oracle DB via pyodbc hangs

1 Answers1