Create, Read, Update, Delete with sqlite3, Tutorial - Python SQLite part I , #34

Create, Read, Update, Delete with sqlite3 in Python

This tutorial demonstrates how to use Python's built-in sqlite3 module to manage a simple employee database. You will learn to connect to a database, create tables, insert and query data safely using parameterized statements, and perform update and delete operations with confirmation.

Code

import sqlite3

# Connect to SQLite database (creates file if not exists)
with sqlite3.connect('employees.db') as conn:
  # Use Row factory for dict-like access to columns
  conn.row_factory = sqlite3.Row
  cursor = conn.cursor()

  # Create table if it doesn't exist
  cursor.execute('''
    CREATE TABLE IF NOT EXISTS employees (
      id INTEGER PRIMARY KEY AUTOINCREMENT,
      name TEXT NOT NULL,
      department TEXT NOT NULL,
      salary REAL NOT NULL,
      active INTEGER NOT NULL DEFAULT 1
    )
  ''')

  # Insert multiple employees safely using parameter placeholders
  employees = [
    ('Alice', 'HR', 70000),
    ('Bob', 'Engineering', 90000),
    ('Charlie', 'Sales', 60000)
  ]
  cursor.executemany('INSERT INTO employees (name, department, salary) VALUES (?, ?, ?)', employees)

  # Query all active employees ordered by salary descending
  cursor.execute('SELECT * FROM employees WHERE active = 1 ORDER BY salary DESC')
  for row in cursor.fetchall():
    print(f"{row['id']}: {row['name']} ({row['department']}) - ${row['salary']}")

  # Update an employee's salary by id
  cursor.execute('UPDATE employees SET salary = ? WHERE id = ?', (95000, 2))
  print(f"Rows updated: {cursor.rowcount}")

  # Soft delete an employee by setting active to 0
  cursor.execute('UPDATE employees SET active = 0 WHERE id = ?', (3,))
  print(f"Rows updated (soft delete): {cursor.rowcount}")

  # Count active employees
  cursor.execute('SELECT COUNT(*) FROM employees WHERE active = 1')
  active_count = cursor.fetchone()[0]
  print(f"Active employees count: {active_count}")

Key Points

• Use sqlite3.connect() with a context manager to ensure safe connection handling.
• Create tables with CREATE TABLE IF NOT EXISTS to avoid errors on repeated runs.
• Use parameterized queries with ? placeholders to prevent SQL injection.
• Set row_factory = sqlite3.Row for convenient dict-like access to query results.
• Perform updates and soft deletes with confirmation via cursor.rowcount to verify affected rows.