Why do you need Commit, Connection, Cursor and Close?

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 lives as long as you need for your operations, if you have consecutive database operations, connection does not need to be closed and reopened; but if you have worked with the database, and then you have a break - for example, you are grinding some data and while you are not writing or reading the database, it is better to close the connection so that it goes into the pool and other processes working with the database have enough of these connections; and yes, it is better to close a connection at the end of working with it and do it for sure (via try/finally) in order to release the resources associated with it
• transaction better create and close using the with conn block: - if an exception is not thrown during the operation of the with block, a commit will be performed automatically, and if there is an exception, a rollback will be performed; in one block of a transaction, you need to combine some kind of inseparable block of work with the database, which must be completely pumped out in case of failure, and in case of successful completion, the data entry of this block should again be a fragment of data in the database that will not break anything, being written to the database by itself
• cursor - it seems that in the usage patte I've given, it's just an object that allows you to perform any write/read operations within a single transaction, and it will close itself at the end of the block with conn.cursor() as curs:

It's a bit strange that in the example you gave, without with, the cursor closes after commit. Apparently, you can do both. If you don't close the cursor yourself, then it probably remains open for the entire duration of the connection. But again, according to the documentation, it is better to close the cursor (and it is more convenient to do this implicitly using the with block) so that it releases some resources that are allocated to it. Although, for sure, closing the connection will free up all resources anyway.

In general, it seems to me that there is quite a lot of freedom to choose a template, however, the with blocks allow you to more clearly separate the stages of the program's work with the database, if any.

P.S. Specifically for your questions separately:

I stuffed 20,000 records into the database using 100 batches. Do I need to create a new connection?

If you are working with the database continuously, then you do not need to create a new connection.

And the cursor? At the same time, I believe that I will have more recordings.

Again, it depends on whether you plan to keep the connection open, whether your work can be recorded in the database in parts, and which template you choose for working with the database. When working continuously with the database, it looks like you can open a connection and a cursor once, and then, if necessary, make commits after each piece of work done (if in your case it can be written to the database in these pieces and nothing breaks).

P.P.S. Answering the subject of the question, the same documentation says that if autocommit mode is not enabled, in which connection itself does commit after each cursor operation, then if it is not done explicitly after cursor operations commit and at the same time close the connection, you may end up with dirty data - the state of your data in the database will be uncertain (but it strongly depends on the specific database), in the worst case, the transaction may hang altogether and lock up further database operations until the database server is forced to reboot. A normal database will, of course, make a rollback in case of connection loss, but it's better not to bring it to that in any case.