Для чего нужен commit, connection, cursor и close?

Question

Базовый пример отсюда

import psycopg2

# Connect to an existing database
conn = psycopg2.connect("dbname=test user=postgres")

# Open a cursor to perform database operations
cur = conn.cursor()

# Execute a command: this creates a new table
cur.execute("CREATE TABLE test (id serial PRIMARY KEY, num integer, data varchar);")

# Pass data to fill a query placeholders and let Psycopg perform
# the correct conversion (no more SQL injections!)
cur.execute("INSERT INTO test (num, data) VALUES (%s, %s)",
...      (100, "abc'def"))

# Query the database and obtain data as Python objects
cur.execute("SELECT * FROM test;")
cur.fetchone()

# Make the changes to the database persistent
conn.commit()

# Close communication with the database
cur.close()
conn.close()

Для каких нужд используется cur.close(), conn.commit(), conn.close? В чём их отличия?

Например conn.commit(), как указано в примере, применяет изменения. Но для меня кажется логичнее, что за это будет отвечать курсор, потому как именно он является итератором по данным.

Answer 1

Судя по той же документации, правильный шаблон использования будет примерно такой:

conn = psycopg2.connect(DSN)
try:

    with conn:
        with conn.cursor() as curs:
            curs.execute(SQL1)
    
    with conn:
        with conn.cursor() as curs:
            curs.execute(SQL2)

finally:
    conn.close()

Что тут имеется в виду.

connection живёт столько, сколько нужно вам для ваших операций, если у вас операции с базой идут подряд - connection не надо закрывать и переоткрывать; но если вы поработали с базой, а потом у вас перерыв - вы, например, перемалываете какие-то данные и пока базу не пишете и не читаете, то connection лучше закрыть, чтобы она ушла в пул и другим процессам, работающим с базой, хватило этих самых connection; и да - connection лучше закрывать в конце работы с ней и делать это наверняка (через try/finally), чтобы точно освободить связанные с ней ресурсы
transaction лучше создавать и закрывать с помощью блока with conn: - если не будет брошено исключение в течении работы блока with, то будет автоматически сделан commit, а если будет исключение - будет выполнен rollback; в одном блоке транзакции нужно объединять некий неразрывный блок работы с базой, который должен быть откачен целиком в случае неудачи, а в случае успешного завершения запись данных этого блока опять же должна представлять из себя в базе фрагмент данных, который ничего не поломает, будучи записанным в базу сам по себе
cursor - похоже, в приведённом мной шаблоне использования это просто объект, который позволяет выполнять любые операции записи/чтения внутри одной транзакции и он сам закроется по окончании блока with conn.cursor() as curs:

Немного странно, что в приведённом вами примере без with курсор получается закрывается уже после commit, видимо, можно делать и так и так. Если курсор не закрывать самому, то он, видимо, остаётся открытым всё время существования connection. Но опять же, согласно документации, лучше курсор обязательно закрыть (и удобнее сделать это неявно с помощью блока with), чтобы он точно освободил какие-то ресурсы, которые на него выделены. Хотя, наверняка, закрытие connection и так освободит все ресурсы.

В общем, мне кажется, тут есть довольно большая свобода выбора шаблона, однако, блоки with позволяют более чётко разделить этапы работы программы с базой, если таковые этапы имеют место быть.

P.S. Конкретно по вашим вопросам отдельно:

Я напихал в БД 20000 записей при помощи 100 батчей. Нужно ли мне создавать новое соединение?

Если вы работаете с базой непрерывно, то новое соединение создавать не нужно.

А курсор? При этом, я считаю, что у меня будут ещё записи.

Опять же - это зависит от того, планируете ли вы держать и дальше открытым соединение, можно ли вашу работу записывать в базу по частям, и какой шаблон работы с базой вы выберете. При непрерывной работе с базой похоже можно открыть соединение и курсор один раз, а потом, при необходимости, делать коммиты после каждого куска выполненной работы (если она в вашем случае может быть записана в базу этими кусками и ничего при этом не поломается).

P.P.S. Отвечая на тему вопроса - в той же документации написано, что если не включен autocommit mode, при котором connection сам делает commit после каждой операции с курсором, то если не сделать после операций с курсором явный commit и при этом закрыть connection, то можно остаться в результате с грязными данными - состояние ваших данных в базе будет неопределённое (но это сильно зависит от конкретной базы данных), в худшем случае транзакция может вообще подвиснуть и залочить дальнейшие операции с базой до принудительной перезагрузки сервера БД. Нормальная БД в случае потери коннекшена, конечно, сделает rollback, но лучше до такого в любом случае не доводить.

Answer 2

В упрощенном виде:

connection представляет реальную базу данных на диске вашего или другого компьютера.
cursor — это объект в памяти вашего компьютера с методами для проведения SQL команд, хранения итогов их выполнения (например части таблицы или вьюшкы (view)) и методов доступа к ним.

Теперь к вашим вопросам:

Пока вы работаете с курсором, вы только «играетесь» (например вы только готовитесь что-то изменить в реальной базе данных). Без применения connection.commit() итоги вашей «игры» потеряете — они не запишутся в реальную базу данных.

Нужно добавить, что когда вы только читаете из базы данных, этой команды не надо.
cursor.close() освобождает память, в которой вы «игрались».
connection.close() освобождает память, нужную для управления связью с базой данных. (Эта команда соответствует закрытию файла - «закрывает» базу данных.)

Терминологическое примечание:

Файлы открываются (open), но к базам данных присоединяются (connect).

БЛОГ НА HUSL

Для чего нужен commit, connection, cursor и close?

Ответы (2 шт):