SQLAlchemy 1.4 / 2.0 Tutorial

    The new SQLAlchemy Tutorial is now integrated between Core and ORM and serves as a unified introduction to SQLAlchemy as a whole. In the new of working, fully available in the 1.4 release, the ORM now 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 SQLAlchemy 2.0 within the 1.4 transitional phase should check out the Migrating to SQLAlchemy 2.0 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.

    SQLAlchemy ORM builds upon the Core to provide optional object relational mapping capabilities. The ORM provides an additional configuration layer allowing user-defined Python classes to be mapped to database tables and other constructs, as well as an object persistence mechanism known as the Session. It then extends the Core-level SQL Expression Language to allow SQL queries to be composed and invoked in terms of user-defined objects.

    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 a more ORM-centric concepts.

    The major sections of this tutorial are as follows:

    • - all SQLAlchemy applications start with an Engine object; here’s how to create one.

    • - the usage API of the Engine and it’s related objects and Result are presented here. This content is Core-centric however ORM users will want to be familiar with at least the object.

    • Working with Data - here we learn how to create, select, update and delete data in the database. The so-called operations here are given in terms of SQLAlchemy Core with links out towards their ORM counterparts. The SELECT operation that is introduced in detail at Selecting Data applies equally well to Core and ORM.

    • lists a series of major top-level documentation sections which fully documents the concepts introduced in this tutorial.

    This tutorial is written using a system called doctest. 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 perform quick check to verify that we are on version 1.4 of SQLAlchemy:

    A Note on the Future

    This tutorial describes a new API that’s released in SQLAlchemy 1.4 known as . The purpose of the 2.0-style API is to provide forwards compatibility with SQLAlchemy 2.0, which is planned as the next generation of SQLAlchemy.

    In order to provide the full 2.0 API, a new flag called future will be used, which will be seen as the tutorial describes the and objects. These flags fully enable 2.0-compatibility mode and allow the code in the tutorial to proceed fully. When using the future flag with the function, the object returned is a sublass of sqlalchemy.engine.Engine described as . This tutorial will be referring to .

    SQLAlchemy 1.4 / 2.0 Tutorial