예제 #1
0
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
예제 #2
0
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
예제 #3
0
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
예제 #4
0
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
예제 #5
0
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
예제 #6
0
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
예제 #7
0
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
예제 #8
0
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
예제 #9
0
                '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)
예제 #10
0
def _getReConn():
    """获取Redis连接"""

    return ibbdlib.get_redis_conn(**redis_server)
예제 #11
0
 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
예제 #12
0
#!/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
예제 #13
0
#!/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))
예제 #14
0
 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
예제 #15
0
#!/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
예제 #16
0
#!/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
예제 #18
0
def _getReConn():
    """获取Redis连接"""

    return ibbdlib.get_redis_conn(**redis_server)
예제 #19
0
#!/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
예제 #20
0
def get_re_conn():
    return ibbdlib.get_redis_conn(**re_server)
예제 #21
0
        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)
예제 #22
0
        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
예제 #24
0
#!/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')