Search code examples
pythonmysqlsqlalchemyflask-sqlalchemyflask-migrate

Trying to change auto increment start value in my tables with Flask SqlAlchemy event.listen.But I am encountering table doesn't exist

models.py

#imports
class User(db.Model):
    __tablename__ = 'userstore'
    id = db.Column(db.Integer, primary_key=True)
    username = db.Column(db.String(20), unique=True)
    password = db.Column(db.String(128))
    role = db.Column(db.String(15))
    created_on = db.Column(db.TIMESTAMP, default=datetime.now())
    logged_in = db.Column(db.TIMESTAMP, nullable=True)

    def set_password(self, password):
        self.password = generate_password_hash(password)
    def check_password(self, password):
        return check_password_hash(self.password, password)

#Here i am trying to change autoincment vaue
event.listen(User.__table__,"after_create",db.engine.execute(""" ALTER TABLE userstore AUTO_INCREMENT = 100000001""")
)

Also tried to use DDL like

event.listen(User.__table__,"after_create", DDL(""" ALTER TABLE userstore AUTO_INCREMENT =100000001"""))

when using DDL, migration ran without any errors but id value still starts from 1 which means DDL query inside above event.listen is not executed somehow

app.py

#imports

from config import Config

app = Flask(__name__)
app.config.from_object(Config)
db = SQLAlchemy(app)
from models import User, Patient,MedicineDetails

migrate = Migrate(app, db)

#route codes

if __name__ == '__main__':
    app.run(debug=True)

Error after performing flask db init

qlalchemy.exc.ProgrammingError: (mysql.connector.errors.ProgrammingError) 1146 (42S02): Table 'hms.userstore' doesn't exist
[SQL:  ALTER TABLE userstore AUTO_INCREMENT = 100000001]
(Background on this error at: http://sqlalche.me/e/f405)
Solution

  • The problem with this one:

    event.listen(User.__table__,"after_create",db.engine.execute(""" ALTER TABLE userstore AUTO_INCREMENT = 100000001"""))

    is that the db.engine.execute() call is going to run immediately, not when the event fires.

    I believe the correct version is your second one, which will cause the SQL statement to run when the table is created.

    event.listen(User.__table__,"after_create", DDL(""" ALTER TABLE userstore AUTO_INCREMENT =100000001"""))

    Running flask db init does not cause any tables to be created. You will need to create a migration with flask db migrate and then execute the migration with flask db upgrade. Only at that point the event will fire.