Home / Python / Day 9: Automation & Databases / Working with SQLite Databases

Working with SQLite Databases

SQLite is a lightweight, file-based database built into Python via the sqlite3 module — perfect for small applications and learning SQL.

What is SQLite?

SQLite is a serverless, file-based relational database. Python includes the sqlite3 module in its standard library, so no installation is needed. The entire database lives in a single .db file.

Connecting and Cursors

sqlite3.connect("file.db") opens (or creates) a database file and returns a connection object. A cursor is used to execute SQL statements.

Creating Tables

Use CREATE TABLE IF NOT EXISTS with standard SQL syntax to define your schema.

Inserting Data Safely

Always use parameterized queries (placeholders ?) instead of string formatting to prevent SQL injection and handle special characters correctly.

Querying Data

cursor.execute("SELECT ...") followed by fetchone(), fetchall(), or iterating over the cursor retrieves results.

Committing and Closing

Changes (INSERT, UPDATE, DELETE) must be saved with connection.commit(). Always close the connection when done, or use a with statement for automatic cleanup.

Syntax

<pre><code>import sqlite3

# Connect (creates the file if it doesn't exist)
conn = sqlite3.connect("contacts.db")
cursor = conn.cursor()

# Create a table
cursor.execute("""
    CREATE TABLE IF NOT EXISTS contacts (
        id INTEGER PRIMARY KEY AUTOINCREMENT,
        name TEXT NOT NULL,
        email TEXT UNIQUE,
        phone TEXT
    )
""")

# Insert data safely with placeholders
cursor.execute(
    "INSERT INTO contacts (name, email, phone) VALUES (?, ?, ?)",
    ("Alice", "alice@example.com", "555-1234")
)

# Insert multiple rows
contacts = [
    ("Bob", "bob@example.com", "555-5678"),
    ("Charlie", "charlie@example.com", "555-9999"),
]
cursor.executemany(
    "INSERT INTO contacts (name, email, phone) VALUES (?, ?, ?)", contacts
)

# Save changes
conn.commit()

# Query data
cursor.execute("SELECT id, name, email FROM contacts WHERE name LIKE ?", ("A%",))
rows = cursor.fetchall()
for row in rows:
    print(row)

# Update data
cursor.execute("UPDATE contacts SET phone = ? WHERE name = ?", ("555-0000", "Alice"))
conn.commit()

# Delete data
cursor.execute("DELETE FROM contacts WHERE name = ?", ("Bob",))
conn.commit()

# Always close the connection
conn.close()

# Better: use a context manager
with sqlite3.connect("contacts.db") as conn:
    cur = conn.cursor()
    cur.execute("SELECT COUNT(*) FROM contacts")
    print(cur.fetchone()[0])
</code></pre>

Revision Notes

• sqlite3 is built into Python — no install required
• connect() opens/creates a .db file; cursor executes SQL
• Always use ? placeholders, never string-format SQL with user input
• commit() saves INSERT/UPDATE/DELETE changes
• fetchone() returns one row, fetchall() returns all rows as a list
• Use "with sqlite3.connect(...)" for automatic cleanup

Query Top Scores from SQLite

Medium

You have a SQLite table "scores" with columns (id, player, score). Write a function top_players(conn, limit) that returns the top `limit` players ordered by score descending, as a list of (player, score) tuples, using a parameterized query.

Input:

top_players(conn, 2) with rows [("Alice",90), ("Bob",75), ("Carl",95)]

Output:

[('Carl', 95), ('Alice', 90)]

Show Hint

Use "SELECT player, score FROM scores ORDER BY score DESC LIMIT ?" with cursor.execute() and fetchall().

Solve this Challenge

Show Solution

import sqlite3

def top_players(conn, limit):
    cursor = conn.execute(
        "SELECT player, score FROM scores ORDER BY score DESC LIMIT ?",
        (limit,)
    )
    return cursor.fetchall()

# Example setup
conn = sqlite3.connect(":memory:")
conn.execute("CREATE TABLE scores (id INTEGER PRIMARY KEY, player TEXT, score INTEGER)")
conn.executemany("INSERT INTO scores (player, score) VALUES (?, ?)", [("Alice",90), ("Bob",75), ("Carl",95)])
conn.commit()
print(top_players(conn, 2))

Automating Tasks with Python Connecting Python to MySQL