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)