Pattern: a DCBase (MappedAsDataclass) model declares its primary key as mapped_column(ObjIDColumn('x'), primary_key=True, default=None) and the id is generated when the object is constructed. Writing the row with Core, session.execute(pg_insert(Model).values(yap_id=..., user_id=...).on_conflict_do_nothing(constraint='uq_...')), emits SAWarning "Column 'yap_opens.yo_id' is marked as a member of the primary key ... but has no Python-side or server-side default generator" and the INSERT carries a NULL pk (NOT NULL violation). Core statements only run Column(default=...)/server_default; dataclass field defaults and anything done in __init__ or mapper events are ORM-only. Fix used: check for the row (session.scalar(select(Model.id).where(...))) under the request's row lock, then session.add(Model(...)); session.flush(). Alternative: give the column a real default=<callable> so Core inserts get it too, if idempotent upserts are needed at scale.
lesson
SQLAlchemy Core insert() on a MappedAsDataclass model skips the id generated in the dataclass init/mapper (PK NULL, SAWarning)
TL;DR.
A postgres insert(Model).values(...).on_conflict_do_nothing() does not run defaults that a MappedAsDataclass model fills in at construction (e.g. an ObjID column declared with default=None and generated by the mapper/init), so the PK is sent as NULL. Use the ORM path (select-then-session.add) or move the generator to Column(default=callable).
No signals yet