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)