How To Automatically Close SQLite DB Connection and Cursor Object When Using Python ‘with as’ Statement

In the realm of database operations in Python, SQLite stands out as a lightweight and versatile option. Efficient resource management is crucial when dealing with database connections and cursors to ensure optimal performance and prevent potential issues. This article delves into the use of Python’s `with` statement for automatic closing of SQLite database connections and cursor objects, promoting clean and robust code.

1. Understanding the ‘with’ Statement in Python.

1. The `with` statement is a powerful tool in Python for resource management, providing a clean and concise way to handle resources such as files, network connections, and databases.
2. It ensures that resources are properly initialized before entering the block and automatically cleaned up upon exiting the block, even in the presence of exceptions.

2. Using ‘with’ for SQLite Database Connections.

1. When working with SQLite databases, the `sqlite3` module in Python allows you to create a database connection using the `with` statement. Here’s an example:

   import sqlite3
   
   # Using 'with' statement for automatic resource management
   with sqlite3.connect('example.db') as connection:
       # Database operations go here
       cursor = connection.cursor()
       cursor.execute("SELECT * FROM users")
       data = cursor.fetchall()
       print(data)
   

2. Output.

   <sqlite3.Cursor object at 0x000001B161F40030>
   [(2, 'Jerry'), (1, 'Tom')]

3. In this example, the `with` statement ensures that the `connection` is properly managed.
4. But when exiting the block, the connection is not automatically closed.
   

   import sqlite3
   
   # Using 'with' statement for automatic resource management
   with sqlite3.connect('example.db') as connection:
       # Database operations go here
       cursor = connection.cursor()
       cursor.execute("SELECT * FROM users")
       data = cursor.fetchall()
       print(data)
       # cursor.close()
       # connection.close()
   
   cursor.execute("SELECT * FROM users")
   data = cursor.fetchall()
   print(data)

5. Output.

   <sqlite3.Cursor object at 0x000001B161F40650>
   [(2, 'Jerry'), (1, 'Tom')]
   >>>
   <sqlite3.Cursor object at 0x000001B161F40650>
   [(2, 'Jerry'), (1, 'Tom')]

3. Automatically Closing Cursors.

1. To automatically close SQLite DB connection and cursor object when using Python `with as` statement, you can use the `contextlib.closing` function,.
2. Following is the example source code.

   import contextlib
   import sqlite3
   
   def test_sqlite_db_connection_auto_close():
   
       # Replace 'your_database.db' with the actual name of your database
       db_file = 'example.db'
   
       # Using contextlib.closing for automatic resource management
       with contextlib.closing(sqlite3.connect(db_file)) as conn:
           # Your database operations go here
           with contextlib.closing(conn.cursor()) as cursor:
               cursor.execute("SELECT * FROM users")
               rows = cursor.fetchall()
               print(rows)
   
           # The connection is automatically closed at this point
           cursor.execute("SELECT * FROM users")
           rows = cursor.fetchall()
           print(rows)
   
   if __name__ == "__main__":
       test_sqlite_db_connection_auto_close()
   

3. Output.

   [(2, 'Jerry'), (1, 'Tom')]
   Traceback (most recent call last):
     File "d:\WorkSpace\Work\python-courses\python-files-io\auto_close_sqlite_db_connection.py", line 23, in <module>
       test_sqlite_db_connection_auto_close()
     File "d:\WorkSpace\Work\python-courses\python-files-io\auto_close_sqlite_db_connection.py", line 18, in test_sqlite_db_connection_auto_close
       cursor.execute("SELECT * FROM users")
   sqlite3.ProgrammingError: Cannot operate on a closed cursor.

4. In this example, the sqlite3.connect returns a database connection object. And contextlib.closing ensures that the close method of the connection object is called when leaving the with block.
5. Then this code will automatically close the SQLite DB connection and cursor object, even if an exception occurs.