python + Oracle

[toddyl]

Hi,

I am connecting to an oracle DB using python with the following code:

try:
conn = cx_Oracle.connect('username','password','DB')
curs = conn.cursor()
sql = """SELECT indicator, value FROM mytable WHERE param1 = aram1 AND param2 = aram2"""
curs.execute(sql, param1=param1, param2=param2)
row = curs.fetchone() or None
if row != None:
(ind, flag) = (row[0], row[1])
if ind== 0:
set_prepost( flag )
curs.close()
conn.close()
except:
curs.close()
conn.close()
raise

For each incoming param1 and param2 I need to connect to Oracle, retrieve values from an Oracle Table, then based on the indicator I need to set a column in the DB.

This works fine in testing but when I run a large file through, say containing around 100,000 param1 and param2 entries I start getting ORA-12560 errors.

Can anyone please advise on why I am getting these errors and how to avoid them. Also, any recommendations on how to make this faster/better would be much appreciated.

Thanks,

Tom

 

[Tarwn]

Some basic things I would consider:

Don't close and open your connection for each individual query. This is probably creating some overhead just because your opening and lcosing the connection 100,000 times.

Rather than do the indicator check in your code, do it in your SQL statement. The less you have to wait on the network thefaster things will go. This way you will only get back records that need to be operated on, so less network overhead, less local memory/CPU, etc.

Additionally, if your set_prepost function is simply updating a few fields, change your SQL statement to an UPDATE with a condition that indicator be = 0, plus the params conditions. Unless your doing something fairly complicated (like using the value field to open a file or something similar) then there is no need to do a SELECT and an UPDATE (or whatever set_prepost does). Oracle ought to be able to handle 100,0000 update statements.

-T