CRUD Operations with SQLAlchemy

Key Concepts

• Create
• Read
• Update
• Delete

Create

The "Create" operation involves adding new records to a database table. In SQLAlchemy, this is typically done by creating an instance of the model class and adding it to the session.

from sqlalchemy import create_engine
from sqlalchemy.orm import sessionmaker
from models import User

engine = create_engine('sqlite:///example.db')
Session = sessionmaker(bind=engine)
session = Session()

new_user = User(name='John Doe', email='john@example.com')
session.add(new_user)
session.commit()
    

Read

The "Read" operation involves querying the database to retrieve existing records. SQLAlchemy provides a powerful query API to filter and retrieve data.

from sqlalchemy.orm import sessionmaker
from models import User

Session = sessionmaker(bind=engine)
session = Session()

users = session.query(User).all()
for user in users:
    print(user.name, user.email)

specific_user = session.query(User).filter_by(name='John Doe').first()
print(specific_user.name, specific_user.email)
    

Update

The "Update" operation involves modifying existing records in the database. This is done by querying the record, making changes to it, and then committing the session.

from sqlalchemy.orm import sessionmaker
from models import User

Session = sessionmaker(bind=engine)
session = Session()

user_to_update = session.query(User).filter_by(name='John Doe').first()
user_to_update.email = 'john.doe@example.com'
session.commit()
    

Delete

The "Delete" operation involves removing records from the database. This is done by querying the record and then deleting it from the session.

from sqlalchemy.orm import sessionmaker
from models import User

Session = sessionmaker(bind=engine)
session = Session()

user_to_delete = session.query(User).filter_by(name='John Doe').first()
session.delete(user_to_delete)
session.commit()
    

Example: Full CRUD Operations

Here is a complete example demonstrating all CRUD operations on a simple User model.

from sqlalchemy import create_engine, Column, Integer, String
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.orm import sessionmaker

Base = declarative_base()

class User(Base):
    __tablename__ = 'users'
    id = Column(Integer, primary_key=True)
    name = Column(String)
    email = Column(String)

engine = create_engine('sqlite:///example.db')
Base.metadata.create_all(engine)
Session = sessionmaker(bind=engine)
session = Session()

# Create
new_user = User(name='John Doe', email='john@example.com')
session.add(new_user)
session.commit()

# Read
users = session.query(User).all()
for user in users:
    print(user.name, user.email)

# Update
user_to_update = session.query(User).filter_by(name='John Doe').first()
user_to_update.email = 'john.doe@example.com'
session.commit()

# Delete
user_to_delete = session.query(User).filter_by(name='John Doe').first()
session.delete(user_to_delete)
session.commit()