def updatePrice():
db_conn = ibbdlib.get_db_conn(**db_server)
cmd_str = 'SELECT DISTINCT(item_id) AS item_id FROM ibbd2.user_item_monitor'
iids = [row.item_id for row in list(db_conn.iter(cmd_str))]
db_conn.close()
re_conn = ibbdlib.get_redis_conn(**redis_server)
re_conn.sadd('Schedule_itempricemonitor', *iids)
del re_conn
def master():
db_conn = ibbdlib.get_db_conn(**db_server['223.4.155.172'])
db_conn.execute("TRUNCATE TABLE ibbd2.lib_cat_industry_keyword_2")
db_conn.close()
ibbdlib.migratetable.migrate_table(db_server['223.4.155.152'],
db_server['223.4.155.172'],
'ibbd2.user2')
ibbdlib.migratetable.migrate_table(db_server['223.4.155.152'],
db_server['223.4.155.172'],
'ibbd2.user_keywords')
ibbdlib.migratetable.migrate_table(db_server['223.4.155.152'],
db_server['223.4.155.172'],
'ibbd2.lib_cat_industry_keyword_2')
ibbdlib.migratetable.migrate_table_by_query(
db_server['223.4.155.152'], db_server['223.4.155.172'],
'ibbd2.user_shop_monitor_items', """SELECT user, shop_type,
(CASE WHEN shop_id='' THEN 0 ELSE shop_id END) shop_id,
user_num_id, item_id, status,
population_tsmp
FROM ibbd2.user_shop_monitor_items""")
# keep connection
dbConnPool = None
reConn = None
while True:
try:
dbConnPool = ConnectionPool(max_connections=1000,
host='localhost',
user='******',
passwd='ibbd_etl_secure',
db='topspider',
charset='utf8')
# reConn = getRedisConn2()
reConn = ibbdlib.get_redis_conn(**redis_server)
break
except Exception as e:
print e
runKeywordsSchedule(reConn, dbConnPool)
_ensure_schedule_complete(reConn)
dbConnPool.disconnect()
dbConnPool = ConnectionPool(max_connections=1000,
host='localhost',
user='******',
passwd='ibbd_etl_secure',
db='topspider',
charset='utf8')
while True:
try:
# migrateScheduleToRedis(reConn, dbConnPool)
break
except:
traceback.print_exc()
dbConnPool.disconnect()
del reConn
def clear_error_schedule():
redis_conn = ibbdlib.get_redis_conn(**redis_server)
for schedule_error_key in redis_conn.keys("Sche*Error"):
if redis_conn.type(schedule_error_key) == "hash":
print "delete", schedule_error_key, redis_conn.hlen(schedule_error_key)
redis_conn.delete(schedule_error_key)
for schedule_error_key in redis_conn.keys("Sche*process"):
if redis_conn.type(schedule_error_key) == "hash":
print "delete", schedule_error_key, redis_conn.hlen(schedule_error_key)
redis_conn.delete(schedule_error_key)
for schedule_error_key in redis_conn.keys("Sche*error"):
if redis_conn.type(schedule_error_key) == "hash":
print "delete", schedule_error_key, redis_conn.hlen(schedule_error_key)
redis_conn.delete(schedule_error_key)
del redis_conn
def clear_error_schedule():
redis_conn = ibbdlib.get_redis_conn(**redis_server)
for schedule_error_key in redis_conn.keys('Sche*Error'):
if redis_conn.type(schedule_error_key) == 'hash':
print 'delete', schedule_error_key, redis_conn.hlen(
schedule_error_key)
redis_conn.delete(schedule_error_key)
for schedule_error_key in redis_conn.keys('Sche*process'):
if redis_conn.type(schedule_error_key) == 'hash':
print 'delete', schedule_error_key, redis_conn.hlen(
schedule_error_key)
redis_conn.delete(schedule_error_key)
for schedule_error_key in redis_conn.keys('Sche*error'):
if redis_conn.type(schedule_error_key) == 'hash':
print 'delete', schedule_error_key, redis_conn.hlen(
schedule_error_key)
redis_conn.delete(schedule_error_key)
del redis_conn
def _prepar_trade_date():
# rename schedule
redis_conn = ibbdlib.get_redis_conn(**redis_server)
if redis_conn.scard('Schedule_itemTrade2') > 0:
redis_conn.rename('Schedule_itemTrade2', 'Schedule_itemTrade2_bak')
# insert lock
db_conn = ibbdlib.get_db_conn(**db_server)
# while True:
# try:
# is_inserting = False
# for row in db_conn.iter("SHOW PROCESSLIST"):
# if row.Info:
# if row.Info.encode('utf-8', 'ignore').find('top_item_trade') > -1:
# is_inserting = True
# if is_inserting:
# print 'processing insert lock, wait for 10 seconds'
# time.sleep(10)
# else:
# print 'no insert lock'
# break
# except:
# traceback.print_exc()
try:
# db_conn.execute(
# "ALTER TABLE topspider.top_item_trade RENAME topspider.top_item_trade_his_%s" % fetch_date_short)
# db_conn.execute(
# "CREATE TABLE topspider.top_item_trade LIKE topspider.top_item_trade_his_%s" % fetch_date_short)
for i in range(split_tb_num):
table_name = 'top_item_trade_his_%s_%d' % (
datetime.now().strftime('%Y%m%d'), i + 1)
db_conn.execute(
"CREATE TABLE topspider.%s LIKE topspider.top_item_trade" %
table_name)
db_conn.execute("TRUNCATE TABLE topspider.top_itemsearchresult")
except:
traceback.print_exc()
db_conn.close()
# rename schedule name
if redis_conn.scard('Schedule_itemTrade2_bak') > 0:
redis_conn.rename('Schedule_itemTrade2_bak', 'Schedule_itemTrade2')
pass
del redis_conn
def _prepar_trade_date():
# rename schedule
redis_conn = ibbdlib.get_redis_conn(**redis_server)
if redis_conn.scard("Schedule_itemTrade2") > 0:
redis_conn.rename("Schedule_itemTrade2", "Schedule_itemTrade2_bak")
# insert lock
db_conn = ibbdlib.get_db_conn(**db_server)
# while True:
# try:
# is_inserting = False
# for row in db_conn.iter("SHOW PROCESSLIST"):
# if row.Info:
# if row.Info.encode('utf-8', 'ignore').find('top_item_trade') > -1:
# is_inserting = True
# if is_inserting:
# print 'processing insert lock, wait for 10 seconds'
# time.sleep(10)
# else:
# print 'no insert lock'
# break
# except:
# traceback.print_exc()
try:
# db_conn.execute(
# "ALTER TABLE topspider.top_item_trade RENAME topspider.top_item_trade_his_%s" % fetch_date_short)
# db_conn.execute(
# "CREATE TABLE topspider.top_item_trade LIKE topspider.top_item_trade_his_%s" % fetch_date_short)
for i in range(split_tb_num):
table_name = "top_item_trade_his_%s_%d" % (datetime.now().strftime("%Y%m%d"), i + 1)
db_conn.execute("CREATE TABLE topspider.%s LIKE topspider.top_item_trade" % table_name)
db_conn.execute("TRUNCATE TABLE topspider.top_itemsearchresult")
except:
traceback.print_exc()
db_conn.close()
# rename schedule name
if redis_conn.scard("Schedule_itemTrade2_bak") > 0:
redis_conn.rename("Schedule_itemTrade2_bak", "Schedule_itemTrade2")
pass
del redis_conn
def _prepar_trade_date():
# rename schedule
redis_conn = ibbdlib.get_redis_conn()
if redis_conn.scard('Schedule_itemTrade2') > 0:
redis_conn.rename('Schedule_itemTrade2', 'Schedule_itemTrade2_bak')
# insert lock
db_conn = ibbdlib.get_db_conn(**db_server)
while 1:
try:
is_inserting = False
for row in db_conn.iter("SHOW PROCESSLIST"):
if row.Info:
if row.Info.encode('utf-8', 'ignore').find('top_item_trade') > -1:
is_inserting = True
if is_inserting:
print 'processing insert lock, wait for 10 seconds'
time.sleep(10)
else:
print 'no insert lock'
break
except:
traceback.print_exc()
try:
db_conn.execute(
"ALTER TABLE topspider.top_item_trade RENAME topspider.top_item_trade_his_%s" % fetch_date_short)
db_conn.execute(
"CREATE TABLE topspider.top_item_trade LIKE topspider.top_item_trade_his_%s" % fetch_date_short)
except:
traceback.print_exc()
db_conn.close()
# rename schedule name
if redis_conn.scard('Schedule_itemTrade2_bak') > 0:
redis_conn.rename('Schedule_itemTrade2_bak', 'Schedule_itemTrade2')
del redis_conn
def master():
db_conn = ibbdlib.get_db_conn(**db_server['223.4.155.172'])
db_conn.execute("TRUNCATE TABLE ibbd2.lib_cat_industry_keyword_2")
db_conn.close()
ibbdlib.migratetable.migrate_table(
db_server['223.4.155.152'],
db_server['223.4.155.172'],
'ibbd2.user2')
ibbdlib.migratetable.migrate_table(
db_server['223.4.155.152'],
db_server['223.4.155.172'],
'ibbd2.user_keywords')
ibbdlib.migratetable.migrate_table(
db_server['223.4.155.152'],
db_server['223.4.155.172'],
'ibbd2.lib_cat_industry_keyword_2')
ibbdlib.migratetable.migrate_table_by_query(
db_server['223.4.155.152'],
db_server['223.4.155.172'],
'ibbd2.user_shop_monitor_items',
"""SELECT user, shop_type,
(CASE WHEN shop_id='' THEN 0 ELSE shop_id END) shop_id,
user_num_id, item_id, status,
population_tsmp
FROM ibbd2.user_shop_monitor_items"""
)
# keep connection
dbConnPool = None
reConn = None
while True:
try:
dbConnPool = ConnectionPool(max_connections=1000,
host='localhost',
user='******',
passwd='ibbd_etl_secure',
db='topspider',
charset='utf8')
# reConn = getRedisConn2()
reConn = ibbdlib.get_redis_conn(**redis_server)
break
except Exception as e:
print e
runKeywordsSchedule(reConn, dbConnPool)
_ensure_schedule_complete(reConn)
dbConnPool.disconnect()
dbConnPool = ConnectionPool(max_connections=1000,
host='localhost',
user='******',
passwd='ibbd_etl_secure',
db='topspider',
charset='utf8')
while True:
try:
# migrateScheduleToRedis(reConn, dbConnPool)
break
except:
traceback.print_exc()
dbConnPool.disconnect()
del reConn
'Schedule_shopmonitor_*_process',
'Schedule_shopmonitor_*_error',
'Schedule_itemprice_*_process',
'Schedule_itemprice_*_error'
]
)
).strip(',')
return set(blocked_keys.split(',') if blocked_keys != '' else [])
def remove_redis_blocked_keys(re_conn, blocked_keys):
for key in blocked_keys:
re_conn.delete(key)
try:
re_conn = ibbdlib.get_redis_conn(**redis_server)
keys = [
'Schedule_itemFullInfo',
'Schedule_itemTrade2',
'Schedule_shopFullInfo'
]
extend_keys = []
for key_pre in ['Schedule_shopmonitor_', 'Schedule_itemprice_']:
extend_keys.extend(
[key for key in re_conn.keys('%s*' % key_pre)
if re.match(r'^%s\d+$' % key_pre, key)]
)
total1 = get_redis_sets_len(re_conn, keys + extend_keys)
blocked_keys = get_redis_blocked_keys(re_conn)
time.sleep(60 * 2)
def _getReConn():
"""获取Redis连接"""
return ibbdlib.get_redis_conn(**redis_server)
def test_slave_db_server(self):
from slave import _get_item_trade_save_table
re_conn = ibbdlib.get_redis_conn()
print _get_item_trade_save_table(re_conn, '10984795')
del re_conn
#!/usr/bin/python2.7 # -*- coding: utf-8 -*- import ibbdlib re_conn = ibbdlib.get_redis_conn(host="223.4.155.152") re_conn.save() del re_conn
#!/usr/bin/python2.7
# -*- coding: utf-8 -*-
import json
import ibbdlib
from slave_config import slave_config
re_conn = ibbdlib.get_redis_conn(**slave_config.get('default').get('redis'))
schedule_db_mapping = {
'Schedule_topItemFullInfo': {
'host': '223.4.155.152',
'user': '******',
'passwd': 'spider',
'db': 'topspider',
'charset': 'utf8'
}
}
slave_db_server = json.loads(re_conn.get('slave_db_server'))
slave_db_server.update(schedule_db_mapping)
re_conn.set('slave_db_server', json.dumps(slave_db_server))
def test_slave_db_server(self):
from slave import _get_item_trade_save_table
re_conn = ibbdlib.get_redis_conn()
print _get_item_trade_save_table(re_conn, '10984795')
del re_conn
#!/usr/bin/python2.7
# -*- coding: utf-8 -*-
from schedule_rollback import rollbackErrorSchedule
import time
import ibbdlib
from slave_config import slave_config
re_conn = ibbdlib.get_redis_conn(**slave_config["default"]["redis"])
if re_conn.scard("Schedule_itemTrade2") == 0:
print time.asctime(), "roll back schedules"
try:
rollbackErrorSchedule()
except Exception, e:
print e
else:
print time.asctime(), "keep runing"
# clean error topshop schedule
# key Schedule_shopmonitor_*_process/Schedule_shopmonitor_*_error
for redis_k in ["Schedule_shopmonitor_*_process", "Schedule_shopmonitor_*_error"]:
if re_conn.keys(redis_k):
print "delete key", redis_k
re_conn.delete(redis_k)
del re_conn
#!/usr/bin/python2.7
# -*- coding: utf-8 -*-
from schedule_rollback import rollbackErrorSchedule
import time
import ibbdlib
from slave_config import slave_config
re_conn = ibbdlib.get_redis_conn(**slave_config['default']['redis'])
if re_conn.scard('Schedule_itemTrade2') == 0:
print time.asctime(), "roll back schedules"
try:
rollbackErrorSchedule()
except Exception, e:
print e
else:
print time.asctime(), "keep runing"
# clean error topshop schedule
# key Schedule_shopmonitor_*_process/Schedule_shopmonitor_*_error
for redis_k in [
'Schedule_shopmonitor_*_process', 'Schedule_shopmonitor_*_error'
]:
if re_conn.keys(redis_k):
print 'delete key', redis_k
re_conn.delete(redis_k)
del re_conn
#!/usr/bin/python2.7
# -*- coding: utf-8 -*-
import ibbdlib
import ibbdlib.redislib
from datetime import datetime, timedelta
cmd_str = "SELECT item_id, MAX(date_of) AS date_of FROM ibbd2.top_item2 GROUP BY item_id"
commands = []
db_conn = ibbdlib.get_db_conn()
for row in db_conn.iter(cmd_str):
commands.append(['HSET', 'TopItemTradeUpdateTime', str(row.item_id), (row.date_of+timedelta(1)).strftime('%Y/%m/%d')])
print commands[0]
db_conn.close()
re_conn = ibbdlib.get_redis_conn()
ibbdlib.redislib.redis_pipe(re_conn, commands)
del re_conn
def _getReConn():
"""获取Redis连接"""
return ibbdlib.get_redis_conn(**redis_server)
#!/usr/bin/python2.7 # -*- coding: utf-8 -*- import ibbdlib re_conn = ibbdlib.get_redis_conn(host='223.4.155.152') re_conn.save() del re_conn
def get_re_conn():
return ibbdlib.get_redis_conn(**re_server)
FROM topspider.top_itemsearchresult T1
WHERE T1.population_tsmp > curdate()
)T4
ON T2.keyword = T4.keyword
WHERE T4.keyword IS NULL"""
for row in db_conn.iter(query):
keyword = row.keyword.encode('utf-8')
items = getItemsByKeyword(keyword, 'utf-8', page=5)
saveKeywordSearchResult(items, db_pool)
time.sleep(1)
db_conn.close()
# db_pool.disconnect()
re_conn = ibbdlib.get_redis_conn(**{
'host': 'localhost',
'port': 6379,
'password': '******'
})
db_conn = ibbdlib.get_db_conn(**db_server)
iids = set()
uids = set()
query = """SELECT DISTINCT item_id, user_num_id
FROM topspider.top_itemsearchresult
WHERE population_tsmp > '%s'""" % now.strftime('%Y-%m-%d %H:%M:%S')
for row in db_conn.iter(query):
iids.add(row.item_id)
uids.add(row.user_num_id)
print 'iids', len(iids), 'uids', len(uids)
if iids:
re_conn.sadd('Schedule_itemTrade2', *iids)
re_conn.sadd('Schedule_itemFullInfo', *iids)
WHERE T1.population_tsmp > curdate()
)T4
ON T2.keyword = T4.keyword
WHERE T4.keyword IS NULL"""
for row in db_conn.iter(query):
keyword = row.keyword.encode('utf-8')
items = getItemsByKeyword(keyword, 'utf-8', page=5)
saveKeywordSearchResult(items, db_pool)
time.sleep(1)
db_conn.close()
# db_pool.disconnect()
re_conn = ibbdlib.get_redis_conn(**{
'host': 'localhost',
'port': 6379,
'password': '******'
})
db_conn = ibbdlib.get_db_conn(**db_server)
iids = set()
uids = set()
query = """SELECT DISTINCT item_id, user_num_id
FROM topspider.top_itemsearchresult
WHERE population_tsmp > '%s'""" % now.strftime('%Y-%m-%d %H:%M:%S')
for row in db_conn.iter(query):
iids.add(row.item_id)
uids.add(row.user_num_id)
print 'iids', len(iids), 'uids', len(uids)
if iids:
re_conn.sadd('Schedule_itemTrade2', *iids)
re_conn.sadd('Schedule_itemFullInfo', *iids)
#!/usr/bin/python2.7
# -*- coding: utf-8 -*-
import ibbdlib
import ibbdlib.redislib
from datetime import datetime, timedelta
cmd_str = "SELECT item_id, MAX(date_of) AS date_of FROM ibbd2.top_item2 GROUP BY item_id"
commands = []
db_conn = ibbdlib.get_db_conn()
for row in db_conn.iter(cmd_str):
commands.append([
'HSET', 'TopItemTradeUpdateTime',
str(row.item_id), (row.date_of + timedelta(1)).strftime('%Y/%m/%d')
])
print commands[0]
db_conn.close()
re_conn = ibbdlib.get_redis_conn()
ibbdlib.redislib.redis_pipe(re_conn, commands)
del re_conn
#!/usr/bin/python2.7
# -*- coding: utf-8 -*-
import ibbdlib
re_conn = ibbdlib.get_redis_conn(**{'host': '223.4.155.152'})
schedules = ['Schedule_itemTrade2', 'Schedule_itemFullInfo']
schedule_sum = 0
for sche in schedules:
schedule_sum += re_conn.scard(sche)
del re_conn
if schedule_sum == 0:
open('status', 'w').write('1')
else:
open('status', 'w').write('0')