Python Flask Migrate is not ignoring existing tables in database outside my models

Issue

Following this link: https://alembic.sqlalchemy.org/en/latest/cookbook.html#don-t-generate-any-drop-table-directives-with-autogenerate I wanted flask db migrate to ignore existing tables outside of the context of my application, i thought all i needed to do was add the function from the link and set it in the context.

This is my env.py file, but flask migrate is still erroring because my "testing" table exists in my database, but not in my models:

from __future__ import with_statement

import logging
from logging.config import fileConfig

from flask import current_app

from alembic import context


# this is the Alembic Config object, which provides
# access to the values within the .ini file in use.
config = context.config

# Interpret the config file for Python logging.
# This line sets up loggers basically.
fileConfig(config.config_file_name)
logger = logging.getLogger('alembic.env')

# add your model's MetaData object here
# for 'autogenerate' support
# from myapp import mymodel
# target_metadata = mymodel.Base.metadata
config.set_main_option(
    'sqlalchemy.url',
    str(current_app.extensions['migrate'].db.get_engine().url).replace(
        '%', '%%'))
target_metadata = current_app.extensions['migrate'].db.metadata

# other values from the config, defined by the needs of env.py,
# can be acquired:
# my_important_option = config.get_main_option("my_important_option")
# ... etc.




def run_migrations_offline():
    """Run migrations in 'offline' mode.

    This configures the context with just a URL
    and not an Engine, though an Engine is acceptable
    here as well.  By skipping the Engine creation
    we don't even need a DBAPI to be available.

    Calls to context.execute() here emit the given string to the
    script output.

    """
    def include_object(object, name, type_, reflected, compare_to):
        if type_ == "table" and reflected and compare_to is None:
            return False
        else:
            return True

    url = config.get_main_option("sqlalchemy.url")
    context.configure(
        url=url, 
        include_object=include_object,
        target_metadata=target_metadata, 
        literal_binds=True
    )

    with context.begin_transaction():
        context.run_migrations()


def run_migrations_online():
    """Run migrations in 'online' mode.

    In this scenario we need to create an Engine
    and associate a connection with the context.

    """

    # this callback is used to prevent an auto-migration from being generated
    # when there are no changes to the schema
    # reference: http://alembic.zzzcomputing.com/en/latest/cookbook.html
    def process_revision_directives(context, revision, directives):
        if getattr(config.cmd_opts, 'autogenerate', False):
            script = directives[0]
            if script.upgrade_ops.is_empty():
                directives[:] = []
                logger.info('No changes in schema detected.')
    
    def include_object(object, name, type_, reflected, compare_to):
        if type_ == "table" and reflected and compare_to is None:
            return False
        else:
            return True

    connectable = current_app.extensions['migrate'].db.get_engine()

    with connectable.connect() as connection:
        context.configure(
            include_object=include_object,
            connection=connection,
            target_metadata=target_metadata,
            process_revision_directives=process_revision_directives,
            **current_app.extensions['migrate'].configure_args
        )

        with context.begin_transaction():
            context.run_migrations()



if context.is_offline_mode():
    run_migrations_offline()
else:
    run_migrations_online()

And this is the script that runs the migration:

flask db init || true
flask db migrate -m "db migration."
flask db upgrade

And this is the error:

raise exc.NoSuchTableError(table.name)
sqlalchemy.exc.NoSuchTableError: testing

Solution

For anyone else who hits this issue, I solved it by modifying sqlalchemy since that is what was throwing the error. The alembic docs are correct, but alembic fails when sqlalchemy throws the table not found error.

These are my versions of my libraries:

Package          Version
---------------- -------
alembic          1.6.5  
click            8.0.1  
Flask            1.1.2  
Flask-Migrate    3.0.1  
Flask-Script     2.0.6  
Flask-SQLAlchemy 2.4.1  
greenlet         1.1.0  
itsdangerous     2.0.1  
Jinja2           3.0.1  
Mako             1.1.4  
MarkupSafe       2.0.1  
pip              19.2.3 
psycopg2         2.9.1  
python-dateutil  2.8.1  
python-editor    1.0.4  
setuptools       41.2.0 
six              1.16.0 
SQLAlchemy       1.4.18 
SQLAlchemy-Utils 0.37.7 
Werkzeug         2.0.1 

If you open the reflection.py file (location will vary mine was in lib/python3.8/site-packages/sqlalchemy/engine/reflection.py) and scroll down and comment out line 787 and 788 you can ignore the error:

# Comment these out to perform the migration
# if not found_table:
#     raise exc.NoSuchTableError(table.name)

For our application’s startup, we simply comment out these lines, run the migration, and then uncomment them with bash, not the cleanest but it works for us:

#!/bin/bash

# Hacky fix to not delete tables outside our application context (i.e tables that arent in our models)
# Comment out when in docker image
if [ ! -z ${MOD_SQLALCHEMY_CODE+x} ]; then 
    echo "MOD_SQLALCHEMY_CODE is set"
    sed -i '/^        if not found_table:/ s/./#&/' /opt/app-root/lib/python3.8/site-packages/sqlalchemy/engine/reflection.py
    sed -i '/^            raise exc.NoSuchTableError(table.name)/ s/./#&/' /opt/app-root/lib/python3.8/site-packages/sqlalchemy/engine/changed-reflection.py
else 
    echo "MOD_SQLALCHEMY_CODE is not set, running with dev configuration."
fi

# Comment out for local dev TODO add in working sed for mac users


# Check to see if we need to do a migration on our database:
echo "Running database migrations."
flask db init || true
chmod 777 /root/jira-app/env.py
cp /root/jira-app/env.py /root/jira-app/migrations/env.py
# incase db needs alembic table < flask db stamp head >
flask db migrate -m "db migration."
flask db upgrade
echo "Database migrations complete."

# undo hacky fix after migrations are completed
if [ ! -z ${MOD_SQLALCHEMY_CODE+x} ]; then 
    sed -i 's/#        if not found_table:/        if not found_table:/g' /opt/app-root/lib/python3.8/site-packages/sqlalchemy/engine/reflection.py
    sed -i 's/#            raise exc.NoSuchTableError(table.name)/            raise exc.NoSuchTableError(table.name)/g' /opt/app-root/lib/python3.8/site-packages/sqlalchemy/engine/reflection.py
    echo "Undoing sqlalchemy code modifications."
fi

My env.py file that works:

from __future__ import with_statement

import logging
from logging.config import fileConfig

from flask import current_app

from alembic import context

# this is the Alembic Config object, which provides
# access to the values within the .ini file in use.
config = context.config

# Interpret the config file for Python logging.
# This line sets up loggers basically.
fileConfig(config.config_file_name)
logger = logging.getLogger('alembic.env')

# add your model's MetaData object here
# for 'autogenerate' support
# from myapp import mymodel
# target_metadata = mymodel.Base.metadata
config.set_main_option(
    'sqlalchemy.url',
    str(current_app.extensions['migrate'].db.get_engine().url).replace(
        '%', '%%'))
target_metadata = current_app.extensions['migrate'].db.metadata

# other values from the config, defined by the needs of env.py,
# can be acquired:
# my_important_option = config.get_main_option("my_important_option")
# ... etc.


def run_migrations_offline():
    """Run migrations in 'offline' mode.

    This configures the context with just a URL
    and not an Engine, though an Engine is acceptable
    here as well.  By skipping the Engine creation
    we don't even need a DBAPI to be available.

    Calls to context.execute() here emit the given string to the
    script output.

    """
    url = config.get_main_option("sqlalchemy.url")
    context.configure(
        url=url, target_metadata=target_metadata, literal_binds=True
    )

    with context.begin_transaction():
        logging.inf(context)
        context.run_migrations()


def run_migrations_online():
    """Run migrations in 'online' mode.

    In this scenario we need to create an Engine
    and associate a connection with the context.

    """

    # New Function
    def include_object(object, name, type_, reflected, compare_to):
        if type_ == "table" and reflected and compare_to is None:
            return False
        else:
            return True


    # this callback is used to prevent an auto-migration from being generated
    # when there are no changes to the schema
    # reference: http://alembic.zzzcomputing.com/en/latest/cookbook.html
    def process_revision_directives(context, revision, directives):
        if getattr(config.cmd_opts, 'autogenerate', False):
            script = directives[0]
            if script.upgrade_ops.is_empty():
                directives[:] = []
                logger.info('No changes in schema detected.')

    connectable = current_app.extensions['migrate'].db.get_engine()

    with connectable.connect() as connection:
        context.configure(
            include_object = include_object,
            connection=connection,
            target_metadata=target_metadata,
            process_revision_directives=process_revision_directives,
            **current_app.extensions['migrate'].configure_args
        )

        with context.begin_transaction():
            context.run_migrations()


if context.is_offline_mode():
    run_migrations_offline()
else:
    run_migrations_online()

Answered By – BLang

This Answer collected from stackoverflow, is licensed under cc by-sa 2.5 , cc by-sa 3.0 and cc by-sa 4.0

Leave a Reply

(*) Required, Your email will not be published