python logo

sqlalchemy orm


Python hosting: Host, run, and code Python in the cloud!

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.


Related course:

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)

Back





Leave a Reply:




Besh Wed, 09 Sep 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

Frank Wed, 09 Sep 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

Joe Bauer Mon, 22 Feb 2016

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

Frank Thu, 25 Feb 2016

Strange,

engine = create_engine(dbname, echo=False)

should not echo.

Ashish Cherian Sat, 20 May 2017

Thank you so much for this awesome well explained tutorial.