Instantiated a SQLAlchemy Declarative mapped class requires the autogenerated and relationship attributes to be set

Question

I have a one-many table relationship between Company and Element and think I have defined them correctly

class Base(MappedAsDataclass, DeclarativeBase):
    pass

class Company(Base):
    __tablename__ = "company"
    id: Mapped[int] = mapped_column(primary_key=True, autoincrement=True)
    name: Mapped[str] = mapped_column(String(30))
    url: Mapped[str] = mapped_column(String(50))
    stype: Mapped[str] = mapped_column(String(10))
    elements: Mapped[List["Element"]] = relationship()

    def __repr__(self):
        return f"<Company(name={self.name!r})>"


class Element(Base):
    __tablename__ = "element"
    id: Mapped[int] = mapped_column(primary_key=True, autoincrement=True)
    element: Mapped[str] = mapped_column(String(50))
    company_id: Mapped[int] = mapped_column(ForeignKey("company.id"))
    company: Mapped["Company"] = relationship(back_populates="elements")

However, when I attempt to instantiate the class with

company = Company(name=df['name'][index], url=df['url'][index], stype=df['stype'][index])

I get the interpreter complaining that I am missing the company and id arguments. Its my understanding that neither needs to be explicitly defined

Traceback (most recent call last):
  File "C:\Program Files\JetBrains\PyCharm Community Edition 2023.2.3\plugins\python-ce\helpers\pydev\pydevd.py", line 1500, in _exec
    pydev_imports.execfile(file, globals, locals)  # execute the script
    ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "C:\Program Files\JetBrains\PyCharm Community Edition 2023.2.3\plugins\python-ce\helpers\pydev\_pydev_imps\_pydev_execfile.py", line 18, in execfile
    exec(compile(contents+"\n", file, 'exec'), glob, loc)
  File "F:\Onedrive\8. Coding\Python\pvp\database\model.py", line 67, in <module>
    main()
  File "F:\Onedrive\8. Coding\Python\pvp\database\model.py", line 63, in main
    insert_companies()
  File "F:\Onedrive\8. Coding\Python\pvp\database\model.py", line 55, in insert_companies
    company = Company(name=df['name'][index], url=df['url'][index], stype=df['stype'][index])
              ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
TypeError: __init__() missing 2 required positional arguments: 'id' and 'elements'
python-BaseException

Process finished with exit code -1073741510 (0xC000013A: interrupted by Ctrl+C)

Complete code (which I should have just started with....)

from typing import List

import pandas as pd
from sqlalchemy import (
    create_engine,
    Column, String, Float, Integer,
    ForeignKey
)
from sqlalchemy.orm import Mapped, mapped_column, relationship, DeclarativeBase, MappedAsDataclass
from sqlalchemy.orm import sessionmaker

engine = create_engine("mariadb+mariadbconnector://dkhokhar:1286Qyts@gd2-dbmain-service:3306/pvp")
session_factory = sessionmaker(bind=engine)


class Base(MappedAsDataclass, DeclarativeBase):
    pass


class Company(Base):
    __tablename__ = "company"
    id: Mapped[int] = mapped_column(primary_key=True, autoincrement=True, init=False)
    name: Mapped[str] = mapped_column(String(30))
    url: Mapped[str] = mapped_column(String(50))
    stype: Mapped[str] = mapped_column(String(10))
    elements: Mapped[List["Element"]] = relationship()

    def __repr__(self):
        return f"<Company(name={self.name!r})>"


class Element(Base):
    __tablename__ = "element"
    id: Mapped[int] = mapped_column(primary_key=True, autoincrement=True)
    element: Mapped[str] = mapped_column(String(50))
    company_id: Mapped[int] = mapped_column(ForeignKey("company.id"))
    company: Mapped["Company"] = relationship(back_populates="elements")


class ItemPrice(Base):
    __tablename__ = "item_price"
    id: Mapped[int] = mapped_column(primary_key=True, autoincrement=True)
    name: Mapped[str] = mapped_column(String(50))
    price: Mapped[float]
    company_id: Mapped[int] = mapped_column(ForeignKey("company.id"))


def insert_companies():
    print("Reading csv: pvp_init.csv")
    df = pd.read_csv("../data/pvp_dbinit.csv")
    print("Populating Company table..")
    for index in df.index:
        # [''] = column [index] = row for 2d array
        print(f"{df['name'][index]} {df['url'][index]} {df['stype'][index]}")

        company = Company(name=df['name'][index], url=df['url'][index], stype=df['stype'][index])
        with session_factory.begin() as sess:
            sess.merge(company)


def main():
    with engine.begin() as conn:
        Base.metadata.create_all(conn)
    insert_companies()


if __name__ == "__main__":
    main()

Answer 1

From the documentation:

init, as in mapped_column.init, relationship.init, if False indicates the field should not be part of the init() method

You should set init=False to all fields that are not be a part of the constructor:

class Company(Base):
    __tablename__ = "company"
    id: Mapped[int] = mapped_column(primary_key=True, autoincrement=True, init=False)  # <- HERE
    name: Mapped[str] = mapped_column(String(30))
    url: Mapped[str] = mapped_column(String(50))
    stype: Mapped[str] = mapped_column(String(10))
    elements: Mapped[List["Element"]] = relationship(init=False)  # <- HERE

    def __repr__(self):
        return f"<Company(name={self.name!r})>"

Edit: you can also remove MappedAsDataclass from Base class declaration if it's not necessary to use dataclass:

class Base(DeclarativeBase):
    pass

class Company(Base):
    __tablename__ = "company"
    id: Mapped[int] = mapped_column(primary_key=True, autoincrement=True)
    name: Mapped[str] = mapped_column(String(30))
    url: Mapped[str] = mapped_column(String(50))
    stype: Mapped[str] = mapped_column(String(10))
    elements: Mapped[List["Element"]] = relationship()

    def __repr__(self):
        return f"<Company(name={self.name!r})>"