WLST and Oracle DB, zxJDBC, Jython...

the zxJDBC doc:
http://www.jython.org/jythonbook/en/1.0/DatabasesAndJython.html
and also here
http://www.jython.org/archive/21/docs/zxjdbc.html

it seems that WLS 10.3.5 incorporates this library (enter zxJDBC in Everything Search)

java weblogic.WLST

from com.ziclix.python.sql import zxJDBC
params = {}
params['serverName'] = 'localhost'
params['databaseName'] = 'xe'
params['user'] = 'DEV_SOAINFRA'
params['password'] = 'DEV_SOAINFRA'
params['port'] = 1521
db = apply(zxJDBC.connectx, ("oracle.jdbc.xa.client.OracleXADataSource",), params)

this fails with "Error: no such method [setPort] using arg type [class java.lang.Integer], value [1521]"


then I try

jdbc_url = "jdbc:oracle:thin:@pierrepc:1521:XE"
username = "DEV_SOAINFRA"
password = "DEV_SOAINFRA"
driver = "oracle.jdbc.xa.client.OracleXADataSource"

conn = zxJDBC.connect(jdbc_url, username, password, driver)

cursor = conn.cursor(1)
cursor.execute("select count(*) from WLI_QS_REPORT_ATTRIBUTE")
print cursor.rowcount
0
cursor.fetchone()
(4.0,)
print cursor.rowcount
1

it seems to work!

cursor.execute("select MSG_LABELS from WLI_QS_REPORT_ATTRIBUTE")
for a in cursor.description:
print a

('MSG_LABELS', 12, 2048, None, None, None, 1)

try also

print cursor.fetchall()
print cursor.fetchmany()

cursor.execute("select MSG_GUID from WLI_QS_REPORT_ATTRIBUTE order by MSG_GUID")
res = cursor.fetchall()
print res

for a in res:
   print a

for a in res:
   print a[0]

To access a specific column without guessing its position:

cursor.execute("select * from SOMETABLE where ID = '29'")

columnNames = []
for item in cursor.description:
 columnNames.append(item[0])
 
for a in cursor.fetchall():
 ID = a[columnNames.index('ID')]
 DSNAME = a[columnNames.index('DSNAME')]
 print 'ID=' + ID
 print 'DSNAME=' + DSNAME

where ID and DSNAME are column names