#81: CRUD in SQLAlchemy ORM

With the foundation for SQLAlchemy ORM in place, it is now time to do some CRUD operations without writing SQL statements.

Pre-requisites

You need all the classes we created in the last post on the SQLAlchemy ORM foundation.

Make sure that you have the setup_db() in your application and that you call it before you access the database:

import os
import data.db_session as db_session
from data.employee import Employee


def setup_db():
    db_file = os.path.join(
        os.path.dirname(__file__),
        'db',
        'Northwind_small.sqlite')
    db_session.global_init(db_file)

if __name__ == '__main__':
    print("--- setup_db() ---")
    setup_db()
    print("--- add_employee() ---")
    id = add_employee()
    # ...

Create (or insert)

We use our Employee class and add values to describe our employee as we would in a regular Python object. To insert our employee in the database, we need a session from our session factory, add the employee and call session.commit() to save all changes in the session:

def add_employee():
    employee = Employee()
    employee.last_name = "King"
    employee.first_name = "Robert"
    employee.birth_date = '1990-05-29'   
    print(employee)

    session = db_session.factory()
    session.add(employee)
    session.commit()

    print(employee)
    return employee.id

At the end I return the employee.Id so that I can use the same entry in the database for the other CRUD operations.

Read (or select)

In SQLAlchemy Core we use the .where() method to get the rows we want. In SQLAlchemy ORM we use the filter() method instead:

def load_employee(id):
    session = db_session.factory()

    employee = session.query(Employee) \
        .filter(Employee.id == id) \
        .first()

    print(employee)

    session.close()

The filter() method returns a list of objects that matches the filter. Since we ask for the primary key filed, it can only have one entry in the Employee table, and we can call first() to get the employee instance. As with SQLAlchemy Core, first() can return None.

We do not want to change anything in the database; therefore, we can use session.close() to end our session.

Update

To change an employee, we need to load it as we did in the Read section. Then we change the values and call session.commit() to save the changes in the database:

def update_employee(id):
    session = db_session.factory()
    employee = session.query(Employee) \
        .filter(Employee.id == id) \
        .first()

    print(employee)

    employee.last_name = "Lord"    
    session.commit()

    print(employee)

Delete

As with update, we first need to load the employee. Then we can call session.delete() with the employee as an argument. As soon as we call session.commit() the employee gets deleted from the database:

def delete_employee(id):
    session = db_session.factory()
    employee = session.query(Employee) \
        .filter(Employee.id == id) \
        .first()

    print(employee)

    session.delete(employee)
    session.commit() 

Loading an object before we can delete it looks a bit strange, but it is consistent with updates and works fast enough outside of batch processing.