python – How to display a tooltip and how to refresh the downloadfolder with flask and jquery?

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">&times;</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">&times;</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">&times;</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!')

Leave a Comment