Python Oracle Requirements o Oracle Instant Client o

Python & Oracle • Requirements o Oracle Instant Client o cx_Oracle module http: //cx-oracle. sourceforge. net/ • Installation o Windows: Win Installer o Linux: RPM or cx_Oracle. so

Example: accessing database import cx_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, lastname in rows: print str(id)+' '+firstname+' '+lastname+"n“ cursor. close() connection. close()

Example: connection pooling import cx_Oracle pool = cx_Oracle. Session. Pool( 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)", connection. commit() cursor. close() #end of thread body pool. release(connection) {'a': first, 'b': last} )

Connection API • Functions o o o o begin() cancel() close() commit() cursor() ping() rollback() - explicitly begin a new transaction - cancel a long-running transaction - close connection - commit any pending transactions to the database - returns new cursor objects - tests if the connection is still active - rollback any pending transactions • Attributes o o o o autocommit current_schema password encoding stmtcachesize tnsentry username version - read-write, autocommit mode is on or off - read-write, sets the current schema for the session - read-write - read-only, character set in use by the Oracle client - read-write, specifies the size of the statement cache - read-only, returns the TNS entry of the database - read only - read-only, version of the database

Cursor API • Functions o o o o callfunc(name, return. Type, parameters=[], keyword. Parameters = {}) callproc(name, parameters=[], keyeword. Parameters = {}) connection() - returns a reference to the connection object bindnames() - return list of bind variable names execute(statement[, parameters], **keyword. Parameters) executemany(statement, parameters) fetchall() fetchmany([num. Rows=cursor. arraysize]) fetchone() - fetching of next row next() - like fetchone() parse(statement) - does parsing only prepare(statement[, tag]) – does preparation of the statement close() • Attributes o arraysize o bindvars o rowcount - read-write, number of rows to fetch - read-onle, bind variables used for the last execute - read-only, number of rows fatched