SQLAlchemy Unified Tutorial

    The SQLAlchemy Unified Tutorial is integrated between the Core and ORM components of SQLAlchemy and serves as a unified introduction to SQLAlchemy as a whole. For users of SQLAlchemy within the 1.x series, in the 2.0 style of working, the ORM uses Core-style querying with the construct, and transactional semantics between Core connections and ORM sessions are equivalent. Take note of the blue border styles for each section, that will tell you how “ORM-ish” a particular topic is!

    Users who are already familiar with SQLAlchemy, and especially those looking to migrate existing applications to work under the SQLAlchemy 2.0 series within the 1.4 transitional phase should check out the SQLAlchemy 2.0 - Major Migration Guide document as well.

    For the newcomer, this document has a lot of detail, however by the end they will be considered an Alchemist.

    SQLAlchemy is presented as two distinct APIs, one building on top of the other. These APIs are known as Core and ORM.

    SQLAlchemy Core is the foundational architecture for SQLAlchemy as a “database toolkit”. The library provides tools for managing connectivity to a database, interacting with database queries and results, and programmatic construction of SQL statements.

    Sections that have a dark blue border on the right will discuss concepts that are primarily Core-only; when using the ORM, these concepts are still in play but are less often explicit in user code.

    Sections that have a light blue border on the left will discuss concepts that are primarily ORM-only. Core-only users can skip these.

    A section that has both light and dark borders on both sides will discuss a Core concept that is also used explicitly with the ORM.

    The tutorial will present both concepts in the natural order that they should be learned, first with a mostly-Core-centric approach and then spanning out into more ORM-centric concepts.

    The major sections of this tutorial are as follows:

    • Working with Transactions and the DBAPI - the usage API of the and its related objects Connection and are presented here. This content is Core-centric however ORM users will want to be familiar with at least the Result object.

    • covers the persistence framework of the ORM; basically the ORM-centric ways to insert, update and delete, as well as how to handle transactions.

    • Working with Related Objects introduces the concept of the construct and provides a brief overview of how it’s used, with links to deeper documentation.

    This tutorial is written using a system called . All of the code excerpts written with a are actually run as part of SQLAlchemy’s test suite, and the reader is invited to work with the code examples given in real time with their own Python interpreter.

    If running the examples, it is advised that the reader performs a quick check to verify that we are on version 2.0 of SQLAlchemy:

    Next Section: Establishing Connectivity - the Engine