python oracle orm_【原创】Python ORM 实现及python在linux下连接oracle

ORM “Object Relational Mapping”，即对象-关系映射，就是把关系数据库的一行映射为一个对象，也就是一个类对应一个表，这样，写代码更简单，不用直接操作SQL语句。

orm模块：orm.py

#!/usr/bin/env python#coding=utf-8

'Simple ORM using metaclass'

__author__ = 'Spook Zhang'

classField(object):def __init__(self, name, column_type):

self.name=name

self.column_type=column_typedef __str__(self):return '' % (self.__class__.__name__, self.name)classStringField(Field):def __init__(self, name):

super(StringField, self).__init__(name, 'varchar(100)')classIntegerField(Field):def __init__(self, name):

super(IntegerField, self).__init__(name, 'bigint')classModelMetaclass(type):def __new__(cls, name, bases, attrs):if name=='Model':return type.__new__(cls, name, bases, attrs)print('Found model: %s' %name)

mappings=dict()for k, v inattrs.iteritems():ifisinstance(v, Field):print('Found mapping: %s ==> %s' %(k, v))

mappings[k]=vfor k inmappings.iterkeys():

attrs.pop(k)

attrs['__mappings__'] = mappings #保存属性和列的映射关系

attrs['__table__'] = name #假设表名和类名一致

return type.__new__(cls, name, bases, attrs)classModel(dict):__metaclass__ =ModelMetaclassdef __init__(self, **kw):

super(Model, self).__init__(**kw)def __getattr__(self, key):try:returnself[key]exceptKeyError:raise AttributeError(r"'Model' object has no attribute '%s'" %key)def __setattr__(self, key, value):

self[key]=valuedefsave(self):

fields=[]

params=[]

args=[]for k, v in self.__mappings__.iteritems():

fields.append(v.name)

params.append('?')

args.append("'"+str(getattr(self, k, None))+"'")

sql= "insert into {table} ({keys}) values ({value})".format(table=self.__table__,keys=','.join(fields),value=','.join(args))print('SQL: %s' %sql)print('ARGS: %s' %str(args))returnsql#testing code:

classproductInfo(Model):

productID= IntegerField('productID')

productName= StringField('productName')

parentID= IntegerField('parentID')

clickNum= IntegerField('clickNum')

test= productInfo(productID=12345, productName='Iphone', parentID=1111, clickNum=99999)

sql=test.save()importcx_Oracle

db= cx_Oracle.connect('DBA/yiyi521@mydb')

cursor=db.cursor()

cursor.execute(sql)

db.commit()

执行 python orm.py

[oracle@njrd120 pythonscript]$ python orm.py

Found model: productInfo

Found mapping: parentID ==>

Found mapping: clickNum ==>

Found mapping: productName ==>

Found mapping: productID ==>

SQL: insert into productInfo (clickNum,productName,productID,parentID) values ('99999','Iphone','12345','1111')

ARGS: ["'99999'", "'Iphone'", "'12345'", "'1111'"]

数据库查看

相关：

Python连接Oracle数据库需要依赖第三方模块 cx_Oracle

cx_Oracle的安装：

(1)pip ： pip install cx_Oracle

(2)rpm ：rpm -ivh cx_Oracle-5.1.1-11g-py26-1.x86_64.rpm        需要对应oracle版本的安装包

环境变量的配置：

需要oracle用户的所有环境变量，所以最好在oracle用户下使用，另外需在oracle用户下.bash_profile文件中新增一行

export LD_LIBRARY_PATH=$ORACLE_HOME/lib:/usr/lib:/usr/lib

保存然后 source .bash_profile

遇到的一个问题：

修改前的save()

defsave(self):

fields=[]

params=[]

args=[]for k, v in self.__mappings__.iteritems():

fields.append(v.name)

params.append('?')

args.append(str(getattr(self, k, None)))

sql= "insert into %s (%s) values ('%s')" % (self.__table__, ','.join(fields), ','.join(args))print('SQL: %s' %sql)print('ARGS: %s' %str(args))return sql

执行结果

SQL: insert into productInfo (clickNum,productName,productID,parentID) values ('2222,Michael,12345,1111')

ARGS: ['2222', 'Michael', '12345', '1111']

Traceback (most recent call last):

File "orm.py", line 83, in 

cursor.execute(sql)

cx_Oracle.DatabaseError: ORA-00947: not enough values

执行的sql格式存在问题

将 args.append(str(getattr(self, k, None))) 修改为args.append("'"+str(getattr(self, k, None))+"'") 解决。