How to reduce nesting, or am I doing something wrong?

Question

Python

Hello!
I have shit code where I interact with the database, namely PostgreSQL. Library: Psycopg2.
The fact is that the nesting of the shit code turns out to be quite large, and it seems to me that I am doing something wrong.
Here is the code:

with psycopg2.connect('данные бд') as conn:
    with conn.cursor() as cursor:
        # для примера:
        cursor.execute('SELECT name FROM users WHERE id = %s', [id])
        result_name = [x[0] for x in cursor.fetchall()]
        with psycopg2.connect('данные бд') as conn: # получается вложенность
            with conn.cursor() as cursor: # без этой вложенности - получается ошибка
                cursor.execute('SELECT name FROM confirm WHERE name = %s', [result_name[0]])
                result_confirm = [x[0] for x in cursor.fetchall()]
                if result_confirm.__len__() == 0:
                    ...

If you do not register these nestings, then an error occurs: connection already is closed, so you have to clutter up the already shitty code.
How to fix it? Or is this how it should be?

Answer 1

It is not entirely clear on the example what exactly is wrong, but this option should work:

with psycopg2.connect('данные бд') as conn:
    with conn.cursor() as cursor:
        cursor.execute('SELECT name FROM users WHERE id = %s', [id])
        result_name = [x[0] for x in cursor.fetchall()]
    with conn.cursor() as cursor:
        cursor.execute('SELECT name FROM confirm WHERE name = %s', [result_name[0]])
        result_confirm = [x[0] for x in cursor.fetchall()]

Perhaps you exit the block somewhere, or close the connection yourself?
In general, I would recommend using SQLAlchemy , it is simple as a stick, but working with the database is much easier.

Answer 2

figure out why "connection already is closed" occurs, and not make unnecessary connections