def run():
# 运行查询:
cursor = conn.cursor()
cursor.execute('select distinct user_id from user_chatting_history')
values = cursor.fetchall()
user_ids = []
for i in values:
user_ids.append(i[0])
all_user_message = []
for i in user_ids:
cursor.execute(
'select message_content from user_chatting_history where user_id=%s and message_type=1',
(i, ))
message_list = utils.tuple_to_list(cursor.fetchall())
all_user_message.append(message_list)
frequency_list = []
for i in all_user_message:
frequency_list.append(utils.sentence_to_frequency_dict(i))
for index, i in enumerate(user_ids):
cursor.execute(
'insert into data_statistics (user_id, value, create_time, type) values (%s, %s, %s, 2)',
(i, utils.dict_to_json_str(
frequency_list[index]), utils.get_now_timestamp()))
conn.commit()
print('type2 complete!')
# 关闭Cursor:
cursor.close()
def run():
# 运行查询:
cursor = conn.cursor()
cursor.execute('select distinct user_id from user_chatting_history')
values = cursor.fetchall()
# 所有用户的 id
user_ids = utils.tuple_to_list(values)
result_list = []
for i in user_ids:
cursor.execute(
'select distinct from_who from user_chatting_history where user_id=%s and message_type=0',
(i, ))
from_who_list = utils.tuple_to_list(cursor.fetchall())
obj = {}
for n in from_who_list:
cursor.execute(
'select message_content from user_chatting_history where user_id=%s and message_type=0 and from_who=%s',
(i, n))
message_list = utils.tuple_to_list(cursor.fetchall())
frequency = utils.sentence_to_frequency_dict(message_list)
obj[n] = frequency
result_list.append(obj)
for index, i in enumerate(user_ids):
cursor.execute(
'insert into data_statistics (user_id, value, create_time, type) values (%s, %s, %s, 4)',
(i, utils.dict_to_json_str(
result_list[index]), utils.get_now_timestamp()))
conn.commit()
print('type4 complete!')
# 关闭Cursor:
cursor.close()
def run():
# 运行查询:
cursor = conn.cursor()
cursor.execute('select distinct user_id from user_chatting_history')
values = cursor.fetchall()
# 所有用户的 id
user_ids = utils.tuple_to_list(values)
result_list = []
for i in user_ids:
cursor.execute(
'select distinct group_name from user_chatting_history where user_id=%s and message_type=1',
(i, ))
group_names = utils.tuple_to_list(cursor.fetchall())
# print(group_names)
group_obj = {}
for g in group_names:
cursor.execute(
'select from_who, count(from_who) as count from user_chatting_history where user_id=%s and message_type=1 and group_name=%s group by from_who',
(i, g))
frequency = utils.tuple_to_map(cursor.fetchall())
group_obj[g] = frequency
result_list.append(group_obj)
for index, i in enumerate(user_ids):
cursor.execute(
'insert into data_statistics (user_id, value, create_time, type) values (%s, %s, %s, 3)',
(i, utils.dict_to_json_str(
result_list[index]), utils.get_now_timestamp()))
conn.commit()
print('type3 complete!')
# 关闭Cursor:
cursor.close()
def run():
# 运行查询:
cursor = conn.cursor()
cursor.execute('select message_content from user_chatting_history')
values = cursor.fetchall()
all_message = []
for i in values:
all_message.append(i[0])
# 统计词频
obj = utils.sentence_to_frequency_dict(all_message)
cursor.execute(
'insert into data_statistics (user_id, value, create_time, type) values (0, %s, %s, 0)',
(utils.dict_to_json_str(obj), utils.get_now_timestamp())
)
conn.commit()
print('type0 complete!')
# 关闭Cursor:
cursor.close()