How to insert, update, and delete data using SQLAlchemy in Python

SQLAlchemy’s ORM is more than just a convenience layer; it’s a powerful bridge between Python objects and relational database tables. At its core, the ORM lets you define Python classes mapped directly to database tables, turning database rows into instances of those classes. This abstraction means you spend less time wrestling with SQL syntax and more time thinking about your application’s logic.

To start, you define your model classes by inheriting from a base class created with declarative_base(). Each attribute on the class corresponds to a column in the database, and SQLAlchemy handles the heavy lifting of mapping those attributes to their underlying SQL counterparts.

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)
    name = Column(String(50))
    email = Column(String(120))

Notice how simpler the class definition looks—no SQL strings, no manual cursor handling. When you create an instance of User, you’re essentially creating an in-memory representation of a database row. The ORM keeps track of changes to these objects and can flush those changes back to the database when you commit the session.

One of the most powerful features is the way the ORM handles relationships. Defining foreign keys and related objects becomes as simple as adding attributes to your classes, letting you navigate between linked tables using pure Python syntax.

from sqlalchemy import ForeignKey
from sqlalchemy.orm import relationship

class Address(Base):
    __tablename__ = 'addresses'
    id = Column(Integer, primary_key=True)
    user_id = Column(Integer, ForeignKey('users.id'))
    email_address = Column(String(100), nullable=False)

    user = relationship("User", back_populates="addresses")

User.addresses = relationship("Address", order_by=Address.id, back_populates="user")

This way, querying a user’s addresses or an address’s user is a matter of accessing attributes, not writing joins manually. SQLAlchemy translates these high-level operations into efficient SQL queries behind the scenes.

When querying, the ORM returns model instances, so you can work with data naturally:

session = Session()
user = session.query(User).filter_by(name='Alice').first()
print(user.email)
for addr in user.addresses:
    print(addr.email_address)

There’s no explicit SQL here, but SQLAlchemy’s query builder composes the necessary JOINs and filters. This lets you focus on what data you want, not how to fetch it.

Besides basic CRUD, the ORM supports advanced features like eager loading, lazy loading, and session scoping, crucial for performance tuning in real-world apps. It’s important to understand the lifecycle of ORM objects and the session to avoid common pitfalls such as detached instances or unnecessary database hits.

Inserting data with SQLAlchemy’s session management

Inserting data with SQLAlchemy revolves around the Session object, which acts as a staging ground for all your ORM operations. The session manages persistence, tracking which objects are new, modified, or deleted, and handles batching those changes into transactions. This transactional scope ensures consistency and atomicity, critical for any robust application.

To insert new records, you instantiate your mapped classes and add them to the session with session.add() or session.add_all(). Adding an object to the session doesn’t immediately push it to the database; instead, it marks the object as “pending.” The actual INSERT happens when you call session.commit(), or session.flush() if you want to send changes without committing.

# Create a new user instance
new_user = User(name='Bob', email='[email protected]')

# Add to session
session.add(new_user)

# Commit the transaction, issuing an INSERT
session.commit()

Using commit() wraps the INSERT in a transaction and also flushes any pending changes. If you want to insert multiple objects efficiently, use add_all():

users = [
    User(name='Carol', email='[email protected]'),
    User(name='Dave', email='[email protected]'),
]

session.add_all(users)
session.commit()

Sometimes you need to insert a parent and its related children in one go. Thanks to the ORM’s relationship handling, you can create the whole object graph in memory, add the root object to the session, and SQLAlchemy figures out the right order of INSERTs to satisfy foreign key constraints.

new_user = User(name='Eve', email='[email protected]')
new_user.addresses = [
    Address(email_address='[email protected]'),
    Address(email_address='[email protected]')
]

session.add(new_user)
session.commit()

Behind the scenes, SQLAlchemy inserts the User first, retrieves its primary key, then inserts the related Address rows with the correct user_id. This cascading behavior saves you from manual ID management and multiple round-trips.

If you need to access the generated primary key after insertion, the ORM makes it accessible immediately after the flush or commit:

new_user = User(name='Frank', email='[email protected]')
session.add(new_user)
session.flush()  # Pushes to DB but doesn't commit

print(new_user.id)  # The primary key is now populated

This is essential for cases where you want to use the new ID in subsequent operations before committing.

Be mindful of the session’s lifecycle; once you commit, the session expires all instances by default, meaning accessing attributes that weren’t loaded will trigger a lazy load query. You can control this behavior with expire_on_commit=False when creating the session.

Inserting large batches of data can be optimized by disabling autoflush during the operation and then flushing once at the end:

session.autoflush = False

for i in range(1000):
    user = User(name=f'User{i}', email=f'user{i}@example.com')
    session.add(user)

session.flush()
session.autoflush = True
session.commit()

This avoids multiple intermediate flushes, improving performance significantly.

Finally, remember that the session is not thread-safe and should be scoped appropriately, usually to a single request or unit of work. Using scoped sessions or context managers can help maintain clean boundaries:

from sqlalchemy.orm import scoped_session, sessionmaker

session_factory = sessionmaker(bind=engine)
Session = scoped_session(session_factory)

def create_user(name, email):
    session = Session()
    try:
        user = User(name=name, email=email)
        session.add(user)
        session.commit()
    except:
        session.rollback()
        raise
    finally:
        Session.remove()

With this pattern, you ensure each thread or request gets its own session, avoiding conflicts and stale state. The session’s role as a transactional cache is what makes SQLAlchemy’s ORM insertion both powerful and safe, abstracting away the messy details of SQL transactions while giving you full control when you need it.

Next, updating existing records follows a similar pattern but requires careful handling of object states to avoid unintended side effects. You can query objects, modify their attributes, and commit the session, but understanding how SQLAlchemy tracks changes is key to efficient updates. The same session that manages inserts will also detect changes and generate the appropriate UPDATE statements when you commit.

Updating and deleting records efficiently in SQLAlchemy

Updating records in SQLAlchemy’s ORM is deceptively simple but benefits greatly from understanding how the session tracks object states. When you query an object from the database, SQLAlchemy returns a live instance attached to the session. Any modification you make to its attributes is recorded in the session’s identity map. On commit(), these changes are flushed as UPDATE statements.

Here’s a simpler example where we update a user’s email:

user = session.query(User).filter_by(name='Bob').first()
if user:
    user.email = '[email protected]'
    session.commit()

Behind the scenes, SQLAlchemy detects that the email attribute has changed and issues an UPDATE targeting just that column. This fine-grained tracking avoids unnecessary writes and keeps your database interactions efficient.

You can batch updates by iterating over multiple objects or by using the ORM’s bulk update methods. For example, to update all users named “Carol” to have a new domain in their email:

users = session.query(User).filter(User.name == 'Carol').all()
for user in users:
    user.email = user.email.replace('@example.com', '@newdomain.com')
session.commit()

This approach loads the objects into memory, applies changes, and flushes them in a single transaction. While simpler, it can be inefficient for large datasets because it involves loading all objects.

For mass updates without loading objects, SQLAlchemy offers the update() construct, which generates a single SQL UPDATE statement. This bypasses the ORM’s unit-of-work but is much faster for bulk changes:

from sqlalchemy import update

stmt = (
    update(User).
    where(User.name == 'Carol').
    values(email=User.email.op('REPLACE')('@example.com', '@newdomain.com'))
)
session.execute(stmt)
session.commit()

This executes an UPDATE directly in SQL, skipping the overhead of loading objects and tracking changes. However, it doesn’t update in-memory Python objects, so be cautious when mixing ORM state and bulk SQL operations.

Deleting records follows a similar pattern. The simplest way is to query the object, call session.delete(), then commit. For example, to delete a user named “Dave”:

user = session.query(User).filter_by(name='Dave').first()
if user:
    session.delete(user)
    session.commit()

This marks the object as deleted in the session. When committed, SQLAlchemy issues a DELETE statement for that row. The session removes the instance from its identity map, preventing further use.

For bulk deletes, use the ORM’s delete() construct, which issues a single DELETE SQL command without loading objects:

from sqlalchemy import delete

stmt = delete(User).where(User.name == 'Dave')
session.execute(stmt)
session.commit()

Again, that is much faster for large operations but bypasses the ORM’s session state. If you have related objects with cascading deletes defined, using session.delete() on loaded objects ensures proper cascading in Python, while the bulk delete may require manual cleanup.

When updating or deleting, consider transaction scoping and error handling carefully. Wrapping changes in try-except blocks with rollback ensures your session and database remain consistent:

try:
    user = session.query(User).filter_by(name='Eve').first()
    if user:
        user.email = '[email protected]'
        session.commit()
except:
    session.rollback()
    raise

For complex update logic, you can also use the ORM’s with_for_update() option to lock rows during a transaction, preventing race conditions in concurrent environments:

user = (
    session.query(User).
    filter_by(name='Frank').
    with_for_update().
    first()
)
if user:
    user.email = '[email protected]'
    session.commit()

This issues a SELECT … FOR UPDATE, locking the row until the transaction completes. This pattern is invaluable when multiple processes might update the same data at once.

Efficient updates and deletes hinge on understanding when to leverage the ORM’s object state tracking and when to use bulk operations. By mixing these appropriately, you can optimize performance without sacrificing the clarity and safety of SQLAlchemy’s ORM abstractions.