Transcript pythonx - CERN Indico

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.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)",
connection.commit()
cursor.close()
#end of thread body
pool.release(connection)
{'a':first,'b':last} )
Connection API
• Functions
o
o
o
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
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
o
o
o
o
o
o
o
o
o
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 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