ORM with SqlAlchemy


An object relational mapper maps a relational database system to objects.  If you are unfamiliar with object orientated programming, read this tutorial first. The ORM is independent of which relational database system is used. From within Python, you can talk to objects and the ORM will map it to the database. In this article you will learn to use the SqlAlchemy ORM.

What an ORM does is shown in an illustration below:

ORM Object Relational Mapping
ORM Object Relational Mapping. We communicate with the database using the ORM and only use Python objects and classes.

Creating a class to feed the ORM
We create the file tabledef.py. In this file we will define a class Student. An abstract visualization of the class below:

class
Class definition

Observe we do not define any methods, only variables of the class. This is because we will map this class to the database and thus won’t need any methods.

This is the contents of tabledef.py:

from sqlalchemy import *
from sqlalchemy import create_engine, ForeignKey
from sqlalchemy import Column, Date, Integer, String
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.orm import relationship, backref
 
engine = create_engine('sqlite:///student.db', echo=True)
Base = declarative_base()
 
########################################################################
class Student(Base):
    """"""
    __tablename__ = "student"
 
    id = Column(Integer, primary_key=True)
    username = Column(String)
    firstname = Column(String)
    lastname = Column(String)
    university = Column(String)
 
    #----------------------------------------------------------------------
    def __init__(self, username, firstname, lastname, university):
        """"""
        self.username = username
        self.firstname = firstname
        self.lastname = lastname
        self.university = university
 
# create tables
Base.metadata.create_all(engine)

Execute with:

python tabledef.py

The ORM created the database file tabledef.py.  It  will output the SQL query to the screen, in our case it showed:

CREATE TABLE student (
	id INTEGER NOT NULL, 
	username VARCHAR, 
	firstname VARCHAR, 
	lastname VARCHAR, 
	university VARCHAR, 
	PRIMARY KEY (id)
)

Thus, while we defined a class, the ORM created the database table for us. This table is still empty.

Inserting data into the database
The database table is still empty. We can insert data into the database using Python objects. Because we use the SqlAlchemy ORM we do not have to write a single SQL query. We now simply create Python objects that we feed to the ORM.  Save the code below as dummy.py

import datetime
from sqlalchemy import create_engine
from sqlalchemy.orm import sessionmaker
from tabledef import *
 
engine = create_engine('sqlite:///student.db', echo=True)
 
# create a Session
Session = sessionmaker(bind=engine)
session = Session()
 
# Create objects  
user = Student("james","James","Boogie","MIT")
session.add(user)
 
user = Student("lara","Lara","Miami","UU")
session.add(user)
 
user = Student("eric","Eric","York","Stanford")
session.add(user)
 
# commit the record the database
session.commit()

Execute with:

python dummy.py

The ORM will map the Python objects to a relational database. This means you do not have any direct interaction from your application, you simply interact with objects. If you open the database with SQLiteman or an SQLite database application you’ll find the table has been created:

Data in database table.
Data in database table.

Query the data
We can query all items of the table using the code below. Note that Python will see every record as a unique object as defined by the Students class. Save the code as demo.py

import datetime
from sqlalchemy import create_engine
from sqlalchemy.orm import sessionmaker
from tabledef import *
 
engine = create_engine('sqlite:///student.db', echo=True)
 
# create a Session
Session = sessionmaker(bind=engine)
session = Session()
 
# Create objects  
for student in session.query(Student).order_by(Student.id):
    print student.firstname, student.lastname

On execution you will see:

James Boogie
Lara Miami
Eric York

To select a single object use  the filter() method. A demonstration below:

import datetime
from sqlalchemy import create_engine
from sqlalchemy.orm import sessionmaker
from tabledef import *
 
engine = create_engine('sqlite:///student.db', echo=True)
 
# create a Session
Session = sessionmaker(bind=engine)
session = Session()
 
# Select objects  
for student in session.query(Student).filter(Student.firstname == 'Eric'):
    print student.firstname, student.lastname

Output:

Eric York

Finally, if you do not want the ORM the output any of the SQL queries change the create_engine statement to:

engine = create_engine('sqlite:///student.db', echo=False)

4 thoughts on “ORM with SqlAlchemy

  1. Joe Bauer - February 22, 2016

    Thanks for the tutorial. I changed the engine statement to echo=False but it still outputs queries.

    1. Frank - February 25, 2016

      Strange,

      engine = create_engine(dbname, echo=False)

      should not echo.

  2. Besh - September 9, 2015

    Hi Frank, thank you for your tutorial is very enriching but I would like to know, is there a detailed tutorial on how to install the SQLalchemy since you didn’t cover that bit here

    1. Frank - September 9, 2015

      Hi Besh,

      The official install guide is: http://docs.sqlalchemy.org/en/rel_1_0/intro.html#installation-guide

      If you have pip installed it’s as simple as:
      pip install SQLAlchemy

      Alternatively you could download and extract the SQLAlchemy files, enter the directory and run:
      python setup.py install

      I recommend using pip because it’s an easy way to install modules. If you do not have pip, you can get it from:
      https://pip.pypa.io/en/latest/installing.html