Course contentsShow
← Data Science
Lesson 1118 of 2,14528. Working with Databases from PythonFree lesson

SQLAlchemy Core vs ORM

Distinguishing between SQLAlchemy's two interfaces: expression language and ORM layer.

SQLAlchemy Core vs ORM

What you'll learn: You'll understand the difference between SQLAlchemy's two interfaces and when to use each approach.

The Two Faces of SQLAlchemy

SQLAlchemy isn't just one tool—it's actually two complementary systems stacked on top of each other. Think of it like a building with two floors: the Core is the foundation level, and the ORM (Object-Relational Mapper) is built on top of it.

SQLAlchemy Core provides a SQL Expression Language—a Pythonic way to write SQL queries using functions and methods instead of raw strings. You still think in terms of tables, columns, and queries, but you construct them programmatically. Core gives you fine-grained control and stays close to SQL semantics.

SQLAlchemy ORM provides a higher-level abstraction where you work with Python classes and objects instead of tables and rows. You define models as classes, and SQLAlchemy handles translating between your Python objects and database rows automatically. When you query, you get back Python objects, not raw tuples.

When to Use Which?

Use Core when:

  • You need maximum control and performance
  • You're writing complex, specialized queries
  • Your workflow is query-centric rather than object-centric

Use ORM when:

  • You want to work with domain objects naturally in Python
  • You're building applications with complex business logic
  • You prefer thinking in terms of relationships between entities
  • You want built-in features like lazy loading and object tracking

The good news? You don't have to choose exclusively—SQLAlchemy lets you mix both approaches in the same application, using whichever fits each specific task best.

Key Takeaway: SQLAlchemy Core provides a SQL expression language for direct database interaction, while SQLAlchemy ORM adds an object-oriented layer on top, letting you work with Python classes instead of raw SQL constructs—choose based on whether you need control or convenience.