flask db upgrade returns "Table already exist" Error

Question

So I created a simple table with MySQLAlchemy, and everything works pretty fine.

My code.

from app import db
from werkzeug.security import generate_password_hash, check_password_hash

class register_users(db.Model):

    __tablename__='new_user'

    id=db.Column(db.Integer,primary_key=True, autoincrement=True)
    name=db.Column(db.String(120), nullable=False)
    email=db.Column(db.String(100), nullable=False)
    date_of_birth=db.Column(db.DateTime(100), nullable=False)
    hash_password=db.Column(db.String(1000), nullable=False)


    @property
    def password(self):
        raise AttributeError('password is not a readable attribute')

    @password.setter
    def password(self, password):
        self.hash_password=generate_password_hash(password)

    def verify_password(self, password):
        return check_password_hash(self.hash_password,password)

    def __repr__(self):
        return '<name % >r'% self.name

However, when I try to update the table with a new column or command, I get a bad error:

sqlalchemy.exc.OperationalError: (pymysql.err.OperationalError) (1050, "Table 'new_users' 
already exists")
[SQL:
CREATE TABLE `New_users` (
        id INTEGER NOT NULL AUTO_INCREMENT,
        name VARCHAR(120) NOT NULL,
        email VARCHAR(100) NOT NULL,
        date_of_birth DATETIME NOT NULL,
        hash_password VARCHAR(1000) NOT NULL,
        PRIMARY KEY (id),
        UNIQUE (email)
)

I know the upgrade command is supposed to update the information of an already existing database table, however, it is like my upgrade command is trying to creat a new table all over again when I had already created the table.

I have looked through all other similar posts, but I have not been able to find something that solves my problem. Please help.

score 0 · Answer 1 · answered Sep 30 '22 at 12:38

If you are using Alembic you can check the current upgrade version in the migrations folder. In there you will find a function name upgrade().

If there is any reference to creating a new database table, you can just delete that and run upgrade again. This can happen for a lot of reasons. I personally check the migrations folder and fix it form there.

score 0 · Accepted Answer · answered Oct 05 '22 at 16:46

0

Try

deleting the last migration ,then running migrations again before checking if the command now works.
downgrading the database version and repeating the command.

answered Oct 05 '22 at 16:46

Oma Collins

16
1

score 0 · Answer 3 · answered Oct 06 '22 at 00:03

0

If you are not using flask-migrations.

# In app.py or terminal

dropdb database_name && createdb database_name

This will cause you to lose existing data. I strongly suggest that you use flask-migrations.

answered Oct 06 '22 at 00:03

Delali Jean-Pierre Dogbevi

1

Thanks for the comment, however, I am using flask migration and flask-sqlalchemy to handle the manipulation of my database. I have been able to solve the issue, I deleted the existing migration folder and created a new migration. I also dropped the alembic_version table from the main database itself and it solved the problem. – Martins. Adesua Oct 06 '22 at 09:25

flask db upgrade returns "Table already exist" Error

3 Answers3