Connection Best Practices

Database connections should be closed when you're done. Context managers handle this automatically.

import sqlite3

with sqlite3.connect("mydata.db") as conn:
    cursor = conn.cursor()
    cursor.execute("SELECT * FROM users")
    rows = cursor.fetchall()
# Connection automatically closes here

The with statement ensures the connection closes even if an error occurs.

For row factory (get dictionaries instead of tuples):

conn.row_factory = sqlite3.Row

with sqlite3.connect("mydata.db") as conn:
    conn.row_factory = sqlite3.Row
    cursor = conn.cursor()
    cursor.execute("SELECT * FROM users")

    for row in cursor.fetchall():
        print(row["name"])  # Access by column name

This makes code more readable than index-based access.

I cover connection handling in my SQL with Python course.