Read SQLite database with sqlite3 module in Python

Connect to a SQLite database and query rows with the standard library sqlite3 module, returning results as dictionaries.

Easy Python 3.9+ Aug 9, 2026 Files & data 17 views 0 copies

sqlite database stdlib sql query

Python code

37 lines
Python 3.9+
Run it 
import sqlite3
from pathlib import Path

# Create an in-memory database and a sample table
connection = sqlite3.connect(":memory:")
cursor = connection.cursor()

cursor.execute("""
CREATE TABLE employees (
    id INTEGER PRIMARY KEY,
    name TEXT NOT NULL,
    department TEXT NOT NULL,
    salary REAL
)
""")

# Insert sample data
employee_data = [
    (1, "Alice Johnson", "Engineering", 85000.0),
    (2, "Bob Smith", "Marketing", 72000.0),
    (3, "Carol Davis", "Engineering", 91000.0),
    (4, "David Wilson", "Sales", 68000.0),
]
cursor.executemany("INSERT INTO employees (id, name, department, salary) VALUES (?, ?, ?, ?)", employee_data)
connection.commit()

# Read all rows
print("All employees:")
for row in cursor.execute("SELECT * FROM employees"):
    print(dict(zip([desc[0] for desc in cursor.description], row)))

print("\nEngineering department:")
cursor.execute("SELECT name, salary FROM employees WHERE department = ?", ("Engineering",))
for name, salary in cursor.fetchall():
    print(f"{name}: ${salary:.2f}")

connection.close()

Output

stdout 
All employees:
{'id': 1, 'name': 'Alice Johnson', 'department': 'Engineering', 'salary': 85000.0}
{'id': 2, 'name': 'Bob Smith', 'department': 'Marketing', 'salary': 72000.0}
{'id': 3, 'name': 'Carol Davis', 'department': 'Engineering', 'salary': 91000.0}
{'id': 4, 'name': 'David Wilson', 'department': 'Sales', 'salary': 68000.0}

Engineering department:
Alice Johnson: $85000.00
Carol Davis: $91000.00

How it works

The sqlite3.connect function establishes a connection to a database file or an in-memory database. Using cursor.execute runs a single SQL statement, while executemany efficiently inserts multiple rows. Accessing cursor.description gives column metadata used to zip column names with row values into dictionaries. Parameterized queries with ? placeholders prevent SQL injection. Always call connection.close() to release database resources.

Common mistakes

  • Forgetting to commit after INSERT statements, losing data on close.
  • Not closing the connection, leading to resource leaks in longer-running apps.
  • Using string formatting for SQL values, which is vulnerable to SQL injection.
  • Calling fetchall twice on the same cursor, getting an empty list the second time.

Variations

  1. Use `sqlite3.Row` as the row_factory to access columns by name directly.
  2. Open the database with `with sqlite3.connect(...) as conn` to auto-commit and close.

Real-world use cases

  • Querying a local analytics database to generate daily reports for stakeholders.
  • Fetching configuration or reference data stored in an embedded SQLite file at application startup.
  • Extracting audit logs or event data from a legacy SQLite store for migration to a cloud database.

Sponsored

Run this sample

Open the browser IDE to tweak the example and see results without installing anything.

Open editor

More from Files & data

Keep learning

Related tutorials and quizzes for this topic.

Tutorials

Quizzes