This is my model.py, which decribes my sqllite database and all entries. Is the relationship correct? A person is uploading a file and all imputs of the data are saved in a sqllite databank. Which relationship I have to use? 1. one to many, one to one, many to many? Can someone explain me this please? (any person can upload a file and the file is connected to the uploading user id)
—>models.py
from . import db
from datetime import datetime
from flask_login import UserMixin
from sqlalchemy.sql import func
#Many to Many Relationship, https://overiq.com/flask-101/database-modelling-in-flask/
class Note(db.Model):
id = db.Column(db.Integer, primary_key=True)
data = db.Column(db.String(10000))
date = db.Column(db.DateTime(timezone=True), default=func.now())
user_id = db.Column(db.Integer, db.ForeignKey('user.id'))
# Thanks to inheritance to db.Model, data provided via constructor call will be mapped
# to DB entries
class User(db.Model, UserMixin):
id = db.Column(db.Integer, primary_key=True)
email = db.Column(db.String(150), unique=True)
password = db.Column(db.String(150))
first_name = db.Column(db.String(150))
AG = db.Column(db.String(150), nullable=False)
notes = db.relationship('Note')
Beschriftung = db.relationship('File')
is_admin = db.Column(db.Boolean, default=False)
def __repr__(self):
return '<User %r>' % (self.Beschriftung)
class File(db.Model):
id = db.Column(db.Integer, primary_key=True)
dateipfad = db.Column(db.String(500), unique=False, nullable=False)
name = db.Column(db.String(150), unique=False, nullable=False)
kategorie = db.Column(db.String(150), unique=False, nullable=False)
bezeichnung = db.Column(db.String(150), unique=False)
beschreibung = db.Column(db.String(500), unique=False)
tv = db.Column(db.String(150), unique=False, nullable=False)
stand = db.Column(db.DateTime, unique=False, nullable=False, default=datetime.utcnow())
uploaddatum = db.Column(db.DateTime, unique=False, nullable=False, default=datetime.utcnow())
uploadbenutzer = db.Column(db.String(150), unique=False, nullable=False)
user_id = db.Column(db.Integer, db.ForeignKey('user.id'))
AG = db.Column(db.String(150), nullable=False)
Vertraulichkeitsstufen = db.Column(db.String(150), nullable=True)
Autorisierte_Person = db.Column(db.String(150), nullable=True)
# here I use a dictionary, because I can display them in the home.html template as a sortable #table
def to_displayable_dict(self):
return {
"Dateiname" : "<a href=/download/" + self.name + ">" + self.name + "</a>",
"Kategorie" : self.kategorie,
"Bezeichnung" : self.bezeichnung,
"TV" : self.tv,
"Stand" : self.stand,
"Upload-Benutzer" : self.uploadbenutzer,
"Vertraulichkeitsstufen" : self.Vertraulichkeitsstufen
}
def __repr__(self):
return '<File %r>' % (self.beschreibung)
Here I have a view.py script. Under views route function home I let all data display from my “file table”. My problem is, how to update automatically my database, so if I go to my upload folder in windows and delete abc.file, it automatically looks into my sqllite databse and deletes the file with name abc. How to program it, I really have no clue about automatization?
# files = get_files(app.config['UPLOAD_FOLDER'])
def get_files(target):
for file in os.listdir(target):
path = os.path.join(target, file)
if os.path.isfile(path):
yield (
file,
datetime.utcfromtimestamp(os.path.getmtime(path)),
os.path.getsize(path)
)
from . import db
from .models import Note
from .models import File,User
from flask import Blueprint, render_template, request, flash, redirect, url_for, send_from_directory, abort, jsonify
from flask_login import login_required, current_user
from datetime import datetime
from werkzeug.security import generate_password_hash, check_password_hash
from werkzeug.utils import secure_filename
import os
import json
from flask_moment import Moment
from flask import Flask
views = Blueprint('views', __name__)
app = Flask(__name__)
app.config['UPLOAD_FOLDER'] = "C:/Users/add706/Documents/NRL_webseite/instance/uploads"
moment = Moment(app)
@views.route("https://stackoverflow.com/", methods=['GET', 'POST'])
@login_required
def home():
if request.method == 'POST':
note = request.form.get('note')
if len(note) < 1:
flash('Note is too short!', category='error')
else:
new_note = Note(data=note, user_id=current_user.id)
db.session.add(new_note)
db.session.commit()
flash('Note added!')
user=current_user
# files = get_files(app.config['UPLOAD_FOLDER'])
files = [file.to_displayable_dict() for file in File.query if is_file_visible(file)]
# by passing **locals() to the template, all local variables
# created here (user, files) will be available to jinja2
return render_template('home.html', **locals())
# Anzeigen der Downloaddateien
def is_file_visible(file):
if current_user.is_admin:
return True
if file.Vertraulichkeitsstufen == None:
return current_user.AG == file.AG
if file.Vertraulichkeitsstufen == "Öffentlich":
return True
if file.Vertraulichkeitsstufen == "Verwendung intern":
return True
auth_user= str(file.Autorisierte_Person).split(",")
#new= auth_user.split(",")
#print(auth_user)
print("test1:", auth_user)
if file.Vertraulichkeitsstufen == "Verwendung nach Rücksprache" and current_user.email in auth_user:
return True
if file.Vertraulichkeitsstufen == "Nicht zugänglich":
return False
return False
# return current_user.is_admin or file.Vertraulichkeitsstufen == "Öffentlich" or (file.Vertraulichkeitsstufen == "Verwendung Intern" and current_user.AG == file.AG)
@views.route('/delete-note', methods=['POST'])
def delete_note():
note = json.loads(request.data)
noteId = note['noteId']
note = Note.query.get(noteId)
if note:
if note.user_id == current_user.id:
db.session.delete(note)
db.session.commit()
return jsonify({})
# ---
@views.route('/about')
def about():
return render_template('about.html', user=None)
# ---
#Upload files
@views.route('/upload', methods = ['GET', 'POST'])
@login_required
def uploadfile():
upload_folder = app.config['UPLOAD_FOLDER']
if not request.method == 'POST':
return redirect("https://stackoverflow.com/")
if 'file' not in request.files:
flash('Klappt nicht, mein Freundchen!', category="error")
return redirect(request.url)
file = request.files['file'] # get the file from the files object
if file.filename == '':
flash('Bitte Datei zum Upload auswählen!', category="error")
return redirect(request.url)
filename = secure_filename(file.filename)
filepath = os.path.join(upload_folder , filename)
query_file = File.query.filter_by(name = str(filename)).first()
#print ("filename:", filename)
#print ("what:", query_file)
if query_file:
flash('Ein Duplikat des Dateinamen existiert schon. Ändere bitte den Dateinamen!', category="error")
return redirect(request.url)
add_file_to_database(request, filepath, filename)
file.save(filepath) # this will secure the file
flash('Datei erfolgreich hochgeladen.', category='success') # Display this message after uploading
return redirect("https://stackoverflow.com/")
def add_file_to_database(req, filepath, filename):
print("date:", req.date)
if "Kategorie" not in req.form or "Bezeichnung" not in req.form or "TV" not in req.form or "Stand" not in req.form:
flash("Missing args in request")
new_file = File(dateipfad=filepath, name=filename,
kategorie=req.form["Kategorie"],
bezeichnung=req.form["Bezeichnung"],
beschreibung=req.form["Beschreibung"],
tv=req.form["TV"],
stand=datetime.fromisoformat(req.form["Stand"]),
uploaddatum=datetime.utcnow(),
uploadbenutzer=current_user.first_name,
user_id=current_user.id,
AG=current_user.AG,
)
db.session.add(new_file)
db.session.commit()
#Not using it anymore, but I have to somehow. It returns also file size and a timestamp. Stackoverflow user detlef programmed it once for me, after I had to change it.
def get_files(target):
for file in os.listdir(target):
path = os.path.join(target, file)
if os.path.isfile(path):
yield (
file,
datetime.utcfromtimestamp(os.path.getmtime(path)),
os.path.getsize(path)
)
@views.route('/download/<path:filename>')
@login_required
def download(filename):
return send_from_directory(
app.config['UPLOAD_FOLDER'],
filename,
as_attachment=True
)
This is my actual home.html template, which has to be changed. I wanted here a tooltip. If I hover with my mouse over a filename, it should show me the “Beschreibung”. In the sense of, file abc shows the “Beschreibung” (description) which I saved with the file in my databank. The problem here is, that I dont know how to programm it in jquery. Here is a link showing how userpictures are highlit by being hovered. I want a tooltip poping up my Beschreibung.
{% extends "base.html" -%}
{% block title %}Home{% endblock -%}
{% block content -%}
<h1 align="center">Notes</h1>
<h4>Hello my Stackoverflow mate, {{ user.first_name}}!</h4>
</h4>Arbeitsgruppe-{{ user.AG }}</h4>
<ul class="list-group list-group-flush" id="notes">
{% for note in user.notes %}
<li class="list-group-item">
{{ note.data }}
<button type="button" class="close" onClick="deleteNote({{ note.id }})">
<span aria-hidden="true">×</span>
</button>
</li>
{% endfor %}
</ul>
<form method="POST">
<textarea name="note" id="note" class="form-control"></textarea>
<br />
<div align="center">
<button type="submit" class="btn btn-primary">Add Note</button>
</div>
</form>
<br>
<br>
<br>
<br>
<!-- upload Folder-->
{% if user.is_authenticated %}
<div class="container">
<div class="row">
<div class="col">
<h1 align="center" class="text-success">Datei Upload</h1>
<hr>
<form
action="http://localhost:5000/upload"
method="POST"
enctype="multipart/form-data">
<input type="file" name="file" /><br>
<div class="font-weight-bold text-success">Kategorie:</div>
<select name="Kategorie" class="form-control" id="Kategorie">
<option value="" selected>Bitte Kategorie auswählen!</option>
<option>Szenarien</option>
<option>Demonstratoren</option>
<option>Annahmen Simulation</option>
<option>Netzdaten</option>
<option>Sonstige</option>
</select>
<div class="font-weight-bold text-success">Bezeichnung:</div>
<input type="text" name="Bezeichnung" id="Bezeichnung"></input>
<div class="font-weight-bold text-success">Beschreibung:</div>
<input type="text" name="Beschreibung" id="Beschreibung"></input>
<div class="font-weight-bold text-success">Teilvorhaben:</div>
<select name="TV" class="form-control" id="TV">
<option value="" selected>Bitte Teilvorhaben auswählen!</option>
<option>TV 1.1 Das Netz</option>
<option>TV 1.2 Die Werkstatt</option>
<option>TV 2.1 Hobby-Raum</option>
</select>
<label for="Stand" class="font-weight-bold text-success">Stand:</label>
<input type="datetime-local" id="Stand" name="Stand">
<br>
<button type="submit" class="btn btn-primary">Upload</button>
</form>
</div>
</div>
</div>
<br>
<br>
<br>
<br>
<!-- POSTGRE-Connection-->
<h1 align="center">POSTGRE-Connection</h1>
<div align="center">
<a href="{{url_for('server.showChart')}}">
<image src="{{ url_for('static', filename="Bilder/postgre.jpeg")}}">
</a>
</div>
<br>
<br>
<br>
<br>
<!-- download Folder-->
{% for file in user.Beschriftung %}
<div class="popup">{{ file.beschreibung }}</div>
{% endfor %}
<h1>Dateien Download</h1>
<!-- for-loop dictionary def to_displayable_dict-->
{% if files %}
<table id="file_list" style="width:100%; margin:auto;" class="table table-striped table-hover table-sm">
<thead>
<tr>
{% for key in files[0].keys() -%}
<th>{{ key }}</th>
{% endfor %}
</tr>
</thead>
{% for file in files %}
<tr>
{% for val in file.values() %}
<td>{{ val | safe }}</td>
{% endfor %}
</tr>
{% endfor %}
</thead>
</table>
{% endif %}
{% endif %}
{% endblock -%}
{% block scripts %}
<script>
$(document).ready(function () {
$('#file_list').DataTable({
columns: [
null,
{searchable: false},
{orderable: false, searchable: true},
{searchable: false},
null,
null,
{searchable: false}],
});
});
$flag = -1;
$('a[href][download]').hover(
function () {
$("div.popup").attr("style", "display:block");
},
function () {
if ($flag == -1) {
$("div.popup").attr("style", "display:none");
}
}
);
</script>
{% endblock %}
My old home.html look like this, before I used my first time jquery from a blog:
https://cc.bingj.com/cache.aspx?q=interactive+table+flask&d=4730455825844638&mkt=de-DE&setlang=de-DE&w=VsvuLVugaKNnKdas_OsYwWv4tOGr7LNi
{% extends "base.html" -%}
{% block title %}Home{% endblock -%}
{% block content -%}
<h1 align="center">Notes</h1>
<ul class="list-group list-group-flush" id="notes">
{% for note in user.notes %}
<li class="list-group-item">
{{ note.data }}
<button type="button" class="close" onClick="deleteNote({{ note.id }})">
<span aria-hidden="true">×</span>
</button>
</li>
{% endfor %}
</ul>
<form method="POST">
<textarea name="note" id="note" class="form-control"></textarea>
<br />
<div align="center">
<button type="submit" class="btn btn-primary">Add Note</button>
</div>
</form>
<br>
<br>
<br>
<br>
<!-- upload Folder-->
{% if user.is_authenticated %}
<div class="container">
<div class="row">
<div class="col">
<h1 align="center">Datei Upload</h1>
<hr>
<form
action="http://localhost:5000/upload"
method="POST"
enctype="multipart/form-data">
<input type="file" name="file" />
<button type="submit" class="btn btn-primary">Upload</button>
</form>
</div>
</div>
</div>
<br>
<br>
<br>
<br>
<!-- POSTGRE-Connection-->
<div align="center">
<a href="{{url_for('server.showChart')}}">
<image src="{{ url_for('static', filename="Bilder/postgre.jpeg")}}">
</a>
</div>
<!-- download Folder-->
<head>
<style>
table, th, td {
border: 1px solid black;
}
</style>
</head>
<body>
<h1>Downloads</h1>
<p>Hi {{ user.first_name, user.email }}!</p>
<table style="width:100%; margin:auto; table-layout: fixed;">
<tr>
<th>Dateiname</th>
<th>Datum und Uhrzeit</th>
<th>Dateigröße</th>
</tr>
{% for filename, mtime, size in files -%}
<tr>
<td><a href="{{ url_for('views.download', filename=filename) }}" download>{{ filename }}</a></td>
<td>{{ moment(mtime, local=False).format('DD.MM.YYYY HH:mm') }}</td>
<td style="text-align:right;">{{ size | byte_units }}</td>
</tr>
{% endfor -%}
</table>
<script type="text/javascript">
(() => {
const elems = document.querySelectorAll('a[href][download]');
elems.forEach(elem => {
elem.addEventListener('click', evt => {
const isDonwload = window.confirm('Möchten Sie die ausgewählte Datei herunterladen??');
if (!isDonwload) { evt.preventDefault(); }
});
});
})();
</script>
{% endif %}
{% endblock -%}
This is my base.html.
<!DOCTYPE html>
<html>
<head>
<meta charset="utf-8" />
<meta name="viewport" content="width=device-width, initial-scale=1" />
<link
rel="stylesheet"
href="https://stackpath.bootstrapcdn.com/font-awesome/4.7.0/css/font-awesome.min.css"
crossorigin="anonymous"
/>
<link href="https://cdn.jsdelivr.net/npm/bootstrap@5.0.1/dist/css/bootstrap.min.css" rel="stylesheet" integrity="sha384-+0n0xVW2eSR5OomGNYDnhzAbDsOXxcvSN1TPprVMTNDbiYZCxYbOOl7+AMvyTG2x" crossorigin="anonymous">
<link rel="stylesheet" type="text/css" href="https://cdn.datatables.net/1.10.25/css/dataTables.bootstrap5.css">
<title>{% block title %}Home{% endblock %}</title>
{{ moment.include_moment() }}
<style>
center {
font-size: 30px;
color: green;
}
.popup {
display: none;
width: 500px;
border: solid red 3px
}
</style>
</head>
<body>
<nav class="navbar navbar-expand-lg navbar-dark bg-dark">
<button
class="navbar-toggler"
type="button"
data-toggle="collapse"
data-target="#navbar"
>
<span class="navbar-toggler-icon"></span>
</button>
<div class="collapse navbar-collapse" id="navbar">
<div class="navbar-nav">
{% if user.is_authenticated %}
<a class="nav-item nav-link" id="home" href="/">Home</a>
<a class="nav-item nav-link" id="logout" href="/logout">Logout</a>
{% else %}
<a class="nav-item nav-link" id="login" href="/login">Login</a>
<a class="nav-item nav-link" id="signUp" href="/sign-up">Sign Up</a>
<a class="nav-item nav-link" id="Über Uns" href="/about">Über uns</a>
{% endif %}
</div>
</div>
</nav>
{% with messages = get_flashed_messages(with_categories=true) %} {% if
messages %} {% for category, message in messages %} {% if category ==
'error' %}
<div class="alert alert-danger alter-dismissable fade show" role="alert">
{{ message }}
<button type="button" class="close" data-dismiss="alert">
<span aria-hidden="true">×</span>
</button>
</div>
{% else %}
<div class="alert alert-success alter-dismissable fade show" role="alert">
{{ message }}
</div>
{% endif %} {% endfor %} {% endif %} {% endwith %}
<div class="container">{% block content %} {% endblock %}</div>
<script
src="https://cdnjs.cloudflare.com/ajax/libs/popper.js/1.12.9/umd/popper.min.js"
integrity="sha384-ApNbgh9B+Y1QKtv3Rn7W3mgPxhU9K/ScQsAP7hUibX39j7fakFPskvXusvfa0b4Q"
crossorigin="anonymous"
></script>
<script
src="https://cdnjs.cloudflare.com/ajax/libs/twitter-bootstrap/5.0.0-alpha1/js/bootstrap.min.jss"
integrity="sha384-JZR6Spejh4U02d8jOt6vLEHfe/JQGiRRSQQxSfFWpi1MquVdAyjUar5+76PVCmYl"
crossorigin="anonymous"
></script>
<script type="text/javascript" charset="utf8" src="https://code.jquery.com/jquery-3.6.0.min.js"
></script>
<script type="text/javascript" charset="utf8" src="https://cdn.datatables.net/1.10.25/js/jquery.dataTables.js"></script>
<script type="text/javascript" charset="utf8" src="https://cdn.datatables.net/1.10.25/js/dataTables.bootstrap5.js"></script>
<script
type="text/javascript"
src="{{ url_for('static', filename="index.js") }}"
></script>
{% block scripts %} {% endblock %}
</body>
</html>
This is my init.py file.
from flask import Flask
from flask_login import LoginManager
from flask_moment import Moment
from flask_sqlalchemy import SQLAlchemy
import os
from flask_admin import Admin
from flask_admin.contrib.sqla import ModelView
from flask_login import current_user
from flask import request, redirect, url_for
DB_NAME = "database.db"
db = SQLAlchemy()
moment = Moment()
def byte_units(value, units=-1):
UNITS=('Bytes', 'KB', 'MB', 'GB', 'TB', 'EB', 'ZB', 'YB')
i=1
value /= 1000.0
while value > 1000 and (units == -1 or i < units) and i+1 < len(UNITS):
value /= 1000.0
i += 1
return f'{round(value,3):.3f} {UNITS[i]}'
class Admin_View(ModelView):
def is_accessible(self):
return current_user.is_authenticated and current_user.is_admin
def inaccessible_callback(self, name, **kwargs):
# redirect to login page if user doesn't have access
return redirect(url_for('home', next=request.url))
def create_app():
app = Flask(__name__)
app.config.from_mapping(
SECRET_KEY=b'your secret here',
SQLALCHEMY_DATABASE_URI='sqlite:///'+os.path.join(app.instance_path, DB_NAME),
SQLALCHEMY_TRACK_MODIFICATIONS=False,
UPLOAD_FOLDER=os.path.join(app.instance_path, 'uploads')
)
app.jinja_env.filters.update(byte_units = byte_units)
# ensure the instance folder exists
try:
os.makedirs(app.config['UPLOAD_FOLDER'])
except:
pass
db.init_app(app)
moment.init_app(app)
from .models import User, File
admin = Admin(app, name="nrl-file-explorer", template_mode="bootstrap3")
admin.add_view(Admin_View(File, db.session))
create_database(app)
login_manager = LoginManager()
login_manager.login_view = 'auth.login'
login_manager.init_app(app)
@login_manager.user_loader
def load_user(id):
return User.query.get(int(id))
from .views import views
from .auth import auth
from .server import server
app.register_blueprint(auth, url_prefix="https://stackoverflow.com/")
app.register_blueprint(views, url_prefix="https://stackoverflow.com/")
app.register_blueprint(server, url_prefix="https://stackoverflow.com/")
return app
def create_database(app):
if not os.path.exists(os.path.join(app.instance_path, DB_NAME)):
db.create_all(app=app)
print('Created Database!')