2

I am building a Flask web app, and at some point accidentally deleted a migration file for my SQLite database. I am using SQLAlchemy in Flask for database commands, and flask-alembic for migrations. I am trying to delete the "alembic_version" file that contains the reference to the missing migration file, but am having trouble doing so.

I deleted the migration file a while ago, but didn't notice until trying to run a migration on my database recently with:

$ flask db migrate

and I received the following error message:

INFO  [alembic.runtime.migration] Context impl SQLiteImpl.
INFO  [alembic.runtime.migration] Will assume non-transactional DDL.
ERROR [root] Error: Can't locate revision identified by '44ab999461f7'

Okay, so I believe that means I deleted the migration file, except the alembic_version table that contains the current migration version thinks it still exists and is trying to reference it. My thought is to delete the entry in alembic_version, then I will re-initialize the migrations folder and migrate with:

$ flask db init
$ flask db migrate

Attempt 1

So I need to directly access my SQLite database, and remove the table. First I tried using this code to remove all tables:

#drop alembic_version table
if db.engine.dialect.has_table(db.engine, 'alembic_version'):
    version = db.Table('alembic_version', db.metadata,autoload_with=db.engine)
    version.drop()

found in this thread.

When running this function I get the error:

sqlalchemy.exc.UnboundExecutionError: Table object 'alembic_version' is not bound to an Engine or Connection.  Execution can not proceed without a database to execute against.

Okay, so there is an issue with the engine configuration, or something. My engine creation is in my __init__.py file, and uses memory:

engine = create_engine('sqlite://')

I am not well versed in engine configuration, and at this point I'm stuck. I don't know if I need to do this outside of my context processor? Maybe it doesn't pull in the engine info? Maybe I need to bind the engine?

Attempt 2

After looking through information about SQLAlchemy engine connection, found here and here, I tried directly dropping the table using my flask shell context processor:

@app.shell_context_processor
def make_shell_context():
        return {'db':db,'User':User}
from sqlalchemy import create_engine
engine=create_engine('sqlite://')
engine
Engine(sqlite://)
engine.execute('DROP TABLE IF EXISTS alembic_version')
<sqlalchemy.engine.result.ResultProxy object at 0x0000028EFEA9A588>
exit()

But it didn't work, because I can query another table in the database and it returns a full table. Just to be sure though, I tried to migrate again:

$ flask db migrate

results in the same error as above:

INFO  [alembic.runtime.migration] Context impl SQLiteImpl.
INFO  [alembic.runtime.migration] Will assume non-transactional DDL.
ERROR [root] Error: Can't locate revision identified by '44ab999461f7'

So not tables were actually dropped. The same questions arise from attempt 1, do I need to do this outside of my context processor? Do I need to do more engine configuration?

Any help or direction is greatly appreciated.

Drew Petersen
  • 25
  • 1
  • 4

3 Answers3

4

To open a sqlite database directly, you can use sqlitebrowser https://sqlitebrowser.org/

gittert
  • 1,238
  • 2
  • 7
  • 15
  • Wow that is an amazing resource! It worked perfectly: I downloaded DB Browser, opened the database, then deleted the "alembic_version" table. A new initialization and migration worked! Thanks! – Drew Petersen Jul 23 '19 at 20:18
  • Wow. Can't believe this resource has been here all along. Will never mess with manual SQLite commands again. Donating to the project! Thank you for passing this along. – dannypernik Aug 20 '21 at 16:22
0

I had the same problem. Deleting the migration folder wasn't enough. As it turns out i wasn't able to see my app.db file from my sublime editors side bar. Check your file system and delete the necessary file and then re-initialize your database.

Nim.th
  • 11
  • 2
0

Below are the steps I took to delete the alembic table from my sqlite db. I did this because I was getting the error (ERROR [root] Error: Can't locate revision identified by '6e09161ceced') . After following the steps below I ran the command (python manage.py db migrate --message "msg commit") without issues.

>>> from sqlalchemy import *
>>> from sqlalchemy.ext.declarative import declarative_base
>>> import os
>>> from app.fifdb.config import basedir
>>> url = 'sqlite:///' + os.path.join(basedir, 'flask_boilerplate_main.db')
>>> engine = create_engine(url)
>>> engine.table_names()
['alembic_version']
>>> metadata = MetaData(engine, reflect=True)                                                                         
>>> table = metadata.tables.get('alembic_version')
>>> base = declarative_base()
>>> base.metadata.drop_all(engine, [table], checkfirst=True)
>>> engine.has_table('alembic_version')
False
Andreina
  • 63
  • 7