Retrying initial connection on connection failure

[ben467834]

I'd like to be able to retry connecting after several seconds when I init_app to my database in case it's not accepting connections.

I've another project I'm using pure sqlalchemy and in a with Session(engine): I might catch:

            except exc.OperationalError as ex:
                print("ex", ex)
                time.sleep(5)

I've looked through the documentation and can't find a way to do this through the package.

Could someone point me in the right direction please?

[davidism]

Why can't you use the except that you've shown?

[ben467834]

Ok, looking at the example, is it possible to catch it in there somewhere? https://github.com/pallets-eco/flask-sqlalchemy?tab=readme-ov-file#a-simple-example

Or do you have a better idea to handle a retry?

[davidism]

You would put the try except around the line that causes the error. You haven't shown that, so it's not possible to say more specifically.

[ben467834]

OK here's an example:

python3 -m venv .venv
source .venv/bin/activate
pip install flask flask_sqlalchemy sqlalchemy psycopg2-binary

from flask import Flask
from flask_sqlalchemy import SQLAlchemy
from sqlalchemy.orm import DeclarativeBase, Mapped, mapped_column

app = Flask(__name__)
app.config["SQLALCHEMY_DATABASE_URI"] = "postgresql://localhost:9999"

class Base(DeclarativeBase):
  pass

db = SQLAlchemy(app, model_class=Base)

class User(db.Model):
    id: Mapped[int] = mapped_column(primary_key=True)
    username: Mapped[str] = mapped_column(unique=True)

with app.app_context():
    db.create_all()

    db.session.add(User(username="example"))
    db.session.commit()

    users = db.session.scalars(db.select(User))

python3 app.py

Traceback (most recent call last):
  File "/home/ben/python/testing/.venv/lib64/python3.13/site-packages/sqlalchemy/engine/base.py", line 143, in __init__
    self._dbapi_connection = engine.raw_connection()
                             ~~~~~~~~~~~~~~~~~~~~~^^
  File "/home/ben/python/testing/.venv/lib64/python3.13/site-packages/sqlalchemy/engine/base.py", line 3309, in raw_connection
    return self.pool.connect()
           ~~~~~~~~~~~~~~~~~^^
  File "/home/ben/python/testing/.venv/lib64/python3.13/site-packages/sqlalchemy/pool/base.py", line 447, in connect
    return _ConnectionFairy._checkout(self)
           ~~~~~~~~~~~~~~~~~~~~~~~~~~^^^^^^
  File "/home/ben/python/testing/.venv/lib64/python3.13/site-packages/sqlalchemy/pool/base.py", line 1264, in _checkout
    fairy = _ConnectionRecord.checkout(pool)
  File "/home/ben/python/testing/.venv/lib64/python3.13/site-packages/sqlalchemy/pool/base.py", line 711, in checkout
    rec = pool._do_get()
  File "/home/ben/python/testing/.venv/lib64/python3.13/site-packages/sqlalchemy/pool/impl.py", line 177, in _do_get
    with util.safe_reraise():
         ~~~~~~~~~~~~~~~~~^^
  File "/home/ben/python/testing/.venv/lib64/python3.13/site-packages/sqlalchemy/util/langhelpers.py", line 224, in __exit__
    raise exc_value.with_traceback(exc_tb)
  File "/home/ben/python/testing/.venv/lib64/python3.13/site-packages/sqlalchemy/pool/impl.py", line 175, in _do_get
    return self._create_connection()
           ~~~~~~~~~~~~~~~~~~~~~~~^^
  File "/home/ben/python/testing/.venv/lib64/python3.13/site-packages/sqlalchemy/pool/base.py", line 388, in _create_connection
    return _ConnectionRecord(self)
  File "/home/ben/python/testing/.venv/lib64/python3.13/site-packages/sqlalchemy/pool/base.py", line 673, in __init__
    self.__connect()
    ~~~~~~~~~~~~~~^^
  File "/home/ben/python/testing/.venv/lib64/python3.13/site-packages/sqlalchemy/pool/base.py", line 899, in __connect
    with util.safe_reraise():
         ~~~~~~~~~~~~~~~~~^^
  File "/home/ben/python/testing/.venv/lib64/python3.13/site-packages/sqlalchemy/util/langhelpers.py", line 224, in __exit__
    raise exc_value.with_traceback(exc_tb)
  File "/home/ben/python/testing/.venv/lib64/python3.13/site-packages/sqlalchemy/pool/base.py", line 895, in __connect
    self.dbapi_connection = connection = pool._invoke_creator(self)
                                         ~~~~~~~~~~~~~~~~~~~~^^^^^^
  File "/home/ben/python/testing/.venv/lib64/python3.13/site-packages/sqlalchemy/engine/create.py", line 661, in connect
    return dialect.connect(*cargs, **cparams)
           ~~~~~~~~~~~~~~~^^^^^^^^^^^^^^^^^^^
  File "/home/ben/python/testing/.venv/lib64/python3.13/site-packages/sqlalchemy/engine/default.py", line 630, in connect
    return self.loaded_dbapi.connect(*cargs, **cparams)  # type: ignore[no-any-return]  # NOQA: E501
           ~~~~~~~~~~~~~~~~~~~~~~~~~^^^^^^^^^^^^^^^^^^^
  File "/home/ben/python/testing/.venv/lib64/python3.13/site-packages/psycopg2/__init__.py", line 122, in connect
    conn = _connect(dsn, connection_factory=connection_factory, **kwasync)
psycopg2.OperationalError: connection to server at "localhost" (::1), port 9999 failed: Connection refused
        Is the server running on that host and accepting TCP/IP connections?
connection to server at "localhost" (127.0.0.1), port 9999 failed: Connection refused
        Is the server running on that host and accepting TCP/IP connections?


The above exception was the direct cause of the following exception:

Traceback (most recent call last):
  File "/home/ben/python/testing/app.py", line 18, in <module>
    db.create_all()
    ~~~~~~~~~~~~~^^
  File "/home/ben/python/testing/.venv/lib64/python3.13/site-packages/flask_sqlalchemy/extension.py", line 900, in create_all
    self._call_for_binds(bind_key, "create_all")
    ~~~~~~~~~~~~~~~~~~~~^^^^^^^^^^^^^^^^^^^^^^^^
  File "/home/ben/python/testing/.venv/lib64/python3.13/site-packages/flask_sqlalchemy/extension.py", line 881, in _call_for_binds
    getattr(metadata, op_name)(bind=engine)
    ~~~~~~~~~~~~~~~~~~~~~~~~~~^^^^^^^^^^^^^
  File "/home/ben/python/testing/.venv/lib64/python3.13/site-packages/sqlalchemy/sql/schema.py", line 5928, in create_all
    bind._run_ddl_visitor(
    ~~~~~~~~~~~~~~~~~~~~~^
        ddl.SchemaGenerator, self, checkfirst=checkfirst, tables=tables
        ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
    )
    ^
  File "/home/ben/python/testing/.venv/lib64/python3.13/site-packages/sqlalchemy/engine/base.py", line 3259, in _run_ddl_visitor
    with self.begin() as conn:
         ~~~~~~~~~~^^
  File "/usr/lib64/python3.13/contextlib.py", line 141, in __enter__
    return next(self.gen)
  File "/home/ben/python/testing/.venv/lib64/python3.13/site-packages/sqlalchemy/engine/base.py", line 3249, in begin
    with self.connect() as conn:
         ~~~~~~~~~~~~^^
  File "/home/ben/python/testing/.venv/lib64/python3.13/site-packages/sqlalchemy/engine/base.py", line 3285, in connect
    return self._connection_cls(self)
           ~~~~~~~~~~~~~~~~~~~~^^^^^^
  File "/home/ben/python/testing/.venv/lib64/python3.13/site-packages/sqlalchemy/engine/base.py", line 145, in __init__
    Connection._handle_dbapi_exception_noconnection(
    ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~^
        err, dialect, engine
        ^^^^^^^^^^^^^^^^^^^^
    )
    ^
  File "/home/ben/python/testing/.venv/lib64/python3.13/site-packages/sqlalchemy/engine/base.py", line 2448, in _handle_dbapi_exception_noconnection
    raise sqlalchemy_exception.with_traceback(exc_info[2]) from e
  File "/home/ben/python/testing/.venv/lib64/python3.13/site-packages/sqlalchemy/engine/base.py", line 143, in __init__
    self._dbapi_connection = engine.raw_connection()
                             ~~~~~~~~~~~~~~~~~~~~~^^
  File "/home/ben/python/testing/.venv/lib64/python3.13/site-packages/sqlalchemy/engine/base.py", line 3309, in raw_connection
    return self.pool.connect()
           ~~~~~~~~~~~~~~~~~^^
  File "/home/ben/python/testing/.venv/lib64/python3.13/site-packages/sqlalchemy/pool/base.py", line 447, in connect
    return _ConnectionFairy._checkout(self)
           ~~~~~~~~~~~~~~~~~~~~~~~~~~^^^^^^
  File "/home/ben/python/testing/.venv/lib64/python3.13/site-packages/sqlalchemy/pool/base.py", line 1264, in _checkout
    fairy = _ConnectionRecord.checkout(pool)
  File "/home/ben/python/testing/.venv/lib64/python3.13/site-packages/sqlalchemy/pool/base.py", line 711, in checkout
    rec = pool._do_get()
  File "/home/ben/python/testing/.venv/lib64/python3.13/site-packages/sqlalchemy/pool/impl.py", line 177, in _do_get
    with util.safe_reraise():
         ~~~~~~~~~~~~~~~~~^^
  File "/home/ben/python/testing/.venv/lib64/python3.13/site-packages/sqlalchemy/util/langhelpers.py", line 224, in __exit__
    raise exc_value.with_traceback(exc_tb)
  File "/home/ben/python/testing/.venv/lib64/python3.13/site-packages/sqlalchemy/pool/impl.py", line 175, in _do_get
    return self._create_connection()
           ~~~~~~~~~~~~~~~~~~~~~~~^^
  File "/home/ben/python/testing/.venv/lib64/python3.13/site-packages/sqlalchemy/pool/base.py", line 388, in _create_connection
    return _ConnectionRecord(self)
  File "/home/ben/python/testing/.venv/lib64/python3.13/site-packages/sqlalchemy/pool/base.py", line 673, in __init__
    self.__connect()
    ~~~~~~~~~~~~~~^^
  File "/home/ben/python/testing/.venv/lib64/python3.13/site-packages/sqlalchemy/pool/base.py", line 899, in __connect
    with util.safe_reraise():
         ~~~~~~~~~~~~~~~~~^^
  File "/home/ben/python/testing/.venv/lib64/python3.13/site-packages/sqlalchemy/util/langhelpers.py", line 224, in __exit__
    raise exc_value.with_traceback(exc_tb)
  File "/home/ben/python/testing/.venv/lib64/python3.13/site-packages/sqlalchemy/pool/base.py", line 895, in __connect
    self.dbapi_connection = connection = pool._invoke_creator(self)
                                         ~~~~~~~~~~~~~~~~~~~~^^^^^^
  File "/home/ben/python/testing/.venv/lib64/python3.13/site-packages/sqlalchemy/engine/create.py", line 661, in connect
    return dialect.connect(*cargs, **cparams)
           ~~~~~~~~~~~~~~~^^^^^^^^^^^^^^^^^^^
  File "/home/ben/python/testing/.venv/lib64/python3.13/site-packages/sqlalchemy/engine/default.py", line 630, in connect
    return self.loaded_dbapi.connect(*cargs, **cparams)  # type: ignore[no-any-return]  # NOQA: E501
           ~~~~~~~~~~~~~~~~~~~~~~~~~^^^^^^^^^^^^^^^^^^^
  File "/home/ben/python/testing/.venv/lib64/python3.13/site-packages/psycopg2/__init__.py", line 122, in connect
    conn = _connect(dsn, connection_factory=connection_factory, **kwasync)
sqlalchemy.exc.OperationalError: (psycopg2.OperationalError) connection to server at "localhost" (::1), port 9999 failed: Connection refused
        Is the server running on that host and accepting TCP/IP connections?
connection to server at "localhost" (127.0.0.1), port 9999 failed: Connection refused
        Is the server running on that host and accepting TCP/IP connections?

(Background on this error at: https://sqlalche.me/e/20/e3q8)

[davidism]

The above exception was the direct cause of the following exception:

Traceback (most recent call last):
  File "/home/ben/python/testing/app.py", line 18, in <module>
    db.create_all()
    ~~~~~~~~~~~~~^^

The traceback shows that it's happening around db.create_all(), so that's where you'd put the except block.

[ben467834]

The above exception was the direct cause of the following exception:

Traceback (most recent call last):
  File "/home/ben/python/testing/app.py", line 18, in <module>
    db.create_all()
    ~~~~~~~~~~~~~^^

The traceback shows that it's happening around db.create_all(), so that's where you'd put the except block.

Thanks David, so the first time we're interacting with the database. Out of curiosity, is there something in the extension where we can handle this directly rather than application code? I see you've created a flask-sqlalchemy-lite extension also which has fewer abstractions, is this extension more suited for such a usecase?

[davidism]

Honestly, this isn't something I'd expect an app to do. The database should be started before the app tries to connect to it. For larger applications, you'd separate out the step of creating the database/running migrations from application startup, to be done as a separate step during deployment. The short readme example is not meant to be a comprehensive robust deployment, it's meant to illustrate a small prototype and show off how using the extension looks.