SQLAlchemy is a Python SQL toolkit and Object-Relational Mapping (ORM) library that provides a full suite of well-known enterprise-level persistence patterns. It has two main components:

• SQLAlchemy Core: A schema-centric model that provides a Pythonic way of working with databases using SQL expressions
• SQLAlchemy ORM: An object-relational mapper built on top of Core that allows you to work with database records as Python objects
  The main benefits include database abstraction, connection pooling, transaction management, and a powerful query API that works across different database engines.

SQLAlchemy Core is a lower-level, schema-centric approach that works directly with tables, columns, and SQL expressions. It's closer to raw SQL and offers more control.
SQLAlchemy ORM is a higher-level, object-centric approach that maps database tables to Python classes and rows to object instances.

# Core approach
from sqlalchemy import text
result = connection.execute(text("SELECT * FROM users WHERE id = :user_id"), {"user_id": 1})
# ORM approach
user = session.query(User).filter(User.id == 1).first()

Core is typically faster and more explicit, while ORM provides more abstraction and is easier for complex object relationships.

A Session in SQLAlchemy ORM is the primary interface for persistence operations. It represents a "workspace" for your objects and acts as a holding zone for all objects you've loaded or created until you commit the changes to the database.
Key characteristics:

• Identity Map: Ensures one object instance per database row per session
• Unit of Work: Tracks changes and flushes them to database in transactions
• Transaction Management: Handles database transactions automatically

from sqlalchemy.orm import sessionmaker
Session = sessionmaker(bind=engine)
session = Session()
# Add new object
user = User(name="John")
session.add(user)
session.commit()  # Persists to database

• add(): Adds a single new object to the session. If object already exists, raises an error.
• merge(): Merges the state of an object into the session. If object exists, updates it; if not, creates new one.
• add_all(): Adds multiple objects to the session at once.

# add() - for new objects
session.add(User(name="Alice"))
# merge() - for existing or uncertain objects
user = session.merge(User(id=1, name="Updated Alice"))
# add_all() - for multiple objects
session.add_all([User(name="Bob"), User(name="Charlie")])

A SQLAlchemy model is defined by creating a class that inherits from a declarative base and includes table metadata:

from sqlalchemy import Column, Integer, String, create_engine
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.orm import sessionmaker
Base = declarative_base()
class User(Base):
    __tablename__ = 'users'
    id = Column(Integer, primary_key=True)
    name = Column(String(50), nullable=False)
    email = Column(String(100), unique=True)
    def __repr__(self):
        return f"<User(name='{self.name}', email='{self.email}')>"