CODEWITHSUNDEEP
pythonsqloracle-databasecx-oracle
arcdev
arcdev

Reputation: 31

Python: cx_Oracle cursor.execute() hangs on UPDATE query

I have looked at similar questions but nothing has worked for me so far

So here it is. I want to update my table through a python script. I'm using the module cx_oracle. I can execute a SELECT query but whenever I try to execute an UPDATE query, my program just hangs (freezes). I realize that I need to use cursor.commit() after cursor.execute() if I am updating a table but my code never gets past cursor.commit(). I have added a code snippet below that I am using to debug.

Any suggestions??

Code

import cx_Oracle

def getConnection():
    ip = '127.0.0.1'
    port = 1521
    service_name = 'ORCLCDB.localdomain'
    username = 'username'
    password = 'password'
    dsn = cx_Oracle.makedsn(ip, port, service_name=service_name)  # (CONNECT_DATA=(SERVICE_NAME=ORCLCDB.localdomain)))
    return cx_Oracle.connect(username, password, dsn) # connection

def debugging():
    con = getConnection()
    print(con)
    cur = con.cursor()
    print('Updating')
    cur.execute('UPDATE EMPLOYEE SET LATITUDE = 53.540943 WHERE EMPLOYEEID = 1')
    print('committing')
    con.commit()
    con.close()
    print('done')

debugging()

**Here is the corresponding output: **

<cx_Oracle.Connection to username@(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=127.0.0.1)(PORT=1521))(CONNECT_DATA=(SERVICE_NAME=ORCLCDB.localdomain)))>
Updating

Solution

After a bit of poking around, I found the underlying cause! I had made changes to the table using Oracle SQL Developer but had not committed them, when the python script tried to make changes to the table it would freeze up because of this. To avoid the freeze, I committed my changes in oracle sql developer before running the python script and it worked fine!

Upvotes: 3

Views: 2581

Answers (1)

Roberto Hernandez
Roberto Hernandez

Reputation: 8528

Do you have any option to look in the database ? I mean , in order to understand whether is a problem of the python program or not, we need to check the v$session in the database to understand whether something is blocked. 

select sid, event, last_call_et, status from v$session where sid = xxx

Where xxx is the sid of the session which has connected with python.

By the way, I would choose to commit explicitly after cursor execute

cur.execute('UPDATE EMPLOYEE SET LATITUDE = 53.540943 WHERE EMPLOYEEID = 1')
con.commit()

Hope it helps Best

Upvotes: 0

Related Questions