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')
