1

My objective was to alter a column's data type from String to DateTime in Flask using Flask SQLAlchemy.

This is the model in question:

class Show(db.Model):
    id = db.Column(db.Integer, primary_key=True)
    venue_id = db.Column(db.Integer, db.ForeignKey('venues.id'))
    venue_name = db.Column(db.String)
    artist_id = db.Column(db.Integer, db.ForeignKey('artists.id'))
    artist_name = db.Column(db.String)
    artist_image_link = db.Column(db.String(500))

    # column at fault 
    start_time = db.Column(db.DateTime(timezone=False))
    
    def __repr__(self):
        return f'<Show id: {self.id} venue_id: {self.venue_id} artist_id: {self.artist_id} start_time: {self.start_time}>'

Running flask db migrate works fine and initially generates this migration script:

from alembic import op
import sqlalchemy as sa


# revision identifiers, used by Alembic.
revision = '3ac508896870'
down_revision = '90b7c2f14692'
branch_labels = None
depends_on = None


def upgrade():
    # ### commands auto generated by Alembic - please adjust! ###
    op.alter_column('shows', 'start_time',
               existing_type=sa.VARCHAR(length=50),
               type_=sa.DateTime(),
               existing_nullable=True)
    # ### end Alembic commands ###


def downgrade():
    # ### commands auto generated by Alembic - please adjust! ###
    op.alter_column('shows', 'start_time',
               existing_type=sa.DateTime(timezone=False),
               type_=sa.VARCHAR(length=50),
               existing_nullable=True)
    # ### end Alembic commands ###

After running flask db upgrade, I get a error message with the following hint at the end:

HINT:  You might need to specify "USING start_time::timestamp without time zone".

[SQL: ALTER TABLE shows ALTER COLUMN start_time TYPE TIMESTAMP WITHOUT TIME ZONE ]
(Background on this error at: https://sqlalche.me/e/14/f405)

Based on that, I took a look at the SQLAlchemy docs on the DateTime type and believing that the issue was the missing timezone parameter, I added it to the migration script like so:

op.alter_column('shows', 'start_time',
           existing_type=sa.VARCHAR(length=50),
           type_=sa.DateTime(timezone=False),
           existing_nullable=True)

However, the error persisted. So I'm asking, what is the appropriate way to specify USING start_time::timestamp without time zone? Or perhaps the error's hint wasn't the right solution?

I'm new to Flask by the way, so I'm just stumbling my way through docs to figure things out.

EDIT: I resolved this issue for myself by completely dropping the existing String type column and replacing it with a new DateTime column. This won't be ideal for all solutions and was only possible because there was no crucial data in the table.

Still looking for less dramatic solutions.

0 Answers0