Sqlite Orm

Master database operations with SQLite and SQLAlchemy ORM for building data-driven applications

Learn Python, JavaScript, Java and more with free interactive lessons, real projects and AI-powered help. Beginner-friendly.

Part of the free Python course at LearnCodingFast — hands-on lessons with examples you run in your browser, plus practice exercises and a quick quiz.

What Is SQLite?

SQLite is a file-based, zero-configuration database that's perfect for learning, prototyping, and small-to-medium applications.

✓ When to Use SQLite

✗ When Not to Use SQLite

Key benefits: No server setup, cross-platform, single file database, used by Chrome, VS Code, and countless apps.

Using SQLite Directly (sqlite3 Module)

Python includes sqlite3 built-in for direct database operations.

What Is an ORM?

ORM (Object-Relational Mapper) maps database tables to Python classes and rows to objects.

Without ORM (Raw SQL):

With ORM (SQLAlchemy):

Benefits of ORMs:

Setting Up SQLAlchemy

Install SQLAlchemy and create the basic infrastructure for database operations.

Basic CRUD Operations

Create, Read, Update, and Delete operations using SQLAlchemy ORM.

Defining Relationships (One-to-Many)

Model real-world connections between data with foreign keys and relationships.

Advanced Query Patterns

Filtering, ordering, pagination, and complex queries with SQLAlchemy.

Many-to-Many Relationships

Handle complex relationships like posts with multiple tags using association tables.

Performance Optimization

Eager loading, indexing, and query optimization to build fast applications.

Real-World Example: Notes Application

A complete CRUD application demonstrating practical ORM usage.

Production Project Structure

Organize your database code for maintainability and scalability.

Summary

You've learned comprehensive database development with SQLite and SQLAlchemy:

SQLAlchemy is the industry-standard ORM used in FastAPI, Flask, and countless Python applications. These patterns apply to any SQL database - simply change the connection string to switch from SQLite to PostgreSQL, MySQL, or others.

📋 Quick Reference — SQLite & ORMs

You can now interact with databases using raw sqlite3 and SQLAlchemy ORM — the foundation of any data-driven Python application.

Up next: REST API Clients — build robust HTTP clients that consume real-world APIs.

Practice quiz

Which built-in module lets Python talk to a SQLite database directly?

  • sqlalchemy
  • pysqlite
  • sqlite3
  • dbapi

Answer: sqlite3. sqlite3 ships with Python's standard library for direct SQLite operations.

What does sqlite3.connect(":memory:") create?

  • A temporary in-memory database
  • A connection to a file named memory.db
  • A read-only database
  • A network connection

Answer: A temporary in-memory database. ":memory:" makes a database that lives in RAM — perfect for tests and demos.

Why use ? placeholders in cursor.execute(sql, params)?

  • To make queries shorter
  • To sort the results
  • They are required by SQLite
  • To prevent SQL injection

Answer: To prevent SQL injection. Parameterised queries with ? safely escape values, preventing SQL injection.

What does cursor.fetchone() return after a SELECT?

  • A list of all rows
  • A single row as a tuple (or None)
  • The number of rows
  • A dictionary

Answer: A single row as a tuple (or None). fetchone() returns the next row as a tuple, or None when there are no more rows.

What does cursor.fetchall() return?

  • A list of all remaining rows (each a tuple)
  • One row
  • A count of rows
  • A boolean

Answer: A list of all remaining rows (each a tuple). fetchall() returns every remaining row as a list of tuples.

After modifying data, which call saves the changes to the database?

  • conn.save()
  • conn.flush()
  • conn.commit()
  • conn.write()

Answer: conn.commit(). conn.commit() persists pending INSERT/UPDATE/DELETE changes; without it they can be lost.

What does an ORM map a database table to?

  • A Python function
  • A Python class
  • A dictionary
  • A SQL string

Answer: A Python class. An ORM maps tables to classes, rows to object instances, and columns to attributes.

In SQLAlchemy, a table row corresponds to what?

  • A class
  • A column
  • A query
  • An object instance

Answer: An object instance. Each row becomes an object instance, e.g. user = User(name='Alice').

What is the N+1 query problem?

  • Running one query too many by mistake
  • Loading a list, then firing a separate query per item for related data
  • A syntax error in SQL
  • Using too many indexes

Answer: Loading a list, then firing a separate query per item for related data. 1 query loads the parents, then N more load each parent's relations — fixed with eager loading.

Which SQLAlchemy option avoids the N+1 problem with a single JOIN?

  • lazy_load()
  • select_all()
  • joinedload(User.posts)
  • prefetch()

Answer: joinedload(User.posts). joinedload eagerly loads the relationship in one JOIN query instead of N separate ones.