Search code examples
javascripthtmlsqliteflaskjinja2

Run queries execution in Jinja template

How can get db.execute("SELECT * FROM table ...") in this html?

enter image description here

app = Flask(__name__)
db_admin = SQL("sqlite:///administration.db")
@app.route("/settings", methods=["GET", "POST"])

def setting():
    if request.method == "POST":

        return render_template("settings.html")

I have a database and need in my template (settings.html) UPDATE table SET ... value in database whenonchange event happens in td.

<td onclick="query();">{{ cell }}</td>

I have try:

function query()
{
    let sqlite3 = require('sqlite3');
    let db = new sqlite3.Database('./administration.db');
    row= db.execute("UPDATE table SET user_group=? WHERE ...", value);
}

Please assume the value is inpute text value.

Solution

  • I think your approach is getting a lot of traffic. I think it makes more sense to point out to the user that they left unsaved data. You could use a beforeunload event for this. However, this hits a limit when the user closes the window.
    The code could look something like this.

    <script type="text/javascript">
    (function() {
        window.addEventListener('DOMContentLoaded', () => {
            let isChanged = false;
            document.querySelectorAll('input').forEach(elem => {
                elem.addEventListener('change', () => {
                    isChanged = true; 
                });
            });

            document.querySelector('form').addEventListener('submit', () => {
                isChanged = false;
            })

            window.addEventListener('beforeunload', (evt) => {
                if (isChanged) {
                    evt.returnValue = isChanged;
                }
            });
        });
    })();
</script>

    It is still possible, as you have thought, to use AJAX to send the form data to the server in the background for each fired event.

    @app.route('/settings', methods=['GET', 'POST'])
def settings():
    if request.method == 'POST':
        print(request.form)
        # Update your database here.
    return render_template('settings.html', **locals())

    <!DOCTYPE html>
<html>
<head>
    <meta charset="utf-8">
    <meta name="viewport" content="width=device-width, initial-scale=1">
    <title>Index</title>
</head>
<body>
    <form method="post">
        <input type="text" name="name" />
        <button type="submit">Submit</button>
    </form>

    <script type="text/javascript">
        // Call the function when available.
        (function() {
            // Wait for the document to load completely.
            window.addEventListener('DOMContentLoaded', () => {
                // Search for all input fields and iterate over them.
                document.querySelectorAll('input').forEach(elem => {
                    // Register an EventListener for 'change' events for each input field.
                    elem.addEventListener('change', function() {
                        // Send the entire form to the server as soon as an event is fired.
                        fetch('/settings', {
                            method: 'post', 
                            body: new FormData(this.form)
                        });
                    });
                });
            });
        })();
    </script>
</body>
</html>

    If you only want to send the changed input and not the whole form, the code is easy to modify.

    <script type="text/javascript">

    (function() {
        window.addEventListener('DOMContentLoaded', () => {
            document.querySelectorAll('input').forEach(elem => {
                elem.addEventListener('change', function() {
                    const formData = new FormData();
                    formData.append(this.name, this.value); 
                    fetch('/settings', {
                        method: 'post', 
                        body: formData
                    });
                });
            });
        });
    })();

</script>