Disconnecting Database in Python

When working with databases in Python, it's essential to properly close connections to free up resources and ensure data integrity. The close() method is used to disconnect from the database.

Basic Syntax

To disconnect a database connection, use the close() method ?

connection.close()

Complete Example with SQLite

Here's a complete example showing how to connect to a database, perform operations, and properly close the connection ?

import sqlite3

# Create connection
connection = sqlite3.connect(':memory:')  # In-memory database for demo
cursor = connection.cursor()

# Create a table and insert data
cursor.execute('''CREATE TABLE users (id INTEGER, name TEXT)''')
cursor.execute('''INSERT INTO users VALUES (1, 'Alice')''')

# Commit the transaction
connection.commit()

# Query the data
cursor.execute('''SELECT * FROM users''')
result = cursor.fetchall()
print("Data:", result)

# Close the connection
connection.close()
print("Database connection closed successfully")

Data: [(1, 'Alice')]
Database connection closed successfully

Best Practice with Exception Handling

Always use a try-finally block or context manager to ensure the connection is closed even if an error occurs ?

import sqlite3

connection = None
try:
    # Create connection
    connection = sqlite3.connect(':memory:')
    cursor = connection.cursor()
    
    # Perform database operations
    cursor.execute('''CREATE TABLE products (id INTEGER, price REAL)''')
    cursor.execute('''INSERT INTO products VALUES (1, 29.99)''')
    
    # Commit changes
    connection.commit()
    print("Operations completed successfully")
    
except sqlite3.Error as e:
    print(f"Database error: {e}")
    if connection:
        connection.rollback()  # Roll back on error
        
finally:
    if connection:
        connection.close()
        print("Connection closed in finally block")

Operations completed successfully
Connection closed in finally block

Using Context Manager

The most pythonic way is using a context manager that automatically handles closing ?

import sqlite3

# Context manager automatically closes connection
with sqlite3.connect(':memory:') as connection:
    cursor = connection.cursor()
    cursor.execute('''CREATE TABLE items (name TEXT)''')
    cursor.execute('''INSERT INTO items VALUES ('Book')''')
    
    cursor.execute('''SELECT * FROM items''')
    items = cursor.fetchall()
    print("Items:", items)
    
# Connection is automatically closed here
print("Connection automatically closed")

Items: [('Book',)]
Connection automatically closed

Important Notes

When a database connection is closed using the close() method, any outstanding transactions are automatically rolled back by the database. However, it's better practice to explicitly call commit() or rollback() before closing the connection rather than relying on the database's implementation details.

Conclusion

Always close database connections using close() to free resources. Use context managers or try-finally blocks to ensure connections are closed even when errors occur. Explicitly commit or rollback transactions before closing for better control.