Refactor to allow SQLite Autoincrement handle IDs

[nanotaboada]

Description

Modify the Player schema to use SQLite’s AUTOINCREMENT feature for primary keys. This will ensure unique ID generation without manual assignment. Additionally, split PlayerModel into separate request and response models to improve API clarity.

Acceptance Criteria

• Update id column in the Player schema to use sqlite_autoincrement=True.
• Refactor PlayerModel into separate PlayerRequestModel and PlayerResponseModel.
• Update all endpoints to work with the new model structure.
• Ensure existing data is migrated without breaking changes.
• Validate the change by running migrations and tests.

Proposal

The Player schema should be updated accordingly:

class Player(Base):
    """
    SQLAlchemy schema describing a database table of football players.

    Attributes:
        id (Integer): The primary key for the player record.
    """

    __tablename__ = "players"
id = Column(Integer, primary_key=True, sqlite_autoincrement=True)

# skipped remaining code

Among other things this change will entail splitting PlayerModel into two separate models (request/response):

class PlayerRequestModel(MainModel):
    """
    Pydantic model representing the data required for Create and Update operations on a football Player.

    Attributes:
        first_name (str): The first name of the Player.
        middle_name (Optional[str]): The middle name of the Player, if any.
        last_name (str): The last name of the Player.
        date_of_birth (Optional[str]): The date of birth of the Player, if provided.
        squad_number (int): The unique squad number assigned to the Player.
        position (str): The playing position of the Player.
        abbr_position (Optional[str]): The abbreviated form of the Player's position, if any.
        team (Optional[str]): The team to which the Player belongs, if any.
        league (Optional[str]): The league where the team plays, if any.
        starting11 (Optional[bool]): Indicates if the Player is in the starting 11, if provided.
    """
    first_name: str
    middle_name: Optional[str]
    last_name: str
    date_of_birth: Optional[str]
    squad_number: int
    position: str
    abbr_position: Optional[str]
    team: Optional[str]
    league: Optional[str]
    starting11: Optional[bool]


class PlayerResponseModel(PlayerRequestModel):
    """
    Pydantic model representing a football Player with an ID for Retrieve operations.

    Attributes:
        id (int): The unique identifier for the Player.
    """
    id: int

Resources

• 🔗 SQLite Autoincrement
• 🔗 SQLAlchemy 2.0 Documentation - Support for the SQLite database
• 🔗 SQLAlchemy 2.0 Documentation - Column INSERT/UPDATE Defaults
• 🔗 SQLAlchemy 2.0 Documentation - Describing Databases with MetaData