Exceeding the queue pool limit in a Flask application problem

[Erabil Erabil]

When using SQL Alchemy in a Flask application, if the application continuously sends queries to the database, it exceeds the maximum number of connections in the connection pool. How can I solve this issue?

sqlalchemy_engine.py

import os

from sqlalchemy import create_engine

from sqlalchemy.orm import sessionmaker

from sqlalchemy.pool import QueuePool

class OrmEngine:

    def __init__(self):

        self.engine = create_engine(os.getenv('DATABASE_URL'),

                                    poolclass=QueuePool,

                                    pool_size=5,

                                    max_overflow=3

                                    )

        self.session = sessionmaker(bind=self.engine)

Example query

with self.orm_engine.session() as session:

    user_repository = UserRepository(session)

    user = user_repository.find_by_email(login_request.email) 

My connection list

MariaDB [(none)]> SHOW PROCESSLIST;

+-----+-------------+-----------------+------+---------+------+--------------------------+------------------+----------+

| Id  | User        | Host            | db   | Command | Time | State                    | Info             | Progress |

+-----+-------------+-----------------+------+---------+------+--------------------------+------------------+----------+

|   2 | system user |                 | NULL | Daemon  | NULL | InnoDB purge worker      | NULL             |    0.000 |

|   1 | system user |                 | NULL | Daemon  | NULL | InnoDB purge coordinator | NULL             |    0.000 |

|   3 | system user |                 | NULL | Daemon  | NULL | InnoDB purge worker      | NULL             |    0.000 |

|   4 | system user |                 | NULL | Daemon  | NULL | InnoDB purge worker      | NULL             |    0.000 |

|   5 | system user |                 | NULL | Daemon  | NULL | InnoDB shutdown handler  | NULL             |    0.000 |

|   8 | root        | localhost:56603 | NULL | Query   |    0 | Init                     | SHOW PROCESSLIST |    0.000 |

| 645 | root        | localhost:64281 | tbot | Sleep   |   74 |                          | NULL             |    0.000 |

| 662 | root        | localhost:64334 | tbot | Sleep   |   56 |                          | NULL             |    0.000 |

| 684 | root        | localhost:64400 | tbot | Sleep   |   33 |                          | NULL             |    0.000 |

| 705 | root        | localhost:64476 | tbot | Sleep   |   16 |                          | NULL             |    0.000 |

| 707 | root        | localhost:64482 | tbot | Sleep   |   14 |                          | NULL             |    0.000 |

| 709 | root        | localhost:64488 | tbot | Sleep   |   13 |                          | NULL             |    0.000 |

| 711 | root        | localhost:64494 | tbot | Sleep   |   12 |                          | NULL             |    0.000 |

| 713 | root        | localhost:64500 | tbot | Sleep   |   10 |                          | NULL             |    0.000 |

| 715 | root        | localhost:64506 | tbot | Sleep   |    9 |                          | NULL             |    0.000 |

| 717 | root        | localhost:64512 | tbot | Sleep   |    8 |                          | NULL             |    0.000 |

| 719 | root        | localhost:64518 | tbot | Sleep   |    7 |                          | NULL             |    0.000 |

| 720 | root        | localhost:64521 | tbot | Sleep   |    4 |                          | NULL             |    0.000 |

| 722 | root        | localhost:64527 | tbot | Sleep   |    3 |                          | NULL             |    0.000 |

| 724 | root        | localhost:64533 | tbot | Sleep   |    2 |                          | NULL             |    0.000 |

| 726 | root        | localhost:64539 | tbot | Sleep   |    1 |                          | NULL             |    0.000 |

+-----+-------------+-----------------+------+---------+------+--------------------------+------------------+----------+

21 rows in set (0.000 sec)

[Nishant Varma]

See if this helps:

https://docs.sqlalchemy.org/en/20/orm/session_basics.html#session-faq-whentocreate

and

https://docs.sqlalchemy.org/en/20/orm/contextual.html

Session should be closed correctly etc.

--
SQLAlchemy -
The Python SQL Toolkit and Object Relational Mapper
 
http://www.sqlalchemy.org/
 
To post example code, please provide an MCVE: Minimal, Complete, and Verifiable Example. See http://stackoverflow.com/help/mcve for a full description.
---
You received this message because you are subscribed to the Google Groups "sqlalchemy" group.
To unsubscribe from this group and stop receiving emails from it, send an email to sqlalchemy+...@googlegroups.com.
To view this discussion on the web visit https://groups.google.com/d/msgid/sqlalchemy/9985cbfa-f4ff-49ed-b777-356cf42c65ebn%40googlegroups.com.

[Erabil Erabil]

Wouldn't the 'with' block already automatically close the session if I'm already using it to establish the session?

29 Nisan 2023 Cumartesi tarihinde saat 05:50:10 UTC+3 itibarıyla Nishant Varma şunları yazdı:

[Mike Bayer]

are you making sure you create only one global OrmEngine object for the whole application? 

SQLA docs recommend engines are global