Partial commit or skip in sqlalchemy

Question

I have a scenario where in i have a session created using an sqlalchemy engine . Now i am looping through 10 insert statements nd in 2nd statement i am getting constraints error .. I want this particular insert to be skipped and rest of the insert statements to continue.

I am using python and doing it in try catch block but still the session is getting errored and not moving ahead.

Any suggestions on this.

I tried placing flush and rollbacks before moving ahead with other inserts still i am getting

Answer 1

You can use nested transactions to set savepoints, allowing actions within the nested transaction to be rolled back without affecting the outer transaction.

import sqlalchemy as sa
from sqlalchemy import exc
from sqlalchemy.orm import mapped_column, Mapped, DeclarativeBase, sessionmaker


class Base(DeclarativeBase):
    pass


class Test(Base):
    __tablename__ = 't75313967'

    id: Mapped[int] = mapped_column(sa.Identity(), primary_key=True)
    name: Mapped[str] = mapped_column(unique=True)


engine = sa.create_engine('postgresql+psycopg2:///test', echo=True)
Base.metadata.drop_all(engine, checkfirst=True)
Base.metadata.create_all(engine)
Session = sessionmaker(engine)


names = ['Alice', 'Bob', 'Bob', 'Carol']

with Session() as s, s.begin():
    for name in names:
        try:
            with s.begin_nested():
                s.add(Test(name=name))
        except exc.IntegrityError:
            print(f'Skipping {name}')