Question 410334 in PostgreSQL Question posted in
The official documentation can be found here.

Update if property already exists on bulk import with SQLAlchemy and PostgreSQL

I have a table with primary uuid key, unique article and some other params.

My model:

class Product(Base):
    __tablename__ = "products"
    idf: Mapped[str] = mapped_column(primary_key=True, unique=True)
    name: Mapped[str] = mapped_column(nullable=False)
    article: Mapped[str] = mapped_column(nullable=False, unique=True)
    category: Mapped[str] = mapped_column(default="")
    description: Mapped[str] = mapped_column(default="")

Firstly, I’m inserting a large list of objects with the function below:

async def import(self, db: AsyncSession, objects):
        await db.run_sync(lambda session: session.bulk_save_objects(objects))
        await db.commit()
        return {"status": "Import has been finished"}

When I use this function with similar list (for ex. one dict has a different name), I need to check unique article existing in postgres table and if it does all properties except primary uuid key and unique article should be updated. Any ideas how to do it?

Tags:

2

Answers


  1. 0

    I think you want to do a upsert use:
    https://docs.sqlalchemy.org/en/20/dialects/postgresql.html#postgresql-insert-on-conflict

    bulk_save_objects is deprecated: https://docs.sqlalchemy.org/en/20/orm/session_api.html#sqlalchemy.orm.Session.bulk_save_objects

    So you do:

    stmt = session.insert(Product).values(objects)
update_stmt = stmt.on_conflict_do_update(
    index_element=['idf'],
    set_={"name":stmt.excluded.name, "category":stmt.excluded.category,"description":stmt.excluded.description}
)
session.execute(update_stmt)
session.commit()
    Login or Signup to reply.
  2. 0

    You can catch Integrity Error, if unique attribute is already used:

    from sqlalchemy.exc import IntegrityError

    try:
        session.flush()
        print(f"Entry created successful")
        
    except IntegrityError:
        session.rollback()
        your_update_function()

    Also see this good explanation for catching Integrity Errors:
    https://stackoverflow.com/a/25877950/21437866

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search