Problem: Commit no longer works
408518Sep 14 2010 — edited Sep 16 2010This is a puzzler - we have many python apps the do inserts and updates to Oracle (10.1.0.4) using cx_oracle 4.4.1 and python 2.5.1 under Windows XP Pro - they all work
writing a new app I found I could no longer do an insert - to be more specific: I can do an insert or update but the commit that fails!
after performing a commit, the changes do not seem to be committed to the db and do not show up in another oracle session. They do not persist.
this behavior happens on 3 separate computers, against 3 separate oracle instances, yet the previously written py apps are working just fine as are apps written in C and C++.
I can perform a create table command (DDL), and a select statement (DML) but cannot commit DML changes
any ideas?
is there a way to test error or sql execution status from within python similar to sqlca.sqlcode in C?
here is sample code:
insert_test.py
//////////////////////////////////////////////////////////////////
import cx_Oracle
## params
CX_LOGON = 'myuser/mypword@myserver'
ORACLE_CHUNK=1000
## open 2 separate connections (oracle sessions)
dbc1 = cx_Oracle.Connection(CX_LOGON)
dbc2 = cx_Oracle.connect(CX_LOGON)
## create 2 separate cursors
crsr1 = dbc1.cursor()
crsr1.arraysize = ORACLE_CHUNK
crsr2 = dbc2.cursor()
crsr2.arraysize = ORACLE_CHUNK
## result lists
result1=[]
count1=[]
count2=[]
## our sql statements
sql_drop = "DROP TABLE PYTEST"
sql_create = "CREATE TABLE PYTEST (name varchar2(20))"
sql_trunc = "TRUNCATE TABLE PYTEST"
sql_select = "SELECT name FROM PYTEST"
sql_insert1 = "INSERT INTO PYTEST VALUES ('xxx')"
sql_insert2 = "INSERT INTO PYTEST VALUES ('yyy')"
sql_count = "SELECT COUNT(*) FROM PYTEST"
sql_update = "UPDATE PYTEST SET NAME = 'zzz' WHERE NAME = 'yyy'"
## first create the table - we only do this once
##crsr1.execute(sql_drop)
##crsr1.execute(sql_create)
## clear out the table
crsr1.execute(sql_trunc)
## check how many rows
crsr1.execute(sql_count)
count1 = crsr1.fetchall()
if(len(count1)>0):
nrows = int(count1[0][0])
else:
nrows = 0
print "before insert we have ", nrows, " rows"
## now insert a row using same connection
crsr1.execute(sql_insert1)
dbc1.commit;
## check how many rows
crsr1.execute(sql_count)
count1 = crsr1.fetchall()
if(len(count1)>0):
nrows = int(count1[0][0])
else:
nrows = 0
print "after insert we have ", nrows, " rows using same connection"
## now insert another row using same connection
crsr1.execute(sql_insert2)
dbc1.commit;
## check how many rows
crsr1.execute(sql_count)
count1 = crsr1.fetchall()
if(len(count1)>0):
nrows = int(count1[0][0])
else:
nrows=0
print "after insert we have ", nrows, " rows using same connection"
## get table contents
crsr1.execute(sql_select)
result1 = crsr1.fetchall()
if(nrows>0):
print "here are the rows: "
for result in result1:
name = result[0]
print " name = ", name
## now do an update using same connection
crsr1.execute(sql_update)
dbc1.commit;
## check how many rows
crsr1.execute(sql_count)
count1 = crsr1.fetchall()
if(len(count1)>0):
nrows = int(count1[0][0])
else:
nrows=0
print "after update we have ", nrows, " rows using same connection"
## get table contents
crsr1.execute(sql_select)
result1 = crsr1.fetchall()
if(nrows>0):
print "here are the rows after update: "
for result in result1:
name = result[0]
print " name = ", name
## now check how many rows using 2nd connection
crsr2.execute(sql_count)
count2 = crsr2.fetchall()
if(len(count2)>0):
nrows2 = int(count2[0][0])
else:
nrows2 = 0
print "second connection sees ", nrows2, " rows"
## lets' try swinging a chicken over our heads and commit both connects again
dbc1.commit
dbc2.commit
print "end - after dual commits - check row count externally with sql*plus"
/////////////////////////////////////
Mike