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.