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
| ORM | Used with |
|---|---|
| Django ORM | Django |
| SQLAlchemy | Flask, FastAPI, standalone |
| SQLModel | FastAPI (built on SQLAlchemy) |
| Peewee | Lightweight projects |
| Tortoise ORM | Async 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()
๐ฌ Comments (0)
No comments yet
Be the first to share your opinion about this article!