[Python] How do I map a column to create an autoincrement primary key?

My SQLAlchemy 2.0 mapped column with an SQLite database is creating a primary key column due to which I'm not able to insert.

Mode class:

import datetime
from typing import Optional

from sqlalchemy import String, TIMESTAMP, Integer
from sqlalchemy import func
from sqlalchemy.dialects import postgresql, sqlite
from sqlalchemy.orm import DeclarativeBase
from sqlalchemy.orm import Mapped, mapped_column
from sqlalchemy.schema import CreateTable
from typing_extensions import Annotated

timestamp = Annotated[datetime.datetime, mapped_column(nullable=False, server_default=func.CURRENT_TIMESTAMP()),]


class Base(DeclarativeBase):
type_annotation_map = {datetime.datetime: TIMESTAMP(timezone=True), }


class UserClass(Base):
__tablename__ = "user"

id: Mapped[Integer] = mapped_column(Integer, primary_key=True, autoincrement=True)
name: Mapped[str] = mapped_column(String(50))
fullname: Mapped[Optional[str]] = mapped_column(String(50), nullable=False)
nickname: Mapped[Optional[str]] = mapped_column(String(30))
date: Mapped[datetime.datetime]
status: Mapped[str]
created_at: Mapped[timestamp] = mapped_column(server_default=func.UTC_TIMESTAMP())
updated_at: Mapped[timestamp] = mapped_column(server_default=func.UTC_TIMESTAMP())


Table:

CREATE TABLE user (
id BIGINT NOT NULL,
name VARCHAR(50) NOT NULL,
fullname VARCHAR(50) NOT NULL,
nickname VARCHAR(30),
date TIMESTAMP NOT NULL,
status VARCHAR NOT NULL,
created_at TIMESTAMP DEFAULT (UTC_TIMESTAMP()) NOT NULL,
updated_at TIMESTAMP DEFAULT (UTC_TIMESTAMP()) NOT NULL,
PRIMARY KEY (id)
);


Insert command:

INSERT INTO user (name, fullname, nickname, date, status, created_at, updated_at) VALUES ('John', 'John Doe', 'Johnny', '2024-09-21 19:09:51.484769', 'active', '2024-09-21 19:09:51.490379', '2024-09-21 19:09:51.490400')


When I insert it's giving this error message:

sqlite3.IntegrityError) NOT NULL constraint failed: user.id
​

I think a primary key needs to be created like this in SQLite: id integer primary key autoincrement. How do I map a column to create the primary key like this?

Continue reading...