flask-migrate not detecting existing tables

Question

I followed this tutorial to learn about migrating databases.

After running the "flask db init" command the migrations folder is created as expected. However when I run flask db migrate -m 'Initial_Migration' then it doesn't detect the tables in the already existing database. It therefore proposes to add all tables from models.py in the migration script. When running flask db upgrade this causes an error because it's trying to add tables that already exist.

This is (a part of) my models.py file:

# Third-party libraries
from sqlalchemy import Table, Column, Integer, String, Boolean, DateTime, ForeignKey, inspect, exc
from sqlalchemy.orm import relationship
from flask_login import UserMixin
from . import basic_functions as bf
import os
cfg = __import__(os.environ['CONFIG_NAME']) # Equal to "import config as cfg"
from . import db


#Set up logger
database_manager_logger = bf.create_logger('data_base_manager')

#Create all tables
class TopicSubscription(db.Model): #based upon: https://stackoverflow.com/questions/5756559/how-to-build-many-to-many-relations-using-sqlalchemy-a-good-example
    __tablename__="TopicSubscription" #We set our own name because otherwise a name is chosen according to SQLAlchemy conventions which can lead to errors (Table name is used for foreign keys)
    id = Column(Integer, primary_key=True)
    user_id = Column(Integer, ForeignKey('User.id')) #Note this is referring to the table name, not the class name!
    topic_id = Column(Integer, ForeignKey('Topic.id'))

class User(db.Model,UserMixin):
    # id = Column(CHAR(21), primary_key=True)
    __tablename__ = "User"
    id = Column(Integer, primary_key=True)
    name = Column(String(80), unique=True, nullable=False)
    email = Column(String(120), unique=True, nullable=False)
    nickname = Column(String(80), unique=True, nullable=True)
    profile_pic = Column(String(200), unique=True, nullable=True)
    last_send_date = Column(DateTime) #Last time a mail was sent to the user.

    topics = relationship('Topic', secondary=TopicSubscription.__table__,
                          back_populates="users")  # Allows to access topics that this user subscribed to through the junction table TopicSubscription
    subscribed_questions = relationship("QuestionSubscription", primaryjoin="User.id==QuestionSubscription.user_id")

class Topic(db.Model):
    __tablename__ = "Topic"
    id = Column(Integer, primary_key=True)
    topic = Column(String(80)) #The topic name
    active = Column(Boolean, default=False)

    users = relationship('User', secondary=TopicSubscription.__table__, back_populates = "topics") #This back_populates refers to the "topics" attribute on the User object.
    # subscriptions = relationship("TopicSubscription", cascade="all, delete-orphan") #Seems like this is not used, delete it?
    questions = relationship("Question", cascade="all, delete-orphan")

This is the init file in which I create the Migrate class of flask-migrate (look at initialize database function):

import os
from flask import Flask
from flask_sqlalchemy import SQLAlchemy
from flask_migrate import Migrate
from flask_login import LoginManager
import time

#######################
#### Configuration ####
#######################

# Initialize the database, login_manager and the app variables in the global scope so they are accessible everywhere.
db = SQLAlchemy()
from project.models import * #Define all tables, must be done at module level for some reason: https://stackoverflow.com/questions/44080824/import-module-inside-python-class
login_manager = LoginManager()
app = None #Initialize the global app variable

######################################
#### Application Factory Function ####
######################################

def create_app(config_filename=None):

    global app #Declare the app as global else it is not accessible for other modules.
    app = Flask(__name__, instance_relative_config=True)
    app.config.from_pyfile(config_filename)  # Load the config file
    app.secret_key = os.environ.get("SECRET_KEY") or os.urandom(24)

    initialize_database(app)
    register_blueprints(app)
    start_scheduler()

    return app


##########################
#### Helper Functions ####
##########################

def initialize_database(app):
    # Since the application instance is now created, pass it to each Flask
    # extension instance to bind it to the Flask application instance (app)
    db.init_app(app)

    with app.app_context():
        db.engine.dialect.supports_sane_rowcount = db.engine.dialect.supports_sane_multi_rowcount = False  # Otherwise there's an error when deleting topics. See https://stackoverflow.com/questions/28824401/sqlalchemy-attempting-to-twice-delete-many-to-many-secondary-relationship
        retry_database_connect(nr_of_retries=50)
        migrate = Migrate(app, db)  # This makes sure that any database changes are migrated.



def retry_database_connect(nr_of_retries:int):
    """Connects to the database, catching errors and re-trying for 25 seconds."""
    connection_error = True
    counter = 0
    while (connection_error == True and counter < nr_of_retries):
        try:
            # This has to be run before the creation of all tables, otherwise the newly created tables are deleted and there are zero tables to work with.
            # It knows which tables to drop because in the drop_all() command the connection to the sql database is made automatically (based on info in the db object)
            if cfg.drop_all_tables:
                print("Dropping all tables")
                db.drop_all()
            db.create_all()  # Must be called after all classes are defined (otherwise it doesn't create the tables.
            connection_error = False  # set connection error to false so we can continue
        except exc.SQLAlchemyError:
            counter += 1
            print("Connection failed " + str(counter) + " time(s).")
            time.sleep(5)
    if connection_error:
        raise exc.SQLAlchemyError  # connection failed, tried for 250 seconds.

def register_blueprints(app):
    # Since the application instance is now created, register each Blueprint
    # with the Flask application instance (app)
    from project.home import home_blueprint
    from project.login import login_blueprint
    from project.topic import topic_blueprint
    from project.question import question_blueprint
    from project.answer import answer_blueprint
    app.register_blueprint(home_blueprint)
    app.register_blueprint(login_blueprint)
    app.register_blueprint(topic_blueprint)
    app.register_blueprint(question_blueprint)
    app.register_blueprint(answer_blueprint)

def start_scheduler():
    import project.scheduler #This will trigger the scheduler.__init__ file which will start the scheduler

This is (part of) the terminal output:

INFO  [alembic.runtime.migration] Context impl MySQLImpl.
INFO  [alembic.runtime.migration] Will assume non-transactional DDL.
INFO  [alembic.autogenerate.compare] Detected added table 'Topic'
INFO  [alembic.autogenerate.compare] Detected added table 'User'
INFO  [alembic.autogenerate.compare] Detected added table 'Question'
INFO  [alembic.autogenerate.compare] Detected added table 'TopicSubscription'
INFO  [alembic.autogenerate.compare] Detected added table 'Evaluation'
INFO  [alembic.autogenerate.compare] Detected added table 'QuestionSubscription'
INFO  [alembic.autogenerate.compare] Detected removed table 'question'
INFO  [alembic.autogenerate.compare] Detected removed table 'topicsubscription'
INFO  [alembic.autogenerate.compare] Detected removed table 'evaluation'
INFO  [alembic.autogenerate.compare] Detected removed index 'email' on 'user'
INFO  [alembic.autogenerate.compare] Detected removed index 'name' on 'user'
INFO  [alembic.autogenerate.compare] Detected removed index 'nickname' on 'user'
INFO  [alembic.autogenerate.compare] Detected removed index 'profile_pic' on 'user'
INFO  [alembic.autogenerate.compare] Detected removed table 'user'
INFO  [alembic.autogenerate.compare] Detected removed table 'topic'
INFO  [alembic.autogenerate.compare] Detected removed table 'questionsubscription'

I tried the answer of this question: Alembic - sqlalchemy does not detect existing tables but for me it didn't work so I reverted env.py back to its default form.

Any ideas what I might be doing wrong?

PS: I'm not really sure where the problem arises, so that's why I only shared these scripts. Let me know if I need to share something else.

Answer 1

To solve this issue: change your table names to all lowercase letters.

Like this:

class TopicSubscription(db.Model): #based upon: https://stackoverflow.com/questions/5756559/how-to-build-many-to-many-relations-using-sqlalchemy-a-good-example
    __tablename__="topic_subscription" #We set our own name because otherwise a name is chosen according to SQLAlchemy conventions which can lead to errors (Table name is used for foreign keys)
    id = Column(Integer, primary_key=True)
    user_id = Column(Integer, ForeignKey('user.id')) #Note this is referring to the table name, not the class name!
    topic_id = Column(Integer, ForeignKey('topic.id'))

Why:

• Windows (or MySQL not entirely sure) does not recognize uppercase letters and automatically converts it to lower case.
• Alembic then compares the database (which windows/mysql gave lowercase letters) with the database names in your models.py file where it finds uppercase
• As Alembic doesn't recognize table name changes it thinks that in the existing database there are some old tables with lowercase letters that don't exist anymore and thus it tries to drop it. At the same time it sees new upercase tables in your models.py that it cannot find in your existing database so it tries to create this.
• When creating the tables it causes an error as windows/mysql converts the uppercase tables that Alembic tries to create back to lowercase tables that already exist.