def do_insert(flag_queue, task_queue, res_queue, db_info): logger.info('Starting: do_insert()') insert_done_sql = db_info['sqls']['insert-done'] delete_todo_sql = db_info['sqls']['delete-todo'] done_conn = gen_db_conn(db_info['done-db']) todo_conn = gen_db_conn(db_info['todo-db']) statsd_cfg = cfg['statsd'] statsd_client = gen_statsd_client(statsd_cfg) STATSD_KEY = statsd_cfg['key-insert'] while True: try: record = task_queue.get() _do_insert(done_conn, insert_done_sql, record) execute_sql(todo_conn, delete_todo_sql, (record[0],), commit=True) res_queue.put(True) statsd_client.incr(STATSD_KEY) except Exception: res_queue.put(False) done_conn.close() done_conn = gen_db_conn(db_info['done-db']) todo_conn.close() todo_conn = gen_db_conn(db_info['todo-db']) logger.error('loop_insert requests.Exception: %r' % traceback.format_exc()) time.sleep(0.5) done_conn.close() todo_conn.close() logger.warning('@@@ Exit: do_insert()')
def insert(cls, initial=False): """ 向表中插入数据 :param initial: 是否先格式化表 :return: """ try: logger.info(f"start update {cls.table_name}'s data.") if initial: execute_sql(cls.splice_truncate_sql(cls.table_name)) df = cls.make_data() if df is None: return logger.info(f"find {cls.table_name}'s {df.shape[0]} records") if df.shape[0] == 0: return sql = cls.splice_insert_sql(cls.table_name, df) execute_sql(sql) except OperationalError as e: logger.error(f"Due to exception: {e}, this task failed!") else: logger.info(f'{cls.table_name} insert {df.shape[0]} records')
def process_msg(self, body, conn, sql): params = body.get('params') # task_uuid = params.get('external_id') client_id = params.get('additional_info').get('client_id') thunder_hash = params.get('thunder_hash') digest = params.get('digest') # URL url = params.get('url') url_loc = url.get('location') url_hash = url.get('hash') # Seed seed_file = params.get('seed_file', {}) seed_hash = seed_file.get('hash', '') swift_path = seed_file.get('path', '') algorithm = params.get('digest_algorithm') mime_type = params.get('mime_type') file_name = params.get('file_name') file_size = params.get('file_size') digest = url_hash if url_hash else seed_hash record = (cfg['custom-type'], client_id, thunder_hash, url_loc, digest, algorithm, mime_type, file_name, file_size, swift_path) try: execute_sql(conn, sql, record, commit=True) except mdb.IntegrityError: pass
def do_insert(flag_queue, task_queue, res_queue, db_info): logger.info('Starting: do_insert()') insert_done_sql = db_info['sqls']['insert-done'] delete_todo_sql = db_info['sqls']['delete-todo'] done_conn = gen_db_conn(db_info['done-db']) todo_conn = gen_db_conn(db_info['todo-db']) statsd_cfg = cfg['statsd'] statsd_client = gen_statsd_client(statsd_cfg) STATSD_KEY = statsd_cfg['key-insert'] while True: try: record = task_queue.get() _do_insert(done_conn, insert_done_sql, record) execute_sql(todo_conn, delete_todo_sql, (record[0], ), commit=True) res_queue.put(True) statsd_client.incr(STATSD_KEY) except Exception: res_queue.put(False) done_conn.close() done_conn = gen_db_conn(db_info['done-db']) todo_conn.close() todo_conn = gen_db_conn(db_info['todo-db']) logger.error('loop_insert requests.Exception: %r' % traceback.format_exc()) time.sleep(0.5) done_conn.close() todo_conn.close() logger.warning('@@@ Exit: do_insert()')
def process_msg(self, body, conn, sql): params = body.get('params') # task_uuid = params.get('external_id') client_id = params.get('additional_info').get('client_id') thunder_hash = params.get('thunder_hash') digest = params.get('digest') # URL url = params.get('url') url_loc = url.get('location') url_hash = url.get('hash') # Seed seed_file = params.get('seed_file', {}) seed_hash = seed_file.get('hash', '') swift_path = seed_file.get('path', '') algorithm = params.get('digest_algorithm') mime_type = params.get('mime_type') file_name = params.get('file_name') file_size = params.get('file_size') digest = url_hash if url_hash else seed_hash record = (cfg['custom-type'], client_id, thunder_hash, url_loc, digest, algorithm, mime_type, file_name, file_size, swift_path) try: execute_sql(conn, sql, record, commit=True) except mdb.IntegrityError: pass
def _do_insert(done_conn, insert_done_sql, record): rid, thunder_hash, digest = record[0], record[3], record[5] try: logger.info('inserting mysql %d' % rid) execute_sql(done_conn, insert_done_sql, record[1:-1], commit=True) logger.info('inserting vddb %d' % rid) resp, logs = insert_vddb_tmp(cfg['vddb-async-url'], [thunder_hash, digest]) if not resp or resp.status_code != 200: raise ValueError('Insert result management Failed! <%r>' % (logs,)) logger.info('inserted mysql %d' % rid) except mdb.IntegrityError: pass except Exception: logger.error('_do_insert requests.Exception: %r' % traceback.format_exc()) time.sleep(0.5)
def _id_df(cls): """ 读取common_id_attribution表中的数据,并转成dataframe格式 :return: """ sql = "select * from common_id_attribution" data = execute_sql(sql, dict_cursor=True) if len(data) <= 1: logger.info( f'common_id_attribution table is empty, initial data of it.') CommonId.insert(initial=True) logger.info(f'common_id_attribution table initial data success.') df = pd.DataFrame(data) df.drop(['id', 'created_time', 'updated_time'], axis=1, inplace=True) def zhi_xia_city(name): if name is None: return name if name[:2] in ('北京', '上海', '天津', '重庆'): return name[:2] + '市' return name df['full_city'] = df['full_city'].apply(zhi_xia_city) return df
def _do_insert(done_conn, insert_done_sql, record): rid, thunder_hash, digest = record[0], record[3], record[5] try: logger.info('inserting mysql %d' % rid) execute_sql(done_conn, insert_done_sql, record[1:-1], commit=True) logger.info('inserting vddb %d' % rid) resp, logs = insert_vddb_tmp(cfg['vddb-async-url'], [thunder_hash, digest]) if not resp or resp.status_code != 200: raise ValueError('Insert result management Failed! <%r>' % (logs, )) logger.info('inserted mysql %d' % rid) except mdb.IntegrityError: pass except Exception: logger.error('_do_insert requests.Exception: %r' % traceback.format_exc()) time.sleep(0.5)
def _exist_numbers(cls): """ 读取已存在common_mobile_attribution表中的所有手机号码(前7位),用于新插入数据的去重 :return: """ number_sql = "select number from common_mobile_attribution" number_data = execute_sql(number_sql, dict_cursor=True) number_set = set(pd.DataFrame(number_data)['number'].to_list()) return number_set
def _last_date(cls): """ 获取common_date表中最近的一条记录的date :return: date格式的日期 """ sql = "select date from common_date order by date desc limit 1" data = execute_sql(sql, dict_cursor=True) if len(data) == 0: return return data[0].get('date')
def _last_time(cls): """ 获取common_mobile_attribution表中最新一条数据的插入时间 :return: datetime """ last_date_sql = "select max(created_time) as last_time from common_mobile_attribution" last_date_data = execute_sql(last_date_sql, dict_cursor=True) if len(last_date_data) == 0: return last_time = last_date_data[0].get('last_time') if last_time is None: return return last_time.date()
def _init_data(cls): """ 从 my_user_profile.DateClass 中拉取历史common_date数据 :return: """ sql = "select distinct date as date_str, class from DateClass" data = execute_sql(sql, db='my_user_profile', dict_cursor=True) df = pd.DataFrame(data) df.drop_duplicates(subset=['date_str'], inplace=True) df['date'] = df['date_str'].apply( lambda x: datetime.date(int(x[:4]), int(x[4:6]), int(x[6:]))) df['weekday'] = df['date'].apply(lambda x: x.weekday()) return df
def process_msg(self, body, db_conn, sql): if isinstance(body, (str, unicode)): body = json.loads(body) params = body.get('params') task_uuid = params.get('external_id') client_id = params.get('additional_info').get('client_id') thunder_hash = params.get('thunder_hash') digest = params.get('digest') # URL url = params.get('url') url_loc = url.get('location') url_hash = url.get('hash') # Seed seed_file = params.get('seed_file', {}) seed_hash = seed_file.get('hash', '') swift_path = seed_file.get('path', '') algorithm = params.get('digest_algorithm') mime_type = params.get('mime_type') file_name = params.get('file_name') file_size = params.get('file_size') if cfg['vddb-async']['should-insert']: logger.info('Insert to vddb: %s' % task_uuid) resp, logs = insert_vddb_tmp(cfg['vddb-async']['url'], [thunder_hash, url_hash, seed_hash]) if not resp or resp.status_code != 200: raise ValueError('Insert result management Failed! <%r>' % (logs, )) record = (cfg['custom-type'], client_id, thunder_hash, url_loc, digest, algorithm, mime_type, file_name, file_size, swift_path) try: execute_sql(db_conn, sql, record) except mdb.IntegrityError: pass
def process_msg(self, body, db_conn, sql): if isinstance(body, (str, unicode)): body = json.loads(body) params = body.get('params') task_uuid = params.get('external_id') client_id = params.get('additional_info').get('client_id') thunder_hash = params.get('thunder_hash') digest = params.get('digest') # URL url = params.get('url') url_loc = url.get('location') url_hash = url.get('hash') # Seed seed_file = params.get('seed_file', {}) seed_hash = seed_file.get('hash', '') swift_path = seed_file.get('path', '') algorithm = params.get('digest_algorithm') mime_type = params.get('mime_type') file_name = params.get('file_name') file_size = params.get('file_size') if cfg['vddb-async']['should-insert']: logger.info('Insert to vddb: %s' % task_uuid) resp, logs = insert_vddb_tmp(cfg['vddb-async']['url'], [thunder_hash, url_hash, seed_hash]) if not resp or resp.status_code != 200: raise ValueError('Insert result management Failed! <%r>' % (logs,)) record = (cfg['custom-type'], client_id, thunder_hash, url_loc, digest, algorithm, mime_type, file_name, file_size, swift_path) try: execute_sql(db_conn, sql, record) except mdb.IntegrityError: pass
def loop_insert(flag_queue, task_queue, res_queue, db_info): logger.info('Starting: loop_insert() => %s' % (cfg['vddb-async-url'])) select_todo_sql = db_info['sqls']['select-todo'] select_limit = db_info['select-limit'] todo_conn = gen_db_conn(db_info['todo-db']) while True: try: while not res_queue.empty(): ok = res_queue.get() rows = execute_sql(todo_conn, select_todo_sql, (select_limit, ), fetch=True) for record in rows: logger.info('Deleted mysql %d' % record[0]) task_queue.put(record) time.sleep(1) results = [] results_ok = 0 rows_len = len(rows) for i in range(rows_len): try: ok = res_queue.get(timeout=5) except Queue.Empty(): ok = False if ok: results_ok += 1 results.append(ok) if rows_len == 0: time.sleep(db_info['select-interval']) logger.info('One turn: (%d/%d), %r' % (results_ok, rows_len, results)) except KeyboardInterrupt: break except Exception: todo_conn.close() todo_conn = gen_db_conn(db_info['todo-db']) logger.error('loop_insert Exception: %r' % traceback.format_exc()) time.sleep(0.5) todo_conn.close() logger.warning('@@@ Exit: loop_insert()')
def loop_insert(flag_queue, task_queue, res_queue, db_info): logger.info('Starting: loop_insert() => %s' % (cfg['vddb-async-url'])) select_todo_sql = db_info['sqls']['select-todo'] select_limit = db_info['select-limit'] todo_conn = gen_db_conn(db_info['todo-db']) while True: try: while not res_queue.empty(): ok = res_queue.get() rows = execute_sql(todo_conn, select_todo_sql, (select_limit, ), fetch=True) for record in rows: logger.info('Deleted mysql %d' % record[0]) task_queue.put(record) time.sleep(1) results = [] results_ok = 0 rows_len = len(rows) for i in range(rows_len): try: ok = res_queue.get(timeout=5) except Queue.Empty(): ok = False if ok: results_ok += 1 results.append(ok) if rows_len == 0: time.sleep(db_info['select-interval']) logger.info('One turn: (%d/%d), %r' % (results_ok, rows_len, results)) except KeyboardInterrupt: break except Exception: todo_conn.close() todo_conn = gen_db_conn(db_info['todo-db']) logger.error('loop_insert Exception: %r' % traceback.format_exc()) time.sleep(0.5) todo_conn.close() logger.warning('@@@ Exit: loop_insert()')
def _update_user_mobile(cls): """ 从 my_v2.credit_user表中读取最新一天的用户手机号(前7位)数据,去重过滤后,标注归属地信息 :return: dateframe格式的数据 """ end = datetime.datetime.now().date() start = end - datetime.timedelta(days=1) sql = f"select distinct mobile as number from credit_user where created_time between '{start}' and '{end}'" data = execute_sql(sql, db='my_v2', dict_cursor=True) if len(data) == 0: logger.info( f'Not add any new user between {start} and {end} in credit_user table' ) return df = pd.DataFrame(data) df = df[df['number'].apply(is_valid_phone)] df['number'] = df['number'].apply(lambda x: x[:7]) df.drop_duplicates(inplace=True) df.dropna(inplace=True) exist_numbers = cls._exist_numbers() df = df[df['number'].apply(lambda x: x not in exist_numbers)] logger.info( f"New unique numbers: {df.values.tolist()} between {start} and {end}" ) if df.shape[0] == 0: logger.info(f"No valid update data for {cls.table_name} table!") return df['phone_info'] = df['number'].apply(cls._phone_query) pdf = df['phone_info'].str.split('|', expand=True) df['city_code'], df['zip_code'], df['short_province'] = pdf[0], pdf[ 1], pdf[2] df['short_city'], df['phone_type'] = pdf[3], pdf[4] df.drop(columns=['phone_info'], axis=1, inplace=True) return df
def _init_data(cls): """ 从 my_user_profile.PhoneCity表中读取历史数据,转为dataframe格式 :return: """ sql = "select distinct code, province, city, corporation, area_code, zip_code from PhoneCity" data = execute_sql(sql, db='my_user_profile', dict_cursor=True) df = pd.DataFrame(data) df.rename(columns={ 'code': 'number', 'corporation': 'phone_type', 'area_code': 'city_code' }, inplace=True) df.rename(columns={ 'province': 'short_province', 'city': 'short_city' }, inplace=True) df = df[df['number'].apply(is_valid_phone_7)] df.replace('', np.nan, inplace=True) return df
def existed_id(cls): sql = "select * from common_id_attribution" data = execute_sql(sql, dict_cursor=True) return data