SQLAlchemy - The Python SQL Toolkit and Object Relational Mapper

Hi, Community!

This article is an overview of SQLAlchemy, so let's begin!

SQLAlchemy is the Python SQL toolkit that serves as a bridge between your Python code and the relational database system of your choice. Created by Michael Bayer, it is currently available as an open-source library under the MIT License. SQLAlchemy supports a wide range of database systems, including PostgreSQL, MySQL, SQLite, Oracle, and Microsoft SQL Server, making it versatile and adaptable to different project requirements.

The SQLAlchemy SQL Toolkit and Object Relational Mapper from a comprehensive set of tools for working with databases and Python. It has several distinct areas of functionality which you can use individually or in various combinations. The major components are illustrated below, with component dependencies organized into layers:

As you can see above, the two most significant front-facing portions of SQLAlchemy are the Object Relational Mapper (ORM) and the Core.

Object Relational Mapper(ORM)

The ORM component provides a way to map Python objects to database tables. It simplifies writing code that interacts with databases. The ORM builds upon Core to provide the means of working with a domain object model mapped to a database schema. When you utilize it, you usually construct SQL statements in almost the same way as with Core. However, the task of DML, which refers to the persistence of business objects in a database here, is automated with the help of a pattern called the unit of work. It translates changes in states against mutable objects into INSERT, UPDATE, and DELETE constructs that then get invoked in terms of those objects. SELECT statements are augmented by ORM-specific automation and object-centric querying capabilities as well.

Below, you can observe a basic example of defining an ORM class in SQLAlchemy:

from sqlalchemy import Column, Integer, String
from sqlalchemy.ext.declarative import declarative_base

Base = declarative_base()

class User(Base):
    __tablename__ = 'users'
    id = Column(Integer, primary_key=True)
    username = Column(String(50), unique=True)
    email = Column(String(100))

With this class, you can easily create, query, update, and delete user records in your database.

 

Core

The Core component provides the underlying functionality for interacting with databases, including connection pooling, transaction management, and SQL expression generation. The central element of both SQLAlchemy Core and ORM is the SQL Expression Language that facilitates fluent and composable construction of SQL queries. The central element of both SQLAlchemy Core and ORM is the SQL Expression Language that facilitates fluent and composable construction of SQL queries.

The SQL Expression Language is a toolkit on its own, independent of the ORM package. It provides a system of constructing SQL expressions represented by composable objects, which can then be “executed” against a target database within the scope of a specific transaction.

The ORM is built upon Core to provide the means of working with a domain object model mapped to a database schema. When you utilize it, you usually construct SQL statements in almost the same way as with Core. However, the task of DML, which refers to the persistence of business objects in a database here, is automated with the help of a pattern called the unit of work. It translates changes in states against mutable objects into INSERT, UPDATE, and DELETE constructs that then get invoked in terms of those objects. SELECT statements are augmented by ORM-specific automation and object-centric querying capabilities as well.

Whereas working with Core and the SQL Expression language offers a schema-centric view of the database, along with an immutability-oriented programming paradigm, the ORM builds a domain-centric view of the database on top of this, with a programming paradigm that is more explicitly object-oriented and reliant upon mutability. Since a relational database is a mutable service itself, the difference is that whereas Core/SQL Expression language is command-oriented, the ORM is state-oriented.

Check out an example of a simple SQLAlchemy query below:

from sqlalchemy import create_engine, text

engine = create_engine('sqlite:///my_database.db')
conn = engine.connect()

query = text("SELECT username FROM users WHERE id = :user_id")
result = conn.execute(query, user_id=1)

SQLAlchemy incorporates a connection pooling system that efficiently manages database connections. It automatically handles connection creation, reuse, and release, reducing overhead and improving performance of applications with frequent database interactions.

Simple Use case of SQLAlchemy CRUD operations

1. Establish Connectivity - the Engine
2. Define your database tables
3. Use ORM to save data
4. Retrieve data by using ORM

1. Establishing Connectivity - the Engine

Every SQLAlchemy application starts with an object called the Engine. This object acts as a central source of connections to a particular database, providing both a factory and a holding space called a connection pool for these database connections. The Engine is typically a global object that needs to be created only once for a particular database server. It is usually configured using a URL string that describes how it should connect to the database host or backend.

We will use an in-memory-only SQLite database. The Engine we will assemble by using the create_engine() function:

from sqlalchemy import create_engine
engine = create_engine("sqlite+pysqlite:///:memory:", echo=True)

2.  Defining your database tables

To operate the ORM, first, we need to define database tables utilizing Python classes. For example, the following Python class defines a User table:

from sqlalchemy import SQLAlchemy

db = SQLAlchemy()

class User(db.Model):
    id = db.Column(db.Integer, primary_key=True)
    email = db.Column(db.String(150), unique=True)
    username = db.Column(db.String(150), unique=True)
    password = db.Column(db.String(150))

3.  Using ORM to save data

To access the User table, we should import the User from our model class and then exploit the session object to add data:

from models import User
from sqlalchemy import SQLAlchemy

db = SQLAlchemy()

new_user = User(email="email address", username="username", password="Password"))
db.session.add(new_user)
db.session.commit()

4. Retrieving data by using ORM

We will employ the query object of the session to retrieve data from the database:

from models import User
from sqlalchemy import SQLAlchemy

db = SQLAlchemy()

# Query the User table
users = db.session.query(User).all()

# Iterate over the records and print them to the console
for user in users:
    print(user.username)

 

Use Cases for SQLAlchemy

SQLAlchemy is widely used in various domains, including the next ones:

• Web Development: Many web frameworks, such as Flask and Django, integrate seamlessly with SQLAlchemy for managing databases.
• Data Analysis and Reporting: SQLAlchemy helps data scientists and analysts interact with relational databases when performing data analysis.
• Enterprise Applications: It is a preferred choice for building robust, scalable, and maintainable enterprise-level applications.
• Microservices: SQLAlchemy supports microservices architecture by enabling each service to interact with its database.

 

Benefits of Using SQLAlchemy

SQLAlchemy has many benefits, some of which are listed below:

• Increased productivity:  SQLAlchemy can help developers to be more productive by providing a high-level abstraction for database access. It lets developers focus on writing application code rather than being worried about the underlying SQL syntax.
• Reduced errors:  SQLAlchemy can help us to reduce errors by providing a type system for database access. It allows us to ensure that developers use the correct data types and do not make any mistakes.
• Improved maintainability: SQLAlchemy can assist in the improvement of the maintainability of the code by making it easier to change the database schema. It is possible because SQLAlchemy code is not tightly coupled to the underlying database schema.
• Portability: SQLAlchemy code is portable to any database supported by SQLAlchemy. It simplifies the migration of applications to different databases.

Conclusion

SQLAlchemy empowers Python developers to work with relational databases in a more pythonic and efficient manner. Its ORM capabilities, SQL expression language, connection pooling, and cross-platform support make it a valuable tool for various applications. Whether you are a beginner or an experienced developer, SQLAlchemy can streamline your database-related tasks and improve your productivity.

In my next article, we will use SQLAlchemy and InterSystems Caché to demonstrate CRUD operations.

Thank you for reading!