将try / except与psycopg2或“ with close”一起使用？ [英] use try/except with psycopg2 or "with closing"?

问题描述

我正在Python中使用Psycopg2访问PostgreSQL数据库。我很好奇是否可以安全地使用 with close（）模式创建和使用游标，或者是否应该使用显式的 try / 除外。我的问题与插入或更新以及事务有关。

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/except wrapped around the query. My question is concerning inserting or updating, and transactions.

据我了解，所有Psycopg2查询都在事务内发生，这取决于调用代码来提交或回滚该事务。交易。如果在中带有close（...... $code>的块内发生错误，是否发出回滚？在旧版本的Psycopg2中，明确在上发出了回滚close（），但现在不再是这种情况了（请参阅 http://initd.org/psycopg/docs/connection.html#connection.close ）。

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).

我的问题可能通过一个例子更有意义。这是一个使用并关闭（...

My question might make more sense with an example. Here's an example using 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()

module.raise_unexpected_error（）引发错误时会发生什么情况？交易回滚了吗？ er需要提交或回滚它们。那么在这种情况下，会发生什么呢？

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?

或者我可以这样写我的查询：

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()

我还要提一下，我不知道Psycopg2的连接类是否为 cursor（）方法是否会引发错误（文档未说），所以比后悔要安全得多，不是吗？

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?

哪个我应该使用发出查询和管理事务的方法吗？

Which method of issuing a query and managing a transaction should I use?

推荐答案

您到Psycopg2文档的链接可以自行解释，不是吗？

Your link to the Psycopg2 docs kind of explains it itself, no?

...请注意，在不先提交更改的情况下关闭连接将使
导致所有待处理的更改都将被丢弃，就像执行了ROLLBACK
一样 （除非选择了不同的隔离级别：请参见
set_isolation_level（））。

... 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()).

在版本2.2中进行了更改：以前$ b $发出了明确的ROLLBACK b Psycopg在close（）上。该命令可能在不适当的时间以
发送到后端，因此 Psycopg当前依赖于
隐式丢弃未提交的更改。尽管在
事务期间关闭连接（状态为STATUS_IN_TRANSACTION时），例如，已知某些中间件
的行为不正确。 PgBouncer
报告服务器不干净并丢弃连接。为避免此
问题，您可以确保在结账前使用
commit（）/ rollback（）终止交易。

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.

因此，除非您使用其他隔离级别或使用PgBouncer，否则第一个示例应该可以正常工作。但是，如果您希望对事务期间发生的事情进行更细粒度的控制，则try / except方法可能是最好的方法，因为它与数据库事务状态本身平行。

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.

这篇关于将try / except与psycopg2或“ with close”一起使用？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！