Database Access with SQLite

Creating databases, CRUD operations, and using sqlite3 in Python.

What is SQLite?

SQLite is a lightweight, file-based database built into Python via the sqlite3 module — no server setup required.

Creating a Database and Table

import sqlite3

conn = sqlite3.connect("school.db")
cursor = conn.cursor()

cursor.execute("CREATE TABLE IF NOT EXISTS students ("
    "  id INTEGER PRIMARY KEY AUTOINCREMENT,"
    "  name TEXT NOT NULL, score REAL)")
conn.commit()

CRUD Operations

# INSERT
cursor.execute("INSERT INTO students (name, score) VALUES (?, ?)", ("Alice", 92.5))
cursor.execute("INSERT INTO students (name, score) VALUES (?, ?)", ("Bob", 78.0))
conn.commit()

# SELECT
cursor.execute("SELECT * FROM students")
for row in cursor.fetchall():
    print(row)

# UPDATE
cursor.execute("UPDATE students SET score = ? WHERE name = ?", (95, "Bob"))
conn.commit()

# DELETE
cursor.execute("DELETE FROM students WHERE score < ?", (50,))
conn.commit()

conn.close()

Using Row Factory

conn.row_factory = sqlite3.Row
cursor = conn.cursor()
cursor.execute("SELECT * FROM students")
for row in cursor.fetchall():
    print(row["name"], row["score"])