【原创】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'

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_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 ==> <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: 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 <module>
    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))+"'") 解决。