Example #1
0
def deleteSameData() :
	global logger

	sql = cMysql.cMysql(con.DB_INFO)
	sql.db_conn()

	try :
		param = []
		qry = """
			DELETE FROM TB_PRODUCT WHERE PRODUCT_SEQ IN (
				SELECT PRODUCT_SEQ FROM (
					SELECT MAX(PRODUCT_SEQ) AS PRODUCT_SEQ, PRODUCT_CD, COUNT(*) AS CNT FROM `TB_PRODUCT` GROUP BY `PRODUCT_CD`
				) A
				WHERE A.CNT > 1
			)
		"""
		logger.debug(qry)
		result = sql.exec('update', qry, param)
		logger.info("delete count == "+ str(result['cnt']))
	#end try
	except Exception as err:
		logger.error("find error : "+ str(err))
	#end except
	finally :
		sql.close()
	#end finally
#end def
Example #2
0
def main() :
	global logger
	global now_dt

	sql = cMysql.cMysql(con.DB_INFO)
	sql.db_conn()

	try :
		# get 1depth Category list
		param = []
		qry = """
			SELECT CATE_CD, CATE_NM
			FROM TB_CATEGORY
			WHERE USE_YN = 'Y'
			AND DEPTH = 1
			ORDER BY CATE_CD
		"""
		result = sql.exec('list', qry, param)
		cnt = result['cnt']
		rs = result['data']

		sql.tran()

		dicCate = fnc.GET_CATEGORY_DICT()

		for ds in rs :
			cate_cd = ds['CATE_CD']
			cate_nm = ds['CATE_NM']
			totalPageCnt = 0

			url = "http://www.amorepacificmall.com/plist/all/"+ cate_cd +"/all/all/list_paging_ajax.do"
			headers = {'Content-Type': 'application/json; charset=utf-8'}
			param = {"i_iNowPageNo": 1, "i_sCategorycd1" : cate_cd}
			res = requests.post(url, headers = headers, data=json.dumps(param))
			
			if res.status_code != 200 :
				raise Exception("URL호출에러(1)")
			else :
				jsonData = res.json()
				dsPage = jsonData['shopProd']['ctgProdPage']

				totalPageCnt = dsPage['i_iTotalPageCnt']

				# api loop
				logger.info(cate_nm +" : "+ totalPageCnt)
				count = run_api(cate_cd, cate_nm, totalPageCnt, dicCate, sql)
				logger.info("success count : "+ str(count))
			#end if
		#end for

		sql.commit()
	#end try
	except Exception as err:
		sql.rollback()
		logger.error("find error : "+ str(err))
	#end except
	finally :
		sql.close()
Example #3
0
def getProductInfo(p_cd):
    try:
        # set db
        sql = cMysql.cMysql(con.DB_INFO)
        sql.db_conn()

        param = []
        qry = """
			SELECT * FROM TB_PRODUCT WHERE PRODUCT_CD = %s
		"""
        param.append(p_cd)
        result = sql.exec('data', qry, param)
        cnt = result['cnt']
        ds = result['data']

        return ds
    #end try
    except Exception as err:
        logger.error("find error : " + str(err))
    #end except
    finally:
        sql.close()
Example #4
0
def getImageList(product_seq) :
	try :
		# set db
		sql = cMysql.cMysql(con.DB_INFO)
		sql.db_conn()

		arParam = []
		qry = """
			SELECT * FROM TB_IMG WHERE PRODUCT_SEQ = %s
		"""
		arParam.append(product_seq)
		result = sql.exec('list', qry, arParam)
		cnt = result['cnt']
		rs = result['data']

		return rs
	#end try
	except Exception as err :
		logger.error("find error : "+ str(err))
	#end except
	finally :
		sql.close()
Example #5
0
def IS_PRODUCT(p_cd):
    rtn = ''

    sql = cMysql.cMysql(con.DB_INFO)
    sql.db_conn()

    param = []
    qry = """
		SELECT COUNT(PRODUCT_SEQ) AS CNT
		FROM TB_PRODUCT
		WHERE PRODUCT_CD = %s
	"""
    param.append(p_cd)
    result = sql.exec('data', qry, param)
    ds = result['data']
    rtn = ds['CNT']

    sql.close()

    return rtn


#end def
Example #6
0
def getProductCode(ctg1):
    try:
        # set db
        sql = cMysql.cMysql(con.DB_INFO)
        sql.db_conn()

        qryWhere = ''
        if ctg1 != '':
            qryWhere = " WHERE CATE_CD1 = %s"
            param.append(ctg1)
        # end if
        qryOrder = ' ORDER BY CATE_CD1, PRODUCT_SEQ'
        if ctg1 != '':
            qryOrder = " ORDER BY PRODUCT_SEQ"
            param.append(ctg1)
        # end if

        param = []
        qry = """
			SELECT PRODUCT_CD 
			FROM TB_PRODUCT 
			""" + qryWhere + """
			""" + qryOrder + """
		"""
        logger.debug(qry)
        logger.debug(str(param))
        result = sql.exec('list', qry, param)
        cnt = result['cnt']
        rs = result['data']

        return rs
    #end try
    except Exception as err:
        logger.error("find error : " + str(err))
    #end except
    finally:
        sql.close()
Example #7
0
def GET_CATEGORY_DICT():
    rtn = {}

    sql = cMysql.cMysql(con.DB_INFO)
    sql.db_conn()

    param = []
    qry = """
		SELECT CATE_CD, CATE_NM, DEPTH
		FROM TB_CATEGORY
		WHERE USE_YN = 'Y'
		ORDER BY CATE_CD
	"""
    result = sql.exec('list', qry, param)
    rs = result['data']

    sql.close()

    for ds in rs:
        key = ds['CATE_NM'] + '_' + str(ds['DEPTH'])
        rtn[key] = ds['CATE_CD']
    #end for

    return rtn
Example #8
0
# import user library
sys.path.insert(0, '/home/crawler')
from _lib import config as con
from _lib import cMysql
from _lib import cLogger
from _lib import function as fnc

# set logger
cLog = cLogger.cLogger("getDetail")
logger = cLog.set_logger()

# import def
import defDetail as det

# set db
sql = cMysql.cMysql(con.DB_INFO)
sql.db_conn()

try:
    logger.info("start page crawling : " + str(datetime.now()))

    param = []
    qry = """
		SELECT * FROM TB_PRODUCT WHERE CATE_CD1 = %s AND PRODUCT_SEQ > %s ORDER BY PRODUCT_SEQ
	"""
    param.append('CTG001')
    param.append(602)
    result = sql.exec('list', qry, param)
    cnt = result['cnt']
    rsPrd = result['data']
Example #9
0
def setDetailInfo(arrMain):
    try:
        # set db
        sql = cMysql.cMysql(con.DB_INFO)
        sql.db_conn()
        sql.tran()

        # set variables
        product_seq = arrMain['product_seq']
        product_nm2 = arrMain['product_nm2']

        # detail product name update
        param = []
        qry = """
			UPDATE TB_PRODUCT SET
			PRODUCT_NM2 = %s
			WHERE PRODUCT_SEQ = %s
		"""
        param.append(product_nm2)
        param.append(product_seq)
        logger.debug(qry)
        logger.debug(str(param))
        result = sql.exec('update', qry, param)
        cnt = result['cnt']

        if cnt < 1:
            raise Exception("상세상품명 업데이트 카운트 오류")
        #end if

        # main image insert
        list_main_img = arrMain["main_img"]
        param = []

        for main_img in list_main_img:
            arr = []
            arr.append(product_seq)
            arr.append(main_img)
            arr.append('M')
            arr.append(now_dt)
            param.append(arr)
        #end for

        qry = """
			INSERT INTO TB_IMG (PRODUCT_SEQ, FILE_NM, IMG_TP, REG_DT)
			VALUES(%s, %s, %s, %s)
		"""
        logger.debug(qry)
        logger.debug(str(param))
        result = sql.execmany(qry, param)
        cnt = result['cnt']

        if cnt < 1:
            raise Exception("메인이미지 등록 카운트 오류")
        #end if

        # detail image insert
        list_desc_img = arrMain['desc_img']

        if len(list_desc_img) > 0:
            param = []

            for desc_img in list_desc_img:
                arr = []
                arr.append(product_seq)
                arr.append(desc_img)
                arr.append('D')
                arr.append(now_dt)
                param.append(arr)
            #end for

            qry = """
				INSERT INTO TB_IMG (PRODUCT_SEQ, FILE_NM, IMG_TP, REG_DT)
				VALUES(%s, %s, %s, %s)
			"""
            logger.debug(qry)
            logger.debug(str(param))
            result = sql.execmany(qry, param)
            cnt = result['cnt']

            if cnt < 1:
                raise Exception("상세이미지 등록 카운트 오류")
            #end if
        #end if

        # option insert
        list_option = arrMain["option"]

        if list_option != None:
            param = []

            for option in list_option:
                arr = []
                arr.append(product_seq)
                arr.append(option['title'])
                arr.append(option['price'])
                arr.append(now_dt)
                param.append(arr)
            #end for

            qry = """
				INSERT INTO TB_OPTION (PRODUCT_SEQ, OPT_NM, OPT_PRICE, REG_DT)
				VALUES(%s, %s, %s, %s)
			"""
            logger.debug(qry)
            logger.debug(str(param))
            result = sql.execmany(qry, param)
            cnt = result['cnt']

            if cnt < 1:
                raise Exception("메인이미지 등록 카운트 오류")
            #end if
        #end if

        sql.commit()
    #end try
    except Exception as err:
        sql.rollback()
        logger.error("find error : " + str(err))
    #end except
    finally:
        sql.close()
    #end finally


#end def
Example #10
0
def setMainInfoInsert(arrMain) :
	try :
		# set db
		sql = cMysql.cMysql(con.DB_INFO)
		sql.db_conn()
		sql.tran()

		# set variables
		cate_cd1 = dic_info['category']['category_cd1']
		cate_cd2 = dic_info['category']['category_cd2']
		cate_cd3 = dic_info['category']['category_cd3']

		# make thumbnail
		thumb = setThumb(arrMain['main_img'], arrMain['brand_cd'])


		# product info insert
		arParam = []
		qry = """
			INSERT INTO `APMALL`.`TB_PRODUCT` (`PRODUCT_CD`,`PRODUCT_NM`,`PRODUCT_DESC`,`BRAND_CD`,`BRAND_NM`,`PRICE`,`CATE_CD1`,`CATE_CD2`,`CATE_CD3`,`THUMB1`,`REG_DT`)
			VALUES (%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s)
		"""
		arParam.append(arrMain['product_cd'])
		arParam.append(arrMain['product_nm'])
		arParam.append(arrMain['product_desc'])
		arParam.append(arrMain['brand_cd'])
		arParam.append(arrMain['brand_nm'])
		arParam.append(arrMain['price'])
		arParam.append(cate_cd1)
		arParam.append(cate_cd2)
		arParam.append(cate_cd3)
		arParam.append(thumb)
		arParam.append(now_dt)
		result = sql.exec('insert', qry, arParam)
		cnt = result['cnt']
		product_seq = result['insertid']

		if cnt < 1 :
			raise Exception("상품정보저장 오류")
		#end if


		# main image insert
		list_main_img = arrMain["main_img"]
		param = []
		
		for main_img in list_main_img :
			arr = []
			arr.append(product_seq)
			arr.append(main_img)
			arr.append('M')
			arr.append(now_dt)
			param.append(arr)
		#end for

		qry = """
			INSERT INTO TB_IMG (PRODUCT_SEQ, FILE_NM, IMG_TP, REG_DT)
			VALUES(%s, %s, %s, %s)
		"""
		logger.debug(qry)
		logger.debug(str(param))
		result = sql.execmany(qry, param)
		cnt = result['cnt']

		if cnt < 1 :
			raise Exception("메인이미지 등록 카운트 오류")
		#end if


		# detail image insert
		list_desc_img = arrMain['desc_img']

		if len(list_desc_img) > 0 :
			param = []

			for desc_img in list_desc_img :
				arr = []
				arr.append(product_seq)
				arr.append(desc_img)
				arr.append('D')
				arr.append(now_dt)
				param.append(arr)
			#end for

			qry = """
				INSERT INTO TB_IMG (PRODUCT_SEQ, FILE_NM, IMG_TP, REG_DT)
				VALUES(%s, %s, %s, %s)
			"""
			logger.debug(qry)
			logger.debug(str(param))
			result = sql.execmany(qry, param)
			cnt = result['cnt']

			if cnt < 1 :
				raise Exception("상세이미지 등록 카운트 오류")
			#end if
		#end if

		# option insert
		list_option = arrMain["option"]

		if list_option != None :
			param = []
			
			for option in list_option :
				arr = []
				arr.append(product_seq)
				arr.append(option['title'])
				arr.append(option['price'])
				arr.append(now_dt)
				param.append(arr)
			#end for

			qry = """
				INSERT INTO TB_OPTION (PRODUCT_SEQ, OPT_NM, OPT_PRICE, REG_DT)
				VALUES(%s, %s, %s, %s)
			"""
			logger.debug(qry)
			logger.debug(str(param))
			result = sql.execmany(qry, param)
			cnt = result['cnt']

			if cnt < 1 :
				raise Exception("옵션정보 등록 카운트 오류")
			#end if
		#end if

		sql.commit()

		return product_seq
	#end try
	except Exception as err :
		sql.rollback()
		logger.error("find error : "+ str(err))
	#end except
	finally :
		sql.close()