Java包Apache DBUtils有一个很好用特性是, 它能自动根据resultset的结果生成JavaBean对象, 用起来很方便.
Apache DBUtils, http://commons.apache.org/dbutils
Automatically populate JavaBean properties from ResultSets. You don't need to manually copy column values into bean instances by calling setter methods. Each row of the ResultSet can be represented by one fully populated bean instance.
受此启发, 自己造了个轮子, 做了个Python版的 DbRowFactory, 功能相似, 完成Relational-->Object的单向mapping. 只要数据模块符合Python Database API Specification v2.0, 就可以使用 pyDbRowFactory, 支持python/jython.
代码和例子都在下面的code中. 最新版在github上了， https://github.com/harryliu/pyDbRowFactory
文件名: pyObjectCreator.py
#! /usr/bin/env python #coding=utf-8 import inspect import sys __author__ = 'Wade Liu, <wadeliu2008@gmail.com>' __date__ = '16 Feb 2012' __version__="0001" ##reference doc #http://www.cnblogs.com/sevenyuan/archive/2010/12/06/1898056.html #http://stackoverflow.com/questions/4513192/python-dynamic-class-names #http://stackoverflow.com/questions/1796180/python-get-list-of-al-classes-within-current-module def createInstance(full_class_name,*args,**kwargs): ''' instantiate class dynamically [arguments] full_class_name: full class name that you want to instantiate, included package and module name if has *args: list style arguments in class constructor *kwargs: dict style arguments in class constructor [return] an instance of this full_class_name [example] import pyObjectCreator full_class_name="pck1.cls1" logger=pyObjectCreator.createInstance(full_class_name,'logname') ''' class_meta=getClassMeta(full_class_name) if class_meta!=None: obj=class_meta(*args,**kwargs) else: obj=None return obj def getClassMeta(full_class_name): ''' get class meta object of full_class_name, then we can use this meta object to instantiate full_class_name [arguments] full_class_name: full class name that you want to instantiate, included package and module name if has [return] an instance of this full_class_name [example] import pyObjectCreator full_class_name="pck1.cls1" loggerMeta=pyObjectCreator.getClassMeta(full_class_name) ''' namespace=full_class_name.strip().rsplit('.',1) if len(namespace)==1: class_name=namespace[0] class_meta=_getClassMetaFromCurrModule(class_name) else: module_name=namespace[0] class_name=namespace[1] class_meta=_getClassMetaFromOtherModule(class_name,module_name) return class_meta def _getClassMetaFromCurrModule(class_name): result=None module_name="__main__" for name, obj in inspect.getmembers(sys.modules[module_name]): if inspect.isclass(obj): if name==class_name: result=obj break return result def _getClassMetaFromOtherModule(class_name, module_name): module_meta=__import__(module_name,globals(), locals(),[class_name]) if module_meta!=None: class_meta=getattr(module_meta,class_name) else: class_meta=None return class_meta
文件名: pyDbRowFactory.py
# -*- coding: utf-8 -*- ''' #@summary: DbRowFactory is one common factory to convert db row tuple into user-defined class object. It is supported SqlAlchemy, and any database modules conformed to Python Database API Specification v2.0. e.g. cx_Oracle, zxJDBC #@note: The DbRowFactory will create one row instance based on row class binding, and try to assign all fields' value to the new object. The DbRowFactory maps field and class setter_method/attribute by matching names. If both a setter_method and an attribute match the same field, the setter_method will be chosen. #@see: http://www.python.org/dev/peps/pep-0249/ #Tested under: Python 2.7, Jython2.5.2 #Change log: #version 0001, 09 Nov. 2011, initial version #version 0002, 16 Feb. 2012, use pyObjectCreator to instantiate rowClass #version 0003, 08 Mar. 2012, fromSqlAlchemyResultProxy(), fetchAllRowObjects() functions added ##====================sample begin======= #sample code , file: OracleJdbcSample,py from __future__ import with_statement from com.ziclix.python.sql import zxJDBC from pyDbRowFactory import DbRowFactory class rowClass2(object): def __init__(self): self.owner=None self.tablename=None def setOWNER(self, value): self.owner=value def print2(self): print("ownerName="+self.owner+",tablename="+self.tablename) if __name__=="__main__": #DB API 2.0 cursor sample 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 cursor : cursor.execute("""select tbl.owner, tbl.table_name tablename, tbl.tablespace_name from all_tables tbl""") #use DbRowFactory to bind rowClass2 class defined in pkg1.OracleJdbcSample.py rowFactory=DbRowFactory(cursor, "pkg1.OracleJdbcSample.rowClass2") for rowObject in rowFactory.fetchAllRowObjects(): rowObject.print2() #sqlalchemy sample from sqlalchemy import create_engine engine=create_engine("sqlite:///:memory:", echo=True) sql="""select tbl.owner, tbl.table_name tablename, tbl.tablespace_name from all_tables tbl""" resultProxy=engine.execute(sql) rowFactory=DbRowFactory.fromSqlAlchemyResultProxy(resultProxy, "pkg1.OracleJdbcSample.rowClass2") for rowObject in rowFactory.fetchAllRowObjects(): rowObject.print2() ##====================sample end======= ''' import pyObjectCreator __author__ = 'Wade Liu, <wadeliu2008@gmail.com>' __date__ = '08 Mar 2012' __version__="0003" class DbRowFactory(object): ''' #@summary: DbRowFactory is one common row factory for any database module conformed to Python Database API Specification v2.0. e.g. cx_Oracle, zxJDBC #@note: The DbRowFactory will create one row instance based on row class binding, and try to assign all fields' value to the new object. The DbRowFactory maps field and class setter_method/attribute by matching names. if both a setter_method and an attribute match the same field, the setter_method will be choosed evently. #@see: http://www.python.org/dev/peps/pep-0249/ #@author: wade liu, wadeliu2008@gmail.com ''' FIELD_TO_SETTER=1 FIELD_TO_ATTRIBUTE=2 FIELD_TO_NONE=0 def __init__(self, cursor, rowClassFullName, setterPrefix="set", caseSensitive=False): ''' ##@summary: Constructor of DbRowFactory [arguments] cursor: Db API 2.0 cursor object rowClassFullName: full class name that you want to instantiate, included package and module name if has setterPrefix: settor method prefix caseSensitive: match fieldname with class setter_method/attribute in case sensitive or not ''' self._cursor=cursor self._setterPrefix=setterPrefix self._caseSensitive=caseSensitive self._fieldMemeberMapped=False self._allMethods=[] self._allAttributes=[] self._fieldMapList={} self._rowClassMeta = pyObjectCreator.getClassMeta(rowClassFullName) self._resultProxy=None @classmethod def fromSqlAlchemyResultProxy(cls, resultProxy, rowClassFullName, setterPrefix="set", caseSensitive=False): ''' ##@summary: another constructor of DbRowFactory [arguments] resultProxy: SqlAlchemyResultProxy object, can returned after engine.execute("select 1") called, rowClassFullName: full class name that you want to instantiate, included package and module name if has setterPrefix: settor method prefix caseSensitive: match fieldname with class setter_method/attribute in case sensitive or not ''' factory= cls(resultProxy.cursor, rowClassFullName, setterPrefix, caseSensitive) factory._resultProxy=resultProxy return factory def createRowInstance(self, row ,*args,**kwargs): ''' #@summary: create one instance object, and try to assign all fields' value to the new object [arguments] row: row tuple in a _cursor *args: list style arguments in class constructor related to rowClassFullName *kwargs: dict style arguments in class constructor related to rowClassFullName ''' #step 1: initialize rowInstance before finding attributes. rowObject = self._rowClassMeta(*args,**kwargs) #mapping process run only once in order to gain better performance if self._fieldMemeberMapped==False: #dir() cannot list attributes before one class instantiation self._allAttributes=self._getAllMembers(rowObject) self._allMethods=self._getAllMembers(rowObject) self._fieldMapList=self._mapFieldAndMember() self._fieldMemeberMapped=True #step 2: assign field values i=0 #self._fieldMapList is [{Field1:(member1Flag,member1)},{Field2:(member2Flag,member2)}] for fieldMemberDict in self._fieldMapList: for field in fieldMemberDict: member=fieldMemberDict[field] if member[0]==self.FIELD_TO_NONE: pass else: fieldValue=row[i] if member[0]==self.FIELD_TO_SETTER: m=getattr(rowObject, member[1]) m(fieldValue) elif member[0]==self.FIELD_TO_ATTRIBUTE: setattr(rowObject, member[1], fieldValue) i=i+1 return rowObject def _getAllMembers(self,clazz) : ''' #@summary: extract all user-defined methods in given class #@param param clazz: class object ''' members=[member for member in dir(clazz)] sysMemberList=['__class__','__doc__','__init__','__new__','__subclasshook__','__dict__', '__module__','__delattr__', '__getattribute__', '__hash__', '__repr__', '__setattr__', '__str__','__format__', '__reduce__', '__reduce_ex__', '__sizeof__', '__weakref__'] members=[member for member in members if str(member) not in sysMemberList] return members def _mapFieldAndMember(self): ''' #@summary: create mapping between field and class setter_method/attribute, setter_method is preferred than attribute #field can be extract from cursor.description, e.g. sql: select 1 a, sysdate dt from dual cursor.description: [(u'A', 2, 22, None, 0, 0, 1), (u'DT', 91, 7, None, None, None, 1)] ''' #print(self._cursor.description) fields=[f[0] for f in self._cursor.description] mapList=[] #result is [{Field1:(member1Flag,member1)},{Field2:(member2Flag,member2)}] for f in fields: m= self._getSetterMethod(f) key=f if m: value=(self.FIELD_TO_SETTER,m) else: m= self._getAttribute(f) if m: value=(self.FIELD_TO_ATTRIBUTE,m) else: value=(self.FIELD_TO_NONE,None) mapList.append({key:value}) return mapList def _getAttribute(self, fieldName): ''' #@summary: get related attribute to given fieldname ''' if self._caseSensitive: if fieldName in self._allAttributes: return fieldName else: fieldNameUpper=fieldName.upper() allAttributesMap={} # attributeUpper=attribute for attr in self._allAttributes: allAttributesMap[attr.upper()]=attr if fieldNameUpper in allAttributesMap: return allAttributesMap[fieldNameUpper] def _getSetterMethod(self, fieldName): ''' ##@summary: get related setter method to given fieldname ''' if self._caseSensitive: setter=self._setterPrefix+fieldName if setter in self._allMethods: return setter else: setterUpper=self._setterPrefix+fieldName setterUpper=setterUpper.upper() allMethodMap={} #methodUpper=method for method in self._allMethods: allMethodMap[method.upper()]=method if setterUpper in allMethodMap: return allMethodMap[setterUpper] def _closeResultProxy(self): if self._resultProxy is not None: if self._resultProxy.closed==False: self._resultProxy.close() def fetchAllRowObjects(self): """Fetch all rows, just like DB-API ``cursor.fetchall()``. the cursor is automatically closed after this is called """ result=[] rows=self._cursor.fetchall() for row in rows: rowObject=self.createRowInstance(row) result.append(rowObject) self._cursor.close() self._closeResultProxy() return result def fetchManyRowObjects(self, size=None): """Fetch many rows, just like DB-API ``cursor.fetchmany(size=cursor.arraysize)``. If rows are present, the cursor remains open after this is called. Else the cursor is automatically closed and an empty list is returned. """ result=[] rows=self._cursor.fetchmany(size) for row in rows: rowObject=self.createRowInstance(row) result.append(rowObject) if len(rows) == 0: self._cursor.close() self._closeResultProxy() return result def fetchOneRowObject(self): """Fetch one row, just like DB-API ``cursor.fetchone()``. If a row is present, the cursor remains open after this is called. Else the cursor is automatically closed and None is returned. """ result=None row = self._cursor.fetchone() if row is not None: result=self.createRowInstance(row) else: self._cursor.close() self._closeResultProxy() return result