Should I call connect() and close() for every Sqlite3 transaction?
Question: I want to write a Python module to abstract away database transactions for my
application. My question is whether I need to call `connect()` and `close()`
for every transaction? In code:

    
    
    import sqlite3
    
    # Can I put connect() here?
    conn = sqlite3.connect('db.py')
    
    def insert(args):
        # Or should I put it here?
        conn = sqlite3.connect('db.py')
        # Perform the transaction.
        c = conn.cursor()
        c.execute(''' insert args ''')
        conn.commit()
        # Do I close the connection here?
        conn.close()
    
    # Or can I close the connection whenever the application restarts (ideally, very rarely)
    conn.close()
    

I have don't much experience with databases, so I'd appreciate an explanation
for _why_ one method is preferred over the other.


Answer: You can use the same connection repeatedly. You can also use the connection
(and the cursor) as a context manager so that you don't need to explicitly
call `close` on either.

    
    
    def insert(conn, args):
        with conn.cursor() as c:
            c.execute(...)
        conn.commit()
    
    with connect('db.py') as conn:
        insert(conn, ...)
        insert(conn, ...)
        insert(conn, ...)
    

There's no reason to close the connection to the database, and re-opening the
connection each time can be expensive. (For example, you may need to establish
a TCP session to connect to a remote database.)

