1

So I using sqlite as my test database and have the following classes in my models.py

class User(UserMixin, db.Model):
    __tablename__ = 'users'
    id = db.Column(db.Integer, primary_key=True, index=True)
    username = db.Column(db.String(40), unique=True, index=True)
    password_hash = db.Column(db.String(256))
    alternate_id = db.Column(db.String(100))
    posts = db.relationship('Posts', backref='author', lazy=True)

    def get_id(self):
        return str(self.alternate_id)

    def __init__(self, username, password):
        self.username = username
        self.password_hash = generate_password_hash(password)
        self.alternate_id = my_serializer.dumps(
            self.username + self.password_hash)

    def verify_password(self, password):
        if check_password_hash(self.password_hash, password):
            return "True"


class Posts(db.Model):
    id = db.Column(db.Integer, primary_key=True)
    title = db.Column(db.String(100), nullable=False, unique=True)
    description = db.Column(db.String(1500))
    author_id = db.Column(db.Integer, db.ForeignKey('users.id'))

    def __init__(self, title, description, author_id):
        self.title = title
        self.description = description
        self.author_id = author_id

I added the unique key constraint to column title in my Posts class and then was trying to update the schema using Flask-Migrate.

Initially I was getting the No support for ALTER of constraints in SQLite dialect errors since sqlite3 does not support it through alembic. So I looked the alembic documentation and found that you can actually do such migrations using the batch mode migrations. So I updated my migration script as below.

def upgrade():
    # ### commands auto generated by Alembic - please adjust! ###
    with op.batch_alter_table("posts") as batch_op:
        batch_op.create_unique_constraint('unique_title', ['title'])
    # ### end Alembic commands ###

Now when I try to run flask db upgrade I get the following error

sqlalchemy.exc.IntegrityError: (sqlite3.IntegrityError) UNIQUE constraint failed: _alembic_tmp_posts.title [SQL: 'INSERT INTO
_alembic_tmp_posts (id, title, description, author_id) SELECT posts.id, posts.title, posts.description, posts.author_id \nFROM posts'] (Background on this error at: http://sqlalche.me/e/gkpj`)

I am not able to understand that why IntegrityError exception is being thrown because if I look at the insert statement the number of columns are same.

Does it have something to do with the authors_id column having a foreignkey constraint on it ?

Rohit
  • 3,659
  • 3
  • 35
  • 57
  • 1
    what worked for me is adding this to env.py in your migrations folder: in the def run_migrations_online(): function. context.configure(connection=connection, target_metadata=target_metadata,process_revision_directives=process_revision_directives, compare_type=True, render_as_batch= config.get_main_option('sqlalchemy.url').startswith('sqlite:///'), **current_app.extensions['migrate'].configure_args).. Are you sure there is no existing data already in db that fails this constraint? – gittert Feb 13 '19 at 15:43

1 Answers1

1

The database table column on which I was adding the unique constraint had duplicate data and that was the reason I was getting the integrity error, I am just surprised why I didn't notice that earlier.

So once I removed one of the duplicate rows, the database upgrade was successful.

Rohit
  • 3,659
  • 3
  • 35
  • 57