jython 访问数据库的方法

jython 访问数据库
基本上有2个方法, 使用 zxJDBC (符合Python DB API2.0规范), 或者直接使用JDBC.

先写点dbexts, dbexts是zxJDBC作者写的一个扩展, 使用该扩展, 可以在jython 的console中做很多事情, 比如查询表结构, 查询表的数据, 导出表数据, 等等. 要使用需要做点配置,
C:\jython2.5.2\Lib\dbexts.ini
[default]
name=local_xe
[jdbc]
name=xe
url=jdbc:oracle:thin:@127.0.0.1:1521:orcl
user=user1
pwd=pwd1
driver=oracle.jdbc.driver.OracleDriver
datahandler=com.ziclix.python.sql.handler.OracleDataHandler



=============================
使用 zxJDBC 访问 Oracle的示例
=============================
from __future__ import with_statement
from com.ziclix.python.sql import zxJDBC
 
#jdbc_url = "jdbc:oracle:thin:@TNSORCL"
#zxJDBC does not support TNS even after java.lang.System.setProperty("oracle.net.tns_admin", r"C:\\oracle\\ora10\\NETWORK\\ADMIN" ) called
jdbc_url="jdbc:oracle:thin:@127.0.0.1:1521:ORCL";
username = "user1"
password = "pwd1"
driver = "oracle.jdbc.driver.OracleDriver"

with zxJDBC.connect(jdbc_url, username, password, driver) as conn:
    with conn.cursor() as cr :
        cr.execute("select * from all_tables")
        rows=cr.fetchall()
        for row in rows:
            print(row)

            
=============================
使用 JDBC 访问 DB2 的示例, 摘自
=============================
http://www.ibm.com/developerworks/cn/data/library/techarticles/dm-0404yang/ , 该文章不错.

from java.lang import *
from java.sql import *
# load DB2 JDBC type 2 driver (app driver)
Class.forName("COM.ibm.db2.jdbc.app.DB2Driver").newInstance()
con = DriverManager.getConnection( 'jdbc:db2:sample','vyang','jythonrocks')
stmt = con.createStatement()
sql='select firstname,lastname,salary from employee where salary > 20000 order by salary'
rs = stmt.executeQuery(sql)
employeeList=[]
while (rs.next()):
    row={}
    row['firstname']=rs.getString(1)
    row['lastname']=rs.getString(2)
    row['salary']=rs.getDouble(3)
    employeeList.append(row)
    
rs.close()
stmt.close()
con.close()
print 'employee salary over $20,000'
print '============================'
print 'firstname lastname salary'
print '============================'
# print the result
for e in employeeList:
    print e['firstname'],e['lastname'],'$'+ str(e['salary'])             
 

备注:

1. jython如果无法找到Oracle 或其它数据库的JDBC driver, 下面这个访问应该很有效:  将Jdbc driver的jar文件名加到jython.jar的manifest.mf文件lib path中, 需要注意manifest.mf苛刻的格式.  这个方法同样适用于, 通过jython调用第三方java类库, 比如jdom等.

2. 参数化查询的示例. 为防止sql 注入, 应避免用%字符操作符拼接sql的方法. 下面的第一种写法, zxJDBC支持, 但第2种写法, zxJDBC不支持, 其它多数符合DB API2的驱动均适用.

cur.execute("select name_last, age from people where name_last=? and age=?", (who, age)) #work when zxJdbc used
cur.execute("select * from people where name_last=:who and age=:age",  {"who": who, "age": age}) #cannot work for zxJDBC