首页 > 代码库 > 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 Liaoclass 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

执行结果

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))+"‘") 解决。

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