manage/uuspider/pyOracle/oracle_manage.py

# coding=utf-8
'''
Created on Apr 21, 2016

@author: root
'''


import os 
import time
os.environ['NLS_LANG'] = 'SIMPLIFIED CHINESE_CHINA.UTF8'

import cx_Oracle
from util_common import Constant

class OracleManage(object):
    
    def __init__(self):
        self.conn = cx_Oracle.connect(Constant.ORACLE_URL)
    
    def _insert_component(self, component_list, db):
        error_component_list = list()
        
        cursor = self.conn.cursor()
        cursor.prepare("INSERT INTO product$component_temp ( cmp_id, cmp_code, cmp_img, cmp_uuid, cmp_description, cmp_packaging, cmp_brid, cmp_kiid, cmp_company )  values(:1, :2, :3, :4, :5, :6, :7, :8, :9)")
        param_oracle = list()
        for index, component in enumerate(component_list):
            try:
                param_oracle.append( (component["id"], component["code"], component["img"], component["uuid"], component["description"], component["packaging"], component["brandid"], component["kindid"], component["company"]) )
            except:
                error_component_list.append(component)
            # commit data whenever 20000
            if index % 20000 == 0:
                print ("insert Component :", index)
                
                cursor.executemany(None, param_oracle)
                self.conn.commit()
                
                param_oracle.clear()
        
        
        
        print (len(error_component_list))
        if (len(error_component_list) > 0):
            db.insert_into_oracle_failed.insert_many(error_component_list)
        
        cursor.executemany(None, param_oracle)
        self.conn.commit()
        cursor.close()
        
    def _insert_propertyvalue(self, propertyvalue_list):
        cursor = self.conn.cursor()
        cursor.prepare('insert into product$propertyvalue_temp (pv_id, pv_componentid, pv_detno, pv_propertyid, pv_value ) values(:1, :2, :3, :4, :5)') #
        param_oracle = list()
        for index, pv in enumerate(propertyvalue_list):
            
            try:
                if len(pv["value"]) > 80:
                    continue
                param_oracle.append( (index + 1, pv["componentid"], pv["detno"], pv["propertyid"], pv["value"]) )
            except:
                param_oracle.append( (index + 1, pv["componentid"], pv["detno"], pv["propertyid"], None) )
            
            # commit data whenever 50000
            if index % 500000 == 0:
                print ("insert propertyvalue :", index)
                cursor.executemany(None, param_oracle)
                self.conn.commit()
                
                param_oracle.clear()
        
        cursor.executemany(None, param_oracle)
        self.conn.commit()
        cursor.close()
    
        
    def craw(self, component_list, propertyvalue_list):
        conn = cx_Oracle.connect(Constant.ORACLE_URL)
        
        self._insert_component(conn, component_list)
        
        self._insert_propertyvalue(conn, component_list)
        
        conn.close ()
        
if __name__ == '__main__':
    pass