def get_raw_kind_batch(site, kind):
try:
now = int(time.time())
connect = db.connect()
sql = text(
'select task_schedule.key, task_schedule.value from task_schedule where site=:site and kind=:kind and status in (0,1) and dealtime < :now limit 10000;'
)
cursor = connect.execute(sql, site=site, kind=kind, now=now)
result = []
dealtime = int(time.time()) + TaskSchedule.STEP
for item in cursor:
sql = text(
'update task_schedule set status=1, dealtime=:dealtime where task_schedule.key=:ts_key and kind=:kind and site=:site;'
)
u_cursor = connect.execute(sql,
dealtime=dealtime,
ts_key=item[0],
kind=kind,
site=site)
u_cursor.close()
result.append({
"key":
item[0].encode("utf8")
if isinstance(item[0], unicode) else item[0],
"value":
item[1].encode("utf8") if
(item[1] and isinstance(item[1], unicode)) else
(item[1] or "")
})
cursor.close()
connect.close()
return result
except:
logger.error(traceback.format_exc())
return []
def clear():
connect = db.connect()
sql = text("delete from task_schedule;")
cursor = connect.execute(sql)
cursor.close()
connect.close()
return True
def set_status(site_id, status):
sql = text(
"update category set status=:status where site_id=:site_id;")
connect = db.connect()
cursor = connect.execute(sql, status=status, site_id=site_id)
cursor.close()
connect.close()
return True
def upsert_review(review):
logger.info(u"正在插入评论, no为%s" % review["review_no"])
connect = db.connect()
try:
sql = text('insert ignore into joom_review (review_no,create_time,update_time,pro_no,variation_id,user_no,joom_review.language,origin_text,new_text,order_id,is_anonymous,colors,star,shop_no,photos) VALUES (:review_no,:create_time,:update_time,:pro_no,:variation_id,:user_no,:language,:origin_text,:new_text,:order_id,:is_anonymous,:colors,:star,:shop_no,:photos) ')
cursor = connect.execute(sql, **review)
cursor.close()
except Exception, e:
logger.info(traceback.format_exc(e))
def upsert_user(user):
logger.info(u"正在插入用户, no为%s" % user["user_no"])
connect = db.connect()
try:
sql = text(
'insert ignore into joom_user (user_no, full_name, images) values (:user_no, :full_name, :images)')
cursor = connect.execute(sql, **user)
cursor.close()
except Exception, e:
logger.info(traceback.format_exc(e))
def upsert_pro(pro):
logger.info(u"正在插入产品, no为%s" % pro["pro_no"])
connect = db.connect()
try:
sql = text(
'insert into joom_pro (joom_pro.name,pro_no,shop_no,category_id,image,rate,msrp,discount,real_price,reviews_count,create_time,update_time,cate_id1,cate_id2,cate_id3,cate_id4,cate_id5,origin_price,r_count_30,r_count_7,r_count_7_14,growth_rate,save_count) values (:name,:pro_no,:shop_no,:category_id,:image,:rate,:msrp,:discount,:real_price,:reviews_count,:create_time,:update_time,"","","","","",0,0,0,0,0,0) on duplicate key update joom_pro.name=:name,category_id=:category_id,rate=:rate,msrp=:msrp,discount=:discount,real_price=:real_price,reviews_count=:reviews_count,update_time=:update_time;'
)
cursor = connect.execute(sql, **pro)
cursor.close()
except Exception, e:
logger.info(traceback.format_exc(e))
def upsert_shop(shop):
logger.info(u"正在插入店铺, no为%s" % shop["shop_no"])
connect = db.connect()
try:
sql = text(
'insert into joom_shop (joom_shop.name,shop_no,logo,rate,save_count,create_time,update_time,is_verify,pro_count,reviews_count,r_count_30,r_count_7,r_count_7_14,growth_rate,cate_id) values (:name,:shop_no,:logo,:rate,:save_count,:create_time,:update_time,:is_verify,0,0,0,0,0,0,"") on duplicate key update rate=:rate, save_count=:save_count, create_time=:create_time, update_time=:update_time, is_verify=:is_verify;'
)
cursor = connect.execute(sql, **shop)
cursor.close()
except Exception, e:
logger.info(traceback.format_exc(e))
def pro_review_cnt(cls, pro_no):
try:
connect = db.connect()
sql = text(
"select count(*) from joom_review where pro_no=:pro_no;")
cursor = connect.execute(sql, pro_no=pro_no)
return cursor.fetchone()[0]
except:
return 0
finally:
cursor.close()
connect.close()
def is_raw_starting(kind):
connect = db.connect()
sql = text(
'select task_schedule.key from task_schedule where kind=:kind and status in (1,2);'
)
cursor = connect.execute(sql, kind=kind)
ts = cursor.fetchone()
cursor.close()
connect.close()
if ts:
return True
return False
def is_raw_complete(kind):
connect = db.connect()
sql = text(
'select task_schedule.key from task_schedule where kind=:kind and status in (0,1) limit 1;'
)
cursor = connect.execute(sql, kind=kind)
result = cursor.fetchone()
cursor.close()
connect.close()
if result:
return False
return True
def raw_set(site, kind, key, status):
# sql = 'update task_schedule set status=%s where task_schedule.key="%s" and kind="%s" and site=%s;' % (status, key, kind, site)
sql = text(
'update task_schedule set status=:status where task_schedule.key=:ts_key and kind=:kind and site=:site;'
)
connect = db.connect()
cursor = connect.execute(sql,
status=status,
ts_key=key,
kind=kind,
site=site)
cursor.close()
connect.close()
return True
def raw_pure_upsert(key_lst, kind, site):
connect = db.connect()
if isinstance(key_lst[0], list):
for k_lst in key_lst:
values = zip(k_lst, [kind] * len(k_lst), [site] * len(k_lst),
[TaskSchedule.INIT] * len(k_lst),
[0] * len(k_lst))
TaskSchedule._raw_batch_upsert(connect, values)
else:
values = zip(key_lst, [kind] * len(key_lst), [site] * len(key_lst),
[TaskSchedule.INIT] * len(key_lst),
[0] * len(key_lst))
TaskSchedule._raw_batch_upsert(connect, values)
connect.close()
return True
def raw_update(site, kind, key, value="", status=0):
try:
sql = text(
'update task_schedule set task_schedule.value=:ts_value, status=:status where task_schedule.key=:ts_key and kind=:kind and site=:site;'
)
connect = db.connect()
cursor = connect.execute(sql,
ts_value=value,
status=status,
ts_key=key,
kind=kind,
site=site)
cursor.close()
connect.close()
return True
except:
logger.error(traceback.format_exc())
logger.error("key: %s, value: %s" % (key, value))
return False
def raw_save(tag, name, p_id, is_leaf, level, site_id, status="0"):
sql = text(
"insert into category (tag, category.name, p_id, is_leaf, category.level, site_id, status) values (:tag, :c_name, :p_id, :is_leaf, :c_level, :site_id, :status) on duplicate key update status=:status"
)
connect = db.connect()
cursor = connect.execute(sql,
tag=tag,
c_name=name,
p_id=p_id,
is_leaf=is_leaf,
c_level=level,
site_id=site_id,
status=status)
cursor.close()
s_sql = text(
"select id from category where tag=:tag and site_id=:site_id;")
cursor = connect.execute(s_sql, tag=tag, site_id=site_id)
result = cursor.fetchone()
connect.close()
return result[0]