我按以下方式使用psycopg2插入项目：
- cursor = connection.cursor()
- for item in items:
- try:
- cursor.execute(
- "INSERT INTO items (name,description) VALUES (%s,%s) RETURNING id",(item[0],item[1])
- )
- id = cursor.fetchone[0]
- if id is not None:
- cursor.execute(
- "INSERT INTO item_tags (item,tag) VALUES (%s,(id,'some_tag')
- )
- except psycopg2.Error:
- connection.rollback()
- print("Postgresql Error: " + e.diag.message_primary)
- continue
- print(item[0])
- connection.commit()
显然,当一个项目已经在数据库中时,将抛出重复的键异常.有没有办法忽略这个例外？抛出异常时是否会中止整个事务？如果是,那么重写查询的最佳选择是什么,可能使用批量插入？
解决方法
从
Graceful Primary Key Error handling in Python/psycopg2：
You should rollback transaction on error.
I’ve added one more try..except..else construction in the code bellow
to show the exact place where exception will occur.
- try:
- cur = conn.cursor()
- try:
- cur.execute( """INSERT INTO items (name,description)
- VALUES (%s,%s) RETURNING id""",item[1]))
- except psycopg2.IntegrityError:
- conn.rollback()
- else:
- conn.commit()
- cur.close()
- except Exception,e:
- print 'ERROR:',e[0]
