sqlalchemyflask-sqlalchemy

Why doesn't Flask-SQLAlchemy raise an exception when "DROP TABLE IF EXISTS" finds no table?

I’m using Flask-SQLAlchemy (version 2.5.1) with Python 3.11 to connect to a PostgreSQL 15 database.

I want to drop a table if it exists using DROP TABLE IF EXISTS ... CASCADE. Here's my code:

from sqlalchemy import text
from sqlalchemy.exc import SQLAlchemyError

try:
    sql = text("DROP TABLE IF EXISTS {table_name} CASCADE")
    db.session.execute(sql)
    db.session.commit()

except Exception as e:
    logger.error(f"Failed to drop {table_name} table: {str(e)}")

except SQLAlchemyError as e:
    db.session.rollback()
    logger.error(f"An error occurred: {str(e)}")

However, when the table does not exist, I don’t see any exception raised in Python, even though the PostgreSQL logs show the following:

LOG:  statement: DROP TABLE IF EXISTS devices CASCADE
NOTICE:  table "devices" does not exist, skipping
LOG:  statement: COMMIT

Since the table doesn’t exist, PostgreSQL logs a NOTICE, but no exception is propagated in my Python code.

My questions are:

  1. Should I expect an exception to be raised when the table doesn’t exist, passing "raw" SQL statement?
  2. Is there a way to bind the database's status messages to my Python code, so I can handle such cases more explicitly?
  3. Should I manually check for the error code at database after execute() and commit() commands?
Solution

    1. Should I expect an exception to be raised when the table doesn’t exist, passing "raw" SQL statement?

      • No: SQLAlchemy will on raise an exception if PostgreSQL does, and since IF EXISTS has been specified PostgreSQL will not error if the table does not exist.

    2. Is there a way to bind the database's status messages to my Python code, so I can handle such cases more explicitly?

      • You can log NOTICE messages if you want to, but you cannot access them directly without introspecting the logs (or at least, there is no documented way to do this). See the documentation for NOTICE logging.

    3. Should I manually check for the error code at database after execute() and commit() commands?

      • There is no reason to do this unless for some reason you need to know that the database has skipped dropping a non-existent table.

    Here's a simple example:

    import logging

import sqlalchemy as sa

logging.basicConfig()
logging.getLogger('sqlalchemy')
logging.getLogger('sqlalchemy.dialects.postgresql').setLevel(logging.INFO)

engine = sa.create_engine('postgresql+psycopg2:///so')

with engine.begin() as conn:
    conn.execute(sa.text("""DROP TABLE IF EXISTS t"""))

    Which would produce this output:

    INFO:sqlalchemy.engine.Engine:select pg_catalog.version()
INFO:sqlalchemy.engine.Engine:[raw sql] {}
INFO:sqlalchemy.engine.Engine:select current_schema()
INFO:sqlalchemy.engine.Engine:[raw sql] {}
INFO:sqlalchemy.engine.Engine:show standard_conforming_strings
INFO:sqlalchemy.engine.Engine:[raw sql] {}
INFO:sqlalchemy.engine.Engine:BEGIN (implicit)
INFO:sqlalchemy.engine.Engine:DROP TABLE IF EXISTS t
INFO:sqlalchemy.engine.Engine:[generated in 0.00009s] {}
INFO:sqlalchemy.dialects.postgresql:NOTICE:  table "t" does not exist, skipping
INFO:sqlalchemy.engine.Engine:COMMIT