SQLAlchemy Bulk Insert scenarios with IntegrityError on Unique Constraint Violation
This might be a silly question, but I've been struggling with this for a few days now and could really use some help. I've looked through the documentation and I'm still confused about I'm trying to perform a bulk insert using SQLAlchemy, but I'm working with an `IntegrityError` when one of the rows violates a unique constraint. I'm using PostgreSQL 13 with SQLAlchemy 1.4. My table `users` has a unique constraint on the `email` field, and I'm attempting to insert multiple user records at once. Here's the code snippet I'm using: ```python from sqlalchemy import create_engine, Column, Integer, String, UniqueConstraint from sqlalchemy.ext.declarative import declarative_base from sqlalchemy.orm import sessionmaker Base = declarative_base() class User(Base): __tablename__ = 'users' id = Column(Integer, primary_key=True) email = Column(String, unique=True) engine = create_engine('postgresql://username:password@localhost/mydatabase') Base.metadata.create_all(engine) Session = sessionmaker(bind=engine) session = Session() users_to_insert = [ User(email='test@example.com'), User(email='hello@world.com'), User(email='test@example.com') # Duplicate email ] try: session.bulk_save_objects(users_to_insert) session.commit() except Exception as e: print(f'behavior: {e}') session.rollback() finally: session.close() ``` When I run this, I get the following behavior message: ``` IntegrityError: (psycopg2.errors.UniqueViolation) duplicate key value violates unique constraint "users_email_key" DETAIL: Key (email)=(test@example.com) already exists. ``` I expected the bulk insert to either skip the duplicate entries or raise a more specific behavior, but it seems to be trying to insert all at once. I've tried using `session.add_all()` instead of `bulk_save_objects()`, but it produces the same behavior. Is there a way to handle duplicates in bulk inserts with SQLAlchemy without having to check for existing records beforehand? What's the best practice here? For context: I'm using Python on Linux. I'm working on a application that needs to handle this. I'd really appreciate any guidance on this.