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
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()
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()
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()
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
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()
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
# 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']
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
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()