def get_column_value(self, line): """ 根据行数据获取需要插入到数据库的字典 :param line: :return: """ data = self.parser.get_dict(line) column_value = { 'uid': data['uid'], 'p_type': Product.MINIK.value, 't_type': Terminal.Mobile.value, 'a_type': data['action'], 'action_time': data['action_time'], 'location': SocketUtil.ip2int(data['ip']), 'aimei_object': data['aimei_object'], 'update_time': -1 } month = DateUtil.now('%Y%m') column_value['month'] = month column_value['update_time'] = DateUtil.now('%Y-%m-%d %H:%M:%S') # 流水需要的 # 总表需要的 column_value['provinceid'] = -1 column_value['isp'] = -1 column_value['count'] = 1 column_value['time_span'] = DateUtil.timestamp2date( data['action_time'], '%d%H') return column_value
def insert_total_many(self, column_values): month = DateUtil.now('%Y%m') prefix = "INSERT INTO t_data_aimei_user_action_mob_%(month)s (p_type, a_type, provinceid, isp, time_span, aimei_object, count, update_time) VALUES " prefix = prefix % {'month': month} value_fmt = "('%(p_type)s', '%(a_type)s', %(provinceid)s, %(isp)s, %(time_span)s, '%(aimei_object)s', %(count)s, '%(update_time)s') " end = "ON DUPLICATE KEY UPDATE count = count + 1, update_time = '%(update_time)s'" end = end % {'update_time': DateUtil.now('%Y-%m-%d %H:%M:%S')} values = [] for value in column_values: value = value_fmt % value values.append(value) sql = prefix + ', '.join(values) + end self.db.execute(sql)
def insert(self, column_value, data=None): """ 插入流水表 :param column_value: """ # 设置默认值 month = DateUtil.now('%Y%m') column_value['month'] = month column_value['update_time'] = DateUtil.now('%Y-%m-%d %H:%M:%S') sql = "INSERT INTO t_data_aimei_user_action_%(month)s (uid, p_type, t_type, a_type, action_time, location, aimei_object, update_time) VALUES (%(uid)s, '%(p_type)s', '%(t_type)s', '%(a_type)s', %(action_time)s, %(location)s, '%(aimei_object)s', '%(update_time)s')" sql %= column_value # 格式化 # self.db.execute(sql) return sql, column_value
def get_column_value(self, line): """ 根据行数据获取需要插入到数据库的字典 :param line: :return: """ index = line.find(']') line = (line[index + 1:]).strip() data = self.parser.split_k_v(line) column_value = { 'uid': data['uid'], 'p_type': Product.MINIK.value, 't_type': Terminal.Mobile.value, 'a_type': -1, 'action_time': -1, 'location': -1, 'aimei_object': '', 'update_time': -1 } action = data['action'] if action == 'click_listen_song': column_value['a_type'] = Action.REQUEST_SONG.value column_value['aimei_object'] = data['songid'] else: column_value['a_type'] = Action.CLICK_BUTTON.value aimei_object = '' if action == 'click_tab': aimei_object = data['tab'] elif action == 'visit_url': aimei_object = data['url'] elif action == 'click_play_album': aimei_object = data['albumid'] column_value['aimei_object'] = aimei_object column_value['action_time'] = DateUtil.date2timestamp( data['timestamp'], fmt='%Y-%m-%d_%H:%M:%S') month = DateUtil.now('%Y%m') column_value['month'] = month column_value['update_time'] = DateUtil.now('%Y-%m-%d %H:%M:%S') # 流水需要的 # 总表需要的 column_value['provinceid'] = -1 column_value['isp'] = -1 column_value['count'] = 1 column_value['time_span'] = self.get_time_span(data['timestamp']) return column_value
def insert_or_update_total(self, column_value, data=None): """ 插入或更新流水总表 :param column_value: """ # 设置默认值 month = DateUtil.now('%Y%m') column_value['month'] = month column_value['provinceid'] = -1 column_value['isp'] = -1 column_value['count'] = 1 column_value['time_span'] = self.get_time_span(data['timestamp']) column_value['update_time'] = DateUtil.now('%Y-%m-%d %H:%M:%S') sql = "INSERT INTO t_data_aimei_user_action_mob_%(month)s (p_type, a_type, provinceid, isp, time_span, aimei_object, count, update_time) VALUES ('%(p_type)s', '%(a_type)s', %(provinceid)s, %(isp)s, %(time_span)s, '%(aimei_object)s', %(count)s, '%(update_time)s') ON DUPLICATE KEY UPDATE count = count + 1, update_time = '%(update_time)s'" sql %= column_value # 格式化 # self.db.execute(sql) return sql, column_value
def insert_many(self, column_values): month = DateUtil.now('%Y%m') prefix = "INSERT INTO t_data_aimei_user_action_%(month)s (uid, p_type, t_type, a_type, action_time, location, aimei_object, update_time) VALUES " prefix = prefix % {'month': month} value_fmt = "(%(uid)s, '%(p_type)s', '%(t_type)s', '%(a_type)s', %(action_time)s, %(location)s, '%(aimei_object)s', '%(update_time)s')" values = [] for value in column_values: value = value_fmt % value values.append(value) sql = prefix + ', '.join(values) self.db.execute(sql)
def get_table_name(cls, column_value): return 't_data_aimei_user_action_%s' % DateUtil.now('%Y%m')