def __init__(self, db_path):
self.session = requests.session()
db_path = os.path.join(BASE_DIR, db_path)
my_logger.debug('db_path: %s', db_path)
try:
self.conn = sqlite3.connect(db_path, check_same_thread=False)
cursor = self.conn.cursor()
cursor.execute("""
CREATE TABLE IF NOT EXISTS 'group' (
member_name VARCHAR( 100 ),
group_number INT,
group_size INT,
date DATE
);""")
cursor.execute("""
CREATE TABLE IF NOT EXISTS 'member' (
member_id INT,
member_name VARCHAR,
group_number INT,
super_tag VARCHAR( 500 )
);""")
cursor.execute("""
CREATE TABLE IF NOT EXISTS 'super_tag' (
member_name VARCHAR( 100 ),
link VARCHAR( 500 ),
size INT,
date DATE
);""")
except Exception as e:
my_logger.error(e)
finally:
cursor.close()
my_logger.debug('读取数据库成功')
def get_super_tag_size(self, member_name):
"""
获取超话人数
:param member_name:
:return:
"""
cursor = self.conn.cursor()
try:
# 获取超话链接
c = cursor.execute(
"""
select super_tag from member WHERE member_name=?
""", (member_name, ))
super_tag = c.fetchone()[0]
r = self.session.get(super_tag).json()
if r['ok'] == 1:
desc_more = r['data']['pageInfo']['desc_more'][0]
desc_arr = desc_more.strip().split('\u3000')
fans_number = int(desc_arr[2][2:])
cur_date = util.convert_timestamp_to_timestr(time.time() *
1000)
my_logger.debug('统计：成员: %s, 超话: %s, 人数: %d, 时间: %s',
member_name, super_tag, fans_number, cur_date)
cursor.execute(
"""
INSERT INTO `super_tag` (`member_name`, `link`, `size`, `date`) VALUES
(?, ?, ?, ?)
""", (member_name, super_tag, fans_number, cur_date))
self.conn.commit()
except Exception as e:
my_logger.error(e)
finally:
cursor.close()
def record_data():
"""
记录数据
:return:
"""
global statistic_handler
try:
my_logger.debug(global_config.POCKET48_LISTEN_TASKS)
for task in global_config.POCKET48_LISTEN_TASKS:
my_logger.debug('member name: %s', task.member.name)
my_logger.debug('记录群人数数据')
statistic_handler.update_group_size(task.member.pinyin)
my_logger.debug('记录超话数据')
statistic_handler.get_super_tag_size(task.member.pinyin)
except Exception as exp:
my_logger.exception(exp)
def update_group_size(self, member_name):
"""
获取群人数
:param member_name:
:return:
"""
cursor = self.conn.cursor()
my_logger.debug('更新群信息')
try:
# 获取群号
my_logger.debug('获取成员群号')
c = cursor.execute(
"""
select group_number from member WHERE member_name=?
""", (member_name, ))
group_number = c.fetchone()[0]
my_logger.debug('群号: %s', group_number)
number = QQHandler.get_group_number(str(group_number))
my_logger.debug('群%s人数: %s', group_number, number)
# number = 800
cur_date = util.convert_timestamp_to_timestr(time.time() * 1000)
my_logger.debug('记录时间: %s', cur_date)
my_logger.debug('统计：成员: %s, 群号: %s, 人数: %s, 时间: %s', member_name,
group_number, number, cur_date)
cursor.execute(
"""
INSERT INTO `group` (`member_name`, `group_number`, `group_size`, `date`) VALUES
(?, ?, ?, ?)
""", (member_name, group_number, number, cur_date))
self.conn.commit()
except Exception as e:
my_logger.error(e)
finally:
cursor.close()
def pocket_msgs(self):
from pocket48 import others
cursor = self.conn.cursor()
today = datetime.date.today().strftime('%Y-%m-%d %H:%M:%S')
today_timestamp = int(util.convert_timestr_to_timestamp(today)) * 1000
yesterday_timestamp = today_timestamp - 60 * 24 * 60 * 1000
room_list = others.get_room_list()
try:
for room in room_list:
others.member_messages = {
'id': room['id'],
'name': room['name'],
'room_id': room['room_id'],
'100': 0,
'101': 0,
'102': 0,
'103': 0,
'104': 0,
'105': 0,
'106': 0,
'200': 0,
'201': 0,
'202': 0,
'203': 0
}
my_logger.debug(room['name'])
if room['id'] in [63, 327683, 327682, 5973]:
continue
others.get_room_history_msg(room['id'], room['room_id'],
today_timestamp - 1,
yesterday_timestamp)
my_logger.debug(others.member_messages)
cur_date = util.convert_timestamp_to_timestr(time.time() *
1000)
sum = others.member_messages['100'] + others.member_messages[
'101'] + others.member_messages[
'102'] + others.member_messages[
'103'] + others.member_messages[
'104'] + others.member_messages[
'105'] + others.member_messages[
'106'] + others.member_messages[
'201'] + others.member_messages[
'202'] + others.member_messages[
'203']
cursor.execute(
"""
INSERT INTO `pocket_message` (`member_id`, `room_id`, `member_name`, `text`, `reply`, `live`, `vote`,
`idol_flip`, `red_packet`, `pic`, `voice`, `video`, `total_num`, `update_time`) VALUES
(?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)
""", (others.member_messages['id'],
others.member_messages['room_id'],
others.member_messages['name'],
others.member_messages['100'],
others.member_messages['101'],
others.member_messages['102'],
others.member_messages['104'],
others.member_messages['105'],
others.member_messages['106'],
others.member_messages['200'],
others.member_messages['201'],
others.member_messages['202'], sum, cur_date))
self.conn.commit()
except Exception as e:
my_logger.exception(e)
finally:
cursor.close()
def read_statistic_conf():
global statistic_handler
my_logger.debug('读取数据配置-statistic_plugin')
ConfigReader.read_conf()