Python ORM 實現及python在linux下串連oracle

標籤：des   style   blog   io   color   ar   os   使用   for   

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/[email protected]‘)cursor= db.cursor()cursor.execute(sql)db.commit()

執行 python orm.py

[[email protected] 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