ReviseAlgo Logo

Database Access

SQLite with Python

Built-in relational database — sqlite3 module for creating databases, tables, CRUD operations, transactions, and parameterized queries.

Interview: Database fundamentals — SQLite is used in mobile apps, embedded systems, and prototyping.

Last Updated: June 12, 2026 • 9 min read

SQLite is a lightweight, serverless, file-based relational database. It's built into Python's standard library via the sqlite3 module — no installation required. SQLite is perfect for prototyping, embedded applications, and small-to-medium datasets.

Getting Started

  • sqlite3.connect("file.db") — connect to a database file (creates it if missing)
  • sqlite3.connect(":memory:") — in-memory database (great for testing)
  • conn.cursor() — create a cursor for executing SQL
  • conn.commit() — save changes (required for INSERT/UPDATE/DELETE)
  • conn.close() — close the connection (always use context managers!)

CRUD Operations

  • Create: INSERT INTO table VALUES (?, ?) — always use placeholders!
  • Read: SELECT * FROM table WHERE id = ? — fetchone(), fetchall(), fetchmany()
  • Update: UPDATE table SET col = ? WHERE id = ?
  • Delete: DELETE FROM table WHERE id = ?

Security: Parameterized Queries

  • NEVER use string formatting/f-strings for SQL — leads to SQL injection
  • Always use ? placeholders: cursor.execute("SELECT * WHERE id = ?", (user_id,))
  • For named parameters: :name with cursor.execute("...", {"name": value})

Interview Insight

SQLite is serverless — it's a file, not a service. Perfect for embedded apps, prototyping, and testing. Always use parameterized queries (?) to prevent SQL injection. Use context managers for connection handling. In-memory databases (:memory:) are great for unit tests.

Common Pitfall

SQL injection via string formatting: f"SELECT * WHERE name = '{user_input}'". If user_input is '; DROP TABLE users; --, you lose your table. Always use ? placeholders.

Use Cases

Prototyping — quick database setup without a server

Testing — in-memory databases for isolated unit tests

Embedded apps — mobile apps, desktop applications

Data analysis — SQLite as a queryable file format

Configuration storage — app settings in a structured database

Common Mistakes

SQL injection via string formatting — ALWAYS use ? placeholders

Forgetting conn.commit() — changes are not saved without commit

Not closing connections — use context managers or try/finally

Concurrent writes — SQLite has limited write concurrency (one writer at a time)

Not using row_factory — default tuple access (row[0]) is error-prone