8.1. SQLAlchemy About

  • ORM converts Python objects to database rows

  • ORM converts database rows to Python objects

  • ORM provides abstraction over database layer

  • ORM allows for object like interaction with database

  • ORM provides ability to migrate database schema

  • SQAlchemy is the most frequently used database ORM in Python [5]

It's not because you don't know how to write the query, it is because at the large scale you have to automate. It is about scaling up something which is very repetitive to do it by hand.

—Mike Bayer, SQLAlchemy creator [4]

8.1.1. ORM Pros

  • Support for database switching with minimal effort

  • Refactoring support (embedded SQL is not easily refactorable)

  • 1-to-1 relation of Python class to database table

  • Historical migration and change history

8.1.2. ORM Cons

  • Some queries could be not well optimized

  • Another layer of abstraction

  • Another dependency

8.1.3. Database Support

  • SQLite3

  • PostgreSQL

  • Oracle

  • MySQL / MariaDB

  • MSSQL

8.1.4. Installation

$ pip install sqlalchemy

>>> import sqlalchemy
>>>
>>>
>>> sqlalchemy.__version__ > '1.4'
True

8.1.5. Architecture

  • Core

  • ORM

  • Plugin structure with injection points

../../_images/sqlalchemy-architecture.png

Figure 8.1. SQLAlchemy architecture [3]

../../_images/sqlalchemy-onion.png

Figure 8.2. SQLAlchemy onion chart depicts layers [3]

../../_images/sqlalchemy-architecture-unitofwork-1.png

Figure 8.3. Unit of Work design pattern [7]

../../_images/sqlalchemy-architecture-unitofwork-2.png

Figure 8.4. Unit of Work design pattern [6]

8.1.6. 1.x vs. 2.x

  • future=True flag to create_engine()

  • On 2023-01-26 SQLAlchemy 2.0 has been released [8]

For SQLAlchemy 1.4, the RemovedIn20Warning deprecation class is emitted only when an environment variable SQLALCHEMY_WARN_20 is set to either of true or 1.

SQLALCHEMY_WARN_20=1 python -W always::DeprecationWarning myfile.py

8.1.7. Good Practices

  • Project Structure

  • What is the SQLAlchemy project layout

  • Where to store configuration (host, port, schema, username, password)

8.1.8. Alternative ORMs

  • Django ORM + Django Migrations [2]

  • SQLModel [1]

  • Raw SQL

  • SQLObject

  • Peewee

  • Tortoise ORM

  • PonyORM

  • Dejavu

../../_images/sqlalchemy-about-alternatives.png

Figure 8.5. ORM software popularity in Python community [5]

../../_images/sqlalchemy-about-databases.png

Figure 8.6. Database popularity in Python community [5]

8.1.9. References

8.1.10. References