首页 > 代码库 > Python ORM 实现及python在linux下连接oracle
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__ = ‘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=http://www.mamicode.com/‘,‘.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
执行结果
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
Python ORM 实现及python在linux下连接oracle