Explain in Detail SQLAlchemy Library in Python

 

SQLAlchemy in Python: A Comprehensive Guide 

SQLAlchemy is a powerful SQL toolkit and Object-Relational Mapper (ORM) for Python. It allows developers to interact with databases in a Pythonic way, abstracting SQL queries while maintaining high performance.

---

🔹 Why Use SQLAlchemy?

✅ ORM & Core: Supports both ORM (Object-Relational Mapping) and Core (direct SQL queries).
✅ Database Agnostic: Works with PostgreSQL, MySQL, SQLite, Oracle, and others.
✅ High Performance: Optimized SQL execution with lazy loading and connection pooling.
✅ Declarative API: Define database schemas using Python classes.
✅ Asynchronous Support: Works with async databases using asyncio.

---

🔹 Installing SQLAlchemy

To install SQLAlchemy, run:

pip install sqlalchemy

For MySQL support, install an additional driver:

pip install pymysql

For PostgreSQL, use:

pip install psycopg2

---

🔹 Connecting to a Database

SQLAlchemy uses connection strings to connect to databases.

from sqlalchemy import create_engine

# SQLite
engine = create_engine("sqlite:///example.db")

# PostgreSQL
# engine = create_engine("postgresql://user:password@localhost/dbname")

# MySQL
# engine = create_engine("mysql+pymysql://user:password@localhost/dbname")

print("Database connected successfully!")

create_engine() creates a connection to the database.

---

🔹 SQLAlchemy ORM (Object-Relational Mapping)

ORM lets you interact with a database using Python classes instead of raw SQL.

---

🔹 Defining a Database Model

In SQLAlchemy ORM, you define a model (table structure) using a Python class.

📌 Example: Defining a User Model

from sqlalchemy import Column, Integer, String
from sqlalchemy.ext.declarative import declarative_base

Base = declarative_base()  # Base class for models

class User(Base):
    __tablename__ = "users"  # Table name in the database
    id = Column(Integer, primary_key=True, autoincrement=True)
    name = Column(String(50), nullable=False)
    email = Column(String(100), unique=True, nullable=False)

print("User model created!")

📌 Explanation

• Base = declarative_base(): Creates a base class for all models.
• __tablename__: Defines the table name.
• Column(): Defines columns with data types (Integer, String, etc.).
• primary_key=True: Sets a primary key.
• nullable=False: Ensures mandatory fields.
• unique=True: Prevents duplicate values.

---

🔹 Creating the Database Table

After defining the models, we create the tables in the database.

from sqlalchemy.orm import sessionmaker

# Create the database engine
engine = create_engine("sqlite:///example.db")

# Create all tables
Base.metadata.create_all(engine)

print("Tables created successfully!")

---

🔹 CRUD Operations in SQLAlchemy

CRUD stands for Create, Read, Update, Delete. SQLAlchemy ORM simplifies these operations.

1️⃣ Creating a Database Session

A session allows us to interact with the database.

Session = sessionmaker(bind=engine)
session = Session()

---

2️⃣ Creating (Inserting) Data

new_user = User(name="Alice", email="alice@example.com")
session.add(new_user)  # Add to session
session.commit()  # Save to database
print("User added successfully!")

---

3️⃣ Reading (Fetching) Data

Fetch all users

users = session.query(User).all()
for user in users:
    print(user.id, user.name, user.email)

Fetch a single user by ID

user = session.query(User).filter_by(id=1).first()
print(user.name)

Using filter() for advanced queries

users = session.query(User).filter(User.name.like("%Alice%")).all()

---

4️⃣ Updating Data

user = session.query(User).filter_by(id=1).first()
user.name = "Alice Johnson"
session.commit()  # Save changes
print("User updated successfully!")

---

5️⃣ Deleting Data

user = session.query(User).filter_by(id=1).first()
session.delete(user)
session.commit()
print("User deleted successfully!")

---

🔹 SQLAlchemy Core (Executing Raw SQL Queries)

SQLAlchemy also allows you to write raw SQL queries.

from sqlalchemy import text

with engine.connect() as conn:
    result = conn.execute(text("SELECT * FROM users"))
    for row in result:
        print(row)

---

🔹 FastAPI + SQLAlchemy Integration

SQLAlchemy is commonly used with FastAPI for building APIs.

📌 Example: FastAPI with SQLAlchemy

from fastapi import FastAPI, Depends
from sqlalchemy.orm import Session
from database import SessionLocal, engine, Base
from models import User

app = FastAPI()

# Dependency to get the database session
def get_db():
    db = SessionLocal()
    try:
        yield db
    finally:
        db.close()

@app.get("/users/")
async def get_users(db: Session = Depends(get_db)):
    users = db.query(User).all()
    return users

---

🔹 SQLAlchemy Relationship (One-to-Many, Many-to-Many)

SQLAlchemy supports relationships between tables.

📌 One-to-Many Relationship

Example: A User can have multiple Posts.

from sqlalchemy import ForeignKey
from sqlalchemy.orm import relationship

class Post(Base):
    __tablename__ = "posts"
    id = Column(Integer, primary_key=True, autoincrement=True)
    title = Column(String(100), nullable=False)
    content = Column(String(500), nullable=False)
    user_id = Column(Integer, ForeignKey("users.id"))  # Foreign key to users table

    user = relationship("User", back_populates="posts")

class User(Base):
    __tablename__ = "users"
    id = Column(Integer, primary_key=True, autoincrement=True)
    name = Column(String(50), nullable=False)
    email = Column(String(100), unique=True, nullable=False)

    posts = relationship("Post", back_populates="user")

📌 Now, when fetching a user, we can also get their posts.

---

🔹 SQLAlchemy Async Support

SQLAlchemy supports async queries using asyncio.

📌 Async SQLAlchemy Example

from sqlalchemy.ext.asyncio import AsyncSession, create_async_engine

async_engine = create_async_engine("sqlite+aiosqlite:///example.db")
async_session = AsyncSession(async_engine)

---

🎯 Conclusion

SQLAlchemy is a powerful ORM & SQL toolkit that simplifies database interactions.

✅ Key Takeaways

• ORM Models: Define database tables using Python classes.
• CRUD Operations: Easily create, read, update, and delete records.
• Relationships: Supports One-to-Many and Many-to-Many relationships.
• FastAPI Integration: Works seamlessly with FastAPI.
• Async Support: Supports asyncio for high-performance applications.