How to handle multiple requests at the same time through one python mysql.connector connection?

R

Rustam Ismailov2021-12-14 13:10:12

MySQL

Rustam Ismailov, 2021-12-14 13:10:12

Good afternoon, while developing a project, I encountered a problem related to the database. The problem is that with multiple simultaneous requests, the connection to the database is broken and gives an IndexError: bytearray index out of range error. I'm new to this, please tell me what could be wrong.
Here is the source code:

from flask import Flask, render_template
import jinja2
import mysql.connector
from mysql.connector import Error

app = Flask (__name__) # Обьявление приложения

def create_connection(host_name, user_name, user_password, db_name): # Подключение базы данных
    connection = None
    try:
        connection = mysql.connector.connect(
            host=host_name,
            user=user_name,
            passwd=user_password,
            database=db_name
        )
        print("Connection to MySQL DB successful")
    except Error as e:
        print(f"The error '{e}' occurred")

    return connection

db_connect = create_connection('test_host', 'test_name', 'test_password', 'test_db_name')

sql_list = []

def execute_read_query(connection, query): # Чтение данных из БД
    cursor = connection.cursor()
    result = None
    try:
        cursor.execute(query)
        result = cursor.fetchall()
        return result
    except Error as e:
        print(f"The error '{e}' occurred")

def execute_query(connection, query): # Запись в БД
    cursor = connection.cursor()
    try:
        cursor.execute(query)
        connection.commit()
        print("Query executed successfully")
    except Error as e:
        print(f"The error '{e}' occurred")

@app.route('/')
def index():
    return render_template('index.html')


@app.route('/check', methods=['POST'])
def check():
    check_sql = f''' SELECT `checked` FROM `messages` WHERE `message_id` = '1' '''
    print(execute_read_query(db_connect, check_sql))
    return 'ok'

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

html/js:

<!DOCTYPE html>
<html lang="en">
    <head>
        <meta charset="UTF-8">
        <meta http-equiv="X-UA-Compatible" content="IE=edge">
        <meta name="viewport" content="width=device-width, initial-scale=1.0">
        <title>Document</title>
    </head>
    <body>
        <h2>Index</h2>
        <input type="submit" id="btn">
        <script>
            let url = '/check';
            let btn = document.getElementById('btn');

            function f_check(){
                fetch(url, {method: 'POST'})
                .then(()=>{
                    console.log('fine');
                })
            }

            btn.addEventListener('click', f_check);
            btn.onclick = f_check;


        </script>
    </body>
</html>

Reply

Answer the question

In order to leave comments, you need to log in

1 answer(s)

A

Akina, 2021-12-14
@Akina

The problem is that with multiple simultaneous requests...

When executing multiple queries at the same time (in parallel), each query must be executed on its own connection.
MySQL basically does not know about parallel queries on the same connection - until the query completes and the result is returned, the connection simply does not interact with the input stream.
If you manage to execute several requests in one program connection, then, apparently, the framework organizes several connections on its own, or builds a queue of requests and executes them sequentially in one connection, but pretends to be in parallel. And if all this falls, then perhaps he does it crookedly. Or you do something crookedly - for example, do not clear the connection from the last data set, and the framework does not do this without an explicit command to be able to prepare for a new task, accept and execute the next request ...