flask-sqlalchemy timeouterror by queuepool overflow using backref option-CodePudding

i'm beginner with flask-sqlalchemy. i encountered a problem maybe caused by using backref.

the api(view api. it renders template) is like this.

@board.route('/')
def index():
page = request.args.get('page', 1, type=int)
q = Article.query.order_by(Article.create_date.desc())
article_list_pagination = q.paginate(page, per_page=10)
return render_template('board.html', article_list=article_list_pagination)

and the model is like this

class Answer(db.Model):
__tablename__ = 'answer'
id = db.Column(db.Integer, primary_key=True)
article_id = db.Column(db.Integer, db.ForeignKey('article.id', ondelete='CASCADE'))
article = db.relationship('Article', backref=db.backref('answer_set'))
user_id = db.Column(db.String(255), nullable=False)
name = db.Column(db.String(255), nullable=False)
content = db.Column(db.Text(), nullable=False)
create_date = db.Column(db.DateTime(), nullable=False)
update_date = db.Column(db.DateTime(), nullable=True)

when i request the api every 20 times, i get this error

sqlalchemy.exc.TimeoutError: QueuePool limit of size 10 overflow 10 reached, connection timed out, timeout 30.00 (Background on this error at: https://sqlalche.me/e/14/3o7r)

here is what i tried to solve the error.

give lazy options to db.relationship. but doesn't work
db.session.close(). but it makes another error

sqlalchemy.orm.exc.DetachedInstanceError: Parent instance <Article at 0x1772756baf0> is not bound to a Session; lazy load operation of attribute 'answer_set' cannot proceed

debug=False. it doesn't work, too.

how can i get over this problem?

when I remove article = db.relationship('Article', backref=db.backref('answer_set')) from the model and add db.session.close() before return template, it doesn't make error, but i need it.

what should i do?

answers for comment @branco

i'm just using flask-sqlalchemy paginate method. https://flask-sqlalchemy.palletsprojects.com/en/2.x/api/

and i'm running this app at app.py

from flask import Flask
from flask_migrate import Migrate
from flask_sqlalchemy import SQLAlchemy

from flask_jwt_extended import JWTManager

import config

db = SQLAlchemy()
migrate = Migrate()

def create_app():
    app = Flask(__name__)
    app.secret_key = 'secret_key_for_flash'

    app.config.from_object(config)
    db.init_app(app)
    migrate.init_app(app, db)
    from models.user import User
    from models.article import Article
    from models.answer import Answer

    from blueprints.main import main
    from blueprints.login import login
    from blueprints.register import register
    from blueprints.board.board import board
    from blueprints.board.article import article
    from blueprints.board.answer import answer

    app.register_blueprint(main)
    app.register_blueprint(login)
    app.register_blueprint(register)
    app.register_blueprint(board)
    app.register_blueprint(article)
    app.register_blueprint(answer)

    app.config['JWT_SECRET_KEY'] = 'jwt_secret_key'
    app.config['JWT_ACCESS_TOKEN_EXPIRES'] = config.expires_access
    app.config['JWT_REFRESH_TOKEN_EXPIRES'] =     config.expires_refresh
    app.config['JWT_TOKEN_LOCATION'] = ['cookies']
    JWTManager(app)

    return app


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

CodePudding user response：

finally, i solved this problem. i just gave lazy options to db.relationship. but didn't work. i had to give lazy option to db.backref. like

article = db.relationship('Article', backref=db.backref('answer_set', lazy='joined'))

and added

db.session.close()

after i called paginate method