I want my model's primary key to be an autoincrementing integer. Here is how my model looks like
class Region(db.Model): __tablename__ = 'regions' id = db.Column(db.Integer, primary_key=True, autoincrement=True) name = db.Column(db.String(100)) parent_id = db.Column(db.Integer, db.ForeignKey('regions.id')) parent = db.relationship('Region', remote_side=id, primaryjoin=('Region.parent_id==Region.id'), backref='sub-regions') created_at = db.Column(db.DateTime, default=db.func.now()) deleted_at = db.Column(db.DateTime)
The above code creates my table but does not make id
autoincrementing. So if in my insert query I miss the id
field it gives me this error
ERROR: null value in column "id" violates not-null constraint
So I changed the id
declaration to look like this
id = db.Column(db.Integer, db.Sequence('seq_reg_id', start=1, increment=1), primary_key=True)
Still the same error. What is wrong with the code above?
Nothing is wrong with the above code. In fact, you don't even need autoincrement=True
or db.Sequence('seq_reg_id', start=1, increment=1),
as SQLAlchemy will automatically set the first Integer
PK column that's not marked as a FK as autoincrement=True
.
Here, I've put together a working setup based on yours. SQLAlechemy's ORM will take care of generating id's and populating objects with them if you use the Declarative Base based class that you've defined to create instances of your object.
from flask import Flask from flask.ext.sqlalchemy import SQLAlchemy app = Flask(__name__) app.debug = True app.config['SQLALCHEMY_DATABASE_URI'] = 'postgresql://user:password@localhost/testdb' app.config['SQLALCHEMY_ECHO'] = True db = SQLAlchemy(app) class Region(db.Model): __tablename__ = 'regions' id = db.Column(db.Integer, primary_key=True) name = db.Column(db.String(100)) db.drop_all() db.create_all() region = Region(name='Over Yonder Thar') app.logger.info(region.id) # currently None, before persistence db.session.add(region) db.session.commit() app.logger.info(region.id) # gets assigned an id of 1 after being persisted region2 = Region(name='Yet Another Up Yar') db.session.add(region2) db.session.commit() app.logger.info(region2.id) # and 2 if __name__ == '__main__': app.run(port=9001)
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With