def get_table_data(): ''' show data from database ''' table_name = request.form.get('name') project_number = session.get('project_number') if session.get( 'project_number') else '' if request.method == 'POST' and table_name: ''' #分页 page = request.values.get('page') if request.values.get('page') else 1 # show all data rows = request.values.get('rows') if request.values.get('page') else 100 offset = (int(page) - 1) * int(rows) ''' #排序 sort_col = request.values.get('sort') if request.values.get( 'sort') else 'sample_name' order_type = request.values.get('order') if request.values.get( 'order') else 'asc' data = [] i = 0 db = DBConn() cmd = "select * from {table} where project_id = '{project_number}' order by {sort_col} {order}".format( table=table_name, project_number=project_number, sort_col=sort_col, order=order_type) results = db.execute(cmd) for result in results: data.append(dict(result)) i += 1 return jsonify({'total': i, 'rows': data}) return redirect(request.referrer)
def upload_return_table(): if request.method == 'POST': file = request.files['file'] if file and interface.allow_files(file.filename): filename = secure_filename(file.filename) if not os.path.exists(app.config['UPLOAD_FOLDER']): os.makedirs(app.config['UPLOAD_FOLDER']) file.save(os.path.join(app.config['UPLOAD_FOLDER'], filename)) filepath = os.path.join(app.config['UPLOAD_FOLDER'], filename) data = interface.transfer_excel_to_json2(file_name=filepath) if not data: flash('your xlsx have more sheets!') return redirect(request.referrer) table_name = interface.check_table(data[0].keys()) if table_name != 'return_table': flash('it not {file_name},please try again!'.format( file_name='return_table')) return redirect(request.referrer) db = DBConn() for each_record in data: db.insert('return_table', each_record) return redirect(request.referrer) elif file == '': flash('no select file!') return redirect(request.referrer) else: flash('only .xls or .xlsx file required!') return redirect(request.referrer)
def export_table_info(table_name, project_id, title_list): book = Workbook() sheet1 = book.worksheets[0] # connect database: db = DBConn() cmd = "select * from {table_name} where project_id = '{project_id}'".format(table_name=table_name, project_id=project_id) results = db.execute(cmd) all_table_data = [] for result in results: all_table_data.append(dict(result)) for i, title in enumerate(title_list): sheet1.cell(row=1, column=i + 1).value = title for i, data in enumerate(all_table_data): for j, title in enumerate(title_list): sheet1.cell(row=i + 2, column=j + 1).value = data.get(header_map[title]) export_path = os.path.join(os.path.abspath(os.path.dirname(__file__)),'static/export/') file_name = table_name + '_' + project_id + '_' + datetime.datetime.now().strftime("%Y%m%d%H%M%S") + '.xls' full_path_name = os.path.join(export_path, file_name) if not os.path.exists(export_path): os.mkdir(export_path) open(full_path_name,'w+').close() book.save(filename=full_path_name) return file_name
def get_table_data(): if request.method == 'POST': table_name = request.values.get('name',None) #分页 page = request.values.get('page') if request.values.get('page') else 1 rows = request.values.get('rows') if request.values.get('page') else 10 offset = (int(page) - 1) * int(rows) #排序 sort_col = request.values.get('sort') if request.values.get('sort') else 'sample_name' order_type = request.values.get('order') if request.values.get('order') else 'asc' if table_name: data = [] i = 0 db = DBConn() cmd = 'select * from {table} order by {sort_col} {order} limit {offset},{rows}'.format( table=table_name, sort_col=sort_col, order=order_type, offset=offset, rows=rows) results = db.execute(cmd) for result in results: data.append(dict(result)) i += 1 return jsonify({'total':i,'rows':data}) else: return redirect(url_for('index')) return redirect(url_for('index'))
def destroy_all_sample(): project_number = session.get('project_number') if session.get( 'project_number') else '' table_name = request.args.get('table') db = DBConn() ans = db.delete(table_name, condition_dict={'project_id': project_number}) if ans: return jsonify({'success': 'true', 'errMsg': 'error'}) else: return jsonify({'success': 'false', 'errMsg': 'del fail!'})
def del_select_data(): sample_name = request.args.get('id') sample_time = request.args.get('time') table_name = request.args.get('table') db = DBConn() ans = db.delete(table_name, condition_dict={'sample_name': sample_name}) if ans: return jsonify({'success': 'true', 'errMsg': 'error'}) else: return jsonify({'success': 'false', 'errMsg': 'del fail!'})
def upload_file(): if request.method == 'POST': file = request.files['file'] if file and allow_files(file.filename): filename = secure_filename(file.filename) if not os.path.exists(app.config['UPLOAD_FOLDER']): os.makedirs(app.config['UPLOAD_FOLDER']) file.save(os.path.join(app.config['UPLOAD_FOLDER'], filename)) filepath = os.path.join(app.config['UPLOAD_FOLDER'], filename) data = transfer_excel_to_json2(file_name=filepath) #input to database: db = DBConn() for each_record in data: db.insert('send_sample_table',each_record) return redirect(url_for('index')) elif file == '': flash('no select file!') return redirect(url_for('index')) else: flash('only .xls or .xlsx file required!') return redirect(url_for('index'))
def save_input_data(): if request.method == 'POST': action = request.form.get('action',None) table_name = request.form.get('table_name',None) #just test two tables send_sample_table_dict = dict.fromkeys(['project_id','sample_name', 'species','extract_part', 'sample_number','comment','time']) quality_inspection_table_dict = dict.fromkeys(['project_id','sample_name','sample_id', 'od_260_or_230','od_260_or_280','25S_or_18S', 'rin','volume','concentration','quantum', 'database_type','judgeresult','comment','time']) up_machine_table_dict = dict.fromkeys(['project_id','sample_name', 'upmachine_type','upmachine_mode','up_quant', 'comment','time']) down_machine_table_dict = dict.fromkeys(['project_id','sample_name','sample_id','down_quant', 'reads','q30','comment','time']) return_table_dict = dict.fromkeys(['project_id','sample_name','species','surplus','comment','time']) all_table_dicts = dict(send_sample_table=send_sample_table_dict, quality_inspection_table=quality_inspection_table_dict, up_machine_table=up_machine_table_dict, down_machine_table=down_machine_table_dict, return_table=return_table_dict) send_sample_row = None for each_table,table_content in all_table_dicts.items(): if table_name == each_table: send_sample_row = all_table_dicts[each_table] for key,value in send_sample_row.items(): send_sample_row[key] = request.form.get(key,None) db = DBConn() if action == 'insert' and send_sample_row: db.insert(table_name, send_sample_row) return jsonify({'success': 'true', 'errMsg': 'error'}) elif action == 'update' and send_sample_row: try: db.update(table_name, condition_dict={'sample_id':send_sample_row['sample_id']}, update_dict=send_sample_row) except Exception,e: db.update(table_name, condition_dict={'sample_name': send_sample_row['sample_name']}, update_dict=send_sample_row) return jsonify({'success': 'true', 'errMsg': 'error'}) else: return jsonify({'success': 'false', 'errMsg': 'error'})