jython mysql_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