How do I properly edit Alembic migration scripts in Flask-Migrate/Flask-SQLAlchemy to add or edit columns?

Question

I have a little app I'm using to scrape data from online articles.

Currently, I am trying to figure out how to edit the migration scripts from Flask-migrate so I don't have to delete all the migration data and the SQlite database then re-scrape the data every time I edit the schema!

Of course, when I edit the models, delete everything, re-init, and scrape the data again, the database adapts just fine. But when I try to edit it manually, it runs the upgrade but I see no change in the data.

Examples below...

Migration script:

def upgrade():
    # ### commands auto generated by Alembic - please adjust! ###
    op.add_column('article', sa.Column('example', sa.DateTime(), nullable=True))
    # ### end Alembic commands ###

I've also tried adding this to populate it with data, but of course it hasn't been successful since the column doesn't exist (correct me if I'm missing something):

from datetime import datetime

...
def upgrade():
...
     op.execute("UPDATE article SET example = datetime.utcnow()")  # (new scraped data would get a new
                                                                   # timestamp but this would be fine
                                                                   # for old data for my purposes)

I'm pretty new to SQL and its frameworks but I did both of these with and without indexing just in case that matters. The upgrade appears to run fine in either case:

(venv) Files\app> flask db upgrade
INFO  [alembic.runtime.migration] Context impl SQLiteImpl.
INFO  [alembic.runtime.migration] Will assume non-transactional DDL.
INFO  [alembic.runtime.migration] Running upgrade 2177b1c9ee45 -> 6c61158ea270, empty message
(venv) Files\app>

But when I query on the command line with Flask-SQLAlchemy:

>>> from app.models import Article
>>> arts = Article.query.all()
>>> arts[0]
<Example Article: "Title" (2018)>       ##(Valid __repr__ of the model)
>>> arts[0].time_added
datetime.datetime(2019, 12, 25, 9, 23, 43, 331296)    ##(achieved this by deleting and restarting db from scratch)
>>> arts[0].example
Traceback (most recent call last):
  File "<stdin>", line 1, in <module>
AttributeError: 'Article' object has no attribute 'example'
>>>

I don't know what is wrong with the upgrade, but something must be, because the downgrade gives me an error:

INFO  [alembic.runtime.migration] Running downgrade 6c61158ea270 -> 2177b1c9ee45, empty message
Traceback (most recent call last):
  File "c:\desktop\projects\site\app\venv\lib\site-packages\sqlalchemy\engine\base.py", line 1246, in _execute_context
    cursor, statement, parameters, context
...
  File "c:\desktop\projects\site\app\venv\lib\site-packages\sqlalchemy\engine\default.py", line 581, in do_execute
    cursor.execute(statement, parameters)
sqlalchemy.exc.OperationalError: (sqlite3.OperationalError) near "DROP": syntax error
[SQL: ALTER TABLE article DROP COLUMN example]

The downgrade function is pretty simple so the column must never have been created:

def downgrade():
    # ### commands auto generated by Alembic - please adjust! ###
    op.drop_column('article', 'example')
    # ### end Alembic commands ###

Can someone please help me understand how to use Alembic in Flask-migrate migration scripts to customize database upgrades?

Alternatively, is there something wrong with the way I'm using op.execute("UPDATE table SET column = values")?

Thanks!

Edit:

Forgot to mention, here is the line from the model:

    example = db.Column(db.DateTime, default=datetime.utcnow())

Also, changing the upgrade and downgrade manually wasn't effective:

def upgrade():
    # ### commands auto generated by Alembic - please adjust! ###
    # ### end Alembic commands ###
    op.execute('UPDATE article SET example = datetime.utcnow()')

def downgrade():
    # ### commands auto generated by Alembic - please adjust! ###
    op.execute('UPDATE article SET example=Null')
    # ### end Alembic commands ###

ADDITIONAL EDIT:

from app.models import set_time ### (set_time is just datetime.utcnow() but I
                                ### thought it might work to import it
                                ### from elsewhere -- it didn't

def upgrade():
    # ### commands auto generated by Alembic - please adjust! ###
    # ### end Alembic commands ###
    op.execute("UPDATE 'article' SET 'example' = VALUES (?)", (set_time))  #*

### (*Importing set_time from elsewhere 

def downgrade():
    # ### commands auto generated by Alembic - please adjust! ###
    op.execute("UPDATE 'article' SET 'example'=Null")
    # ### end Alembic commands ###

I keep getting this error:

TypeError: execution_options() argument after ** must be a mapping, not str

It appears there must be a format for passing a Python variable into an Alembic SQL query that I am unaware of. Searching now.

ANOTHER EDIT (Sorry for so many):

The following just tells me that article (the table name) is undefined. Help with syntax?

connection = op.get_bind()

def upgrade():
    # ### commands auto generated by Alembic - please adjust! ###
    # ### end Alembic commands ###
    connection.execute(
        article.update().values({"example": set_time})
    )

Any ideas are greatly appreciated!!

-Pete

Using just alembic and sqlalchemy (no flask stuff), you would add the column definition to the Python model definition, generate the upgrade script with alembic, and then run `alembic upgrade head`. Are you adding the column definition to the Python model? — snakecharmerb, Dec 25 '19 at 10:14
I am, but I have not heard of ```alembic upgrade head```. Do I enter this into the command line? — Peter Charland, Dec 25 '19 at 11:15
@snakecharberb OK, I see the issue--I can't actually run alembic commands in Flask-migrate. Flask migrate would upgrade through alembic when I run ```flask db migrate``` (creates the alembic migration script) and ```flask db upgrade``` (uses the script to update the database). (Which I did, so it should be working... if you see my edits above, I believe at this point I just need to figure out the syntax to use in alembic for passing a datetime value into the SQL.) — Peter Charland, Dec 25 '19 at 11:53

score 1 · Answer 1 · answered Dec 25 '19 at 10:30

1

if migrations is already settled that you updated with no problem but the column has not been created i would have personally just manually added it to the sqlite database so it will run just fine with no issues, these errors sometimes happens with sqlite.

answered Dec 25 '19 at 10:30

Hossam Ashraf

131
4

I see, so just by using straight SQL to add a column to the db file? – Peter Charland Dec 25 '19 at 10:49
That's a good idea for editing the data, but doesn't solve the Flask-migrate problem... – Peter Charland Dec 25 '19 at 10:51

How do I properly edit Alembic migration scripts in Flask-Migrate/Flask-SQLAlchemy to add or edit columns?

1 Answers1