What happens to an open cursor after rolling back the transaction in Postgres using Psycopg2?

Question

I am trying to copy a list of CSV files into Postgres using Psycopg2's copy_expert().

I'm opening cursor to execute copy command for each file separately and closing it after the data has been copied. But, If I get any error for any file in this process, I'm rolling back the transaction.

If I get an error I'm not sure what's going to happen to the cursor that I have opened before copying the CSV file.

Will it be closed automatically after the rollback is done on the connection or will it stay just like that?

I've checked the docs for rollback on psycopg2 http://initd.org/psycopg/docs/connection.html#connection.rollback. But still, I'm unsure of what happens to the cursor that hasn't been closed as they haven't mentioned anything related to the cursor in the docs.

try:
    for tablename, filename in self.mapping:
        cur = self.conn.cursor()
        filename = f"{self.to_db}{wid}-{filename}"
        filename = f"{os.path.join(self.directory, filename)}.csv"
        sql = f"copy {tablename} from stdin with delimiter as ',' csv header;"
        with open(f"{filename}", 'r') as file:
            cur.copy_expert(sql, file)
        cur.close()
    self.conn.commit()
except Exception as e:
    self.conn.rollback()
    return e

Answer 1

You could make use the cursor's context manager, when the code leaves the `with´-block, the cursor is closed automatically.
Since you are firing the commit after the for-loop, there is no need to 're-create' the cursor every loop iteration.

class foo():
    def __init__(self):
        self.conn = psycopg2.connect('host=...')

    def whatever(self):
        with self.conn.cursor() as cur:
            try:
                for tablename, filename in self.mapping:
                    filename = f"{self.to_db}{wid}-{filename}"
                    filename = f"{os.path.join(self.directory, filename)}.csv"
                    sql = f"copy {tablename} from stdin with delimiter as ',' csv header;"
                    with open(f"{filename}", 'r') as file:
                        cur.copy_expert(sql, file)
                self.conn.commit()
            except Exception as e:
                self.conn.rollback()
                return e