def view_template(): if request.method == 'GET': database = get_postgres_instance(dbname="dbtools") return jsonify({"data": [dict(row) for row in database.get_table("viewfilter", filter=request.values.to_dict(flat=False))]}) if request.method == 'POST': params = request.values.to_dict(flat=False) #formating params to fit database connection string if 'viewname' not in params: params['viewname'] = '' elif 'viewname' in params: params['viewname'] = params['viewname'][0] params['options'] = params['options'][0] params['tablename'] = params['tablename'][0] database = get_postgres_instance(dbname="dbtools") querystring = f""" INSERT INTO viewfilter (viewname, setting, tablename) VALUES('{params['viewname']}', '{params["options"]}', '{params['tablename']}') RETURNING id;""" id = database.engine.execute(querystring).fetchone()[0] return jsonify(id=id) if request.method == "DELETE": params = request.values.to_dict(flat=False) id = params["id"][0] database = get_postgres_instance(dbname="dbtools") return jsonify(success=database.delet_view_template(id))
def get_table_rows(table_name): if request.method == "GET": database = db_actions.get_postgres_instance() return jsonify({ "data": [ dict(row) for row in database.get_table( table_name, filter=request.values.to_dict(flat=False)) ] }) if request.method == "DELETE": database = db_actions.get_postgres_instance() response = database.delete_from_table( table_name, filter=request.values.to_dict(flat=False)) return jsonify(response)
def page_view(): database = get_postgres_instance() req = request.values.to_dict(flat=False) alltables = [] table_names = database.get_view_names() for table_name in table_names: alltables.append({"table_name": str(table_name), "parameters": req}) return render_template("view.html", alltables=alltables)
def alltables(): database = get_postgres_instance() alltables = [] table_names = database.get_view_names() table_names.extend(database.get_table_names()) for i in table_names: alltables.append({"table_name": str(i)}) return render_template("view.html", alltables=alltables)
def database_initialization(): database = get_postgres_instance(dbname="dbtools") database.engine.execute(""" CREATE TABLE IF NOT EXISTS public.viewfilter ( id SERIAL, viewname text COLLATE pg_catalog."default", setting json, tablename text, PRIMARY KEY (id)) """)
def export_folder_pictures(): database = db_actions.get_postgres_instance() id = time.strftime("%Y%m%d-%H%M%S") with open("parameters.json") as file: data = json.load(file) folder_name = Path(str(data["export_path"]), id) folder_name.mkdir(parents=True, exist_ok=True) pathes_to_pictures = database.get_table_column_values( "trigger_image_links", "image1", filter=request.values.to_dict(flat=False)) if len(pathes_to_pictures) > 0: for file in pathes_to_pictures: shutil.copy(file) return jsonify(export_folder=str(folder_name)) return ""
def download_csv(): database = db_actions.get_postgres_instance() memory_file = BytesIO() with py7zr.SevenZipFile(memory_file, 'w') as zf: for table_name in database.get_view_names(): data_list = [ dict(row) for row in database.get_table( table_name, filter=request.values.to_dict(flat=False)) ] if len(data_list) > 0: df = pd.DataFrame(data_list) df.columns = database.get_table_columns(table_name) zf.writestr(df.to_csv().encode(), arcname=f"{table_name}.csv") memory_file.seek(0) return send_file(memory_file, attachment_filename="testing.7z", as_attachment=True)
def download_excel(): database = db_actions.get_postgres_instance() output = BytesIO() writer = pd.ExcelWriter(output, engine='xlsxwriter') for table_name in database.get_view_names(): data_list = [ dict(row) for row in database.get_table( table_name, filter=request.values.to_dict(flat=False)) ] if len(data_list) > 0: df = pd.DataFrame(data_list) df.columns = database.get_table_columns(table_name) df.to_excel(writer, sheet_name=str(table_name)) writer.close() output.seek(0) return send_file(output, attachment_filename="testing.xlsx", as_attachment=True)
def export_folder_csv(): database = db_actions.get_postgres_instance() id = time.strftime("%Y%m%d-%H%M%S") with open("parameters.json") as file: data = json.load(file) folder_name = Path(str(data["export_path"]), id) folder_name.mkdir(parents=True, exist_ok=True) # creating a csv file for every table for table_name in database.get_view_names(): data_list = [ dict(row) for row in database.get_table( table_name, filter=request.values.to_dict(flat=False)) ] if len(data_list) > 0: df = pd.DataFrame(data_list) df.columns = database.get_table_columns(table_name) df.to_csv(os.path.join(folder_name, f"{table_name}.csv")) return jsonify(export_folder=str(folder_name))
def delete_pictures(): database = db_actions.get_postgres_instance() pathes_to_pictures = database.get_table_column_values( "trigger_image_links", "image1", filter=request.values.to_dict(flat=False)) missing_files = [] deleted_files = [] if len(pathes_to_pictures) > 0: for picture in pathes_to_pictures: picture = Path(picture) if picture.exists(): picture.unlink() deleted_files.append(picture) else: missing_files.append(picture) return jsonify(success=True, deleted_files=len(deleted_files), missing_files=len(missing_files)) return jsonify(success=False)
def export_folder_excel(): database = db_actions.get_postgres_instance() output = BytesIO() writer = pd.ExcelWriter(output, engine='xlsxwriter') for table_name in database.get_view_names(): data_list = [ dict(row) for row in database.get_table( table_name, filter=request.values.to_dict(flat=False)) ] if len(data_list) > 0: df = pd.DataFrame(data_list) df.columns = database.get_table_columns(table_name) df.to_excel(writer, sheet_name=str(table_name)) writer.close() output.seek(0) id = time.strftime("%Y%m%d-%H%M%S") with open("parameters.json") as file: data = json.load(file) folder_name = Path(str(data["export_path"]), id) folder_name.mkdir(parents=True, exist_ok=True) with open(f"{folder_name}\\Tables.xlsx", "wb") as outfile: outfile.write(output.getbuffer()) return jsonify(export_folder=str(folder_name))
def get_pictures_7z(): database = db_actions.get_postgres_instance() pathes_to_pictures = database.get_table_column_values( "trigger_image_links", "image1", filter=request.values.to_dict(flat=False)) if len(pathes_to_pictures) > 0: zip_uuid = f"{str(uuid.uuid4())}" with open(zip_uuid + '.txt', "w") as file: for picture in pathes_to_pictures: file.write(picture + "\n") subprocess.run([ "7z", "a", "-spf", "-t7z", "-m0=LZMA2:d64k:fb32", "-ms=8m", "-mmt=32", "-mx=1", zip_uuid + ".7z", "@" + zip_uuid + ".txt" ], stdout=subprocess.PIPE) with open(zip_uuid + ".7z", 'rb') as fh: memory_file = BytesIO(fh.read()) #removing temporary files os.remove(zip_uuid + ".7z") os.remove(zip_uuid + ".txt") return memory_file return ""
def delete_db(): database = db_actions.get_postgres_instance() return jsonify( database.delete_vision(filter=request.values.to_dict(flat=False)))
def get_column_content(table_name, column_name): database = db_actions.get_postgres_instance() return jsonify( database.get_table_column_values( table_name, column_name, filter=request.values.to_dict(flat=False)))
def get_table_columns(table_name): database = db_actions.get_postgres_instance() return jsonify({"columns": [*database.get_table_columns(table_name)]})
def get_table_names(): database = db_actions.get_postgres_instance() table_names = [*database.get_table_names()] return jsonify(columns=table_names)