def insert_history(self):
try:
dic = self.get_tencent_data()[0] # 0代表历史数据字典
print(f"{time.asctime()}开始插入历史数据")
self.conn, self.cursor = db.get_conn()
sql = "insert into history values (%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s)"
for k, v in dic.items():
self.cursor.execute(sql, [
k,
v.get("confirm"),
v.get("confirm_add"),
v.get("suspect"),
v.get("suspect_add"),
v.get("heal"),
v.get("heal_add"),
v.get("dead"),
v.get("dead_add"),
v.get("importedCase"),
v.get("importedCase_add")
])
self.conn.commit()
print(f"{time.asctime()}插入历史数据完毕")
except:
traceback.print_exc()
finally:
db.close_conn(self.conn, self.cursor)
def download_file(update, context, file_type):
"""
This function downloads files from Telegram.
:param update:
:param context:
:param file_type: `photo` or `voice`. Type of downloaded file.
:return: old filename and new_filename if file_type is correct else False.
filename - path to file, new_filename - filename with order number of file
in database.
"""
user_id = update.effective_user.id
if file_type == 'voice':
file_id = update.message.voice.file_id
filename = f'{user_id}_voice.ogg'
path = CONFIG['VOICE_FOLDER_PATH']
elif file_type == 'photo':
file_id = update.message.photo[-1].file_id
filename = f'{user_id}_photo.jpg'
path = CONFIG['PHOTO_FOLDER_PATH']
else:
return False
new_file = context.bot.get_file(file_id)
new_file.download(os.path.join(path, filename))
conn, cursor = create_conn()
new_filename = os.path.join(path, get_name(cursor, user_id, file_type,
f'{file_type}_path'))
close_conn(conn)
return filename, new_filename
def update_word(self, li):
try:
# print(li)
self.conn, self.cursor = db.get_conn()
sql = "insert into word(" \
"update_time,name,confirm_add,confirm,heal,dead" \
") " \
"values(%s,%s,%s,%s,%s,%s)"
sql_query = 'select %s=(' \
'select update_time ' \
'from word ' \
'order by id ' \
'desc limit 1' \
')'
# 对比当前最大时间戳
self.cursor.execute(sql_query, li[0]["update_time"])
if not self.cursor.fetchone()[0]:
print(f"{time.asctime()}开始更新数据")
for item in li:
# k, v = item.items()
print(item)
# print(v)
v = []
v = list(item.values())
# print(list(v))
self.cursor.execute(sql, v)
self.conn.commit()
print(f"{time.asctime()}更新到最新数据")
else:
print(f"{time.asctime()}已是最新数据！")
except:
traceback.print_exc()
finally:
db.close_conn(self.conn, self.cursor)
def update_details(self):
try:
li = self.get_tencent_data()[1] # 1代表最新数据
self.conn, self.cursor = db.get_conn()
sql = "insert into details(" \
"update_time,province,city,confirm,confirm_add,heal,dead" \
") " \
"values(%s,%s,%s,%s,%s,%s,%s)"
sql_query = 'select %s=(' \
'select update_time ' \
'from details ' \
'order by id ' \
'desc limit 1' \
')'
# 对比当前最大时间戳
self.cursor.execute(sql_query, li[0][0])
if not self.cursor.fetchone()[0]:
print(f"{time.asctime()}开始更新数据")
for item in li:
self.cursor.execute(sql, item)
self.conn.commit()
print(f"{time.asctime()}更新到最新数据")
else:
print(f"{time.asctime()}已是最新数据！")
except:
traceback.print_exc()
finally:
db.close_conn(self.conn, self.cursor)
def full_text_search(text):
conn, cursor = open_conn()
sql = "SELECT * FROM voice_audio WHERE make_tsvector(transcription) @@ to_tsquery('" + text + "\')"
cursor.execute(sql)
resp = cursor.fetchall()
close_conn(conn, cursor)
return resp
def get_top_n_words(n):
conn, cursor = open_conn()
sql = "SELECT * FROM ts_stat($$SELECT to_tsvector('english', transcription) FROM voice_audio) ORDER BY ndoc DESC " \
"LIMIT {}".format(n)
cursor.execute(sql)
resp = cursor.fetchall()
conn.commit()
close_conn(conn, cursor)
return resp
def main():
global conn
print("欢迎进入{s}".format(s=format.format_light_content("终端股票行情系统")))
# 打开 db 连接
conn = db.init_conn()
# 展示菜单
menu()
# 关闭 db 连接
db.close_conn(conn)
print("拜拜{s}".format(s=format.format_light_content("~")))
def message_processing(update, context):
"""
Function saves voice messages in wav format with simple rate 16MHz and
photos if a face is detected there. All path store in database `bot`.
:return: None
"""
logger.info(f'Waiting for message_processing function for '
f'{update.effective_user.name} at '
f'{update.effective_message.date}')
user_id = update.effective_user.id
conn, cursor = create_conn()
if update.message.voice:
filename, new_filename = download_file(update, context, 'voice')
new_filename = f'{new_filename}.wav'
convert(os.path.join(CONFIG['VOICE_FOLDER_PATH'], filename),
new_filename)
insert_data(conn, cursor, 'voice', user_id, 'audio_path', new_filename)
answer_text = 'Thanks, I\'ve saved this voice message to my database.'
elif update.message.photo:
filename, new_filename = download_file(update, context, 'photo')
new_filename = f'{new_filename}.jpg'
PHOTO_FOLDER_PATH = CONFIG['PHOTO_FOLDER_PATH']
if check_face(f'{PHOTO_FOLDER_PATH}/{user_id}_photo.jpg',
new_filename):
insert_data(conn, cursor, 'photo', user_id, 'photo_path',
new_filename)
answer_text = ('I saved this photo in the database because I\'ve '
'detected the face here.')
else:
answer_text = ('I didn\'t save this photo in my database, because '
'I haven\'t found the face here.')
else:
context.bot.send_sticker(chat_id=update.effective_chat.id,
sticker=CONFIG['STICKER_PATH'])
answer_text = 'Send me a voice message or a photo, please.'
context.bot.send_message(parse_mode=ParseMode.MARKDOWN,
chat_id=update.effective_chat.id,
text=answer_text)
close_conn(conn)
logger.info(f'Answer ready for {update.effective_user.name} '
f'at {update.effective_message.date}')
def select_random():
conn, cursor = open_conn()
sql = "SELECT base_filename, source_subdir, source_name FROM voice WHERE not exists (SELECT audio_id FROM" \
" transcription WHERE audio_id = voice.id) ORDER BY random() LIMIT 1;"
cursor.execute(sql)
item = cursor.fetchone()
sql = "SELECT base_filename, file_extension, source_subdir, source_dir FROM voice_and_source WHERE ( base_filename = " \
+ item[0] + " AND file_extension = " + item[1] + " AND source_name = " + item[3] + ");"
cursor.execute(sql)
file = cursor.fetchone()
close_conn(conn, cursor)
return os.path.join(file[3] + file[2], file[0] + '.' + file[1])
def main():
print("Welcome to wish service !!!")
while True:
action = input("Выберите действие:\n"
"1. Оставить пожелание \n"
"2. Проверить пожелание \n"
"3. Выход \n")
if action == "1":
try:
phone = input("Введите свой номер:\n")
text = input("Введите текст пожелания: \n")
recipient = input("Введите номер получателя: \n")
if len(phone.strip()) > 0 and len(text.strip()) > 0 and len(
recipient.strip()) > 0:
try:
user = User(phone=phone)
db.inserting_user(user, recipient)
wish = Wishes(
text=text,
sender_id=db.find_sender_id(phone),
recipient_id=db.find_recipient_id(recipient))
db.inserting_wish(wish)
except:
print("У вас закончились попытки!")
finally:
print("Ваше желание отправленно успешно!")
else:
print("Error!")
except Exception as ex:
print(ex)
finally:
pass
elif action == "2":
phone = input("Введите номер для проверки почты: \n")
phone = db.find_phone(phone)
if db.check_box(phone) > 0:
db.show_text(phone)
else:
print("К сожалению вам ещё не отправляли пожелание!")
elif action == "3":
print("До скорой встречи :)")
db.close_conn()
break
else:
print("Error!\n")
def update_history(self):
try:
dic = self.get_tencent_data()[0] # 0代表历史数据字典
print(f"{time.asctime()}开始更新历史数据")
self.conn, self.cursor = db.get_conn()
sql = "insert into history values (%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s)"
update_sql = "update history " \
"set " \
"confirm=%s,confirm_add=%s," \
"suspect=%s,suspect_add=%s," \
"heal=%s,heal_add=%s," \
"dead=%s,dead_add=%s," \
"importedCase=%s,importedCase_add=%s " \
"where ds=%s"
sql_query = "select confirm from history where ds=%s"
for k, v in dic.items():
if not self.cursor.execute(sql_query, k):
self.cursor.execute(sql, [
k,
v.get("confirm"),
v.get("confirm_add"),
v.get("suspect"),
v.get("suspect_add"),
v.get("heal"),
v.get("heal_add"),
v.get("dead"),
v.get("dead_add"),
v.get("importedCase"),
v.get("importedCase_add")
])
else:
self.cursor.execute(update_sql, [
v.get("confirm"),
v.get("confirm_add"),
v.get("suspect"),
v.get("suspect_add"),
v.get("heal"),
v.get("heal_add"),
v.get("dead"),
v.get("dead_add"),
v.get("importedCase"),
v.get("importedCase_add"), k
])
self.conn.commit()
print(f"{time.asctime()}历史数据更新完毕")
except:
traceback.print_exc()
finally:
db.close_conn(self.conn, self.cursor)
def update_hotsearch(self):
"""
将疫情数据插入数据库中
:return:
"""
try:
context = self.get_baidu_hot()
print(f"{time.asctime()}开始更新热搜数据")
self.conn, self.cursor = db.get_conn()
sql = "insert into hotsearch(dt,content) values(%s,%s)"
ts = time.strftime("%Y-%m-%d %X")
for i in context:
self.cursor.execute(sql, (ts, i))
self.conn.commit()
print(f"{time.asctime()}热搜数据更新完毕")
except:
traceback.print_exc()
finally:
db.close_conn(self.conn, self.cursor)
readfrom = 'file'
else:
print('\nNo conf/database.ini or data files - exiting')
sys.exit(3)
# readfrom = 'file' # DEBUG
if readfrom == 'db':
print('\nWill read data from DB connection')
conn, cur = db.connect(dbconfig)
print("Getting component data ...")
df_main = db.get_projdata(conn)
print("Getting vulnerability data ...")
df_vuln = db.get_vulndata(conn)
print("Getting policy data ...")
df_pol = db.get_poldata(conn)
db.close_conn(conn, cur)
statusitem = dbc.NavItem(dbc.NavLink("Status: Data from DB", href='#', disabled=True))
elif readfrom == 'file':
print('\nWill read data from json files')
df_main, df_vuln, df_pol = read_data_files()
statusitem = dbc.NavItem(dbc.NavLink("Status: Data from Files", href='#', disabled=True))
if df_main is None or len(df_main) == 0 or df_vuln is None or len(df_vuln) == 0 or df_pol is None:
print("No data obtained from DB or files")
sys.exit(2)
if readfrom == 'db':
print("Writing data to JSON files ...")
write_data_files(df_main, df_vuln, df_pol)
df_proj, df_comp, df_projcompmap, childdata = data.proc_comp_data(df_main, serverurl, expand_child_projects)
if len(post_info['posts']) > 10:
post_info['posts'] = post_info['posts'][0:10]
else:
# 对比最新数据，判断有多少新帖子需要获取并插入
last_post_id = res_list[0][0]
last_post_timestamp = res_list[0][5]
new_posts = []
for post in post_info['posts']:
if post['taken_at_timestamp'] > last_post_timestamp and int(
post['id']) > last_post_id:
new_posts.append(post)
# 首页的帖子都是新帖子，需要加载更多帖子确定新帖子的数量
if len(new_posts) == len(post_info['posts']):
post_info = ins.get_ins_posts(post_info, last_post_id,
last_post_timestamp)
else:
post_info['posts'] = new_posts
print('get new posts for %s, length: %s' %
(post_info['name'], str(len(post_info['posts']))))
# 获取新帖子的详细媒体
post_info['posts'] = ins.get_post_details(post_info['posts'])
print('get new posts for ' + post_info['name'] + ', details done')
# 插入数据库
db.insert_new_posts(conn, post_info['posts'])
print('inserted done')
# 关闭 db 连接
db.close_conn(conn)
