What is an ORM

ORM (Object-Relational Mapping) is a technology that lets you work with a database through Python objects instead of writing raw SQL.

Without ORM (raw SQL)

import sqlite3

conn = sqlite3.connect('db.sqlite3')
cursor = conn.cursor()

cursor.execute("INSERT INTO tasks (title, status) VALUES (?, ?)", ('Task', 'todo'))
cursor.execute("SELECT * FROM tasks WHERE status = ?", ('todo',))
rows = cursor.fetchall()

With ORM (Django ORM)

from tasks.models import Task

# Create
task = Task.objects.create(title='Task', status='todo')

# Read
tasks = Task.objects.filter(status='todo')

# Update
task.status = 'done'
task.save()

# Delete
task.delete()

Advantages of ORM

• Security — protection against SQL injection
• Readability — Python code instead of SQL strings
• Portability — the same code works with SQLite, PostgreSQL, and MySQL
• Migrations — automatic schema updates

How ORM generates SQL

# Python
Task.objects.filter(status='todo', priority__gte=2)

# Generates SQL:
# SELECT * FROM tasks WHERE status = 'todo' AND priority >= 2

You can inspect the generated SQL:

qs = Task.objects.filter(status='todo')
print(qs.query)
# SELECT "tasks_task"."id", ... FROM "tasks_task" WHERE "tasks_task"."status" = 'todo'

Popular ORMs in Python

When ORM is not enough

For complex analytical queries, raw SQL is sometimes more efficient:

from django.db import connection

with connection.cursor() as cursor:
    cursor.execute("SELECT date_trunc('month', created_at), COUNT(*) FROM tasks GROUP BY 1")
    rows = cursor.fetchall()