def personal_change(): if request.method == 'GET': cur = db.cursor() username_get = session.get('user_id') sql = "select email,nickname,phone from SDWZCS.userInformation where email = '%s'" % username_get db.ping(reconnect=True) cur.execute(sql) userinformation = cur.fetchone() email = userinformation[0] phone = userinformation[2] nickname = userinformation[1] return render_template('personal_change.html', email=email, phone=phone, nickname=nickname) else: cur = db.cursor() username_get = session.get('user_id') nickname = request.form.get('nickname') email = request.form.get('email') phone = request.form.get('phone') sql = "update userInformation set SDWZCS.userInformation.nickname = '%s', SDWZCS.userInformation.phone = '%s' where SDWZCS.userInformation.email = '%s'" % ( nickname, phone, username_get) try: db.ping(reconnect=True) cur.execute(sql) db.commit() return redirect(url_for('personal')) except Exception as e: raise e
def detail_question(): formula_id = request.values.get('formula_id') if formula_id is None: return redirect(url_for('formula')) if request.method == 'GET': page = request.values.get('page') if page is None: page = int(1) page = int(page) try: cur = db.cursor() sql = "select max(qno) from question_detail where formula_id = '%s'" % formula_id db.ping(reconnect=True) cur.execute(sql) question = cur.fetchone()[0] page_num = int(question / 20 + 0.96) # 防止页码溢出 if page < 1: page = int(1) if page > page_num: page = int(page_num) cur = db.cursor() sql = "select title from SDWZCS.formula_post where formula_id = '%s'" % formula_id db.ping(reconnect=True) cur.execute(sql) title = cur.fetchone()[0] sql = "select formula_id, qno, content, datetime, nickname from question_detail,SDWZCS.userInformation where question_detail.author = userInformation.email and formula_id = '%s'" % formula_id db.ping(reconnect=True) cur.execute(sql) result = cur.fetchall() return render_template('detail_question.html', question_inf=result, title=title, page=page, page_num=page_num, formula_id=formula_id) except Exception as e: raise e if request.method == 'POST': content = request.form.get('editorValue') datetime = date = time.strftime("%Y-%m-%d %H:%M:%S") username = session.get('user_id') try: cur = db.cursor() sql = "select max(qno) from question_detail where formula_id = '%s'" % formula_id db.ping(reconnect=True) cur.execute(sql) qno = int(cur.fetchone()[0]) + 1 sql = "insert into question_detail(formula_id, qno, content, datetime, author) VALUES ('%s','%s','%s','%s','%s')" % ( formula_id, qno, content, datetime, username) db.ping(reconnect=True) cur.execute(sql) db.commit() cur.close() return redirect(url_for('detail_question', formula_id=formula_id)) except Exception as e: raise e
def new_dispute(message): cursor = db.cursor() text = str(message.text).replace('/new_dispute', '').strip() caption = text.split('\n')[0] content = '' try: content = text.split('\n', 1)[1] except IndexError: pass if len(content) + len(caption) > 160: bot.send_message(message.chat.id, 'Нельзя создавать вопрос длиной более 160 символов') cursor.close() return if caption: cursor.execute( "insert into Dispute(user_id,caption,content) VALUES ({0},'{1}','{2}')".format(message.from_user.id, caption, content)) sent_message = bot.send_message(message.chat.id, 'Вопрос "{0}" создан'.format(caption), reply_markup=markup_common) connect_message(message, dispute_id=cursor.lastrowid) connect_message(sent_message, dispute_id=cursor.lastrowid) cursor.execute('update User set state=state||? WHERE id=?', ['d{}/'.format(cursor.lastrowid), message.from_user.id]) db.commit() cursor.close()
def login(): if request.method == 'GET': return render_template('login.html') if request.method == 'POST': email = request.form.get('email') password = request.form.get('password') if not all([email, password]): flash("请将信息填写完整!") return render_template('login.html') try: cur = db.cursor() sql = "select password from UserInformation where email = '%s'" % email db.ping(reconnect=True) cur.execute(sql) result = cur.fetchone() if result is None: flash("该用户不存在") return render_template('login.html') if check_password_hash(result[0], password): session['email'] = email session.permanent = True cur.close() return redirect(url_for('index')) else: flash("密码错误!") return render_template('login.html') except Exception as e: raise e
def sample_get(): db = get_db() cursor = db.cursor() cursor.execute("select * from employees;") result = cursor.fetchall() cursor.close() return jsonify(result)
def login(): if request.method == 'POST': email = request.form.get('email') password = request.form.get('password') if not all([email, password]): flash('请填写完整信息') else: try: cur = db.cursor() sql = "select password from userInformation where email='%s'" % ( email) db.ping(reconnect=True) cur.execute(sql) result = cur.fetchone() if result is None: flash('无此用户') if check_password_hash(result[0], password): session['user_id'] = email session.permanent = True return redirect(url_for('homepage')) else: flash('密码错误') return redirect(url_for('login')) except Exception as e: flash('无此用户') return redirect(url_for('login')) raise e return render_template('login.html')
def post_question_md(): if request.method == 'GET': return render_template('post_blog_md.html') if request.method == 'POST': try: cur = db.cursor() author = session.get('user_id') title = request.form.get('title') content = request.form.get('html_content') print(request.values) # 如果未登录 则跳转到登录页面 if author is None: return redirect(url_for('login')) date = time.strftime("%Y-%m-%d %H:%M:%S") sql = "select max(bno) from SDWZCS.blog" db.ping(reconnect=True) cur.execute(sql) result = cur.fetchone()[0] if result is None: bno = 1 else: bno = int(result) + 1 sql = "insert into blog(bno, title, content, md_or_fwb, creatTime, author) VALUES ('%s','%s','%s','%s','%s','%s')" % ( bno, title, content, '1', date, author) db.ping(reconnect=True) cur.execute(sql) db.commit() cur.close() return redirect(url_for('technology_Blog')) except Exception as e: raise e
def post_questions(): if request.method == 'GET': return render_template('post_question_fwb.html') else: try: cur = db.cursor() email = session.get('user_id') title = request.form.get('title') content = request.form.get('editorValue') date = time.strftime("%Y-%m-%d %H:%M:%S") sql = "select max(formula_id) from SDWZCS.formula_post" db.ping(reconnect=True) cur.execute(sql) result = cur.fetchone()[0] if result is None: formula_id = 1 else: formula_id = int(result) + 1 sql = "insert into SDWZCS.formula_post(SDWZCS.formula_post.formula_id, SDWZCS.formula_post.author, " \ "SDWZCS.formula_post.title,SDWZCS.formula_post.creat_time) values ('%s','%s','%s','%s')" % ( formula_id,email,title,date) db.ping(reconnect=True) cur.execute(sql) db.commit() sql = "insert into question_detail(formula_id, qno, content, datetime, author) VALUES ('%s','1','%s','%s','%s')" % ( formula_id, content, date, email) db.ping(reconnect=True) cur.execute(sql) db.commit() cur.close() return redirect(url_for('formula')) except Exception as e: raise e
def open_command(message): cursor = db.cursor() text = str(message.text.strip()) cursor.execute('select dispute_id, feedback_id from Messages WHERE chat_id={0} and message_id={1} limit 1'.format( message.chat.id, message.reply_to_message.message_id)) rows = cursor.fetchall() if not rows: bot.send_message(message.chat.id, 'Ой! Вы что-то перепутали, тут не на что отвечать!', reply_markup=markup_common) cursor.close() return dispute_id, feedback_id = rows[0] if dispute_id is not None: step_type = 'd' else: cursor.execute('select is_answer from Feedback where id=?', [feedback_id]) is_answer, = cursor.fetchall()[0] if is_answer: step_type = 'a' else: step_type = 'c' item_id = dispute_id or feedback_id cursor.execute('update User set state=state||? WHERE id=?', ['{}{}/'.format(step_type, item_id), message.from_user.id]) db.commit() cursor.close() send_stuff_by_state(message.from_user.id)
def search_message(message): cursor = db.cursor() text = str(message.text).replace('/search', '').strip().lower().replace('/', '\\x2f') cursor.execute('update User set state=state||? WHERE id=?', ['s({})/'.format(text), message.from_user.id]) db.commit() cursor.close() send_stuff_by_state(message.from_user.id)
def insert_packets(delete=False): filename = sys.argv[1] group_id = sys.argv[2] upload_time = sys.argv[3] upload_name = sys.argv[4] cursor = db.cursor() sql = "INSERT INTO DataGroup(ID, UPLOAD_TIME, UPLOAD_NAME, START_TIME)VALUES (" + group_id + ", '" + upload_time + "','" + upload_name + "', '" + "1000" + "')" # below 100s cursor.execute(sql) db.commit() def insert_single_file(filename): pcap = rdpcap(filename)[TCP] for i in xrange(len(pcap)): length = len(pcap[i]) try: timestamp = pcap[i].time - int( pcap[i].time) / 100 * 100 # below 100s ip_src = pcap[i]['IP'].src ip_dst = pcap[i]['IP'].dst port_src = str(pcap[i]['IP'].sport) port_dst = str(pcap[i]['IP'].dport) apanel_cluster(pcap[i], group_id) # a-panel聚类 except: continue """ <Ether dst=00:23:89:3a:8b:08 src=00:0e:c6:c2:5f:d8 type=0x800 |<IP version=4L ihl=5L tos=0x0 len=40 id=34019 flags=DF frag=0L ttl=64 proto=tcp chksum=0x9920 src=172.29.90.176 dst=42.156.235.98 options=[] |<TCP sport=47571 dport=https seq=3681001345 ack=1822908669 dataofs=5L reserved=0L flags=A window=65280 chksum=0x1ce7 urgptr=0 |>>> """ if bin(pcap[i][TCP].flags)[-1] == '1': flag = 'Fin' elif bin(pcap[i][TCP].flags)[-2] == '1': flag = 'Syn' else: flag = '' sql = "INSERT INTO Packets(GROUP_ID, TIMESTAMP,LENGTH,IP_SRC,IP_DST,PORT_SRC,PORT_DST,FLAG)VALUES (" + group_id + "," + str( timestamp ) + "," + str( length ) + ", '" + ip_src + "','" + ip_dst + "', '" + port_src + "', '" + port_dst + "', '" + flag + "')" cursor.execute(sql) db.commit() if delete: os.remove(filename) if os.path.isfile(filename): insert_single_file(filename) elif os.path.isdir(filename): files = os.listdir(filename) total = len(files) print '[loader] Total pcap files: {}'.format(total) finished = 0 for each in files: finished += 1 insert_single_file(os.path.abspath(os.path.join(filename, each))) print '[loader] {}/{} finished.'.format(finished, total)
def get_all_users(): cur = db.cursor() sql = "select email from user" db.ping(reconnect=True) cur.execute(sql) result = cur.fetchall() db.commit() cur.close() return result
def modify(sql, keys): try: with db: cur = db.cursor() cur.execute(sql, keys) db.commit() return 1 except: return 0
def issue_detail(Ino): if request.method == 'GET': try: if request.method == 'GET': cur = db.cursor() sql = "select Issue.title from Issue where Ino = '%s'" % Ino db.ping(reconnect=True) cur.execute(sql) # 这里返回的是一个列表,即使只有一个数据,所以这里使用cur.fetchone()[0] issue_title = cur.fetchone()[0] sql = "select UserInformation.nickname,Comment.comment,Comment.comment_time,Comment.Cno from Comment,UserInformation where Comment.email = UserInformation.email and Ino = '%s'" % Ino db.ping(reconnect=True) cur.execute(sql) comment = cur.fetchall() cur.close() # 返回视图,同时传递参数 return render_template('issue_detail.html', Ino=Ino, issue_title=issue_title, comment=comment) except Exception as e: raise e if request.method == 'POST': Ino = request.values.get('Ino') email = session.get('email') comment = request.values.get('editorValue') comment_time = time.strftime("%Y-%m-%d %H:%M:%S") try: cur = db.cursor() sql = "select max(Cno) from Comment where Ino = '%s' " % Ino db.ping(reconnect=True) cur.execute(sql) result = cur.fetchone() Cno = int(result[0]) + 1 Cno = str(Cno) sql = "insert into Comment(Cno, Ino, comment, comment_time, email) VALUES ('%s','%s','%s','%s','%s')" % ( Cno, Ino, comment, comment_time, email) cur.execute(sql) db.commit() cur.close() return redirect(url_for('issue_detail', Ino=Ino)) except Exception as e: raise e
def reply_messages(message): cursor = db.cursor() text = str(message.text.strip()) cursor.execute('select dispute_id, feedback_id from Messages WHERE chat_id={0} and message_id={1} limit 1'.format( message.chat.id, message.reply_to_message.message_id)) rows = cursor.fetchall() if not rows: bot.send_message(message.chat.id, 'Ты чот попутал, не на что отвечать)', reply_markup=markup_common) cursor.close() return dispute_id, feedback_id = rows[0] is_answer = dispute_id is not None parent_id = dispute_id or feedback_id first_line = text.splitlines()[0].strip() if first_line == '+': for_sign = True elif first_line == '-': for_sign = False else: for_sign = None if for_sign is not None: try: content = text.split('\n', 1)[1].strip() except IndexError: content = None else: content = text if is_answer and for_sign is not None: bot.send_message(message.chat.id, 'Нельзя оценить вопрос.', reply_markup=markup_common) cursor.close() return params = [message.from_user.id, for_sign, parent_id, content, is_answer] cursor.execute("insert into Feedback(user_id, for, parent_id, rating, content, is_answer) VALUES (?,?,?,0,?,?)", params) sent_message = bot.send_message(message.chat.id, (('Ответ' if is_answer else 'Комментарий') + ' успешно создан') if content else ( 'Ответ/комментарий успешно оценён'), reply_markup=markup_common) if not is_answer: update_feedback_rating(feedback_id) feedback_id = cursor.lastrowid if content: connect_message(message, feedback_id=feedback_id) connect_message(sent_message, feedback_id=feedback_id) cursor.execute('update User set state=state||? WHERE id=?', ['{}{}/'.format('a' if is_answer else 'c', cursor.lastrowid), message.from_user.id]) db.commit() send_stuff_by_state(message.from_user.id) cursor.close()
def edit_meeting_to_database(sql): try: cur = db.cursor() db.ping(reconnect=True) cur.execute(sql) db.commit() cur.close() except Exception as e: print(e)
def get_sponsor(sid): try: cur = db.cursor() db.ping(reconnect=True) sql = 'SELECT * FROM sponsors WHERE sponsor_id=%s' % sid cur.execute(sql) result = cur.fetchone() return result except Exception as e: print(e)
def feed(message): cursor = db.cursor() cursor.execute('SELECT last_dispute_id FROM User WHERE id=?', [message.from_user.id]) user_last_id, = cursor.fetchall()[0] cursor.execute('SELECT id FROM Dispute ORDER BY -id LIMIT 1;') real_last_id, = cursor.fetchall()[0] # todo fix falling cursor.execute('UPDATE User SET last_dispute_id=?,state=state||? WHERE id=?', [real_last_id, 'f({}-{})/'.format(user_last_id, real_last_id), message.from_user.id]) db.commit() cursor.close() send_stuff_by_state(message.from_user.id)
def add_leave_to_leave_history(mid, email, processor): try: cur = db.cursor() sql = "INSERT INTO leave_history(user_email, meeting_id, processor) VALUES ('%s',%s,'%s')" % ( email, mid, processor) db.ping(reconnect=True) cur.execute(sql) db.commit() cur.close() except Exception as e: print(e)
def add_sponsor(sql): try: cur = db.cursor() db.ping(reconnect=True) cur.execute(sql) db.commit() cur.close() except Exception as e: print(e)
def list_all_sponsors(): try: cur = db.cursor() sql = "select * from sponsors" db.ping(reconnect=True) cur.execute(sql) result = cur.fetchall() return result except Exception as e: print(e)
def list_meeting_of_user(user_email): try: cur = db.cursor() sql = "select N.meeting_id,meeting_title,meeting_date,meeting_location from meeting AS M,need_to_meeting AS N where user_email='%s' and M.meeting_id = N.meeting_id" % user_email db.ping(reconnect=True) cur.execute(sql) result = cur.fetchall() return result except Exception as e: print(e)
def add_all_staff_to_meeting(id): users = get_all_users() sql = "INSERT INTO need_to_meeting (user_email,meeting_id) VALUES" for email in users: sql += "('%s',%s)," % (email[0], id) sql = sql[:-1] + ";" cur = db.cursor() db.ping(reconnect=True) cur.execute(sql) db.commit() cur.close()
def get_all_meeting_id(): try: cur = db.cursor() sql = "select meeting_id from meeting" db.ping(reconnect=True) cur.execute(sql) result = cur.fetchall() return result except Exception as e: print(e)
def formula(): if request.method == 'GET': page = request.values.get('page') if page is None: page = int(1) page = int(page) try: cur = db.cursor() sql = "select count(*) from SDWZCS.formula_post" db.ping(reconnect=True) cur.execute(sql) db.commit() result = cur.fetchone()[0] if result is None: article_nums = 0 else: article_nums = int(result) page_num = int(article_nums / 5 + 0.9) # 防止页码溢出 if page < 1: page = int(1) if page > page_num: page = int(page_num) if article_nums > 0: sql = "select formula_id,title,creat_time,nickname from SDWZCS.formula_post, SDWZCS.userInformation where formula_post.author = userInformation.email order by formula_id DESC " cur.execute(sql) db.commit() result = cur.fetchall() formula_article = [] for iter in result: sql = "select content from question_detail where formula_id = '%s' and qno = '1'" % iter[ 0] cur.execute(sql) db.commit() content = cur.fetchone()[0] content = (content, ) formula_article.append(iter[:] + content[:]) # print(formula_article) cur.close() return render_template('formula.html', article_nums=article_nums, formula_article=formula_article, page=page, page_num=page_num) else: return render_template('formula.html', article_nums=article_nums, page=page, page_num=page_num) except Exception as e: raise e
def start(message): cursor = db.cursor() try: cursor.execute("insert into User(id,state) values ({0},'{1}')".format(message.from_user.id, '/')) db.commit() bot.send_message(message.from_user.id, 'Добро пожаловать! Введи /help, чтобы получить помощь') except Exception as e: if 'UNIQUE' in e.args[0]: bot.send_message(message.from_user.id, 'С возвращением!') else: raise cursor.close()
def approve_status(mid, email): try: cur = db.cursor() sql = "update meeting_leave set status='Approved' " \ "where meeting_id=%s and user_email='%s'" % (mid, email) db.ping(reconnect=True) cur.execute(sql) db.commit() cur.close() except Exception as e: print(e)
def source(): if request.method == 'GET': try: cur = db.cursor() sql = "select Fno,filename,file_info,file_time,nickname from Files,UserInformation where Files.email = UserInformation.email" db.ping(reconnect=True) cur.execute(sql) files = cur.fetchall() cur.close() return render_template('source.html', files=files) except Exception as e: raise e
def show_issue(): if request.method == 'GET': email = session.get('email') try: cur = db.cursor() sql = "select ino, email, title, issue_time from Issue where email = '%s' order by issue_time desc" % email db.ping(reconnect=True) cur.execute(sql) issue_detail = cur.fetchall() except Exception as e: raise e return render_template('show_issue.html', issue_detail=issue_detail)
def personal(): if request.method == 'GET': email = session.get('email') try: cur = db.cursor() sql = "select email, nickname, type, create_time, phone from UserInformation where email = '%s'" % email db.ping(reconnect=True) cur.execute(sql) personal_info = cur.fetchone() except Exception as e: raise e return render_template('personal.html', personal_info=personal_info)
""" @summary: A sample controller. @author: Abhishek Shrivastava <i.abhi27 [at] gmail [dot] com> """ from config import db from render import error cur = db.cursor() KEY_NAME = "name" KEY_CITY = "city" KEY_AGE = "age" def output(httpin): """ Every controller must have this output function atleast. This is the entry point. Input is a dict of POST/GET variables and any other extra keys added during URL parsing Output must be a dict of Key-Value pairs that would be substituted in template using YAPTU. """ if KEY_NAME in httpin: name = httpin[KEY_NAME] else: name = "Anonymous" if KEY_CITY in httpin: city = httpin[KEY_CITY] else: