How to use CRUD operations in SqlAlchemy

SQLAlchemy is a powerful Python library that simplifies working with databases, enabling developers to interact with databases using Python code rather than writing raw SQL queries. In this Answer, we’ll dive into using CRUD operations in SQLAlchemy, covering the essential techniques and patterns to create, retrieve, update, and delete data from the database.

Let's discuss how to perform CRUD operations in SQLAlchemy:

Connecting database

We establish a database connection with the create_engine("sqlite:///:memory:") command, which initializes an SQLite database in memory.

Note: For a more detailed explanation of how to connect with databases in SQLAlchemy, you can read this Answer.

Defining table

Following that, we define our database table using the declarative_base() class. Here, we define a database table named “Educative_Answers”, consisting of columns for id and name.

Note: For a more detailed explanation of how to create a table in SQLAlchemy, you can read this Educative Answer.

from sqlalchemy import create_engine, Column, Integer, String
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.orm import sessionmaker
# Create an in-memory SQLite database engine
engine = create_engine('sqlite:///:memory:')
# Define Table Classes
Base = declarative_base()
class CustomTable1(Base):
    __tablename__ = 'Educative_Answers'
    custom_id = Column(Integer, primary_key=True)
    custom_name = Column(String, unique=True)
# Create the tables in the in-memory database
Base.metadata.create_all(engine)
# Establish a session for CRUD operations
Session = sessionmaker(bind=engine)
session = Session()

# Function to Create Records
def create_record(session, name):
    new_record = CustomTable1(custom_name=name)
    session.add(new_record)
    session.commit()
    return new_record
# Example of using the create_record function
create_record(session, 'SQLAlchemy Crud Operations')
create_record(session, 'SQLAlchemy Sessions')
# Query and print all entries from the database
all_records = session.query(CustomTable1).all()
print("All Entries in the Table:")
for record in all_records:
    print(f"ID: {record.custom_id}, Name: {record.custom_name}")

Code explanation

Lines 1–6: These lines define a function, create_record(), which is intended for adding new records to the table. The session.add() function is employed to insert a new record, while the session.commit() function ensures its persistence in the database.
Lines 8–9: These lines simply utilize the specified function to insert new records into the database.
Line 13–16: These lines retrieve and print all data from the database on the console.

Querying records (Read)

We can use session.query() method to read records from the database in SQLAlchemy. Furthermore, we can use multiple sub-methods to filter our records.

Note: You can read this Educative Answer for more detailed work on how to execute queries in SQLAlchemy.

# Function to Update Records
def update_record(session, record_id, new_name):
    record_to_update = session.query(CustomTable1).get(record_id)
    if record_to_update:
        record_to_update.custom_name = new_name
        session.commit()
        return record_to_update
    else:
        return None
# Example of using the update_record function
updated_record = update_record(session, 1, 'Not SQLALchemy')
if updated_record:
    print(f"Updated Record - ID: {updated_record.custom_id}, New Name: {updated_record.custom_name}")
else:
    print("Record with specified ID not found.")
# Query and print all entries from the database after update
all_records_after_update = session.query(CustomTable1).all()
print("All Entries in the Table After Update:")
for record in all_records_after_update:
    print(f"ID: {record.custom_id}, Name: {record.custom_name}")

Code explanation

Lines 1–9: These lines define the update_record() function, which queries the record designated for updating and subsequently modifies its value. The session.commit() function ensures the changes are persistently stored in the database.
Lines 12–16: These lines utilize the specified function to update existing records in the database and print the updates on the console. If the specific record is not found in the database, it also logs that information.
Line 19–22: These lines retrieve and print all data from the database on the console.

Deleting records (Delete)

To delete records, create a session and used session.delete() method with the record ID as a parameter to delete that particular record and session.commit() to confirm changes in SQLAlchemy.

# Function to Delete a Record
def delete_record(session, record_id):
    record_to_delete = session.query(CustomTable1).get(record_id)
    if record_to_delete:
        session.delete(record_to_delete)
        session.commit()
        return True
    else:
        return False
# Query and print all entries from the database before deletion
all_records_after_deletion = session.query(CustomTable1).all()
print("All Entries in the Table Before Deletion:")
for record in all_records_after_deletion:
    print(f"ID: {record.custom_id}, Name: {record.custom_name}")
# Example of using the delete_record function
record_id_to_delete = 1  # Replace with the actual ID of the record to delete
is_deleted = delete_record(session, record_id_to_delete)
# Query and print all entries from the database after deletion
all_records_after_deletion = session.query(CustomTable1).all()
print("All Entries in the Table After Deletion:")
for record in all_records_after_deletion:
    print(f"ID: {record.custom_id}, Name: {record.custom_name}")

Code explanation

Lines 1–9: These lines define a function, delete_record(), designed to remove existing records from the table. The session.delete() function is utilized for deleting a specific record, and the session.commit() function ensures its persistence in the database.
Lines 12–25: These lines essentially display all the records from the database both before and after deleting a specific record, demonstrating the successful deletion process.

Conclusion

By mastering SQLAlchemy’s CRUD operations, we can seamlessly interact with databases, making our Python applications more dynamic and data-driven. This guide serves as a starting point for leveraging the full capabilities of SQLAlchemy in our projects. Happy coding!

Free AI Mock Interviews

Coding Interview

Coding PatternsFree Interview

Gain insights and practical experience with coding patterns through targeted MCQs and coding problems, designed to match and challenge your expertise level.

System Design

YouTubeFree Interview

Learn to design a video streaming platform like YouTube by tackling functional and non-functional requirements, core components, and high-level to detailed design challenges.

Free Resources