Too many connections in database

Deployment
1

Good afternoon

I have a problem with my website when I deploy it in Cloud.

This website uses streamlit user authentication to be able to register and log in.

The problem arises when I deploy it, when several users connect or the web is loaded several times, I get the error mysql.connector.errors.DatabaseError: 1040 (HY000): Too many connections.
and then everything stops working

Is there any way to avoid this error?

Here is the code for the database:

import mysql.connector

class Database:
    """
    A class for establishing a connection to a MySQL database and
    executing queries.
    """
    def __init__(self, host, port, user, password, database):
        """
        Constructor for the Database class.
        Parameters:
        host (str): The hostname or IP address of the MySQL server.
        port (int): The port number to use when connecting to the server.
        user (str): The username to use when connecting to the server.
        password (str): The password to use when connecting to the server.
        database (str): The name of the database to connect to.
        """
        self.host = host
        self.port = port
        self.user = user
        self.password = password
        self.database = database
        self.connection = None

    def connect(self):
        """
        Establishes a connection to the MySQL server.
        Returns:
        Connection: A connection object that can be used to execute queries.
        """
        self.connection = mysql.connector.connect(
            host=self.host,
            port=self.port,
            user=self.user,
            password=self.password,
            database=self.database
        )
        return self.connection

    def close(self):
        """
        Closes the connection to the MySQL server.
        """
        self.connection.close()

    def commit(self, query, params=None):
        """
        Executes a query and commits the changes to the database.
        If the query contains placeholders for parameters, the `params` argument
        should be provided to supply the values for the placeholders.
        Parameters:
        query (str): The query to execute.
        params (Optional[tuple]): A tuple containing the values for the placeholders in the query.
        """
        try:
            cursor = self.connection.cursor(buffered=True)
            cursor.execute(query, params)
        except:
            self.connect()
            cursor = self.connection.cursor(buffered=True)
            cursor.execute(query, params)
        self.connection.commit()
        cursor.close()

    def fetchall(self, query, params = None):
        """
        Executes a query and fetches all the results.
        If the query contains placeholders for parameters, the `params` argument
        should be provided to supply the values for the placeholders.
        Parameters:
        query (str): The query to execute.
        params (Optional[tuple]): A tuple containing the values for the placeholders in the query.
        Returns:
        list: A list of rows returned by the query.
        """
        try:
            cursor = self.connection.cursor(buffered=True)
            cursor.execute(query, params)
        except:
            self.connect()
            cursor = self.connection.cursor(buffered=True)
            cursor.execute(query, params)
        result = cursor.fetchall()
        cursor.close()
        return result

    def fetchone(self, query, params = None):
        """
        Executes a query and fetches a single row.
        If the query contains placeholders for parameters, the `params` argument
        should be provided to supply the values for the placeholders.
        Parameters:
        query (str): The query to execute.
        params (Optional[tuple]): A tuple containing the values for the placeholders in the query.
        Returns:
        list: A list the row returned by the query.
        """
        try:
            cursor = self.connection.cursor(buffered=True)
            cursor.execute(query, params)
        except:
            self.connect()
            cursor = self.connection.cursor(buffered=True)
            cursor.execute(query, params)
        result = cursor.fetchone()
        cursor.close()
        return result

    def get_columns(self, query, params = None):
        """
        Executes a SELECT query and returns a list of column names.
        If the query contains placeholders for parameters, the `params` argument
        should be provided to supply the values for the placeholders.
        Parameters:
        query (str): The SELECT query to execute.
        params (Optional[tuple]): A tuple containing the values for the placeholders in the query.
        Returns:
        list: A list of column names.
        """
        try:
            cursor = self.connection.cursor(buffered=True)
            cursor.execute(query, params)
        except:
            self.connect()
            cursor = self.connection.cursor(buffered=True)
            cursor.execute(query, params)
        column_names = [column[0] for column in cursor.description]
        cursor.close()
        return column_names
2

Several ways:

  • Use only one connection.
  • Usa a pool of connections.
  • Open and close a connection for each query execution.
  • Increase the maximum number of connections in your database.
  • Probably more that don’t come to my mind right now.
3

This topic was automatically closed 365 days after the last reply. New replies are no longer allowed.