Getting Started with SQLAlchemy ORM for Python

SQLAlchemy is a powerful Object-Relational Mapping (ORM) library for Python that bridges the gap between database systems and Python code.

Instead of writing raw SQL queries, SQLAlchemy allows you to interact with your database using familiar Python objects and methods.

This tutorial will guide you through using the basic SQLAlchemy with SQLite to build a simple data-driven application step by step.

By the end of this tutorial, you'll understand how to:

• Set up a SQLAlchemy project with SQLite
• Define database models
• Perform CRUD operations (Create, Read, Update, Delete)

Prerequisites

Before starting this tutorial, ensure you have:

• Python 3.13 or newer installed
• Basic understanding of Python programming
• Familiarity with SQL concepts is helpful but not required

Step 1 — Setting up your SQLAlchemy project

In this section, you'll create your project directory structure, set up a Python virtual environment, and install SQLAlchemy.

First, create a directory for the project and navigate into it:

mkdir sqlalchemy-tutorial && cd sqlalchemy-tutorial

Next, create a Python virtual environment. This creates an isolated environment for installing packages:

python3 -m venv venv

Activate the virtual environment to start using it:

source venv/bin/activate

Now that your virtual environment is active, install SQLAlchemy using pip:

pip install sqlalchemy

This installs the latest version of SQLAlchemy. You'll use SQLite as your database backend since it doesn't require a separate server installation and stores data in a single file, making it perfect for learning and small applications.

With SQLAlchemy installed, you're ready to create your first database connection.

Step 2 — Understanding SQLAlchemy components and creating your first model

In this section, you'll learn about the core components of SQLAlchemy and create your first database model. Understanding these components is essential for building practical database-driven applications with SQLAlchemy.

Before diving into code, let's understand the main components of SQLAlchemy:

• Engine: The core interface to the database, handling connections to a particular database server
• Session: Manages persistence operations (i.e., saving objects to the database)
• MetaData: A registry that tracks which tables exist for a particular database
• Declarative Base: A factory that creates a base class for our model definitions
• Models: Python classes that map to tables in our database

Let's create a basic script called database.py that sets up these components:

from sqlalchemy import create_engine
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.orm import sessionmaker

engine = create_engine('sqlite:///library.db', echo=True)

# Create a base class for our models
Base = declarative_base()

# Create a session factory bound to our engine
SessionLocal = sessionmaker(bind=engine)

This script sets up the foundation for your SQLAlchemy database.

The create_engine function establishes a connection to an SQLite database named library.db. If the file doesn't exist, SQLite will automatically create it. The echo=True parameter enables SQL logging, which helps you see the generated SQL queries.

The Base class, created using declarative_base(), serves as the parent class for all your database models. Any model you define will inherit from this class, allowing SQLAlchemy to map Python classes to database tables.

Finally, SessionLocal is a factory function that creates session instances. These sessions allow you to interact with the database by executing queries, inserting records, and managing transactions efficiently.

For this tutorial, you'll build a simple library management system. Let's now start by defining a model for books.

Create a file called models.py:

from sqlalchemy import Column, Integer, String, Float, Date
from database import Base

class Book(Base):
    __tablename__ = 'books'

    id = Column(Integer, primary_key=True)
    title = Column(String(200), nullable=False)
    author = Column(String(100), nullable=False)
    pages = Column(Integer)
    publication_date = Column(Date)
    price = Column(Float)

    def __repr__(self):
        return f"<Book(title='{self.title}', author='{self.author}')>"

This code defines a Book class that maps to a 'books' table in the database.

The __tablename__ attribute specifies the table name.

Several columns are defined with different data types:

• id: A primary key that auto-increments.
  
• title: A string field that cannot be null (nullable=False).
  
• author: A string field that cannot be null (nullable=False).
  
• pages: An integer representing the number of pages.
  
• publication_date: A date field.
  
• price: A floating-point number representing the book's price.
  

When printed, the __repr__ method provides a string representation of Book objects, making inspecting them in the console easier.

Now, let's create our database tables. Create a file called create_tables.py:

from database import engine, Base
from models import Book

# Create all tables defined in our models
Base.metadata.create_all(engine)

print("Database tables created!")

Run this script to create the tables:

python create_tables.py

You should see output similar to:

2025-03-13 11:47:42,921 INFO sqlalchemy.engine.Engine BEGIN (implicit)
2025-03-13 11:47:42,921 INFO sqlalchemy.engine.Engine PRAGMA main.table_info("books")
2025-03-13 11:47:42,921 INFO sqlalchemy.engine.Engine [raw sql] ()
2025-03-13 11:47:42,921 INFO sqlalchemy.engine.Engine PRAGMA temp.table_info("books")
2025-03-13 11:47:42,921 INFO sqlalchemy.engine.Engine [raw sql] ()
2025-03-13 11:47:42,922 INFO sqlalchemy.engine.Engine 
CREATE TABLE books (
        id INTEGER NOT NULL, 
        title VARCHAR(200) NOT NULL, 
        author VARCHAR(100) NOT NULL, 
        pages INTEGER, 
        publication_date DATE, 
        price FLOAT, 
        PRIMARY KEY (id)
)


2025-03-13 11:47:42,922 INFO sqlalchemy.engine.Engine [no key 0.00009s] ()
2025-03-13 11:47:42,923 INFO sqlalchemy.engine.Engine COMMIT
Database tables created!

From this output, you can see that SQLAlchemy first checks whether the books table already exists by executing PRAGMA main.table_info("books").

Since the table does not exist, it generates and runs a CREATE TABLE statement. This statement defines all the specified columns and their respective data types and constraints.

Once the table creation is complete, the transaction is committed to finalize the changes.

At this point, the library.db file is created in your project directory, containing an empty 'books' table, ready for storing book records.

Step 3 — Adding data to your database

In this section, you'll learn how to insert records into your SQLite database using SQLAlchemy's ORM approach. You'll create book objects, add them to the database, and see how SQLAlchemy handles the conversion between Python objects and database records.

Now that your database structure is set up with the Book model, let's add some data. Create a file called add_books.py:

from datetime import date
from database import SessionLocal
from models import Book

# Create a session
session = SessionLocal()

# Create book objects
books = [
    Book(
        title="The Hobbit",
        author="J.R.R. Tolkien",
        pages=295,
        publication_date=date(1937, 9, 21),
        price=19.99
    ),
    Book(
        title="1984",
        author="George Orwell",
        pages=328,
        publication_date=date(1949, 6, 8),
        price=14.95
    ),
    Book(
        title="To Kill a Mockingbird",
        author="Harper Lee",
        pages=281,
        publication_date=date(1960, 7, 11),
        price=12.99
    )
]

# Add books to the session
session.add_all(books)

# Commit the transaction
session.commit()

# Print the books with their new IDs
for book in books:
    print(f"Added: {book} with ID: {book.id}")

# Close the session
session.close()

First, you created a session using SessionLocal from database.py, establishing a connection to the database. Then, you defined a list of Book objects, each containing a title, author, page count, publication date, and price.

Next, you added all the books to the session with session.add_all(books), marking them for insertion. You committed the transaction using session.commit(), which executed the SQL INSERT statements. After committing, you retrieved and printed each book’s auto-generated id.

Finally, you closed the session with session.close() to release the database connection.

Run this script to add some data:

python add_books.py

You should see output like:

2025-03-13 12:00:22,318 INFO sqlalchemy.engine.Engine BEGIN (implicit)
2025-03-13 12:00:22,319 INFO sqlalchemy.engine.Engine INSERT INTO books (title, author, pages, publication_date, price) VALUES (?, ?, ?, ?, ?) RETURNING id
2025-03-13 12:00:22,319 INFO sqlalchemy.engine.Engine [generated in 0.00007s (insertmanyvalues) 1/3 (ordered; batch not supported)] ('The Hobbit', 'J.R.R. Tolkien', 295, '1937-09-21', 19.99)
2025-03-13 12:00:22,319 INFO sqlalchemy.engine.Engine INSERT INTO books (title, author, pages, publication_date, price) VALUES (?, ?, ?, ?, ?) RETURNING id
2025-03-13 12:00:22,319 INFO sqlalchemy.engine.Engine [insertmanyvalues 2/3 (ordered; batch not supported)] ('1984', 'George Orwell', 328, '1949-06-08', 14.95)
2025-03-13 12:00:22,319 INFO sqlalchemy.engine.Engine INSERT INTO books (title, author, pages, publication_date, price) VALUES (?, ?, ?, ?, ?) RETURNING id
2025-03-13 12:00:22,319 INFO sqlalchemy.engine.Engine [insertmanyvalues 3/3 (ordered; batch not supported)] ('To Kill a Mockingbird', 'Harper Lee', 281, '1960-07-11', 12.99)
2025-03-13 12:00:22,320 INFO sqlalchemy.engine.Engine COMMIT
2025-03-13 12:00:22,320 INFO sqlalchemy.engine.Engine BEGIN (implicit)
....

In the output, you can see the SQL statements that SQLAlchemy generates and executes. Each book generates an INSERT statement with the appropriate values.

With these books in your database, you can query them in the next step.

Step 4 — Querying data from your database

In this section, you'll learn how to retrieve data from your SQLite database using SQLAlchemy's query API.

Now that you have some books in your database, let's write queries to retrieve them. Create a file called query_books.py:

from database import SessionLocal
from models import Book

# Create a session
session = SessionLocal()

# Get all books
print("==== All Books ====")
all_books = session.query(Book).all()
for book in all_books:
    print(f"{book.title} by {book.author}, {book.pages} pages, ${book.price:.2f}")

# Close the session
session.close()

The session.query(Book).all() method retrieves all books from the database as a list of Book objects. You can then access any attribute of these objects, such as title, author, and price.

Run this script to see all the books in your database:

python query_books.py

You should see output like:

==== All Books ====
...
SELECT books.id, books.title, books.author, books.pages, books.publication_date, books.price 
FROM books
...
The Hobbit by J.R.R. Tolkien, 295 pages, $19.99
1984 by George Orwell, 328 pages, $14.95
To Kill a Mockingbird by Harper Lee, 281 pages, $12.99

From the output, you can see that SQLAlchemy executes a SELECT query to fetch all books from the database. It retrieves the book details and displays them in a readable format.

Often, you'll want to retrieve only records that match certain criteria. Here's how to filter data:

# Query books by a specific author
tolkien_books = session.query(Book).filter(Book.author == "J.R.R. Tolkien").all()
for book in tolkien_books:
    print(f"{book.title}, published on {book.publication_date}")

# Query books with more than 300 pages
long_books = session.query(Book).filter(Book.pages > 300).all()
for book in long_books:
    print(f"{book.title}: {book.pages} pages")

The filter() method lets you specify conditions for your query, similar to SQL's WHERE clause.

You can also specify the order of returned records using order_by():

# Order books by price (cheapest first)
ordered_books = session.query(Book).order_by(Book.price).all()
for book in ordered_books:
    print(f"{book.title}: ${book.price:.2f}")

# Order by price in descending order (most expensive first)
expensive_first = session.query(Book).order_by(Book.price.desc()).all()

If you only need one record, you can use first() instead of all():

# Get the first book
first_book = session.query(Book).first()

# Get a specific book by ID
book_by_id = session.query(Book).get(2)  # Gets book with ID 2

These query methods form the foundation of retrieving data with SQLAlchemy. Remember to always close your session to release database resources when you're done with it.

In the next step, you'll learn how to update existing records in your database.

Step 5 — Updating records in your database

In this section, you'll learn how to modify existing records in your SQLite database using SQLAlchemy. After retrieving objects from the database, you can update their attributes and save the changes back to the database.

The most common approach for updating data with SQLAlchemy is to query for a record, modify its attributes, and then commit the changes. Create a file called update_books.py:

from database import SessionLocal
from models import Book

# Create a session
session = SessionLocal()

# Find the book to update
print("=== Before update ===")
book = session.query(Book).filter(Book.title == "1984").first()
print(f"{book.title} current price: ${book.price:.2f}")

# Update the book's price
book.price = 16.99
session.commit()

# Verify the update
updated_book = session.query(Book).filter(Book.title == "1984").first()
print(f"=== After update ===")
print(f"{updated_book.title} new price: ${updated_book.price:.2f}")

# Close the session
session.close()

First, you query the database to find the book you want to update using filter() and first(). Then you modify the book's attributes directly, in this case changing its price. Finally, you commit the transaction with session.commit(), which sends the UPDATE statement to the database.

Run this script:

python update_books.py

You should see output like:

=== Before update ===
1984 current price: $14.95
...
COMMIT
...
=== After update ===
1984 new price: $16.99
...

This approach of modifying objects and letting SQLAlchemy handle the SQL generation is intuitive and powerful. You can update multiple attributes simultaneously, and SQLAlchemy will generate the appropriate UPDATE statement.

You can modify multiple attributes of an object before committing:

# Find the book to update
book = session.query(Book).filter(Book.title == "The Hobbit").first()

# Update multiple attributes
book.pages = 300
book.price = 21.99
book.author = "J.R.R. Tolkien (Revised Edition)"

# Save changes
session.commit()

SQLAlchemy will generate a single UPDATE statement that changes all modified attributes.

In addition to that, you can update records based on conditions:

# Find books with low page count
short_books = session.query(Book).filter(Book.pages < 300).all()

# Mark them as "quick reads"
for book in short_books:
    book.title = f"{book.title} (Quick Read)"

# Save all changes
session.commit()

For scenarios where you need to update multiple records that match certain criteria, you can use the update() method on the query object:

# Bulk update example - increase all book prices by 10%
books_updated = session.query(Book).update(
    {Book.price: Book.price * 1.1},
    synchronize_session=False
)
session.commit()
print(f"Updated prices for {books_updated} books")

This approach is more efficient for bulk updates because it executes a single SQL UPDATE statement rather than retrieving each object individually. The synchronize_session=False parameter tells SQLAlchemy not to update the objects in the current session, which is more efficient for bulk operations.

Now that you can update records in the database, it's time to learn how to delete them.

Step 6 — Deleting records from your database

In this section, you'll remove records from your SQLite database using SQLAlchemy. You'll see how to delete individual records and how to perform bulk deletions based on specific criteria.

The most common approach for deleting records is to retrieve the object first and then use the session.delete() method. Create a file called delete_books.py:

from database import SessionLocal
from models import Book

# Create a session
session = SessionLocal()

# Count books before deletion
count_before = session.query(Book).count()
print(f"Total books before deletion: {count_before}")

# Find and delete a book
book_to_delete = session.query(Book).filter(Book.title == "To Kill a Mockingbird").first()
if book_to_delete:
    print(f"Found book to delete: {book_to_delete.title} by {book_to_delete.author}")
    session.delete(book_to_delete)
    session.commit()
    print(f"Book deleted successfully")
else:
    print("Book not found")

# Count books after deletion
count_after = session.query(Book).count()
print(f"Total books after deletion: {count_after}")

# List remaining books
remaining_books = session.query(Book).all()
print("\nRemaining books:")
for book in remaining_books:
    print(f"- {book.title} by {book.author}")

# Close the session
session.close()

In this script, you first check how many books you have by using query(Book).count(). Then you find the specific book to remove with filter() and first().

Once found, you mark it for deletion with session.delete() and actually perform the deletion by calling session.commit().

To confirm the deletion worked, you count the books again and list the remaining ones to see that "To Kill a Mockingbird" is no longer in the database.

Run this script:

python delete_books.py

You should see output like:

Total books before deletion: 3
...
Found book to delete: To Kill a Mockingbird by Harper Lee
...
DELETE FROM books WHERE books.id = ?
...
COMMIT
Book deleted successfully
...
Total books after deletion: 2
...
Remaining books:
- The Hobbit by J.R.R. Tolkien
- 1984 by George Orwell
...

You can also delete records by primary key if you know it:

# Delete a book by ID
book_id = 2  # ID of the book to delete
book = session.query(Book).get(book_id)
if book:
    session.delete(book)
    session.commit()
    print(f"Deleted book with ID {book_id}")

For cases where you need to delete multiple records that match certain criteria without retrieving them first, you can use the query's delete() method:

# Bulk delete example
deleted_count = session.query(Book).filter(Book.price < 15.00).delete()
session.commit()
print(f"Deleted {deleted_count} inexpensive books")

This method is more efficient than fetching objects and deleting them individually, especially when dealing with large numbers of records, because it executes a single DELETE statement with a WHERE clause instead of multiple statements.

With this, you now have a solid understanding of the basic CRUD operations with SQLAlchemy.

Final thoughts

This tutorial walked you through SQLAlchemy's essential features—connecting to databases, defining models, and performing CRUD operations with SQLite. You now have the fundamentals to build data-driven Python applications.

With the foundation you've built, you can now explore more advanced features like relationships between tables, migrations with Alembic, and optimizing queries for performance. For more detailed information, check out the official SQLAlchemy documentation.

Got an article suggestion? Let us know

Next article

Getting Started with TortoiseORM for Python

Learn how to build asynchronous Python applications with TortoiseORM and SQLite in this comprehensive tutorial. Master the fundamentals of async database operations, from setting up your project and defining models to performing CRUD operations efficiently.

→

This work is licensed under a Creative Commons Attribution-NonCommercial-ShareAlike 4.0 International License.