def run_forever():
while True:
# ** 不能写成全局变量并放在循环中, 否则会一直记录到同一文件中
my_lg = set_logger(
log_file_name=MY_SPIDER_LOGS_PATH + '/天猫/实时更新/' + str(get_shanghai_time())[0:10] + '.txt',
console_log_level=INFO,
file_log_level=ERROR
)
#### 实时更新数据
tmp_sql_server = SqlServerMyPageInfoSaveItemPipeline()
# and GETDATE()-ModfiyTime>0.2
sql_str = '''
select SiteID, GoodsID, IsDelete, Price, TaoBaoPrice, shelf_time, delete_time
from dbo.GoodsInfoAutoGet
where (SiteID=3 or SiteID=4 or SiteID=6) and MainGoodsID is not null
order by ID desc'''
try:
result = list(tmp_sql_server._select_table(sql_str=sql_str))
except TypeError:
my_lg.error('TypeError错误, 原因数据库连接失败...(可能维护中)')
result = None
if result is None:
pass
else:
my_lg.info('------>>> 下面是数据库返回的所有符合条件的goods_id <<<------')
my_lg.info(str(result))
my_lg.info('总计待更新个数: {0}'.format(len(result)))
my_lg.info('--------------------------------------------------------')
my_lg.info('即将开始实时更新数据, 请耐心等待...'.center(100, '#'))
index = 1
# 释放内存,在外面声明就会占用很大的，所以此处优化内存的方法是声明后再删除释放
tmall = TmallParse(logger=my_lg)
for item in result: # 实时更新数据
if index % 5 == 0:
try:del tmall
except: pass
tmall = TmallParse(logger=my_lg)
gc.collect()
if index % 10 == 0: # 每10次重连一次，避免单次长连无响应报错
my_lg.info('正在重置，并与数据库建立新连接中...')
tmp_sql_server = SqlServerMyPageInfoSaveItemPipeline()
my_lg.info('与数据库的新连接成功建立...')
if tmp_sql_server.is_connect_success:
my_lg.info('------>>>| 正在更新的goods_id为(%s) | --------->>>@ 索引值为(%s)' % (str(item[1]), str(index)))
tmp_item = []
if item[0] == 3: # 从数据库中取出时，先转换为对应的类型
tmp_item.append(0)
elif item[0] == 4:
tmp_item.append(1)
elif item[0] == 6:
tmp_item.append(2)
tmp_item.append(item[1])
data = tmall.get_goods_data(goods_id=tmp_item)
if isinstance(data, int): # 单独处理return 4041
index += 1
sleep(TMALL_REAL_TIMES_SLEEP_TIME)
continue
if data.get('is_delete') == 1: # 单独处理下架商品
data['goods_id'] = item[1]
data['shelf_time'], data['delete_time'] = get_shelf_time_and_delete_time(
tmp_data=data,
is_delete=item[2],
shelf_time=item[5],
delete_time=item[6])
# my_lg.info('------>>>| 爬取到的数据为: %s' % str(data))
tmall.to_right_and_update_data(data, pipeline=tmp_sql_server)
sleep(TMALL_REAL_TIMES_SLEEP_TIME)
index += 1
gc.collect()
continue
data = tmall.deal_with_data()
if data != {}:
data['goods_id'] = item[1]
data['shelf_time'], data['delete_time'] = get_shelf_time_and_delete_time(
tmp_data=data,
is_delete=item[2],
shelf_time=item[5],
delete_time=item[6])
data['_is_price_change'], data['_price_change_info'] = _get_price_change_info(
old_price=item[3],
old_taobao_price=item[4],
new_price=data['price'],
new_taobao_price=data['taobao_price']
)
# my_lg.info(str(data['_is_price_change']) + ' ' +str(data['_price_change_info']))
# my_lg.info('------>>>| 爬取到的数据为: %s' % str(data))
tmall.to_right_and_update_data(data, pipeline=tmp_sql_server)
else: # 表示返回的data值为空值
my_lg.info('------>>>| 休眠8s中...')
sleep(8)
else: # 表示返回的data值为空值
my_lg.error('数据库连接失败，数据库可能关闭或者维护中')
sleep(5)
pass
index += 1
gc.collect()
sleep(TMALL_REAL_TIMES_SLEEP_TIME)
my_lg.info('全部数据更新完毕'.center(100, '#')) # sleep(60*60)
if get_shanghai_time().hour == 0: # 0点以后不更新
sleep(60*60*5.5)
else:
sleep(5)
gc.collect()
def run_forever():
while True:
#### 实时更新数据
tmp_sql_server = SqlServerMyPageInfoSaveItemPipeline()
try:
result = list(
tmp_sql_server._select_table(sql_str=pd_select_str_1))
except TypeError:
print('TypeError错误, 原因数据库连接失败...(可能维护中)')
result = None
if result is None:
pass
else:
print('------>>> 下面是数据库返回的所有符合条件的goods_id <<<------')
print(result)
print('--------------------------------------------------------')
print('即将开始实时更新数据, 请耐心等待...'.center(100, '#'))
index = 1
for item in result: # 实时更新数据
# 释放内存,在外面声明就会占用很大的，所以此处优化内存的方法是声明后再删除释放
pinduoduo = PinduoduoParse()
if index % 50 == 0: # 每50次重连一次，避免单次长连无响应报错
print('正在重置，并与数据库建立新连接中...')
tmp_sql_server = SqlServerMyPageInfoSaveItemPipeline()
print('与数据库的新连接成功建立...')
if tmp_sql_server.is_connect_success:
print(
'------>>>| 正在更新的goods_id为(%s) | --------->>>@ 索引值为(%d)'
% (item[0], index))
pinduoduo.get_goods_data(goods_id=item[0])
data = pinduoduo.deal_with_data()
if data != {}:
data['goods_id'] = item[0]
data['shelf_time'], data[
'delete_time'] = get_shelf_time_and_delete_time(
tmp_data=data,
is_delete=item[1],
shelf_time=item[4],
delete_time=item[5])
data['_is_price_change'], data[
'_price_change_info'] = _get_price_change_info(
old_price=item[2],
old_taobao_price=item[3],
new_price=data['price'],
new_taobao_price=data['taobao_price'])
try:
old_sku_info = format_price_info_list(
price_info_list=json_2_dict(item[6]),
site_id=13)
except AttributeError: # 处理已被格式化过的
old_sku_info = item[6]
data['_is_price_change'], data[
'sku_info_trans_time'] = get_sku_info_trans_record(
old_sku_info=old_sku_info,
new_sku_info=format_price_info_list(
data['price_info_list'], site_id=13),
is_price_change=item[7]
if item[7] is not None else 0)
pinduoduo.to_right_and_update_data(
data, pipeline=tmp_sql_server)
else: # 表示返回的data值为空值
pass
else: # 表示返回的data值为空值
print('数据库连接失败，数据库可能关闭或者维护中')
pass
index += 1
# try:
# del pinduoudo
# except:
# pass
gc.collect()
# sleep(1)
print('全部数据更新完毕'.center(100, '#')) # sleep(60*60)
if get_shanghai_time().hour == 0: # 0点以后不更新
sleep(60 * 60 * 5.5)
else:
sleep(5)
# del pinduoduo
gc.collect()
def run_forever():
while True:
#### 实时更新数据
tmp_sql_server = SqlServerMyPageInfoSaveItemPipeline()
# and GETDATE()-ModfiyTime>1 and IsDelete=0
sql_str = '''
select SiteID, GoodsID, IsDelete, Price, TaoBaoPrice, shelf_time, delete_time
from dbo.GoodsInfoAutoGet
where (SiteID=7 or SiteID=8 or SiteID=9 or SiteID=10) and MainGoodsID is not null
'''
try:
result = list(tmp_sql_server._select_table(sql_str=sql_str))
except TypeError as e:
print('TypeError错误, 原因数据库连接失败...(可能维护中)')
continue
print('------>>> 下面是数据库返回的所有符合条件的goods_id <<<------')
print(result)
print('--------------------------------------------------------')
print('总计待更新个数:', len(result))
print('即将开始实时更新数据, 请耐心等待...'.center(100, '#'))
index = 1
# 释放内存,在外面声明就会占用很大的，所以此处优化内存的方法是声明后再删除释放
jd = JdParse()
for item in result: # 实时更新数据
# # 释放内存,在外面声明就会占用很大的，所以此处优化内存的方法是声明后再删除释放
# jd = JdParse()
if index % 10 == 0:
try:
del jd
except:
pass
gc.collect()
jd = JdParse()
if index % 50 == 0: # 每50次重连一次，避免单次长连无响应报错
print('正在重置，并与数据库建立新连接中...')
# try:
# del tmp_sql_server
# except:
# pass
# gc.collect()
tmp_sql_server = SqlServerMyPageInfoSaveItemPipeline()
print('与数据库的新连接成功建立...')
if tmp_sql_server.is_connect_success:
print(
'------>>>| 正在更新的goods_id为(%s) | --------->>>@ 索引值为(%d)' %
(item[1], index))
tmp_item = []
if item[0] == 7 or item[0] == 8: # 从数据库中取出时，先转换为对应的类型
tmp_item.append(0)
elif item[0] == 9:
tmp_item.append(1)
elif item[0] == 10:
tmp_item.append(2)
tmp_item.append(item[1])
jd.get_goods_data(goods_id=tmp_item)
data = jd.deal_with_data(goods_id=tmp_item)
if data != {}:
data['goods_id'] = item[1]
data['shelf_time'], data[
'delete_time'] = get_shelf_time_and_delete_time(
tmp_data=data,
is_delete=item[2],
shelf_time=item[5],
delete_time=item[6])
print('上架时间:', data['shelf_time'], '下架时间:',
data['delete_time'])
data['_is_price_change'], data[
'_price_change_info'] = _get_price_change_info(
old_price=item[3],
old_taobao_price=item[4],
new_price=data['price'],
new_taobao_price=data['taobao_price'])
# print('------>>>| 爬取到的数据为: ', data)
jd.to_right_and_update_data(data, pipeline=tmp_sql_server)
else: # 表示返回的data值为空值
pass
else: # 表示返回的data值为空值
print('数据库连接失败，数据库可能关闭或者维护中')
pass
index += 1
# try:
# del jd
# except:
# pass
gc.collect()
sleep(1.2)
print('全部数据更新完毕'.center(100, '#')) # sleep(60*60)
try:
del jd
except:
pass
if get_shanghai_time().hour == 0: # 0点以后不更新
sleep(60 * 60 * 5.5)
else:
sleep(5)
# del ali_1688
gc.collect()
def run_forever():
while True:
#### 实时更新数据
sql_cli = SqlServerMyPageInfoSaveItemPipeline()
try:
result = list(sql_cli._select_table(sql_str=vip_select_str_1))
except TypeError:
print('TypeError错误, 原因数据库连接失败...(可能维护中)')
continue
_block_print_db_old_data(result=result)
index = 1
for item in result: # 实时更新数据
# 释放内存,在外面声明就会占用很大的，所以此处优化内存的方法是声明后再删除释放
vip = VipParse()
sql_cli = _block_get_new_db_conn(db_obj=sql_cli,
index=index,
remainder=50)
if sql_cli.is_connect_success:
print(
'------>>>| 正在更新的goods_id为(%s) | --------->>>@ 索引值为(%d)' %
(item[0], index))
vip.get_goods_data(goods_id=[0, item[0]])
data = vip.deal_with_data()
if data != {}:
data['goods_id'] = item[0]
data['shelf_time'], data[
'delete_time'] = get_shelf_time_and_delete_time(
tmp_data=data,
is_delete=item[1],
shelf_time=item[4],
delete_time=item[5])
price_info_list = old_sku_info = json_2_dict(
item[6], default_res=[])
try:
old_sku_info = format_price_info_list(
price_info_list=price_info_list, site_id=25)
except AttributeError: # 处理已被格式化过的
pass
new_sku_info = format_price_info_list(
data['price_info_list'], site_id=25)
data['_is_price_change'], data[
'sku_info_trans_time'], price_change_info = _get_sku_price_trans_record(
old_sku_info=old_sku_info,
new_sku_info=new_sku_info,
is_price_change=item[7]
if item[7] is not None else 0,
db_price_change_info=json_2_dict(item[9],
default_res=[]),
old_price_trans_time=item[12],
)
data['_is_price_change'], data[
'_price_change_info'] = _get_price_change_info(
old_price=item[2],
old_taobao_price=item[3],
new_price=data['price'],
new_taobao_price=data['taobao_price'],
is_price_change=data['_is_price_change'],
price_change_info=price_change_info,
)
# 监控纯规格变动
data['is_spec_change'], data[
'spec_trans_time'] = _get_spec_trans_record(
old_sku_info=old_sku_info,
new_sku_info=new_sku_info,
is_spec_change=item[8]
if item[8] is not None else 0,
old_spec_trans_time=item[13],
)
# 监控纯库存变动
data['is_stock_change'], data['stock_trans_time'], data[
'stock_change_info'] = _get_stock_trans_record(
old_sku_info=old_sku_info,
new_sku_info=new_sku_info,
is_stock_change=item[10]
if item[10] is not None else 0,
db_stock_change_info=json_2_dict(item[11],
default_res=[]),
old_stock_trans_time=item[14],
)
vip.to_right_and_update_data(data=data, pipeline=sql_cli)
else: # 表示返回的data值为空值
pass
else: # 表示返回的data值为空值
print('数据库连接失败，数据库可能关闭或者维护中')
pass
index += 1
try:
del vip
except:
pass
gc.collect()
sleep(VIP_SLEEP_TIME)
print('全部数据更新完毕'.center(100, '#')) # sleep(60*60)
if get_shanghai_time().hour == 0: # 0点以后不更新
sleep(60 * 60 * 5.5)
else:
sleep(30)
gc.collect()
def run_forever():
while True:
my_lg = set_logger(log_file_name=MY_SPIDER_LOGS_PATH + '/1688/实时更新/' +
str(get_shanghai_time())[0:10] + '.txt',
console_log_level=INFO,
file_log_level=ERROR)
#### 实时更新数据
tmp_sql_server = SqlServerMyPageInfoSaveItemPipeline()
try:
result = list(
tmp_sql_server._select_table(sql_str=al_select_str_6))
except TypeError:
my_lg.error('TypeError错误, 原因数据库连接失败...(可能维护中)')
result = None
if result is None:
pass
else:
my_lg.info('------>>> 下面是数据库返回的所有符合条件的goods_id <<<------')
my_lg.info(str(result))
my_lg.info(
'--------------------------------------------------------')
my_lg.info('待更新个数: {0}'.format(len(result)))
my_lg.info('即将开始实时更新数据, 请耐心等待...'.center(100, '#'))
index = 1
# 释放内存,在外面声明就会占用很大的，所以此处优化内存的方法是声明后再删除释放
ali_1688 = ALi1688LoginAndParse(logger=my_lg)
for item in result: # 实时更新数据
if index % 5 == 0:
ali_1688 = ALi1688LoginAndParse(logger=my_lg)
if index % 50 == 0:
my_lg.info('正在重置，并与数据库建立新连接中...')
tmp_sql_server = SqlServerMyPageInfoSaveItemPipeline()
my_lg.info('与数据库的新连接成功建立...')
if tmp_sql_server.is_connect_success:
my_lg.info(
'------>>>| 正在更新的goods_id为({0}) | --------->>>@ 索引值为({1})'
.format(item[0], index))
data = ali_1688.get_ali_1688_data(item[0])
if isinstance(data, int) is True: # 单独处理返回tt为4041
continue
else:
pass
if data.get('is_delete') == 1: # 单独处理【原先插入】就是 下架状态的商品
data['goods_id'] = item[0]
data['shelf_time'], data[
'delete_time'] = get_shelf_time_and_delete_time(
tmp_data=data,
is_delete=item[1],
shelf_time=item[4],
delete_time=item[5])
# my_lg.info('上架时间:{0}, 下架时间:{1}'.format(data['shelf_time'], data['delete_time']))
ali_1688.to_right_and_update_data(
data, pipeline=tmp_sql_server)
sleep(1.5) # 避免服务器更新太频繁
index += 1
gc.collect()
continue
data = ali_1688.deal_with_data()
if data != {}:
data['goods_id'] = item[0]
data['shelf_time'], data[
'delete_time'] = get_shelf_time_and_delete_time(
tmp_data=data,
is_delete=item[1],
shelf_time=item[4],
delete_time=item[5])
# my_lg.info('上架时间:{0}, 下架时间:{1}'.format(data['shelf_time'], data['delete_time']))
'''为了实现这个就必须保证price, taobao_price在第一次抓下来后一直不变，变得记录到_price_change_info字段中'''
# 业务逻辑
# 公司后台 modify_time > 转换时间，is_price_change=1, 然后对比pricechange里面的数据，要是一样就不提示平台员工改价格
data['_is_price_change'], data[
'_price_change_info'] = _get_price_change_info(
old_price=item[2],
old_taobao_price=item[3],
new_price=data['price'],
new_taobao_price=data['taobao_price'])
try:
old_sku_info = format_price_info_list(
price_info_list=json_2_dict(item[6]),
site_id=2)
except AttributeError: # 处理已被格式化过的
old_sku_info = item[6]
data['_is_price_change'], data[
'sku_info_trans_time'] = get_sku_info_trans_record(
old_sku_info=old_sku_info,
new_sku_info=format_price_info_list(
data['sku_map'], site_id=2),
is_price_change=item[7]
if item[7] is not None else 0)
ali_1688.to_right_and_update_data(
data, pipeline=tmp_sql_server)
sleep(.3) # 避免服务器更新太频繁
else: # 表示返回的data值为空值
pass
else: # 表示返回的data值为空值
my_lg.error('数据库连接失败，数据库可能关闭或者维护中')
pass
index += 1
gc.collect()
sleep(2.2)
my_lg.info('全部数据更新完毕'.center(100, '#')) # sleep(60*60)
if get_shanghai_time().hour == 0: # 0点以后不更新
sleep(60 * 60 * 5.5)
else:
sleep(5)
gc.collect()
def run_forever():
while True:
#### 实时更新数据
tmp_sql_server = SqlServerMyPageInfoSaveItemPipeline()
sql_str = '''
select GoodsID, IsDelete, Price, TaoBaoPrice, shelf_time, delete_time
from dbo.GoodsInfoAutoGet
where SiteID=25'''
try:
result = list(tmp_sql_server._select_table(sql_str=sql_str))
except TypeError:
print('TypeError错误, 原因数据库连接失败...(可能维护中)')
continue
print('------>>> 下面是数据库返回的所有符合条件的goods_id <<<------')
print(result)
print('--------------------------------------------------------')
print('即将开始实时更新数据, 请耐心等待...'.center(100, '#'))
index = 1
for item in result: # 实时更新数据
# 释放内存,在外面声明就会占用很大的，所以此处优化内存的方法是声明后再删除释放
vip = VipParse()
if index % 50 == 0: # 每50次重连一次，避免单次长连无响应报错
print('正在重置，并与数据库建立新连接中...')
# try:
# del tmp_sql_server
# except:
# pass
# gc.collect()
tmp_sql_server = SqlServerMyPageInfoSaveItemPipeline()
print('与数据库的新连接成功建立...')
if tmp_sql_server.is_connect_success:
print(
'------>>>| 正在更新的goods_id为(%s) | --------->>>@ 索引值为(%d)' %
(item[0], index))
vip.get_goods_data(goods_id=[0, item[0]])
data = vip.deal_with_data()
if data != {}:
data['goods_id'] = item[0]
data['shelf_time'], data[
'delete_time'] = get_shelf_time_and_delete_time(
tmp_data=data,
is_delete=item[1],
shelf_time=item[4],
delete_time=item[5])
data['_is_price_change'], data[
'_price_change_info'] = _get_price_change_info(
old_price=item[2],
old_taobao_price=item[3],
new_price=data['price'],
new_taobao_price=data['taobao_price'])
# print('------>>>| 爬取到的数据为: ', data)
vip.to_right_and_update_data(data=data,
pipeline=tmp_sql_server)
else: # 表示返回的data值为空值
pass
else: # 表示返回的data值为空值
print('数据库连接失败，数据库可能关闭或者维护中')
pass
index += 1
# try:
# del vip
# except:
# pass
gc.collect()
sleep(VIP_SLEEP_TIME)
print('全部数据更新完毕'.center(100, '#')) # sleep(60*60)
if get_shanghai_time().hour == 0: # 0点以后不更新
sleep(60 * 60 * 5.5)
else:
sleep(30)
# del vip
gc.collect()
def run_forever():
#### 实时更新数据
while True:
# ** 不能写成全局变量并放在循环中, 否则会一直记录到同一文件中
my_lg = set_logger(log_file_name=MY_SPIDER_LOGS_PATH + '/淘宝/实时更新/' +
str(get_shanghai_time())[0:10] + '.txt',
console_log_level=INFO,
file_log_level=ERROR)
sql_str = '''
select GoodsID, IsDelete, Price, TaoBaoPrice, shelf_time, delete_time
from dbo.GoodsInfoAutoGet
where SiteID=1 and MainGoodsID is not null
order by ID desc'''
# tmp_sql_server = SqlServerMyPageInfoSaveItemPipeline()
tmp_sql_server = SqlPools() # 使用sqlalchemy管理数据库连接池
try:
# result = list(tmp_sql_server.select_taobao_all_goods_id())
result = tmp_sql_server._select_table(sql_str=sql_str, )
except TypeError:
my_lg.error('TypeError错误, 原因数据库连接失败...(可能维护中)')
result = None
if result is None:
pass
else:
my_lg.info('------>>> 下面是数据库返回的所有符合条件的goods_id <<<------')
my_lg.info(str(result))
my_lg.info(
'--------------------------------------------------------')
my_lg.info('总计待更新个数: {0}'.format(len(result)))
my_lg.info('即将开始实时更新数据, 请耐心等待...'.center(100, '#'))
index = 1
for item in result: # 实时更新数据
taobao = TaoBaoLoginAndParse(logger=my_lg)
if index % 50 == 0: # 每50次重连一次，避免单次长连无响应报错
my_lg.info('正在重置，并与数据库建立新连接中...')
tmp_sql_server = SqlPools()
my_lg.info('与数据库的新连接成功建立...')
if tmp_sql_server.is_connect_success:
my_lg.info(
'------>>>| 正在更新的goods_id为(%s) | --------->>>@ 索引值为(%s)'
% (item[0], str(index)))
data = taobao.get_goods_data(item[0])
if data.get('is_delete') == 1: # 单独处理【原先插入】就是 下架状态的商品
data['goods_id'] = item[0]
data['shelf_time'], data[
'delete_time'] = get_shelf_time_and_delete_time(
tmp_data=data,
is_delete=item[1],
shelf_time=item[4],
delete_time=item[5])
# my_lg.info('------>>>| 爬取到的数据为: ' + str(data))
taobao.to_right_and_update_data(
data, pipeline=tmp_sql_server)
sleep(TAOBAO_REAL_TIMES_SLEEP_TIME) # 避免服务器更新太频繁
index += 1
gc.collect()
continue
data = taobao.deal_with_data(goods_id=item[0])
if data != {}:
data['goods_id'] = item[0]
data['shelf_time'], data[
'delete_time'] = get_shelf_time_and_delete_time(
tmp_data=data,
is_delete=item[1],
shelf_time=item[4],
delete_time=item[5])
data['_is_price_change'], data[
'_price_change_info'] = _get_price_change_info(
old_price=item[2],
old_taobao_price=item[3],
new_price=data['price'],
new_taobao_price=data['taobao_price'])
# my_lg.info('------>>>| 爬取到的数据为: ' + str(data))
taobao.to_right_and_update_data(
data, pipeline=tmp_sql_server)
else:
my_lg.info('------>>>| 休眠5s中...')
sleep(5)
else: # 表示返回的data值为空值
my_lg.error('数据库连接失败，数据库可能关闭或者维护中')
sleep(10)
pass
index += 1
# try:
# del taobao
# except:
# pass
gc.collect()
# 国外服务器上可以缩短时间, 可以设置为0s
sleep(TAOBAO_REAL_TIMES_SLEEP_TIME) # 不能太频繁，与用户请求错开尽量
my_lg.info('全部数据更新完毕'.center(100, '#')) # sleep(60*60)
if get_shanghai_time().hour == 0: # 0点以后不更新
sleep(60 * 60 * 5.5)
else:
sleep(5)
gc.collect()
restart_program()
def run_forever():
while True:
#### 实时更新数据
tmp_sql_server = SqlServerMyPageInfoSaveItemPipeline()
# and GETDATE()-ModfiyTime>1
sql_str = '''
select GoodsID, IsDelete, Price, TaoBaoPrice, shelf_time, delete_time
from dbo.GoodsInfoAutoGet
where SiteID=2 and MainGoodsID is not null and GETDATE()-ModfiyTime>1
order by ID desc
'''
try:
result = list(tmp_sql_server._select_table(sql_str=sql_str))
except TypeError:
print('TypeError错误, 原因数据库连接失败...(可能维护中)')
result = None
if result is None:
pass
else:
print('------>>> 下面是数据库返回的所有符合条件的goods_id <<<------')
print(result)
print('--------------------------------------------------------')
print('待更新个数: ', len(result))
print('即将开始实时更新数据, 请耐心等待...'.center(100, '#'))
index = 1
# 释放内存,在外面声明就会占用很大的，所以此处优化内存的方法是声明后再删除释放
ali_1688 = ALi1688LoginAndParse()
for item in result: # 实时更新数据
if index % 5 == 0:
ali_1688 = ALi1688LoginAndParse()
if index % 50 == 0: # 每50次重连一次，避免单次长连无响应报错
print('正在重置，并与数据库建立新连接中...')
# try:
# del tmp_sql_server
# except:
# pass
# gc.collect()
tmp_sql_server = SqlServerMyPageInfoSaveItemPipeline()
print('与数据库的新连接成功建立...')
if tmp_sql_server.is_connect_success:
print(
'------>>>| 正在更新的goods_id为(%s) | --------->>>@ 索引值为(%d)'
% (item[0], index))
data = ali_1688.get_ali_1688_data(item[0])
if isinstance(data, int) is True: # 单独处理返回tt为4041
continue
else:
pass
if data.get('is_delete') == 1: # 单独处理【原先插入】就是 下架状态的商品
data['goods_id'] = item[0]
data['shelf_time'], data[
'delete_time'] = get_shelf_time_and_delete_time(
tmp_data=data,
is_delete=item[1],
shelf_time=item[4],
delete_time=item[5])
print('上架时间:', data['shelf_time'], '下架时间:',
data['delete_time'])
# print('------>>>| 爬取到的数据为: ', data)
ali_1688.to_right_and_update_data(
data, pipeline=tmp_sql_server)
sleep(1.5) # 避免服务器更新太频繁
index += 1
gc.collect()
continue
data = ali_1688.deal_with_data()
if data != {}:
data['goods_id'] = item[0]
data['shelf_time'], data[
'delete_time'] = get_shelf_time_and_delete_time(
tmp_data=data,
is_delete=item[1],
shelf_time=item[4],
delete_time=item[5])
print('上架时间:', data['shelf_time'], '下架时间:',
data['delete_time'])
'''为了实现这个就必须保证price, taobao_price在第一次抓下来后一直不变，变得记录到_price_change_info字段中'''
# 业务逻辑
# 公司后台 modify_time > 转换时间，is_price_change=1, 然后对比pricechange里面的数据，要是一样就不提示平台员工改价格
data['_is_price_change'], data[
'_price_change_info'] = _get_price_change_info(
old_price=item[2],
old_taobao_price=item[3],
new_price=data['price'],
new_taobao_price=data['taobao_price'])
# print('------>>>| 爬取到的数据为: ', data)
ali_1688.to_right_and_update_data(
data, pipeline=tmp_sql_server)
sleep(.3) # 避免服务器更新太频繁
else: # 表示返回的data值为空值
pass
else: # 表示返回的data值为空值
print('数据库连接失败，数据库可能关闭或者维护中')
pass
index += 1
# try:
# del ali_1688
# except:
# pass
gc.collect()
sleep(2.2)
print('全部数据更新完毕'.center(100, '#')) # sleep(60*60)
if get_shanghai_time().hour == 0: # 0点以后不更新
sleep(60 * 60 * 5.5)
else:
sleep(5)
# del ali_1688
gc.collect()
def run_forever():
while True:
my_lg = set_logger(
log_file_name=MY_SPIDER_LOGS_PATH + '/jd/实时更新/' + str(get_shanghai_time())[0:10] + '.txt',
console_log_level=INFO,
file_log_level=ERROR)
#### 实时更新数据
tmp_sql_server = SqlServerMyPageInfoSaveItemPipeline()
# and GETDATE()-ModfiyTime>1 and IsDelete=0
try:
result = list(tmp_sql_server._select_table(sql_str=jd_select_str_1))
except TypeError:
my_lg.error('TypeError错误, 原因数据库连接失败...(可能维护中)')
continue
my_lg.info('------>>> 下面是数据库返回的所有符合条件的goods_id <<<------')
my_lg.info(str(result))
my_lg.info('--------------------------------------------------------')
my_lg.info('总计待更新个数:{}'.format(len(result)))
my_lg.info('即将开始实时更新数据, 请耐心等待...'.center(100, '#'))
index = 1
# 释放内存,在外面声明就会占用很大的，所以此处优化内存的方法是声明后再删除释放
jd = JdParse(logger=my_lg)
for item in result: # 实时更新数据
# # 释放内存,在外面声明就会占用很大的，所以此处优化内存的方法是声明后再删除释放
# jd = JdParse()
if index % 10 == 0:
try: del jd
except: pass
gc.collect()
jd = JdParse(logger=my_lg)
if index % 50 == 0: # 每50次重连一次，避免单次长连无响应报错
my_lg.info('正在重置，并与数据库建立新连接中...')
tmp_sql_server = SqlServerMyPageInfoSaveItemPipeline()
my_lg.info('与数据库的新连接成功建立...')
if tmp_sql_server.is_connect_success:
my_lg.info('------>>>| 正在更新的goods_id为({}) | --------->>>@ 索引值为({})'.format(item[1], index))
tmp_item = []
if item[0] == 7 or item[0] == 8: # 从数据库中取出时，先转换为对应的类型
tmp_item.append(0)
elif item[0] == 9:
tmp_item.append(1)
elif item[0] == 10:
tmp_item.append(2)
tmp_item.append(item[1])
jd.get_goods_data(goods_id=tmp_item)
data = jd.deal_with_data(goods_id=tmp_item)
if data != {}:
data['goods_id'] = item[1]
data['shelf_time'], data['delete_time'] = get_shelf_time_and_delete_time(
tmp_data=data,
is_delete=item[2],
shelf_time=item[5],
delete_time=item[6])
my_lg.info('上架时间: {0}, 下架时间: {1}'.format(data['shelf_time'], data['delete_time']))
data['_is_price_change'], data['_price_change_info'] = _get_price_change_info(
old_price=item[3],
old_taobao_price=item[4],
new_price=data['price'],
new_taobao_price=data['taobao_price'])
site_id = jd._from_jd_type_get_site_id_value(jd_type=data['jd_type'])
try:
old_sku_info = format_price_info_list(
price_info_list=json_2_dict(item[7]),
site_id=site_id)
except AttributeError: # 处理已被格式化过的
old_sku_info = item[7]
data['_is_price_change'], data['sku_info_trans_time'] = get_sku_info_trans_record(
old_sku_info=old_sku_info,
new_sku_info=format_price_info_list(data['price_info_list'], site_id=site_id),
is_price_change=item[8] if item[8] is not None else 0
)
jd.to_right_and_update_data(data, pipeline=tmp_sql_server)
else: # 表示返回的data值为空值
pass
else: # 表示返回的data值为空值
my_lg.error('数据库连接失败，数据库可能关闭或者维护中')
pass
index += 1
gc.collect()
sleep(1.2)
my_lg.info('全部数据更新完毕'.center(100, '#')) # sleep(60*60)
try: del jd
except: pass
if get_shanghai_time().hour == 0: # 0点以后不更新
sleep(60*60*5.5)
else:
sleep(5)
gc.collect()
def run_forever():
while True:
# ** 不能写成全局变量并放在循环中, 否则会一直记录到同一文件中
my_lg = set_logger(log_file_name=MY_SPIDER_LOGS_PATH + '/网易考拉/实时更新/' +
str(get_shanghai_time())[0:10] + '.txt',
console_log_level=INFO,
file_log_level=ERROR)
#### 实时更新数据
sql_cli = SqlServerMyPageInfoSaveItemPipeline()
try:
result = list(sql_cli._select_table(sql_str=kl_select_str_1))
except TypeError:
my_lg.error('TypeError错误, 原因数据库连接失败...(可能维护中)')
result = None
if result is None:
pass
else:
_block_print_db_old_data(result=result, logger=my_lg)
index = 1
# 释放内存,在外面声明就会占用很大的，所以此处优化内存的方法是声明后再删除释放
kaola = KaoLaParse(logger=my_lg)
for item in result: # 实时更新数据
goods_id = item[1]
if index % 5 == 0:
try:
del kaola
except:
pass
kaola = KaoLaParse(logger=my_lg)
collect()
sql_cli = _block_get_new_db_conn(
db_obj=sql_cli,
index=index,
logger=my_lg,
remainder=10,
)
if sql_cli.is_connect_success:
my_lg.info(
'------>>>| 正在更新的goods_id为(%s) | --------->>>@ 索引值为(%s)'
% (str(goods_id), str(index)))
db_goods_info_obj = KLDbGoodsInfoObj(item=item,
logger=my_lg)
data = kaola._get_goods_data(goods_id=goods_id)
if data.get('is_delete', 0) == 1:
# 单独处理下架商品
data['goods_id'] = goods_id
data['shelf_time'], data[
'delete_time'] = get_shelf_time_and_delete_time(
tmp_data=data,
is_delete=db_goods_info_obj.is_delete,
shelf_time=db_goods_info_obj.shelf_time,
delete_time=db_goods_info_obj.delete_time,
)
try:
kaola.to_right_and_update_data(data,
pipeline=sql_cli)
except Exception:
my_lg.error(exc_info=True)
sleep(TMALL_REAL_TIMES_SLEEP_TIME)
index += 1
collect()
continue
data = kaola._deal_with_data()
if data != {}:
if data.get('is_delete', 0) == 1:
_handle_goods_shelves_in_auto_goods_table(
goods_id=goods_id,
logger=my_lg,
sql_cli=sql_cli,
)
sleep(TMALL_REAL_TIMES_SLEEP_TIME)
continue
else:
data = get_goods_info_change_data(
target_short_name='kl',
logger=my_lg,
data=data,
db_goods_info_obj=db_goods_info_obj,
)
kaola.to_right_and_update_data(data, pipeline=sql_cli)
else: # 表示返回的data值为空值
my_lg.info('------>>>| 休眠8s中...')
sleep(8)
else: # 表示返回的data值为空值
my_lg.error('数据库连接失败，数据库可能关闭或者维护中')
sleep(5)
pass
index += 1
collect()
sleep(TMALL_REAL_TIMES_SLEEP_TIME)
my_lg.info('全部数据更新完毕'.center(100, '#')) # sleep(60*60)
if get_shanghai_time().hour == 0: # 0点以后不更新
sleep(60 * 60 * 5.5)
else:
sleep(60)
collect()
def run_forever():
while True:
# ** 不能写成全局变量并放在循环中, 否则会一直记录到同一文件中
my_lg = set_logger(
log_file_name=MY_SPIDER_LOGS_PATH + '/网易严选/实时更新/' + str(get_shanghai_time())[0:10] + '.txt',
console_log_level=INFO,
file_log_level=ERROR
)
#### 实时更新数据
tmp_sql_server = SqlServerMyPageInfoSaveItemPipeline()
try:
result = list(tmp_sql_server._select_table(sql_str=yx_select_str_1))
except TypeError:
my_lg.error('TypeError错误, 原因数据库连接失败...(可能维护中)')
result = None
if result is None:
pass
else:
my_lg.info('------>>> 下面是数据库返回的所有符合条件的goods_id <<<------')
my_lg.info(str(result))
my_lg.info('--------------------------------------------------------')
my_lg.info('总计待更新个数: {0}'.format(len(result)))
my_lg.info('即将开始实时更新数据, 请耐心等待...'.center(100, '#'))
index = 1
# 释放内存,在外面声明就会占用很大的，所以此处优化内存的方法是声明后再删除释放
yanxuan = YanXuanParse(logger=my_lg)
for item in result: # 实时更新数据
if index % 5 == 0:
try:
del yanxuan
except:
pass
yanxuan = YanXuanParse(logger=my_lg)
gc.collect()
if index % 10 == 0: # 每10次重连一次，避免单次长连无响应报错
my_lg.info('正在重置，并与数据库建立新连接中...')
tmp_sql_server = SqlServerMyPageInfoSaveItemPipeline()
my_lg.info('与数据库的新连接成功建立...')
if tmp_sql_server.is_connect_success:
my_lg.info('------>>>| 正在更新的goods_id为(%s) | --------->>>@ 索引值为(%s)' % (str(item[1]), str(index)))
yanxuan._get_goods_data(goods_id=item[1])
data = yanxuan._deal_with_data()
if data != {}:
data['goods_id'] = item[1]
data['shelf_time'], data['delete_time'] = get_shelf_time_and_delete_time(
tmp_data=data,
is_delete=item[2],
shelf_time=item[5],
delete_time=item[6])
if data.get('is_delete') == 1: # 单独处理下架商品
my_lg.info('@@@ 该商品已下架...')
tmp_sql_server._update_table_2(sql_str=yx_update_str_2, params=(item[1],), logger=my_lg)
sleep(TMALL_REAL_TIMES_SLEEP_TIME)
continue
else:
data['_is_price_change'], data['_price_change_info'] = _get_price_change_info(
old_price=item[3],
old_taobao_price=item[4],
new_price=data['price'],
new_taobao_price=data['taobao_price']
)
try:
old_sku_info = format_price_info_list(price_info_list=json_2_dict(item[7]), site_id=30)
except AttributeError: # 处理已被格式化过的
old_sku_info = item[7]
data['_is_price_change'], data['sku_info_trans_time'] = get_sku_info_trans_record(
old_sku_info=old_sku_info,
new_sku_info=format_price_info_list(data['price_info_list'], site_id=30),
is_price_change=item[8] if item[8] is not None else 0
)
yanxuan.to_right_and_update_data(data, pipeline=tmp_sql_server)
else: # 表示返回的data值为空值
my_lg.info('------>>>| 休眠8s中...')
sleep(8)
else: # 表示返回的data值为空值
my_lg.error('数据库连接失败，数据库可能关闭或者维护中')
sleep(5)
pass
index += 1
gc.collect()
sleep(TMALL_REAL_TIMES_SLEEP_TIME)
my_lg.info('全部数据更新完毕'.center(100, '#')) # sleep(60*60)
if get_shanghai_time().hour == 0: # 0点以后不更新
sleep(60 * 60 * 5.5)
else:
sleep(60)
gc.collect()
def run_forever():
while True:
# ** 不能写成全局变量并放在循环中, 否则会一直记录到同一文件中
my_lg = set_logger(log_file_name=MY_SPIDER_LOGS_PATH + '/天猫/实时更新/' +
str(get_shanghai_time())[0:10] + '.txt',
console_log_level=INFO,
file_log_level=ERROR)
#### 实时更新数据
tmp_sql_server = SqlServerMyPageInfoSaveItemPipeline()
try:
result = list(
tmp_sql_server._select_table(sql_str=tm_select_str_3))
except TypeError:
my_lg.error('TypeError错误, 原因数据库连接失败...(可能维护中)')
result = None
if result is None:
pass
else:
my_lg.info('------>>> 下面是数据库返回的所有符合条件的goods_id <<<------')
my_lg.info(str(result))
my_lg.info('总计待更新个数: {0}'.format(len(result)))
my_lg.info(
'--------------------------------------------------------')
my_lg.info('即将开始实时更新数据, 请耐心等待...'.center(100, '#'))
index = 1
# 释放内存,在外面声明就会占用很大的，所以此处优化内存的方法是声明后再删除释放
tmall = TmallParse(logger=my_lg)
for item in result: # 实时更新数据
if index % 5 == 0:
try:
del tmall
except:
pass
tmall = TmallParse(logger=my_lg)
gc.collect()
if index % 10 == 0: # 每10次重连一次，避免单次长连无响应报错
my_lg.info('正在重置，并与数据库建立新连接中...')
tmp_sql_server = SqlServerMyPageInfoSaveItemPipeline()
my_lg.info('与数据库的新连接成功建立...')
if tmp_sql_server.is_connect_success:
my_lg.info(
'------>>>| 正在更新的goods_id为(%s) | --------->>>@ 索引值为(%s)'
% (str(item[1]), str(index)))
tmp_item = []
if item[0] == 3: # 从数据库中取出时，先转换为对应的类型
tmp_item.append(0)
elif item[0] == 4:
tmp_item.append(1)
elif item[0] == 6:
tmp_item.append(2)
tmp_item.append(item[1])
oo = tmall.get_goods_data(goods_id=tmp_item)
oo_is_delete = oo.get('is_detele', 0) # 避免下面解析data错误休眠
if isinstance(oo, int): # 单独处理return 4041
index += 1
sleep(TMALL_REAL_TIMES_SLEEP_TIME)
continue
data = tmall.deal_with_data()
if data != {}:
data['goods_id'] = item[1]
data['shelf_time'], data[
'delete_time'] = get_shelf_time_and_delete_time(
tmp_data=data,
is_delete=item[2],
shelf_time=item[5],
delete_time=item[6])
data['_is_price_change'], data[
'_price_change_info'] = _get_price_change_info(
old_price=item[3],
old_taobao_price=item[4],
new_price=data['price'],
new_taobao_price=data['taobao_price'])
site_id = tmall._from_tmall_type_get_site_id(
type=data['type'])
try:
old_sku_info = format_price_info_list(
price_info_list=json_2_dict(item[7]),
site_id=site_id)
except AttributeError: # 处理已被格式化过的
old_sku_info = item[7]
data['_is_price_change'], data[
'sku_info_trans_time'] = get_sku_info_trans_record(
old_sku_info=old_sku_info,
new_sku_info=format_price_info_list(
data['price_info_list'], site_id=site_id),
is_price_change=item[8]
if item[8] is not None else 0)
tmall.to_right_and_update_data(data,
pipeline=tmp_sql_server)
else: # 表示返回的data值为空值
if oo_is_delete == 1:
pass
else:
my_lg.info('------>>>| 休眠8s中...')
sleep(8)
else: # 表示返回的data值为空值
my_lg.error('数据库连接失败，数据库可能关闭或者维护中')
sleep(5)
pass
index += 1
gc.collect()
sleep(TMALL_REAL_TIMES_SLEEP_TIME)
my_lg.info('全部数据更新完毕'.center(100, '#')) # sleep(60*60)
if get_shanghai_time().hour == 0: # 0点以后不更新
sleep(60 * 60 * 5.5)
else:
sleep(5)
gc.collect()
def run_forever():
#### 实时更新数据
while True:
# ** 不能写成全局变量并放在循环中, 否则会一直记录到同一文件中
my_lg = set_logger(log_file_name=MY_SPIDER_LOGS_PATH + '/淘宝/实时更新/' +
str(get_shanghai_time())[0:10] + '.txt',
console_log_level=INFO,
file_log_level=ERROR)
# tmp_sql_server = SqlServerMyPageInfoSaveItemPipeline()
tmp_sql_server = SqlPools() # 使用sqlalchemy管理数据库连接池
try:
# result = list(tmp_sql_server.select_taobao_all_goods_id())
result = tmp_sql_server._select_table(sql_str=tb_select_str_3, )
except TypeError:
my_lg.error('TypeError错误, 原因数据库连接失败...(可能维护中)')
result = None
if result is None:
pass
else:
my_lg.info('------>>> 下面是数据库返回的所有符合条件的goods_id <<<------')
my_lg.info(str(result))
my_lg.info(
'--------------------------------------------------------')
my_lg.info('总计待更新个数: {0}'.format(len(result)))
my_lg.info('即将开始实时更新数据, 请耐心等待...'.center(100, '#'))
index = 1
for item in result: # 实时更新数据
taobao = TaoBaoLoginAndParse(logger=my_lg)
if index % 50 == 0: # 每50次重连一次，避免单次长连无响应报错
my_lg.info('正在重置，并与数据库建立新连接中...')
tmp_sql_server = SqlPools()
my_lg.info('与数据库的新连接成功建立...')
if tmp_sql_server.is_connect_success:
my_lg.info(
'------>>>| 正在更新的goods_id为(%s) | --------->>>@ 索引值为(%s)'
% (item[0], str(index)))
oo = taobao.get_goods_data(item[0])
oo_is_delete = oo.get('is_delete', 0) # 避免下面解析data错误休眠
data = taobao.deal_with_data(goods_id=item[0])
if data != {}:
data['goods_id'] = item[0]
data['shelf_time'], data[
'delete_time'] = get_shelf_time_and_delete_time(
tmp_data=data,
is_delete=item[1],
shelf_time=item[4],
delete_time=item[5])
data['_is_price_change'], data[
'_price_change_info'] = _get_price_change_info(
old_price=item[2],
old_taobao_price=item[3],
new_price=data['price'],
new_taobao_price=data['taobao_price'])
try:
old_sku_info = format_price_info_list(
price_info_list=json_2_dict(item[6]),
site_id=1)
except AttributeError: # 处理已被格式化过的
old_sku_info = item[6]
data['_is_price_change'], data[
'sku_info_trans_time'] = get_sku_info_trans_record(
old_sku_info=old_sku_info,
new_sku_info=format_price_info_list(
data['price_info_list'], site_id=1),
is_price_change=item[7]
if item[7] is not None else 0)
taobao.to_right_and_update_data(
data, pipeline=tmp_sql_server)
else:
if oo_is_delete == 1:
pass
else:
my_lg.info('------>>>| 休眠5s中...')
sleep(4)
else: # 表示返回的data值为空值
my_lg.error('数据库连接失败，数据库可能关闭或者维护中')
sleep(10)
pass
index += 1
gc.collect()
# 国外服务器上可以缩短时间, 可以设置为0s
sleep(TAOBAO_REAL_TIMES_SLEEP_TIME) # 不能太频繁，与用户请求错开尽量
my_lg.info('全部数据更新完毕'.center(100, '#')) # sleep(60*60)
if get_shanghai_time().hour == 0: # 0点以后不更新
sleep(60 * 60 * 5.5)
else:
sleep(5)
gc.collect()
restart_program()
