unable to create autoincrementing primary key with flask-sqlalchemy

Question

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?

Answer 1

Try this code out, it worked for me.

Within the __init__ function don't specify the id, so when you create a new "User" object SQLAlchemy will automatically generate an id number for you uniquely.

from flask import Flask
from flask_sqlalchemy import SQLAlchemy

app = Flask(__name__)

app.config['SQLALCHEMY_DATABASE_URI'] = 'sqlite:///users.sqlite3'
app.config['SQLAlCHEMY_TRACK_MODIFICATIONS'] = False

db = SQLAlchemy(app)

class User(db.Model):
    _id = db.Column(db.Integer, primary_key = True, autoincrement = True)
    username = db.Column(db.String(80), unique = True, nullable = False)
    email = db.Column(db.String(120), unique = True, nullable = False)

def __init__(self, username, email):
    self.username = username
    self.email = email

This line of code will create our intended table inside our database.

with app.app_context():
    db.create_all()


admin = User(username = 'another admin', email='another_admin@mail.com')
guest = User(username = 'another guest', email='another_guest@mail.com')

This code below will push our data into our table.

with app.app_context():
    db.session.add(admin)
    db.session.add(guest)
    db.session.commit()

Answer 2

In my case, I just added the id as external parameter, without relying on sqlalchemy

Answer 3

You cannot add "autoincrement" flag in column definition, moreover add "__table__args" attribute just after __table__name. Something like this:

    __tablename__ = 'table-name'
    __table_args__ = {'sqlite_autoincrement': True} -> This adds autoincrement to your primary key.

Try it, I hope this work for you ;) !

Answer 4

I had this issue declaring Composite Keys on a model class.

If you are wanting an auto-incrementing id field for a composite key (ie. more than 1 db.Column(..) definition with primary_key=True, then adding autoincrement=True fixed the issue for me.

class S3Object(db.Model):
    __tablename__ = 's3_object'

    id = db.Column(db.Integer, primary_key=True, autoincrement=True)

    # composite keys
    bucket_name = db.Column(db.String(), primary_key=True)
    key = db.Column(db.String(), primary_key=True)

So the statements above about not requiring autoincrement=True should be :

you don't even need autoincrement=True, as SQLAlchemy will automatically set the first Integer PK column that's not marked as a FK as autoincrement=True unless you are defining a composite key with more than one primary_key=True

Answer 5

Your id auto increments by default even without setting the autoincrement=True flag.

So there's nothing wrong with using

id = db.Column(db.Integer, primary_key=True, autoincrement=True)

The error you're getting is as a result of attempting to populate the table with an id attribute. Your insert query shouldn't at any point contain an id attribute otherwise you'll get that error.

Answer 6

I had the same error, even after adding autoincrement=True.

The problem was I already had the migration created. So I downgraded to the previous migration, deleted the migration, created the migration again and upgraded.

Then the error was gone.

Hope it helps someone stuck on this.

Wrapping off: Add autoincrement=True, and ensure your migration is updated and applied.

Answer 7

So I landed here with an issue that my SQLite table wasn't auto-incrementing the primary key. I have a slightly complex use case where I want to use postgres in production but sqlite for testing to make life a bit easier when continuously deploying.

It turns out SQLite doesn't like columns defined as BigIntegers, and for incrementing to work they should be set as Integers. Remarkably SQLAlchemy can handle this scenario as follows using the with_variant function. Thought this may be useful for someone:

id = db.Column(db.BigInteger().with_variant(db.Integer, "sqlite"), primary_key=True)

Further details here https://docs.sqlalchemy.org/en/13/dialects/sqlite.html

Answer 8

I think you do not need the autoincrement once you set ,

id = db.Column(db.Integer , primary_key=True , autoincrement=True)

I think that it should be ,

id = db.Column(db.Integer , primary_key=True)

it will give you the uniqueness your looking for .

Answer 9

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)