SQL Servers are awesome and allow us to build great things, but sometimes writing SQL just to get the boiler plate logic out can get in the way of readability and convenience of writing business logic.

The Why

What is an Object Relational Mapper (ORM)?

Essentially, it's job is to map objects (classes) to rows in a database. There's alot that goes in between creating and object and saving it to a row in a database, and this can differ depending on the database flavour you are using (such as Oracle or MYSQL and many others), luckily SQLAlchemy abstracts this all, in many cases code can be deployed to connect arbitrarily to different databases without changes (of course there are dialects that make some things non transportable).

While the issue of using Object Relational Mappers can be contentious at times with some like Hibernate in Java receiving not always fantastic feedback about performance, in my experience and a search around on the internet, SQLAlchemy for Python provides both great features and generally very acceptable performance.

It does more than just create tables from object schemas, it allows you to perform search queries and represent one-one, one-many or even many-many as members of objects. Most profoundly of all though is that no longer will many files contain two langauges: the host-language (e.g. python) and the code for SQL to create, select and do many other things with objects (of course you can still write native SQL if it tickles your fancy for performance or to do something hard-core).

The How

Let's take a common scenario, we have a webservice that deals with records for employees, clients and interactions between those two, let's call them interactions - this is looking like any generic CRM (except we will treat clients as single people and not a structured entity).

How does it look?

In terms of an entity relationship diagram (EDR) check below, as you can see it's very simple.

For the sake of this example, let's consider SQLite as a driver because it's so portable and won't require any database service setup.

SQLAlchemy Classes

Constructing Models

First we need to get the declarive base that we use to inherit all our model classes from, we use

Base = declarative_base()

In this way we then create the Employee class for example in the normal python way, and specify our fields - generally using the Column() method, with arguments such as sqlalchemy.BIGINT and primary_key=True for primary keys

class Employee(Base):
    __tablename__ = 'employee'''
    Column(sqlalchemy.BIGINT, primary_key=True)

 This is enough to create an Employee model with a primary key. Trivial, but insightful for illustrative purposes. We can add to it by using different types for columns (String, DateTime, Bool). I glossed over __tablename__ which is what you think - it just tells the ORM what to call the table when it needs to create or look for it.

We can even specify relationships between different models (bare in mind this can get quite tricky, I will just be providing the basics here so please check the documents). For instance on employee we will specify a reltionship that's one (Employee) to many (interactions) and we do this like so:

relationship("Interaction", backref="employee")

The salient nformation there is the first argument, a string specifying the model name "Interaction" that it points to, and we also provide backref which is nice and courteous and creates a reference back to the Employee in this case called employee.

Our complete model and init code

from sqlalchemy import Column, ForeignKey, Integer, create_engine
from sqlalchemy.orm import relationship, sessionmaker
import sqlalchemy
from sqlalchemy.ext.declarative import declarative_base
from datetime import datetime, timezone
Base = declarative_base()


class Employee(Base):
    __tablename__ = 'employee'''
    id = Column(sqlalchemy.BIGINT, primary_key=True)
    firstName = Column(sqlalchemy.String)
    lastName = Column(sqlalchemy.String)
    role = Column(sqlalchemy.String)
    email = Column(sqlalchemy.String)
    password = Column(sqlalchemy.String)
    phoneNumber = Column(sqlalchemy.String)
    remarks = Column(sqlalchemy.String)
    createdDate = Column(sqlalchemy.DATETIME)
    interactions = relationship("Interaction", backref="employee")


class Client(Base):
    __tablename__ = 'client'''
    id = Column(sqlalchemy.BIGINT, primary_key=True)
    firstName = Column(sqlalchemy.String)
    lastName = Column(sqlalchemy.String)
    email = Column(sqlalchemy.String)
    phoneNumber = Column(sqlalchemy.String)
    remarks = Column(sqlalchemy.String)
    createdDate = Column(sqlalchemy.DATETIME)
    interactions = relationship("Interaction", backref="client")


class Interaction(Base):
    __tablename__ = 'interaction'''
    id = Column(sqlalchemy.BIGINT, primary_key=True)
    date = Column(sqlalchemy.DATETIME)
    subject = Column(sqlalchemy.String)
    remarks = Column(sqlalchemy.String)
    isFinished = Column(sqlalchemy.Boolean)
    client_id = Column(Integer, ForeignKey('client.id'))
    employee_id = Column(Integer, ForeignKey('employee.id'))


# Create an engine that stores data in current dir as 'crm.db'
engine = create_engine('sqlite:///crm.db')
Base.metadata.create_all(engine) # Perform creation of tables

SQL code to construct (SQLite)

CREATE TABLE client (
    id BIGINT NOT NULL,
    "firstName" VARCHAR,
    "lastName" VARCHAR,
    email VARCHAR,
    "phoneNumber" VARCHAR,
    remarks VARCHAR,
    "createdDate" DATETIME,
    PRIMARY KEY (id)
)

CREATE TABLE employee (
    id BIGINT NOT NULL,
    "firstName" VARCHAR,
    "lastName" VARCHAR,
    role VARCHAR,
    email VARCHAR,
    password VARCHAR,
    "phoneNumber" VARCHAR,
    remarks VARCHAR,
    "createdDate" DATETIME,
    PRIMARY KEY (id)
)

CREATE TABLE interaction (
    id BIGINT NOT NULL,
    date DATETIME,
    subject VARCHAR,
    remarks VARCHAR,
    "isFinished" BOOLEAN,
    client_id INTEGER,
    employee_id INTEGER,
    PRIMARY KEY (id),
    CHECK ("isFinished" IN (0, 1)),
    FOREIGN KEY(client_id) REFERENCES client (id),
    FOREIGN KEY(employee_id) REFERENCES employee (id)
)

How does it work?

Ok so in terms of raw length - there's not much difference but don't forget you'll stll need to execute that SQL code from within Python. More profound is what thise code lets us do, using the schemas we have just defined - so let's take a look at what the ORM can do for us.

In Action

Adding Data

It's nice that we can create schemas so easily but we need to do something about it, don't we?

Well the nice thing about SQLAlchemy's ORM is that we can use the class init method to actually create our model instances, so for an employee we can go Employee(); however we can do more than that as we usually want to add information in like an Id or even the rest of it right away (and it would pile downwards in code).

 employee1  = Employee(id=101, createdDate= datetime.now(timezone.utc), firstName="Jane", lastName="Doe",
                 role="Supervisor", password="Hello123", email="[email protected]", phoneNumber="555-121212")

So you can see every item that we would want to attach directly to that employee can be enterted as named arguments in the Employee model's init method, but you can enter as many or as little as you like.

Saving Data

Now that we create an object, and it's registered in object space (memory) as it were - we'd like it to persist on disk (in the store - SQLite) to be of any real use. Normally you can use transactions with SQLAlchemy but we won't go into that much detail, for the simple sake of argument here is how you save an object to the database:

session.add(employee1)
session.commit()

What about updating data?

Just like above where we used session.add(employee1), you can use session.merge(employee1) to take an existing object and push any changes off to the database records. If we had made changes to this item we wished to save it would be:

session.merge(employee1)
session.commit()

How do we get data back out?

Well, in this particular case you could reuse the reference to client1 that you saved, however for the sake of a thorough explanation assume you don't have it and need to get it from the session (database).

If you just want to get all clients into a list, you can ask the session to query it.

all_items = session.query(Model).all()

How do we find specific data I've already entered into the database?

Queries may be built up by chaining the return object, it doesn't get executed/prepared until you use a call to .all() or .first() to fire a select query on those items.

So to find the client we created with an name of Jane:

found_client  = session.query(Client).filter_by(firstName='name').first()

In this example, filter_by takes the name of the property id and the value you wish to find, you can use more sophisticated filtering but that's enough to illustrate the basics for now. first() just does a select of the top record to save the database extra effort in the case there was more than 1 record that matched.

Even simpler I know the primary key

If we just wanted to get the client whose id was 1001 (the same client as our last query) we could do this even easier and write:

found_client = session.query(Client).get(1001)

The rest of our source code (Follows the previous block)

# Bind the engine to the metadata for access
Base.metadata.bind = engine
# Create a session maker and a session
DBSession = sessionmaker(bind=engine)
session = DBSession()

# Insert a Employee in the employee table
employee1 = Employee(id=101, createdDate= datetime.now(timezone.utc), firstName="Jane", lastName="Doe",
                     role="Supervisor", password="Hello123", email="[email protected]", phoneNumber="555-121212")
session.add(employee1)
session.commit()

# Insert a Client in the client table
client1 = Client(id=1001, createdDate= datetime.now(timezone.utc), firstName="Jane", lastName="Doe",
                 email="[email protected]", phoneNumber="555-121212")

session.add(client1)
session.commit()

# Create some interactions
for a in range(1, 4):
    interaction = Interaction(id=a, date= datetime.now(timezone.utc), subject="Reached Out", remarks="",
                              isFinished=True, client_id=client1.id, employee_id=employee1.id)
    session.add(interaction)
    session.commit()

found_client = session.query(Client).get(1001)

for interaction in found_client.interactions:
    print("Found an interaction Id of {0} at {1}".format(interaction.id, interaction.date))

Let's run it!

Found an interaction Id of 1 at 2016-05-25 02:40:07.735245
Found an interaction Id of 2 at 2016-05-25 02:40:07.746788
Found an interaction Id of 3 at 2016-05-25 02:40:07.754276