Python ORM 实现及python在linux下连接oracle，,ORM &ldquo

Python ORM 实现及python在linux下连接oracle，,ORM &ldquo

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

orm模块：orm.py

#!/usr/bin/env python# -*- coding: utf-8 -*-‘ Simple ORM using metaclass ‘__author__ = ‘Michael Liao‘class Field(object):    def __init__(self, name, column_type):        self.name = name        self.column_type = column_type    def __str__(self):        return ‘<%s:%s>‘ % (self.__class__.__name__, self.name)class StringField(Field):    def __init__(self, name):        super(StringField, self).__init__(name, ‘varchar(100)‘)class IntegerField(Field):    def __init__(self, name):        super(IntegerField, self).__init__(name, ‘bigint‘)class ModelMetaclass(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 in attrs.iteritems():            if isinstance(v, Field):                print(‘Found mapping: %s ==> %s‘ % (k, v))                mappings[k] = v        for k in mappings.iterkeys():            attrs.pop(k)        attrs[‘__mappings__‘] = mappings # 保存属性和列的映射关系        attrs[‘__table__‘] = name # 假设表名和类名一致        return type.__new__(cls, name, bases, attrs)class Model(dict):    __metaclass__ = ModelMetaclass    def __init__(self, **kw):        super(Model, self).__init__(**kw)    def __getattr__(self, key):        try:            return self[key]        except KeyError:            raise AttributeError(r"‘Model‘ object has no attribute ‘%s‘" % key)    def __setattr__(self, key, value):        self[key] = value    def save(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))    return sql    # testing code:class productInfo(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()import cx_Oracledb = cx_Oracle.connect(‘AIDBA/12345678@mydb‘)cursor= db.cursor()cursor.execute(sql)db.commit()

执行 python orm.py

[oracle@njrd120 pythonscript]$ python orm.py
Found model: productInfo
Found mapping: parentID ==> <IntegerField:parentID>
Found mapping: clickNum ==> <IntegerField:clickNum>
Found mapping: productName ==> <StringField:productName>
Found mapping: productID ==> <IntegerField: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()

def save(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:insertintoproductInfo(clickNum,productName,productID,parentID)values(‘2222,Michael,12345,1111‘)
ARGS:[‘2222‘,‘Michael‘,‘12345‘,‘1111‘]
Traceback(mostrecentcalllast):
File"orm.py",line83,in<module>
cursor.execute(sql)
cx_Oracle.DatabaseError:ORA-00947:notenoughvalues
执行的sql格式存在问题将 args.append(str(getattr(self, k, None))) 修改为args.append("‘"+str(getattr(self, k, None))+"‘") 解决。

Python ORM 实现及python在linux下连接oracle