Java包Apache DBUtils有一个很好用特性是, 它能自动根据resultset的结果生成JavaBean对象, 用起来很方便. Apache DBUtils, http://commons.apache.org/dbutils Automatically populate JavaBean properties from ResultSets. Yo
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://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