CODEWITHSUNDEEP
python-3.x
winem
winem

Reputation: 68

cx_oracle - deletes don't work. No errors or exceptions

I'd like to write a script to run several SQL commands in a for-while-loop-construct. Everything works fine so far.. Except for deletes.

Script: 

#!bin/python3.2
# script to remove batches of obsolete stuff from the tracking DB
#

import sys
import getpass
import platform
import cx_Oracle

# print some infos 
print("Python version")
print(("Python version: " + platform.python_version()))
print("cx_Oracle  version: " + cx_Oracle.version)
print("Oracle client: " + str(cx_Oracle.clientversion()).replace(', ','.'))

dbconn = cx_Oracle.connect('xxxx','yyyy', '1.2.3.4:1521/xxxRAC')
print ("Oracle DB version: " + dbconn.version)
print ("Oracle client encoding: " + dbconn.encoding)

cleanupAdTaKvpQuery = "delete from TABLE1 where TABLE2_ID < 320745354908598 and rownum <= 5"
getOldRowsQuery = "select count(*) from TABLE2 where ID < 320745354908598"

dbconn.begin()

cursor = dbconn.cursor() 
cursor.execute(getOldRowsQuery)
rowCnt = cursor.fetchall()

print("# rows (select before delete): " + str(rowCnt))

try:
    cursor.execute(cleanupAdTaKvpQuery)
    rows = cursor.rowcount
except: 
    print("Cleanup Failed.")

cursor.execute(getOldRowsQuery)
rowCnt = cursor.fetchall()

print("# rows (select after delete): " + str(rowCnt))

try: 
    dbconn.commit
    print("Success!")
except:
    print("Commit failed " + arg)

dbconn.close 

print("# of affected rows:" + str(rows))

As you can see in the output. The script runs fine, the results (see rowCnt) are valid and make sense, there are no errors and no exceptions and it does not raise an exception.

Output: 

Python version
Python version: 3.2.3
cx_Oracle  version: 5.2
Oracle client: (11.2.0.3.0)
Oracle DB version: 11.2.0.3.0
Oracle client encoding: US-ASCII
# rows (select before delete): [(198865,)]
# rows (select after delete): [(198860,)]      <--- the result above decreased by 5!
Success!
# of rows:5
(ayemac_ora_cleanup)marcel@mw-ws:~/scripts/python/virt-envs/ayemac_ora_cleanup$

What am I missing or doing wrong? I tried to debug it with several additional select statements, trying to catch exceptions, etc...

Any help is appreciated! Thank you!

UPDATE: Fixed, thanks for the hint with the missing brackets!

Upvotes: 0

Views: 734

Answers (1)

hiro protagonist
hiro protagonist

Reputation: 46899

you are missing the brackets in

dbconn.commit()

without them the command will not raise an exception, but simply do nothing. the same goes for dbconn.close()

Upvotes: 2

Related Questions