Introduction to ORM in Python with SqlAlchemy

11 Apr 2019, 00:00

Introduction

Building custom queries for your database can be time consuming and complex. Object-Relational Mapping is a concept designed to ease this burden and make code easier to read. The job of an ORM is to take our data objects and take care of the heavy lifting for us. Today we will utilize the built in ORM features of the SqlAlchemy library for python.

Installing sqlalchemy

pip install sqlalchemy

Declarative Base

From within sqlalchemy we will utilize something called declarative_base. This function will construct a base class that we will associate with our data models.

from sqlalchemy import create_engine, Column, Integer, String
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.orm import sessionmaker

Building Our Data Models

Next we will create a base object from our declarative base class and build a few data model classes.

Base = declarative_base()

class PetOwners(Base):
    __tablename__ = "pet_owners"

    owner_id = Column('owner_id', Integer, primary_key=True)
    owner_name = Column('owner_name', String)
    owner_age = Column('owner_age', Integer)


class Pets(Base):
    __tablename__ = "pets"

    pet_id = Column('pet_id', Integer, primary_key=True)
    pet_name = Column('pet_name', String)
    pet_species = Column('pet_species', String)
    pet_age = Column('pet_age', Integer)

Start your engines

Now we have a two classes, 1 for owners and another for pets. Each class will translate into a table and each attribute to a field. Now lets build a database session and sqlalchemy will auto build our schema for us. For this example we will be utilizing a sqllite database with a file name ‘testdb.db’

#Create our connection to our sqllite Database
engine = create_engine('sqlite:///testdb.db', echo=True)

#Create a database schema based on our pet owner and pet classes above. 
Base.metadata.create_all(bind=engine) 

#Create a session object we can use to communicate with our database
Session = sessionmaker(bind=engine)
session=Session()

Add some data

Now that we have our data models built and schema deployed we can start adding data. All we have to do is create a object from one of our classes, pass data to it and add it to our session. SqlAlchemy will automatically take care of our unique IDs and auto increment them as necessary.

#Create our owner and pet objects and pass some data to them
newOwner = PetOwners(owner_name="Mary", owner_age="34")
newPet = Pets(pet_name='Olly', pet_species='Golden Retriever', pet_age='3')

#Add our data objects to our session
session.add(newOwner)
session.add(newPet)

#Commit our data to the database and close our session
session.commit()
session.close()

Sqlalchemy will take our data objects and generate the insert statements on the backend. It’s just that easy! You can also check that the data is there using a tool like sqlite browser (Found here: https://sqlitebrowser.org/). SqlAlchemy ORM is a powerful tool that can speed up development time and make your code much easier to read. This concept can easily be paired with flask to make a restful api or web form. Check back soon and we will go over how to model relationships with SqlAlchemy ORM.

Full Code can be found here: https://github.com/BuckarewBanzai/SqlAlchemy-ORM-Example/blob/master/main.py