Lesson 18: Working with Databases (SQLite)

What Is SQLite?

SQLite is a lightweight, file‑based database engine included with Python. It requires no server setup and is perfect for small to medium‑sized applications, prototypes, and learning SQL.

Connecting to a Database

Use Python’s built‑in sqlite3 module:

import sqlite3

connection = sqlite3.connect("example.db")
cursor = connection.cursor()

Creating a Table

cursor.execute("""
CREATE TABLE IF NOT EXISTS users (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    name TEXT,
    age INTEGER
)
""")
connection.commit()

Inserting Data

cursor.execute("INSERT INTO users (name, age) VALUES (?, ?)", ("Alice", 30))
connection.commit()

Querying Data

cursor.execute("SELECT * FROM users")
rows = cursor.fetchall()

for row in rows:
    print(row)

Updating Data

cursor.execute("UPDATE users SET age = ? WHERE name = ?", (31, "Alice"))
connection.commit()

Deleting Data

cursor.execute("DELETE FROM users WHERE name = ?", ("Alice",))
connection.commit()

Using Context Managers

You can simplify database handling using with:

with sqlite3.connect("example.db") as conn:
    cursor = conn.cursor()
    cursor.execute("SELECT * FROM users")
    print(cursor.fetchall())

Why SQLite Matters

• It requires no installation or server.
• It is perfect for learning SQL.
• It works well for small applications and prototypes.
• It integrates seamlessly with Python.

Common SQL Commands

• CREATE TABLE — create a new table
• INSERT INTO — add new data
• SELECT — retrieve data
• UPDATE — modify existing data
• DELETE — remove data

Next Steps

Now that you can work with SQLite databases, you're ready to explore how to build graphical applications in Lesson 19: Introduction to GUI Programming (Tkinter).

← Back to Lesson Index