Re: [sqlalchemy] Exceeding the queue pool limit in a Flask application problem
are you making sure you create only one global OrmEngine object for the whole
application?
SQLA docs recommend engines are global
On Fri, Apr 28, 2023, at 12:48 PM, Erabil Erabil wrote:
> 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)
>
>
> --
> 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+unsubscr...@googlegroups.com.
> To view this discussion on the web visit
>
Re: [sqlalchemy] Exceeding the queue pool limit in a Flask application problem
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ı:
> 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.
>
> On Fri, Apr 28, 2023, 10:18 PM Erabil Erabil wrote:
>
>> 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)
>>
>> --
>> 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
