In SQL Alchemy how to use 'on_conflict_do_update' when one use a dictionary for the values

ailauli69 0
Viewed 23

In SqlAlchemy I want to insert a new row in my_table, but if the value for the column "name" already exists then I want to update this column:

dico =     {
  "name": "somename",
  "other_column": "some_value"
}

result = conn.execute(
    insert(my_table.on_conflict_do_update(
        constraint="name",
        set_=dico
    ),
    [ dico ]
)

But I get this error:

'Insert' object has no attribute 'on_conflict_do_update'

Very important: I need to specify the values for the insert/update as a dictionary.

Thank you

sqlalchemy fastapi python
1 Answers

Suppose you have a table like that:

from sqlalchemy import Column, String, Integer, UniqueConstraint
from sqlalchemy.orm import declarative_base
from sqlalchemy.dialects.postgresql import insert


Base = declarative_base()


class Table(Base):
    __tablename__ = "example"
    __table_args__ = (UniqueConstraint("name"),)
    row_id = Column(Integer, primary_key=True)
    name = Column(String, unique=True)
    other_column = Column(String)

You can use on_conflict_do_update like that:

dico = {
  "name": "somename",
  "other_column": "some_value"
}
stmt = insert(Table).values(dico)
stmt = stmt.on_conflict_do_update(
            constraint="example_name_key",
            set_={col: getattr(stmt.excluded, col) for col in dico}
        )

Important to know is that you have to import it from sqlalchemy.dialects.postgresql and that stmt.excluded holds the "new" value for your update.

EDIT: To get your constraints dynamically, you can use the inspector.

inspector = sqlalchemy.inspect(engine)
inspector.get_unique_constraints(table.__tablename__)  # or provide the table name as a string
bitflip0
Related