sqlalchemy ORM conflict update，主键或者唯一索引冲突，do nothing or update

do nothing

from sqlalchemy import create_engine
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.orm import sessionmaker
from sqlalchemy import Column,BigInteger,String

engine = create_engine("postgresql+psycopg2://postgresadmin:admin123@192.168.214.133:32222/postgresdb", echo=False, client_encoding='utf8', pool_size=50,pool_pre_ping=True,max_overflow=20)

base = declarative_base(engine)

class EllisUpdate(base):
    __tablename__='ellisupdate'
    id = Column(BigInteger,primary_key=True,autoincrement=True)
    name = Column(String(255))
    entity = Column(BigInteger)
    

class Server:
    def __init__(self) -> None:
        sessionMaker = sessionmaker(engine)
        self.session = sessionMaker()

    def on_conflict(self,obj):
        self.session.execute(obj)
        self.session.commit()
        
base.metadata.create_all(engine)

from sqlalchemy.dialects.postgresql import insert

stmt = insert(EllisUpdate).values(
    [
        dict(name="ellis", entity=1),
        dict(name="squidward", entity=2),
        dict(name="ellis", entity=1),
        dict(name="ellis2", entity=1),
    ]
)

# 这里的index_elements 表示name以及entity是联合唯一的
stmt = stmt.on_conflict_do_nothing(
    index_elements=[EllisUpdate.name,EllisUpdate.entity]
)


server = Server()

server.on_conflict(stmt)


# 这里的index_elements 表示name以及entity是联合唯一的
# stmt = stmt.on_conflict_do_update(
#     index_elements=[EllisUpdate.name,EllisUpdate.entity],set_=dict(
#         name=stmt.excluded.name,
#     )
# )

do update

from sqlalchemy.dialects.postgresql import insert
from sqlalchemy import func
from sqlalchemy import create_engine
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy import Column,String,Text
from sqlalchemy.orm import sessionmaker

base = declarative_base()

class NewTable(base):
    __tablename__='ellis'
    field1 = Column(String(255),primary_key=True)
    field2 = Column(String(255),primary_key=True)
    field3 = Column(Text)
    
engine = create_engine("postgresql://postgresadmin:admin123@192.168.214.133:32222/postgresdb")

session = sessionmaker(engine)

Session = session()

stmt = insert(NewTable).values([{"field1":"1","field2":"1","field3":"sap1"},{"field1":"2","field2":"2","field3":"sap2"}])

#这里的stmt.excluded.field3 是指传入的数据，并不是数据库已经保存的数据
# stmt = stmt.on_conflict_do_nothing(index_elements=[NewTable.field1,NewTable.field2])
stmt = stmt.on_conflict_do_update(index_elements=[NewTable.field1,NewTable.field2],set_={"field3":func.concat(stmt.excluded.field3,NewTable.field3)})

Session.execute(stmt)
Session.commit()
Session.close()

参考
https://stackoverflow.com/questions/70261541/use-on-conflict-do-update-with-sqlalchemy-orm
https://docs.sqlalchemy.org/en/14/orm/persistence_techniques.html#using-postgresql-on-conflict-with-returning-to-return-upserted-orm-objects

https://docs.sqlalchemy.org/en/20/dialects/postgresql.html#sqlalchemy.dialects.postgresql.Insert.on_conflict_do_update