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

Question

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)

I'm not versed in Flask or SQLAlchemy but the error says that table userstore doesn't exist. Are you sure that it does exist when the Alter Table is run against it? — J2112O, Jun 28 '20 at 19:27
No they won't exist initially that's why i am using event listener's "after_create" to invoke the DDL when tables are created using flask migration commands. — Vikash Singh, Jun 29 '20 at 03:22

score 0 · Accepted Answer · answered Jun 29 '20 at 11:16

0

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.

answered Jun 29 '20 at 11:16

Miguel Grinberg

65,299
14
133
152

Thanks for helping, after lots of looking around I realized that the second one containing DDL is correct one and the event is only invoked when you explicitly do `db.create_all()` which was missing from my model file. – Vikash Singh Jun 29 '20 at 11:39
If you are using Flask-Migrate then no, you would not call `db.create_all()`. Instead you would create a migration script (`flask db migrate`) and then run it (`flask db upgrade`) as I mentioned in my answer. Flask-Migrate competes with db.create_all(), you have to pick which method you want to use to maintain your database schema. – Miguel Grinberg Jun 30 '20 at 14:23

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

1 Answers1