manage/uuspider/create_table/panel_oracle.py

'''
Created on 2016年8月18日

@author: uas
'''
import os 
import time
os.environ['NLS_LANG']='SIMPLIFIED CHINESE_CHINA.UTF8'
from pymongo.mongo_client import MongoClient
import csv

import cx_Oracle

from util_common import Constant

class Panel_Oracle_Connector(object):
    
    def __init__(self):
        self.conn=cx_Oracle.connect(Constant.ORACLE_URL)
        self.cli=MongoClient(Constant.MONGODB_URL)
        self.db=self.cli.spider
        
    def _checkupdata(self):
        
        
        result=self.db.panel_propertyvalue_0728.find().limit(10)
        for r in result:
            for key,value in r.items():
                print('key:',key)
                print('value:',value)
        result=self.db.panel_propertyvalue_0728.find()
        print('component property number: ',result.count())       
         
#         result=self.db.component_panel_0720.find().limit(1)
#         for r in result:
#             for key,value in r.items():
#                 if key=='str_html':
#                     continue
#                 print('key:',key)
#                 print('value:',value)
#                  
#         result=self.db.component_panel_0720.find()
#         print('component number: ',result.count())
#         
#         result=self.db.component_panel_0720.find()
#         kind_set=set()
#         for index,r in enumerate(result):
#             kind_set.add(r['kind'])
#             print(index)
#         print(kind_set)
#         
        
        
    def _create_table(self):
            
        cursor=self.conn.cursor()
#         sql="create table product$panelcomponent"+"(cmp_id  int primary key,cmp_attach  varchar(255) ,cmp_img varchar(255),cmp_brid  varchar2(222) ,cmp_code   varchar(255),cmp_kiid  varchar2(255),cmp_company  varchar(255) ,cmp_company_url varchar(255) ,cmp_description varchar(4000),cmp_packaging varchar(255))"
#         cursor.execute(sql)
        
        sql="create table product$panel_propertyvalue"+"(pv_id  int primary key,pv_componentid  number(38,0) ,pv_propertyid varchar2(255),pv_value  varchar2(255),pv_detno varchar2(255))"
        cursor.execute(sql)
        self.conn.commit()
        
        
        
    def _parse_data(self):
        
        result=self.db.panel_propertyvalue_0728.find({'property':'面板品牌'}).limit(1)
        component_list=list()
        for index,r in enumerate(result):
            d=dict()
            d['cmp_code']=r['cmp_code']
            d['cmp_attach']=''
            d['cmp_id']=r['cmp_id']+4674555
            d['cmp_img']=''
            d['cmp_brid']=r['value']
            d['cmp_kiid']=r['kind']
            d['cmp_company']=''
            d['cmp_company_url']=''
            d['cmp_description']=''
            d['cmp_packaging']=''
            component_list.append(d)
        
        print(component_list)

        return component_list
    
    def _insert_panel(self,panel_list):       
        
        cursor=self.conn.cursor()
        sql="Insert into product$panelcomponent\
        (cmp_id,cmp_attach,cmp_img,cmp_brid,cmp_code,cmp_kiid,cmp_company,cmp_company_url,cmp_description,cmp_packaging)\
        values(:1,:2,:3,:4,:5,:6,:7,:8,:9,:10)"
        cursor.prepare(sql)
        param_oracle=list()
        for index,component in enumerate(panel_list):
            try:
                param_oracle.append((component['cmp_id'],component['cmp_attach'],component['cmp_img'],component['cmp_brid'],component['cmp_code'],component['cmp_kiid'],component['cmp_company'],component['cmp_company_url'],component['cmp_description'],component['cmp_packaging']))
            except Exception as e:
                pass
            
            if index%20000==0:
                cursor.executemany(None,param_oracle)
                self.conn.commit()
                param_oracle.clear()
        
        cursor.executemany(None,param_oracle)
        self.conn.commit()
        cursor.close()
        
        
    def _insert_panel_parameter(self,panel_list):       
        
        cursor=self.conn.cursor()
        sql="Insert into product$panel_propertyvalue (pv_id,pv_componentid,pv_propertyid,pv_value,pv_detno) values(:1,:2,:3,:4,:5)"
        cursor.prepare(sql)
        param_oracle=list()
        for index,component in enumerate(panel_list):
            try:
                param_oracle.append((component['pv_id,'],component['pv_componentid'],component['pv_propertyid'],component['pv_value'],component['pv_detno']))
            except Exception as e:
                pass
            
            if index%20000==0:
                cursor.executemany(None,param_oracle)
                self.conn.commit()
                param_oracle.clear()
            print(index)
        cursor.executemany(None,param_oracle)
        self.conn.commit()
        cursor.close()
    
    def _parse_parameter(self):
        result=self.db.panel_propertyvalue_0728.find().limit(1)
        component_list=list()
        total=51998620
        for index,r in enumerate(result):
            d=dict()
            print(r)
            d['pv_id']=total
            d['pv_componentid']=r['cmp_id']+4674555
            d['pv_propertyid']=r['property']
            d['pv_value']=r['value']
            d['pv_detno']=''
            component_list.append(d)
            total+=1
        print(component_list)
 
        return component_list
        
        
        
if __name__=='__main__':
    
    connector=Panel_Oracle_Connector()
    connector._parse_parameter()
#     connector._checkupdata()
#     connector._create_table()
#     connector._parse_data()
#     connector._insert_panel(connector._parse_data())