SQLAlchemy Core berbanding ORM

from sqlalchemy import create_engine
engine = create_engine('sqlite:///:memory:')
print('Engine:', engine)

from sqlalchemy import create_engine
engine = create_engine('sqlite:///app.db')
print('Dialect:', engine.dialect.name)

# Core: think in tables and SQL expressions
# ORM: think in Python classes and objects
print('Core = SQL toolkit, ORM = object mapping')

from sqlalchemy import MetaData, Table, Column, Integer, String
metadata = MetaData()
users = Table('users', metadata,
    Column('id', Integer, primary_key=True),
    Column('name', String))
print('Table defined:', users.name)

from sqlalchemy import MetaData, Table, Column, Integer, String, select
metadata = MetaData()
users = Table('users', metadata,
    Column('id', Integer, primary_key=True),
    Column('name', String))
stmt = select(users).where(users.c.name == 'Alice')
print(stmt)

from sqlalchemy.orm import DeclarativeBase, Mapped, mapped_column
class Base(DeclarativeBase):
    pass
class User(Base):
    __tablename__ = 'users'
    id: Mapped[int] = mapped_column(primary_key=True)
    name: Mapped[str]
print('Mapped class:', User.__tablename__)

# Core is great for:
# - bulk inserts/updates
# - complex analytical queries
# - close control over generated SQL
print('Core: control and performance')

# ORM is great for:
# - application domain models
# - automatic change tracking
# - relationships between objects
print('ORM: productivity and clarity')

from sqlalchemy import create_engine
engine = create_engine('sqlite:///:memory:')
# Core uses engine.connect()
# ORM uses a Session bound to the same engine
print('One engine powers both layers')

from sqlalchemy import create_engine
from sqlalchemy.orm import DeclarativeBase, Mapped, mapped_column
class Base(DeclarativeBase):
    pass
class User(Base):
    __tablename__ = 'users'
    id: Mapped[int] = mapped_column(primary_key=True)
engine = create_engine('sqlite:///:memory:')
Base.metadata.create_all(engine)
print('Tables created')

# Rule of thumb:
# Start with the ORM. Use Core where you need raw power.
print('Default ORM, escape to Core when needed')