Tutorial

Python SQLite Tutorial – Complete Database Guide

Master SQLite in Python with CRUD operations, parameterized queries, connection management, and best practices for secure database programming.

📌 Python SQLite, sqlite3, SQL Python, database Python, CRUD operations, parameterized queries

SQLite is a powerful relational database built directly into Python—no installation required. It's ideal for learning SQL fundamentals and building applications needing persistent data storage without database server overhead.

Why learn SQL before ORM? Understanding raw SQL provides insight into relational database principles, ability to debug and optimize queries, and confidence working with any database management system. ORMs are great, but SQL knowledge is invaluable.

SQLite is perfect for learning because it's built into Python, file-based (everything in one .db file), and supports full SQL standard. The sqlite3 module is your gateway to creating tables, inserting data, querying, and more—all within Python.

CRUD operations form the foundation of database programming: CREATE (INSERT) adds new records, READ (SELECT) retrieves data, UPDATE modifies existing records, and DELETE removes records. Master these four operations and you can work with any database.

Parameterized queries using ? placeholders are critical for security—they prevent SQL injection attacks by separating SQL logic from data values. Never use string formatting or f-strings to build queries with user input.

Code Examples

Creating Database and Connection

import sqlite3

# Connect to database (creates file if doesn't exist)
connection = sqlite3.connect('tasks.db')
cursor = connection.cursor()
print("Database created successfully!")
connection.close()

Creating Tables

import sqlite3

with sqlite3.connect('tasks.db') as connection:
    cursor = connection.cursor()
    cursor.execute('''
        CREATE TABLE IF NOT EXISTS tasks (
            id INTEGER PRIMARY KEY AUTOINCREMENT,
            title TEXT NOT NULL,
            completed BOOLEAN DEFAULT FALSE
        )
    ''')
    connection.commit()
    print("Table created successfully!")

# SQL keywords explained:
# CREATE TABLE - create new table
# IF NOT EXISTS - only create if doesn't exist
# INTEGER PRIMARY KEY - unique ID, auto-incrementing
# TEXT NOT NULL - required text field
# BOOLEAN DEFAULT FALSE - boolean with default value

INSERT - Adding Data

def add_task(title):
    with sqlite3.connect('tasks.db') as connection:
        cursor = connection.cursor()
        # Use ? placeholder for security
        cursor.execute(
            "INSERT INTO tasks (title) VALUES (?)",
            (title,)
        )
        connection.commit()
        print(f"Task '{title}' added!")

add_task("Learn Python")
add_task("Build a project")

SELECT - Reading Data

def get_all_tasks():
    with sqlite3.connect('tasks.db') as connection:
        cursor = connection.cursor()
        cursor.execute("SELECT * FROM tasks")
        return cursor.fetchall()

def get_task_by_id(task_id):
    with sqlite3.connect('tasks.db') as connection:
        cursor = connection.cursor()
        cursor.execute(
            "SELECT * FROM tasks WHERE id = ?",
            (task_id,)
        )
        return cursor.fetchone()

def show_tasks():
    tasks = get_all_tasks()
    for task in tasks:
        status = "✅" if task[2] else "⏳"
        print(f"{status} {task[1]}")

UPDATE - Modifying Data

def complete_task(task_id):
    with sqlite3.connect('tasks.db') as connection:
        cursor = connection.cursor()
        cursor.execute(
            "UPDATE tasks SET completed = TRUE WHERE id = ?",
            (task_id,)
        )
        connection.commit()
        print(f"Task {task_id} marked complete!")

def update_task_title(task_id, new_title):
    with sqlite3.connect('tasks.db') as connection:
        cursor = connection.cursor()
        cursor.execute(
            "UPDATE tasks SET title = ? WHERE id = ?",
            (new_title, task_id)
        )
        connection.commit()

DELETE - Removing Data

def delete_task(task_id):
    with sqlite3.connect('tasks.db') as connection:
        cursor = connection.cursor()
        cursor.execute(
            "DELETE FROM tasks WHERE id = ?",
            (task_id,)
        )
        connection.commit()
        print(f"Task {task_id} deleted!")

def clear_completed_tasks():
    with sqlite3.connect('tasks.db') as connection:
        cursor = connection.cursor()
        cursor.execute(
            "DELETE FROM tasks WHERE completed = TRUE"
        )
        connection.commit()

Complete CRUD Application

import sqlite3

class TaskManager:
    def __init__(self, db_name='tasks.db'):
        self.conn = sqlite3.connect(db_name)
        self.create_table()

    def create_table(self):
        cursor = self.conn.cursor()
        cursor.execute('''
            CREATE TABLE IF NOT EXISTS tasks (
                id INTEGER PRIMARY KEY AUTOINCREMENT,
                title TEXT NOT NULL,
                completed BOOLEAN DEFAULT FALSE
            )
        ''')
        self.conn.commit()

    def add(self, title):
        cursor = self.conn.cursor()
        cursor.execute(
            "INSERT INTO tasks (title) VALUES (?)",
            (title,)
        )
        self.conn.commit()
        return cursor.lastrowid

    def get_all(self):
        cursor = self.conn.cursor()
        cursor.execute("SELECT * FROM tasks")
        return cursor.fetchall()

    def complete(self, task_id):
        cursor = self.conn.cursor()
        cursor.execute(
            "UPDATE tasks SET completed = TRUE WHERE id = ?",
            (task_id,)
        )
        self.conn.commit()

    def delete(self, task_id):
        cursor = self.conn.cursor()
        cursor.execute(
            "DELETE FROM tasks WHERE id = ?",
            (task_id,)
        )
        self.conn.commit()

    def close(self):
        self.conn.close()

# Usage
manager = TaskManager()
manager.add("Write Python code")
manager.add("Test database")
manager.complete(1)
print(manager.get_all())
manager.close()