def initialize(self, logger, config_result):
GetData.__init__(self,
service_code_tuple=(100646, 100683),
txt_list=['链接', '差价'],
dict_=True)
self.__logger = logger
self.__conf = config_result
# self._mysql_sqyn = self.__conf['mysql_2']
# self._mysql_hisense = self.__conf['mysql_1']
self._mysql_conn_sqyn = get_mysql_conn('mysql_2')
self._cursor_mysql_sqyn = self._mysql_conn_sqyn.cursor(
cursor=DictCursor)
self._mysql_conn_hisense = get_mysql_conn('mysql_1')
self._cursor_mysql_hisense = self._mysql_conn_sqyn.cursor(
cursor=DictCursor)
self.sql_insert = """
INSERT INTO cb_new_goods_rec_results(
owner_code,
area_code,
spu_code_list
)
VALUES ({})
ON DUPLICATE KEY update
owner_code = VALUES(owner_code),
area_code = VALUES(area_code),
spu_code_list = VALUES(spu_code_list)
""".format('%s' + ', %s' * 2)
def _data_insert(self, data_all):
_mysql_conn_sqyn = get_mysql_conn('mysql_2')
_cursor_mysql_sqyn = _mysql_conn_sqyn.cursor(cursor=DictCursor)
_cursor_mysql_sqyn.executemany(self.sql_insert, data_all)
_mysql_conn_sqyn.commit()
_cursor_mysql_sqyn.close()
_mysql_conn_sqyn.close()
def _get_data(self, sql_select):
data = None
try:
self.conn_sqyn = get_mysql_conn('mysql_2')
self.cur_sqyn = self.conn_sqyn.cursor(cursor=DictCursor)
self.cur_sqyn.execute(sql_select)
data = self.cur_sqyn.fetchall()
self.cur_sqyn.close()
self.conn_sqyn.close()
except Exception as e:
print(e)
return data
def __init__(self,
service_code_tuple=None,
txt_list=None,
dict_=False,
db='sqyn'):
self.db = db
if dict_:
self.conn_sqyn = get_mysql_conn('mysql_2')
self.cur_sqyn = self.conn_sqyn.cursor(cursor=DictCursor)
self.conn_hisense = get_mysql_conn('mysql_1')
self.cur_hisense = self.conn_hisense.cursor(cursor=DictCursor)
else:
self.conn_sqyn = get_mysql_conn('mysql_2')
self.cur_sqyn = self.conn_sqyn.cursor()
self.conn_hisense = get_mysql_conn('mysql_1')
self.cur_hisense = self.conn_hisense.cursor()
tmp_txt = ''
if txt_list:
for txt in txt_list:
tmp_txt = tmp_txt + '|' + txt
self._sql_select_spu_all = """
SELECT
b.area_code,
c.shop_code,
a.spu_code,
a.sale_month_count
FROM cb_goods_spu_for_filter a
JOIN cb_goods_scope b ON a.spu_code=b.spu_code
JOIN cb_goods_spu_search c ON a.spu_code=c.spu_code
WHERE a.spu_name NOT REGEXP "{}"
AND a.price_line > a.sale_price
AND c.shop_type_code = 1
AND a.second_cate NOT IN {}
""".format(tmp_txt[1:], service_code_tuple)
self._sql_select_coupon_shop = """
SELECT area_code, shop_code, coupon_type
FROM cb_shop_coupon
WHERE status = '02' AND coupon_type='02' OR coupon_type='03'
GROUP BY area_code, shop_code, coupon_type
"""
#
self._sql_select_promotion = """
SELECT act_code,
end_time,
begin_time,
shop_code,
area_code,
spu_code
FROM cb_goods_promotion
WHERE CURRENT_TIMESTAMP BETWEEN
DATE_FORMAT(begin_time, '%Y-%m-%d %H:%i:%s')
AND
DATE_FORMAT(end_time, '%Y-%m-%d %H:%i:%s');
"""
self._sql_select_price_line = """
SELECT b.area_code, a.spu_code
FROM cb_goods_spu_for_filter a
JOIN cb_goods_scope b ON a.spu_code=b.spu_code
WHERE a.price_line > a.sale_price
"""
self._sql_select_discount_hot_sale = """
SELECT
FROM cb_discount_goods_rec_results a
JOIN cb_goods_scope b ON a.spu_code=b.spu_code
WHERE area_code='{}'
"""
self._sql_select_spu_all_new_goods = """
SELECT b.area_code,
a.spu_code,
a.sale_month_count,
a.created_time,
a.first_cate,
a.second_cate
FROM cb_goods_spu_for_filter AS a
JOIN cb_goods_scope AS b ON a.spu_code=b.spu_code
JOIN cb_goods_spu_search c ON a.spu_code=c.spu_code
WHERE a.sale_price > 10
AND a.spu_name NOT REGEXP "{}"
AND a.second_cate NOT IN {}
AND c.shop_type_code = 1
AND TO_DAYS(NOW()) - TO_DAYS(a.created_time) <= 30
GROUP BY b.area_code,spu_code
ORDER BY a.created_time DESC
""".format(tmp_txt[1:], service_code_tuple)
self._sql_select_all_area = """
def initialize(self, logger, config_result):
GetData.__init__(self)
self.__conf = config_result
self.__logger = logger
self._mysql_conn_sqyn = get_mysql_conn('mysql_2')
self._cursor_mysql_sqyn = self._mysql_conn_sqyn.cursor()