flask sqlalchemy postgres model issue:How to avoid entering "id" manually

Question

I have a Model, a Blueprint, and some Issues I need to solve about this code.

MODEL

from datetime import datetime
from app import db


class FeedItem(db.Model):
    __tablename__ = "feed_item"

    id = db.Column(db.Integer, primary_key=True)
    caption = db.Column(db.String(255), index=True, unique=True, primary_key=True)
    url = db.Column(db.String(255), index=True, unique=True, primary_key=True)
    createdAt = db.Column(db.DateTime, index=True, default=datetime.utcnow)
    updatedAt = db.Column(db.DateTime, index=True, default=datetime.utcnow)

    
    def from_dict(self, data):
        for field in ['caption', 'url']:
            if field in data:
                setattr(self, field, data[field])

    def addNewFeed(feed):
        """Add a new test (test)"""
        db.session.add(feed)
        db.session.commit()

    @classmethod
    def createFeed(cls, caption, url):
        """Create a new feed (caption, url)"""
        newFeed = cls(
            caption=caption,
            url=url
        )
        db.session.add(newFeed)
        db.session.commit()
        return newFeed

BLUEPRINT

@v1.route("/feed", methods=["POST"])
@v0.route("/feed", methods=["POST"])
def post_feed():
    data = request.get_json() or {}
    if 'caption' not in data or 'url' not in data:
        return bad_request('must include caption and url fields')

    # Code that needs to be fixed
    feed = FeedItem()
    feed.caption = data['caption']
    feed.url = data['url']
    feed.id = random.randint(100, 99999)
    db.session.add(feed)
    db.session.commit()
    # End of code that needs to be fixed

    feed.url = create_presigned_url(feed.url)
    return jsonify({
        "caption": feed.caption,
        "url": feed.url
    })

ISSUE:

Above blue prints work perfectly. However as you can see I'm creating the feed.id by myself and that is suboptimal

Now lets say I try to substitute these line of code above :

# Code that needs to be fixed
FeedItem()
feed.caption = data['caption']
feed.url = data['url']
feed.id = random.randint(100, 99999)
db.session.add(feed)
db.session.commit()
# End of code that needs to be fixed

With any of these 3 options:

OPTION 1

feed = FeedItem(caption=data['caption'], url=data['url'])
db.session.add(feed)
db.session.commit()

OPTION 2

feed = FeedItem()
feed.from_dict(data)
db.session.add(feed)
db.session.commit()

OPTION 3

feed = FeedItem()
feed.caption = data['caption']
feed.url = data['url']
db.session.add(feed)
db.session.commit()

For all cases I will always get the the following error that states:

"..null value in column "id" violates not-null constraint.."

sqlalchemy.exc.IntegrityError: (psycopg2.errors.NotNullViolation) null value in column "id" violates not-null constraint
DETAIL:  Failing row contains (null, DogPicture34, my_small_dog34jpg, 2020-06-30 18:51:00.679511, 2020-06-30 18:51:00.679518).

[SQL: INSERT INTO feed_item (caption, url, "createdAt", "updatedAt") VALUES (%(caption)s, %(url)s, %(createdAt)s, %(updatedAt)s)]
[parameters: {'caption': 'DogPicture34', 'url': 'my_small_dog34jpg', 'createdAt': datetime.datetime(2020, 6, 30, 18, 51, 0, 679511), 'updatedAt': datetime.datetime(2020, 6, 30, 18, 51, 0, 679518)}]
(Background on this error at: http://sqlalche.me/e/13/gkpj)

QUESTIONS

1 -What is the best approach to solve this issue ( What should I do for not having to enter the id manually?) What is the best pattern to follow ?

Answer 1

To have a column autoincrement use the autoincrement parameter to SQLAlchemy/flask-sqlalchemy Column() where type integer. From above link:

autoincrement –

Set up “auto increment” semantics for an integer primary key column.