postgresql 将 try/except 与 psycopg2 或“关闭”一起使用？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/12608288/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
use try/except with psycopg2 or "with closing"?
提问by skyler
I'm using Psycopg2 in Python to access a PostgreSQL database. I'm curious if it's safe to use the with closing()pattern to create and use a cursor, or if I should use an explicit try/exceptwrapped around the query. My question is concerning inserting or updating, and transactions.
我在 Python 中使用 Psycopg2 来访问 PostgreSQL 数据库。我很好奇使用with closing()模式来创建和使用游标是否安全，或者我是否应该使用显式try/except包裹查询。我的问题是关于插入或更新，以及交易。
As I understand it, all Psycopg2 queries occur within a transaction, and it's up to calling code to commit or rollback the transaction. If within a with closing(...block an error occurs, is a rollback issued? In older versions of Psycopg2, a rollback was explicitly issued on close()but this is not the case anymore (see http://initd.org/psycopg/docs/connection.html#connection.close).
据我了解，所有 Psycopg2 查询都发生在一个事务中，由调用代码来提交或回滚事务。如果with closing(...块内发生错误，是否发出回滚？在旧版本的 Psycopg2 中，明确发出了回滚，close()但情况不再如此（请参阅http://initd.org/psycopg/docs/connection.html#connection.close）。
My question might make more sense with an example. Here's an example using with closing(...
举个例子，我的问题可能更有意义。这是一个使用示例with closing(...
with closing(db.cursor()) as cursor:
cursor.execute("""UPDATE users
SET password = %s, salt = %s
WHERE user_id = %s""",
(pw_tuple[0], pw_tuple[1], user_id))
module.rase_unexpected_error()
cursor.commit()
What happens when module.raise_unexpected_error() raises its error? Is the transaction rolled back? As I understand transactions, I either need to commit them or roll them back. So in this case, what happens?
当 module.raise_unexpected_error() 引发错误时会发生什么？事务是否回滚？据我了解事务，我要么需要提交它们，要么回滚它们。那么在这种情况下，会发生什么？
Alternately I could write my query like this:
或者，我可以像这样编写查询：
cursor = None
try:
cursor = db.cursor()
cursor.execute("""UPDATE users
SET password = %s, salt = %s
WHERE user_id = %s""",
(pw_tuple[0], pw_tuple[1], user_id))
module.rase_unexpected_error()
cursor.commit()
except BaseException:
if cursor is not None:
cursor.rollback()
finally:
if cursor is not None:
cursor.close()
Also I should mention that I have no idea if Psycopg2's connection class cursor()method could raise an error or not (the documentation doesn't say) so better safe than sorry, no?
另外我应该提到我不知道 Psycopg2 的连接类cursor()方法是否会引发错误（文档没有说）比抱歉更安全，不是吗？
Which method of issuing a query and managing a transaction should I use?
我应该使用哪种发出查询和管理事务的方法？
采纳答案by voithos
Your link to the Psycopg2 docskind of explains it itself, no?
您指向 Psycopg2 文档的链接本身就说明了这一点，不是吗？
... Note that closing a connection without committing the changes first will cause any pending change to be discarded as if a ROLLBACK was performed(unless a different isolation level has been selected: see set_isolation_level()).
Changed in version 2.2: previously an explicit ROLLBACK was issued by Psycopg on close(). The command could have been sent to the backend at an inappropriate time, so Psycopg currently relies on the backend to implicitly discard uncommitted changes. Some middleware are known to behave incorrectly though when the connection is closed during a transaction (when status is STATUS_IN_TRANSACTION), e.g. PgBouncer reports an unclean server and discards the connection. To avoid this problem you can ensure to terminate the transaction with a commit()/rollback() before closing.
... 请注意，在不首先提交更改的情况下关闭连接将 导致任何挂起的更改被丢弃，就像执行了 ROLLBACK 一样（除非选择了不同的隔离级别：请参阅 set_isolation_level()）。
在 2.2 版更改：以前 Psycopg 在 close() 上发出了显式 ROLLBACK。命令可能在不适当的时间发送到后端，因此Psycopg 目前依赖后端隐式丢弃未提交的更改。尽管在事务期间关闭连接（当状态为 STATUS_IN_TRANSACTION 时），一些中间件的行为是不正确的，例如 PgBouncer 报告一个不干净的服务器并丢弃连接。为避免此问题，您可以确保在关闭之前使用 commit()/rollback() 终止事务。
So, unless you're using a different isolation level, or using PgBouncer, your first example should work fine. However, if you desire some finer-grained control over exactly what happens during a transaction, then the try/except method might be best, since it parallels the database transaction state itself.
因此，除非您使用不同的隔离级别或使用 PgBouncer，否则您的第一个示例应该可以正常工作。但是，如果您希望对事务期间发生的事情进行更细粒度的控制，那么 try/except 方法可能是最好的，因为它与数据库事务状态本身并行。
