Dynamic output to the page of information from the database. Flask + pyMySQL?

M

Malmind2021-05-22 16:16:25

Flask

Malmind, 2021-05-22 16:16:25

It is necessary to display information from the database on the page, after adding a record to the database via INSERT. I just tried to update every second through js:

setInterval(function()
{
    $('#main-table').load(document.URL +  ' #main-table');
}, 1000);

I thought the point is that he cannot call the function again, he tried to insert it into the decorator. Then I noticed that the only way to see a change in the database is to restart the project.
If any libraries, tools to solve this problem?
Here is my Flask application code:
main.py:

from flask import Flask
from flask import render_template
import pymysql
import pymysql.cursors

app = (Flask(__name__))

try:
    connection = pymysql.connect(
        host='localhost',
        port=3306,
        user='admin',
        password='123',
        database='dynamic_output',
        cursorclass=pymysql.cursors.DictCursor
    )
    print("Good")
except Exception as ex:
    print('Bad')
    print(ex)


def select_all():
    with connection.cursor() as cursor:
        select_all = "select * from names"
        cursor.execute(select_all)
        return cursor.fetchall()


@app.route('/')
def index():
    return render_template("index.html", select=select_all())


if __name__ == '__main__':
    app.run(debug=True)

templates/index.html:

<!DOCTYPE html>
<html lang="en">
<head>
    <meta charset="UTF-8">
    <title>Title</title>
    <style>
      .main-table{
          position: absolute;
          top: 50%;
          left: 50%;
          transform: translate(-50%, -50%);

          width: 600px;
          border-collapse: collapse;
          border: 1px solid #F2F2F2;
          box-shadow: 0px 0px 20px rgb(0 0 0 / 8%);
      }

      .title-table{
          text-align: start;
          font-size: 23px;
      }

      .content-table{
          font-size: 20px;
      }

    .table-item{
        padding: 20px;
        border: 1px solid #F2F2F2;
    }
    </style>
</head>
<body>
    <table class="main-table" id="main-table">
        <tr class="table-string">
            {% for key in select[0].keys() %}
                <th class="title-table table-item"> {{ key }}</th>
            {% endfor %}
        </tr>
        {% for lib in select %}
            <tr class="table-string">
                {% for key, value in lib.items() %}
                    <td class="content-table table-item"> {{ value }} </td>
                {% endfor %}
            </tr>
        {% endfor %}
    </table>
</body>
</html>

DB creation script:

create database dynamic_output;
use dynamic_output;

CREATE TABLE IF NOT EXISTS `dynamic_output`.`names` (
  `id` INT NOT NULL,
  `name` VARCHAR(45) NULL,
  `age` INT NULL,
  PRIMARY KEY (`id`))
ENGINE = InnoDB;

INSERT INTO names (id, name, age) VALUES (1, 'Jhon', 25);

Reply

Answer the question

In order to leave comments, you need to log in

1 answer(s)

M

Malmind, 2021-05-23
@Malmind

Solved the problem with SQLAlchemy. Replaced this code in the main.py file:

import pymysql
import pymysql.cursors

app = (Flask(__name__))

try:
    connection = pymysql.connect(
        host='localhost',
        port=3306,
        user='admin',
        password='123',
        database='dynamic_output',
        cursorclass=pymysql.cursors.DictCursor
    )
    print("Good")
except Exception as ex:
    print('Bad')
    print(ex)


def select_all():
    with connection.cursor() as cursor:
        select_all = "select * from names"
        cursor.execute(select_all)
        return cursor.fetchall()

On the:

import sqlalchemy

engine = sqlalchemy.create_engine('mysql+pymysql://admin:[email protected]/dynamic_output')
res = engine.execute('SELECT * FROM names') 
rows = res.fetchall()

The last two lines must be inserted into the decorator so that they are called.
And I added a function that after a certain point in time makes a request to the server:

setInterval(function()
{
    $('#main-table').load(document.URL +  ' #main-table'); // #main-table id блока который нужно обновлять
}, 2000);

I did the same but with pyMySQL, but it didn't work for some reason