python - 从 IntegrityError 异常中获取 key

Question

我正在尝试使用 sqlalchemy 保存自定义对象列表:

session.bulk_save_objects(listOfObjects)

并获取 IntegrityError:

psycopg2.IntegrityError: duplicate key value violates unique constraint "ppoi_ukey"

并且: key (“id”)=(42555)已存在。

有没有办法以整数形式获取 key ID (42555)，以便回滚，从列表中提取该 key ，然后在没有它的情况下再次重新插入列表？

Answer 1

我尝试了一些我认为可能对你有帮助的东西。

from sqlalchemy import Column, Integer, String
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy import create_engine
from sqlalchemy.orm import sessionmaker 
from sqlalchemy.exc import IntegrityError

Base = declarative_base()

#Create a dummy model 
class Person(Base):
    __tablename__ = 'person'
    id = Column(Integer, primary_key=True)
    name = Column(String(250), nullable=False)

    def __repr__(self):
        return "{ id="+str(self.id)+", name="+self.name+ " }"

 # Create an engine that stores data in the local directory's
# sqlalchemy_example.db file.
engine = create_engine('sqlite:///sqlalchemy_example.db')

# Create all tables 
Base.metadata.create_all(engine)
DBSession = sessionmaker(bind=engine)
session = DBSession()
list_of_objects = []
new_person = Person(id=1,name='new person')
list_of_objects.append(new_person)
new_person2 = Person(id=2,name='new person2')
list_of_objects.append(new_person2)
# Violating unique constraint purposely 
new_person3 = Person(id=1,name='new person')
list_of_objects.append(new_person3)
print(list_of_objects)

for person in list_of_objects:
# with session.no_autoflush:
  try:
    session.add(person)
    session.commit()
  except IntegrityError as e:
    session.rollback()
    #print(e) #prints whole exception 
    print(e.params) #print lists of param to the query
    print(e.params[0]) #assuming the first param is unique id which is violating constraint you can get it from here and use it as you wanted ..
  except Exception as ex:
      pass
person = session.query(Person).all()
print(len(person)) #returns 2