SQLite - Python

SQLite and Python

Python has built-in support for SQLite via the sqlite3 module, which is part of the standard library. This makes it incredibly easy to integrate a powerful, file-based database into your Python applications without external dependencies. This tutorial covers modern best practices for using sqlite3.

Core Concepts

The sqlite3 module provides an SQL interface compliant with the DB-API 2.0 specification (PEP 249). The main objects you will work with are:

• Connection Object: Represents the database connection. Used to manage transactions (commit, rollback) and create Cursor objects.
• Cursor Object: Used to execute SQL queries, fetch results, and inspect query metadata.

Connecting to a Database

The best practice for managing connections is using a with statement, which automatically closes the connection even if errors occur.

# Modern Python 3 Syntax
import sqlite3

db_file = 'company_database.db'

try:
    # The 'with' statement ensures the connection is closed automatically
    with sqlite3.connect(db_file) as conn:
        print(f"Successfully connected to database {db_file}")
        # You can now create a cursor and execute queries
        # cursor = conn.cursor()
except sqlite3.Error as e:
    print(f"Database error: {e}")

# To use an in-memory database, pass ":memory:"
# with sqlite3.connect(':memory:') as conn:

Executing Queries: Best Practices

Always use parameterized queries (with ? placeholders) to prevent SQL injection vulnerabilities. Never use f-strings or string formatting to insert variable data into your queries.

CREATE TABLE

import sqlite3

# Using a context manager for the connection and cursor
def create_table(conn):
    try:
        cursor = conn.cursor()
        cursor.execute('''
            CREATE TABLE IF NOT EXISTS employees (
                id INTEGER PRIMARY KEY,
                name TEXT NOT NULL,
                department TEXT NOT NULL,
                salary REAL
            );
        ''')
        print("Table 'employees' created or already exists.")
    except sqlite3.Error as e:
        print(f"Error creating table: {e}")

with sqlite3.connect('company_database.db') as conn:
    create_table(conn)

INSERT Operation

To insert single records, use cursor.execute(). For multiple records, cursor.executemany() is much more efficient.

import sqlite3

def add_employee(conn, name, department, salary):
    sql = 'INSERT INTO employees(name, department, salary) VALUES(?, ?, ?)'
    try:
        cursor = conn.cursor()
        cursor.execute(sql, (name, department, salary))
        conn.commit() # Commit the transaction to save changes
        print(f"Added employee: {name}")
    except sqlite3.Error as e:
        print(f"Error adding employee: {e}")
        conn.rollback() # Rollback on error

def add_multiple_employees(conn, employees_list):
    sql = 'INSERT INTO employees(name, department, salary) VALUES(?, ?, ?)'
    try:
        cursor = conn.cursor()
        cursor.executemany(sql, employees_list)
        conn.commit()
        print(f"Added {len(employees_list)} new employees.")
    except sqlite3.Error as e:
        print(f"Error adding multiple employees: {e}")
        conn.rollback()

with sqlite3.connect('company_database.db') as conn:
    # Single insert
    add_employee(conn, 'Alice', 'Engineering', 80000.00)

    # Multiple inserts
    new_staff = [
        ('Bob', 'Marketing', 65000.00),
        ('Charlie', 'Engineering', 95000.00),
        ('Diana', 'HR', 72000.00)
    ]
    add_multiple_employees(conn, new_staff)

SELECT Operation

To make results easier to work with, you can set the connection’s row_factory to sqlite3.Row. This allows you to access columns by name, like a dictionary.

import sqlite3

def get_engineers(conn):
    # Set row_factory to access columns by name
    conn.row_factory = sqlite3.Row

    sql = 'SELECT * FROM employees WHERE department = ? ORDER BY name'
    try:
        cursor = conn.cursor()
        cursor.execute(sql, ('Engineering',))

        engineers = cursor.fetchall() # Use fetchall(), fetchone(), or fetchmany()

        print("\n--- Engineering Team ---")
        for emp in engineers:
            print(f"ID: {emp['id']}, Name: {emp['name']}, Salary: ${emp['salary']:.2f}")

    except sqlite3.Error as e:
        print(f"Error fetching data: {e}")

with sqlite3.connect('company_database.db') as conn:
    get_engineers(conn)

UPDATE Operation

import sqlite3

def give_raise(conn, employee_id, new_salary):
    sql = 'UPDATE employees SET salary = ? WHERE id = ?'
    try:
        cursor = conn.cursor()
        cursor.execute(sql, (new_salary, employee_id))
        conn.commit()
        if cursor.rowcount == 0:
            print(f"No employee found with ID {employee_id}.")
        else:
            print(f"Updated salary for employee {employee_id}. Total changes: {conn.total_changes}")
    except sqlite3.Error as e:
        print(f"Error updating data: {e}")
        conn.rollback()

with sqlite3.connect('company_database.db') as conn:
    give_raise(conn, 1, 85000.00) # Give Alice a raise

DELETE Operation

import sqlite3

def delete_employee(conn, employee_id):
    sql = 'DELETE FROM employees WHERE id = ?'
    try:
        cursor = conn.cursor()
        cursor.execute(sql, (employee_id,))
        conn.commit()
        if cursor.rowcount == 0:
            print(f"No employee found with ID {employee_id}.")
        else:
            print(f"Deleted employee with ID {employee_id}.")
    except sqlite3.Error as e:
        print(f"Error deleting data: {e}")
        conn.rollback()

with sqlite3.connect('company_database.db') as conn:
    delete_employee(conn, 2) # Delete Bob

Further Learning: ORMs

For larger applications, writing raw SQL can become tedious. Object-Relational Mappers (ORMs) allow you to interact with your database using Python objects. They can improve productivity and reduce errors. Consider exploring popular libraries like:

• SQLAlchemy: A powerful and comprehensive ORM and SQL toolkit, suitable for large-scale applications.
• Peewee: A simple, small, and expressive ORM, great for smaller projects.