Many to many field setup error about sqladmin

[murrple-1]

Yes, the top of main (ba761e8) seems to be working. Thank you.

Looking forward to the next release then :)

[aminalaee]

Hey can you provide an example for this? I think basic M2M should be ok now.

[aminalaee]

Closing this as this is not valid anymore.

[murrple-1]

I am currently seeing this issue. Why is it no longer a valid issue?

[aminalaee]

Please post a full example and trace of what is wrong.
It should be reproducible so it can be fixed.

[murrple-1]

Some edits made to simplify and hide privileged data, but this should be enough to cause the issue.

main.py

from fastapi import FastAPI
from sqladmin import Admin

from .admin import (
    InstitutionAdmin,
    TrajectoryModelAdmin,
    UserAdmin,
)
from .databases import engine

app = FastAPI()

admin = Admin(app, engine)

admin.register_model(InstitutionAdmin)
admin.register_model(UserAdmin)
admin.register_model(TrajectoryModelAdmin)

databases.py

import databases
from sqlalchemy.ext.asyncio import AsyncSession, create_async_engine
from sqlalchemy.orm import sessionmaker

DATABASE_URL = "postgresql+asyncpg://postgres:password@db/postgres"

database = databases.Database(DATABASE_URL)

engine = create_async_engine(DATABASE_URL)

AsyncSessionLocal = sessionmaker(
    autocommit=False,
    autoflush=False,
    expire_on_commit=False,
    bind=engine,
    class_=AsyncSession,
)

admin.py

from fastapi import Request
from sqladmin import ModelAdmin

from .models import Institution, TrajectoryModel, User


class InstitutionAdmin(ModelAdmin, model=Institution):
    column_list = [Institution.name, Institution.uuid]
    column_details_list = [Institution.name, Institution.users]

    form_columns = [Institution.name]


class UserAdmin(ModelAdmin, model=User):
    column_list = [User.username, User.uuid]
    column_details_list = [
        User.institution,
        User.username,
        User.first_name,
        User.last_name,
    ]

    form_columns = [
        User.institution,
        User.username,
        User.first_name,
        User.last_name,
        User.password_hash,
    ]


class TrajectoryModelAdmin(ModelAdmin, model=TrajectoryModel):
    column_list = [TrajectoryModel.name, TrajectoryModel.uuid]

    form_columns = [
        TrajectoryModel.institution,
        TrajectoryModel.name,
        TrajectoryModel.json_data,
    ]

models.py

import datetime
import uuid as uuid_

from sqlalchemy import TIMESTAMP, Column, ForeignKey, String, UniqueConstraint
from sqlalchemy.dialects.postgresql import JSONB, UUID
from sqlalchemy.orm import Mapped, declarative_base, relationship
from sqlalchemy.sql.functions import current_timestamp

Base = declarative_base()


class Institution(Base):
    __tablename__ = "institutions"

    uuid: Mapped[uuid_.UUID] = Column(
        UUID(as_uuid=True), primary_key=True, default=uuid_.uuid4
    )
    name: Mapped[str] = Column(String(), nullable=False, unique=True)
    users: Mapped[list["User"]] = relationship(
        "User",
        back_populates="institution",
        cascade="all, delete",
        passive_deletes=True,
    )
    trajectory_models: Mapped[list["TrajectoryModel"]] = relationship(
        "TrajectoryModel",
        back_populates="institution",
        cascade="all, delete",
        passive_deletes=True,
    )

    def __repr__(self):
        return f"{self.name} ({self.uuid})"


class User(Base):
    __tablename__ = "users"

    uuid: Mapped[uuid_.UUID] = Column(
        UUID(as_uuid=True), primary_key=True, default=uuid_.uuid4
    )
    institutions_uuid: Mapped[uuid_.UUID] = Column(
        UUID(as_uuid=True),
        ForeignKey("institutions.uuid", ondelete="CASCADE"),
        nullable=False,
    )
    institution: Mapped[Institution] = relationship(Institution, back_populates="users")
    username: Mapped[str] = Column(String(), nullable=False, unique=True)
    first_name: Mapped[str] = Column(String(), nullable=False)
    last_name: Mapped[str] = Column(String(), nullable=False)
    password_hash: Mapped[str | None] = Column(String(), nullable=True)

    def __repr__(self):
        return f"{self.username} ({self.uuid})"


class TrajectoryModel(Base):
    __tablename__ = "trajectorymodels"
    __table_args__ = (
        UniqueConstraint(
            "name",
            "institutions_uuid",
            name="trajectorymodels__uq__name__institutions_uuid",
        ),
    )

    uuid: Mapped[uuid_.UUID] = Column(
        UUID(as_uuid=True), primary_key=True, default=uuid_.uuid4
    )
    name: Mapped[str] = Column(String(), nullable=False)
    institutions_uuid: Mapped[uuid_.UUID] = Column(
        UUID(as_uuid=True),
        ForeignKey("institutions.uuid", ondelete="CASCADE"),
        nullable=False,
    )
    institution: Mapped[Institution] = relationship(
        Institution, back_populates="trajectory_models"
    )
    json_data: Mapped[dict] = Column(
        "data", JSONB(none_as_null=True), default=dict, nullable=False
    )
    date_created: Mapped[datetime.datetime] = Column(
        TIMESTAMP(timezone=True),
        nullable=False,
        server_default=current_timestamp(),
        default=lambda: datetime.datetime.now(datetime.timezone.utc),
    )
    last_modified: Mapped[datetime.datetime] = Column(
        TIMESTAMP(timezone=True),
        nullable=False,
        server_default=current_timestamp(),
        default=lambda: datetime.datetime.now(datetime.timezone.utc),
        onupdate=current_timestamp(),
    )

    def __repr__(self):
        return f"{self.name} ({self.uuid})"

pyproject.toml

[tool.poetry.dependencies]
fastapi = "^0.78.0"
psycopg2 = "^2.9"
python = ">=3.10,<3.11"
databases = { version = "*", extras = ["asyncpg"] }
SQLAlchemy = { version = ">=1.3.0", extras = ["asyncio"] }
alembic = { version = "^1.8.0", extras = ["tz"] }
sqladmin = "^0.1.12"

DB schema (edited down from pg_dump --table institutions --table users --table trajectorymodels --schema-only

CREATE TABLE institutions (
    uuid uuid NOT NULL PRIMARY KEY,
    name character varying NOT NULL
);

CREATE TABLE trajectorymodels (
    uuid uuid NOT NULL PRIMARY KEY,
    name character varying NOT NULL,
    institutions_uuid uuid NOT NULL,
    data jsonb NOT NULL,
    date_created timestamp with time zone DEFAULT CURRENT_TIMESTAMP NOT NULL,
    last_modified timestamp with time zone DEFAULT CURRENT_TIMESTAMP NOT NULL
);

CREATE TABLE users (
    uuid uuid NOT NULL PRIMARY KEY,
    institutions_uuid uuid NOT NULL,
    first_name character varying NOT NULL,
    last_name character varying NOT NULL,
    username character varying NOT NULL,
    password_hash character varying
);

ALTER TABLE institutions
    ADD CONSTRAINT institutions_name_key UNIQUE (name);

ALTER TABLE users
    ADD CONSTRAINT users_username_key UNIQUE (username);

ALTER TABLE users
    ADD CONSTRAINT users_institutions_uuid_fkey FOREIGN KEY (institutions_uuid) REFERENCES institutions(uuid) ON DELETE CASCADE;

ALTER TABLE trajectorymodels
    ADD CONSTRAINT trajectorymodels__uq__name__institutions_uuid UNIQUE (name, institutions_uuid);

ALTER TABLE trajectorymodels
    ADD CONSTRAINT trajectorymodels_institutions_uuid_fkey FOREIGN KEY (institutions_uuid) REFERENCES institutions(uuid) ON DELETE CASCADE;

Creating a new user works, but attempting to save after an edit causes this stacktrace:

 Traceback (most recent call last):
   File "/venv/lib/python3.10/site-packages/fastapi/applications.py", line 269, in __call__
     await super().__call__(scope, receive, send)
   File "/venv/lib/python3.10/site-packages/starlette/applications.py", line 124, in __call__
     await self.middleware_stack(scope, receive, send)
   File "/venv/lib/python3.10/site-packages/starlette/middleware/errors.py", line 184, in __call__
     raise exc
   File "/venv/lib/python3.10/site-packages/starlette/middleware/errors.py", line 162, in __call__
     await self.app(scope, receive, _send)
   File "/venv/lib/python3.10/site-packages/starlette/middleware/cors.py", line 92, in __call__
     await self.simple_response(scope, receive, send, request_headers=headers)
   File "/venv/lib/python3.10/site-packages/starlette/middleware/cors.py", line 147, in simple_response
     await self.app(scope, receive, send)
   File "/venv/lib/python3.10/site-packages/starlette/exceptions.py", line 93, in __call__
     raise exc
   File "/venv/lib/python3.10/site-packages/starlette/exceptions.py", line 82, in __call__
     await self.app(scope, receive, sender)
   File "/venv/lib/python3.10/site-packages/fastapi/middleware/asyncexitstack.py", line 21, in __call__
     raise e
   File "/venv/lib/python3.10/site-packages/fastapi/middleware/asyncexitstack.py", line 18, in __call__
     await self.app(scope, receive, send)
   File "/venv/lib/python3.10/site-packages/starlette/routing.py", line 670, in __call__
     await route.handle(scope, receive, send)
   File "/venv/lib/python3.10/site-packages/starlette/routing.py", line 418, in handle
     await self.app(scope, receive, send)
   File "/venv/lib/python3.10/site-packages/starlette/applications.py", line 124, in __call__
     await self.middleware_stack(scope, receive, send)
   File "/venv/lib/python3.10/site-packages/starlette/middleware/errors.py", line 184, in __call__
     raise exc
   File "/venv/lib/python3.10/site-packages/starlette/middleware/errors.py", line 162, in __call__
     await self.app(scope, receive, _send)
   File "/venv/lib/python3.10/site-packages/starlette/exceptions.py", line 93, in __call__
     raise exc
   File "/venv/lib/python3.10/site-packages/starlette/exceptions.py", line 82, in __call__
     await self.app(scope, receive, sender)
   File "/venv/lib/python3.10/site-packages/starlette/routing.py", line 670, in __call__
     await route.handle(scope, receive, send)
   File "/venv/lib/python3.10/site-packages/starlette/routing.py", line 266, in handle
     await self.app(scope, receive, send)
   File "/venv/lib/python3.10/site-packages/starlette/routing.py", line 65, in app
     response = await func(request)
   File "/venv/lib/python3.10/site-packages/sqladmin/application.py", line 371, in edit
     await model_admin.update_model(pk=request.path_params["pk"], data=form.data)
   File "/venv/lib/python3.10/site-packages/sqladmin/models.py", line 925, in update_model
     setattr(result, name, value)
   File "/venv/lib/python3.10/site-packages/sqlalchemy/orm/attributes.py", line 459, in __set__
     self.impl.set(
   File "/venv/lib/python3.10/site-packages/sqlalchemy/orm/attributes.py", line 1268, in set
     value = self.fire_replace_event(state, dict_, value, old, initiator)
   File "/venv/lib/python3.10/site-packages/sqlalchemy/orm/attributes.py", line 1294, in fire_replace_event
     value = fn(
   File "/venv/lib/python3.10/site-packages/sqlalchemy/orm/unitofwork.py", line 130, in set_
     sess._save_or_update_state(newvalue_state)
   File "/venv/lib/python3.10/site-packages/sqlalchemy/orm/session.py", line 2639, in _save_or_update_state
     self._save_or_update_impl(state)
   File "/venv/lib/python3.10/site-packages/sqlalchemy/orm/session.py", line 3223, in _save_or_update_impl
     self._update_impl(state)
   File "/venv/lib/python3.10/site-packages/sqlalchemy/orm/session.py", line 3212, in _update_impl
     self.identity_map.add(state)
   File "/venv/lib/python3.10/site-packages/sqlalchemy/orm/identity.py", line 151, in add
     raise sa_exc.InvalidRequestError(
 sqlalchemy.exc.InvalidRequestError: Can't attach instance <Institution at 0x7f9067bd5f60>; another instance with key (<class 'models.Institution'>, (UUID('f1a80960-249f-459d-822a-bc09b01a6035'),), None) is already present in this session.

[aminalaee]

@murrple-1 Have you also tried this on latest code on git main branch?

[aminalaee]

Vsrsion 0.2.0 is released so closing this :)