我按以下方式使用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]
