ORM with SQLAlchemy and Django ORM

ORM with SQLAlchemy and Django ORM: Complete Guide

ORM (Object-Relational Mapping) is a programming technique that allows you to interact with a database using objects instead of writing SQL queries. ORM tools help manage the translation between object-oriented programming concepts and relational databases. In Python, two of the most popular ORM tools are SQLAlchemy and Django ORM.

Let's dive into both ORMs in detail.

1. SQLAlchemy ORM

SQLAlchemy is a widely used SQL toolkit and ORM for Python. It provides a set of high-level API functions that allow developers to interact with databases in a more Pythonic manner.

Installing SQLAlchemy

pip install sqlalchemy

Setting Up a Database Connection

from sqlalchemy import create_engine
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.orm import sessionmaker

# Create an engine that knows how to connect to the database
engine = create_engine('sqlite:///example.db', echo=True)

# Declare a base class
Base = declarative_base()

# Create a session class
Session = sessionmaker(bind=engine)
session = Session()

Defining Models (Classes)

You define classes that inherit from Base, which represent tables in your database.

from sqlalchemy import Column, Integer, String

class User(Base):
    __tablename__ = 'users'
    
    id = Column(Integer, primary_key=True)
    name = Column(String)
    email = Column(String)

    def __repr__(self):
        return f""

Creating the Table

To create the table in the database:

Base.metadata.create_all(engine)

Performing CRUD Operations

Create (Insert)

new_user = User(name="Alice", email="alice@example.com")
session.add(new_user)
session.commit()

Read (Query)

user = session.query(User).filter_by(name="Alice").first()
print(user)

Update

user.name = "Alice Updated"
session.commit()

Delete

session.delete(user)
session.commit()

2. Django ORM

Django ORM is a part of the Django web framework that helps you interact with the database using Python objects.

Setting Up Django ORM

Install Django:

pip install django

Start a Django project and create an app:

django-admin startproject myproject
cd myproject
python manage.py startapp myapp

Define models in models.py:

from django.db import models

class User(models.Model):
    name = models.CharField(max_length=100)
    email = models.EmailField()

    def __str__(self):
        return self.name

Migrate the models to the database:

python manage.py makemigrations
python manage.py migrate

Django CRUD Operations

Django provides an easy-to-use QuerySet API for interacting with the database.

Create (Insert)

user = User.objects.create(name="Alice", email="alice@example.com")

Read (Query)

user = User.objects.get(name="Alice")
print(user)

Update

user.name = "Alice Updated"
user.save()

Delete

user.delete()

Key Differences Between SQLAlchemy ORM and Django ORM

Feature	SQLAlchemy ORM	Django ORM
Integration	Used as an independent library	Integrated into the Django framework
Configuration	More flexible, needs manual configuration	Comes pre-configured in Django projects
Database Support	Wide support for various databases	Primarily focused on relational DBs
Querying Style	SQL-like queries, more control over SQL	High-level, abstract query interface
Learning Curve	Steeper learning curve	Easier for Django developers

When to Use SQLAlchemy or Django ORM?

Use SQLAlchemy when:

You need to build flexible, standalone applications
You want more control over your database queries
Your project does not require the full Django framework

Use Django ORM when:

You are building a web application using Django
You need an integrated solution with the Django framework
You want to take advantage of Django's built-in admin interface

Example: Full Example with SQLAlchemy and Django ORM

SQLAlchemy Example

from sqlalchemy import create_engine
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.orm import sessionmaker

# Database setup
engine = create_engine('sqlite:///mydb.db')
Base = declarative_base()
Session = sessionmaker(bind=engine)
session = Session()

# Define User model
class User(Base):
    __tablename__ = 'users'
    id = Column(Integer, primary_key=True)
    name = Column(String)
    email = Column(String)

    def __repr__(self):
        return f""

# Create table
Base.metadata.create_all(engine)

# Insert new user
new_user = User(name="John", email="john@example.com")
session.add(new_user)
session.commit()

# Fetch user
user = session.query(User).filter_by(name="John").first()
print(user)

Django ORM Example

models.py

from django.db import models

class User(models.Model):
    name = models.CharField(max_length=100)
    email = models.EmailField()

    def __str__(self):
        return self.name

CRUD in Django Shell:

python manage.py shell

from myapp.models import User

# Create a user
user = User.objects.create(name="John", email="john@example.com")

# Fetch user
user = User.objects.get(name="John")
print(user)

# Update user
user.name = "John Updated"
user.save()

# Delete user
user.delete()

Conclusion

Both SQLAlchemy ORM and Django ORM provide powerful tools to work with databases in Python. If you're working within the Django ecosystem, Django ORM is your best bet. For standalone applications or non-Django projects, SQLAlchemy offers more flexibility and control.