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

Answer 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.