Do I need to put a commit in the with construct?

Question

Python

Python 3.7.3
PostgreSQL 10

import psycopg2

class User:

    def __init__(self, database):
        self.conn = psycopg2.connect(database)
        self.cursor = self.conn.cursor()

    def set_address(self, address, user_id):
        with self.conn:
            self.cursor.execute('UPDATE users SET address=%s WHERE user_id=%s', (user_id, address))
            self.conn.commit()

The with construct seems to imply closing the database connection, should I put self.conn.commit() ?

Answer 1

The with clause seems to imply closing the database connection.

does not include :

Cursors can be used as context managers: leaving the context will close the cursor.

The connection only closes the close() method of the connection object (or del on the connection).
Do commit() in your case is necessary, because. by default, psycopg opens a transaction before executing the first request. Another option is to work in autocommit mode .
In general, for the future, constructions with with do different things in different DBMS drivers and are not regulated by DB-API. So you need to either carefully smoke the documentation, or not use them.