14.2 Introduction to SQLAlchemy

NicePython

— 5 min read

SQLAlchemy is a popular and powerful Python library used for interacting with relational databases such as PostgreSQL, MySQL, and SQLite. It provides a high-level interface that allows developers to interact with databases in a more Pythonic way, without writing raw SQL. SQLAlchemy supports both the Object Relational Mapping (ORM) and SQL Expression Language layers, giving developers flexibility in how they interact with databases.

This section will introduce the core concepts of SQLAlchemy, how it simplifies database interactions, and the fundamental components you need to get started.

14.2.1 What is SQLAlchemy?

SQLAlchemy is a comprehensive library that provides tools to:

  1. Connect to databases and manage connections.
  2. Query and manipulate data using Python classes and objects (through ORM).
  3. Perform complex database queries using SQL-like syntax (through SQL Expression Language).
  4. Manage database schemas using migrations and DDL (Data Definition Language) statements.

SQLAlchemy provides two primary layers for interacting with databases:

  1. Core Layer (SQL Expression Language): Enables you to write SQL-like queries in Python, giving full control over SQL queries.
  2. ORM (Object Relational Mapper): Maps database tables to Python classes and rows to Python objects, enabling you to interact with databases in an object-oriented way.

14.2.2 Key Concepts of SQLAlchemy

SQLAlchemy introduces several concepts that you should understand before diving deeper into its usage:

1. Engine

The Engine is the starting point of any SQLAlchemy application. It manages the connection to the database and translates Python commands into SQL queries. The engine allows you to interact with the database using either the Core or ORM approach.

    • create_engine() creates a new Engine object that is connected to the specified PostgreSQL database.
    • The connection string contains the database type (PostgreSQL), username, password, host, and database name.

Engine Creation:

from sqlalchemy import create_engine

# Create an engine that connects to a PostgreSQL database
engine = create_engine('postgresql://testuser:password@localhost/testdb')

Here:

2. Session

A Session is a central concept in SQLAlchemy ORM, representing the workspace where database operations are performed. The session manages transactions and keeps track of all changes made to objects (insert, update, delete) before committing them to the database.

Session Creation:

from sqlalchemy.orm import sessionmaker

# Create a session factory
Session = sessionmaker(bind=engine)

# Create a session
session = Session()

In this example, the sessionmaker binds the session to the database engine, and the session allows us to perform transactions.

3. Declarative Base

The declarative base is a foundational class from which all SQLAlchemy ORM models (Python classes) are derived. The declarative base allows us to define the database schema using Python classes.

Creating the Declarative Base:

from sqlalchemy.ext.declarative import declarative_base

# Create the base class for ORM models
Base = declarative_base()

4. ORM (Object Relational Mapper)

SQLAlchemy’s ORM allows you to map database tables to Python classes. Each class represents a table, and each class attribute represents a column in the table.

    • User is a Python class that represents the users table.
    • __tablename__ defines the table name.
    • Each Column represents a field in the table, with a data type and constraints (e.g., primary key).

Defining a Model (Table):

from sqlalchemy import Column, Integer, String

class User(Base):
    __tablename__ = 'users'

    id = Column(Integer, primary_key=True)
    name = Column(String)
    age = Column(Integer)

In this example:

14.2.3 SQLAlchemy ORM Workflow

Let’s walk through the basic workflow of SQLAlchemy’s ORM, from creating tables to performing database operations like adding, querying, updating, and deleting records.

1. Connecting to a Database

To begin, you create an engine that connects to your PostgreSQL database:

from sqlalchemy import create_engine

# Create the engine that connects to the PostgreSQL database
engine = create_engine('postgresql://testuser:password@localhost/testdb')

2. Defining Models (Tables)

You define your database schema by creating Python classes that inherit from the Base class. Each class corresponds to a table in the database.

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

# Create the base class for ORM models
Base = declarative_base()

# Define the User model
class User(Base):
    __tablename__ = 'users'

    id = Column(Integer, primary_key=True)
    name = Column(String)
    age = Column(Integer)

3. Creating Tables in the Database

To create tables in the database based on your models, use the Base.metadata.create_all() method.

# Create the tables in the database
Base.metadata.create_all(engine)

This command will automatically generate the SQL statements necessary to create the users table in the database.

4. Adding Data to the Database

Now, you can use the session to add new records (rows) to the database.

from sqlalchemy.orm import sessionmaker

# Create a session
Session = sessionmaker(bind=engine)
session = Session()

# Create a new user
new_user = User(name='Alice', age=25)

# Add the new user to the session
session.add(new_user)

# Commit the transaction (save changes to the database)
session.commit()

In this example:

  • We create a new user object (new_user) and add it to the session.
  • The session.commit() call saves the changes to the database.

5. Querying Data from the Database

SQLAlchemy ORM makes querying the database simple and Pythonic.

# Query all users
users = session.query(User).all()

# Print user details
for user in users:
    print(f"ID: {user.id}, Name: {user.name}, Age: {user.age}")

This example queries all users from the users table and prints their details.

6. Updating Data in the Database

To update data in SQLAlchemy, you can modify the attributes of an object and commit the changes.

# Query the user to update
user_to_update = session.query(User).filter_by(name='Alice').first()

# Update the user's age
user_to_update.age = 26

# Commit the changes
session.commit()

In this example, we first query for a user named "Alice," update their age, and then commit the changes to the database.

7. Deleting Data from the Database

To delete a record, simply query for the object and call session.delete() followed by session.commit().

# Query the user to delete
user_to_delete = session.query(User).filter_by(name='Alice').first()

# Delete the user
session.delete(user_to_delete)

# Commit the changes
session.commit()

In this example, we delete the user named "Alice" and commit the changes.

14.2.4 Benefits of Using SQLAlchemy

  1. Pythonic Interface: SQLAlchemy allows developers to interact with databases using Python classes and objects, reducing the need to write raw SQL.
  2. Cross-database Support: SQLAlchemy works with multiple databases (e.g., PostgreSQL, MySQL, SQLite) without requiring changes to your code.
  3. ORM Flexibility: SQLAlchemy's ORM provides full control over how you map Python classes to database tables, making complex database operations easy to manage.
  4. Declarative Mapping: Using SQLAlchemy’s declarative base, developers can define database models in a straightforward, readable way.
  5. Abstraction of Complex SQL: While SQLAlchemy supports writing raw SQL, its ORM and SQL Expression Language can abstract complex queries into simple Python code.
  6. Migrations Support: SQLAlchemy integrates with tools like Alembic for managing database migrations, making schema changes easier to manage.

14.2.5 SQLAlchemy Core vs. ORM

SQLAlchemy offers two layers for database interaction: Core and ORM.

SQLAlchemy Core

  • Low-level layer that interacts directly with the database using SQL.
  • Suitable for developers who want more control over raw SQL queries.
  • Supports complex SQL queries and statements.

SQLAlchemy ORM

  • High-level layer that abstracts SQL into Python classes and objects.
  • Ideal for developers who prefer object-oriented programming.
  • Simplifies common database operations like inserting, querying, updating, and deleting records.

Here’s a comparison of SQLAlchemy Core vs. ORM:

Feature SQLAlchemy Core SQLAlchemy ORM
Query Language SQL-like expressions in Python Object-oriented queries
Control Fine control over raw SQL Abstraction of SQL into Python objects
Complexity More complex for basic queries

Simplifies basic database operations |
| Use Cases | Advanced SQL queries, optimizations | Object-oriented applications |

14.2.6 Summary

  • SQLAlchemy is a powerful Python library that provides both Object Relational Mapping (ORM) and SQL Expression Language for interacting with databases.
  • The Engine is the connection to the database, while the Session manages transactions and changes.
  • The ORM allows developers to define database schemas as Python classes and work with rows as objects.
  • SQLAlchemy offers a high-level abstraction for performing common database operations such as adding, querying, updating, and deleting records.
  • The Core layer provides direct control over SQL, while the ORM layer offers a more Pythonic and object-oriented interface.

By using SQLAlchemy, you can work efficiently with databases in Python, taking advantage of its powerful ORM and SQL handling capabilities.

Read more