Python & Oracle

1. Python & Oracle • Requirements • Oracle Instant Client • cx_Oraclemodule http://cx-oracle.sourceforge.net/ • Installation • Windows: Win Installer • Linux: RPM orcx_Oracle.so

2. Example: accessing database importcx_Oracle connection = cx_Oracle.connect('username/password@localhost') cursor = connection.cursor() bind_vars={'uid':25} sql='SELECT id,Firstname,Lastname FROM TB_NAME where id>:uid‘ cursor.execute( sql, bind_vars) rows = cursor.fetchall() for id,firstname,lastnameinrows: print str(id)+' '+firstname+' '+lastname+"\n“ cursor.close() connection.close()

3. Example: connection pooling importcx_Oracle pool = cx_Oracle.SessionPool( USER, PASSWORD, TNS, 1, #min number of sessions controlled by pool 3, #max number of sessions controlled by pool 1, #additional sessions to be opened per acquire DB.Connection, #connection type True) #OCI_THREADED pool.timeout = 120 #idle session timeout #thread body first='Cristiano' lase='Ronaldo' connection = pool.acquire() cursor = connection.cursor() cursor.execute("insert into players values (:a,:b)", {'a':first,'b':last} ) connection.commit() cursor.close() #end of thread body pool.release(connection)

4. Connection API • Functions • begin() - explicitlybegin a new transaction • cancel() - cancela long-running transaction • close() - closeconnection • commit() - commitany pending transactions to the database • cursor() - returnsnewcursorobjects • ping() - testsif the connection is still active • rollback() - rollbackany pending transactions • Attributes • autocommit - read-write, autocommitmode is on or off • current_schema - read-write,sets the current schema for the session • password - read-write • encoding - read-only, character set in use by the Oracle client • stmtcachesize - read-write, specifies the size of the statement cache • tnsentry - read-only, returns the TNS entry of the database • username - readonly • version - read-only, version of the database

5. Cursor API • Functions • callfunc(name, returnType, parameters=[], keywordParameters = {}) • callproc(name, parameters=[], keyewordParameters = {}) • connection() - returns a reference to the connection object • bindnames() - return list of bind variable names • execute(statement[, parameters], **keywordParameters) • executemany(statement, parameters) • fetchall() • fetchmany([numRows=cursor.arraysize]) • fetchone() - fetching of nextrow • next() - likefetchone() • parse(statement)- doesparsingonly • prepare(statement[, tag]) – doespreparation of thestatement • close() • Attributes • arraysize-  read-write, number of rows to fetch • bindvars - read-onle, bind variables used for the last execute • rowcount- read-only, number of rowsfatched