ORM for Python with SQLAlchemy

Hey, everyone! This is a simple tutorial on how to use SQLAlchemy for ORM. When we write software that connects to a database it is very useful to use a ORM library, so we don't have to write queries manually, which makes our code much harder to maintain. A popular option to do this in Python is the SQLAlchemy lib.

Let's say we have created the following tables in our database (we are using a Postgres database):

create table teacher (
    teacher_id INT GENERATED ALWAYS AS IDENTITY,
    name varchar(100) not null,
    primary key(teacher_id)
);

create table student (
    student_id INT GENERATED ALWAYS AS IDENTITY,
    name varchar(100) not null,
    primary key(student_id)    
);

create table course (
    course_id INT GENERATED ALWAYS AS IDENTITY,
    name varchar(50) not null,
    teacher_id int,
    CONSTRAINT fk_teacher
            FOREIGN KEY(teacher_id) 
        REFERENCES teacher(teacher_id),
    primary key(course_id)
);

create table student_course (
   student_course_id INT GENERATED ALWAYS AS IDENTITY,
   student_id int,
   course_id int,
    CONSTRAINT fk_student
            FOREIGN KEY(student_id) 
        REFERENCES student(student_id),
    CONSTRAINT fk_course
            FOREIGN KEY(course_id) 
        REFERENCES course(course_id),
    primary key(student_course_id)
);

This is our database diagram:

Now, we are going to write the python code to represent them.

1.Install the libs

First, install de SQLAlchemy library, if you don't already have it:

pip install sqlalchemy

Since we are using postgres for this, we also need to install its driver psycopg2:

pip install psycopg2-binary

If you are using another database, you have to download the appropriated driver

2.Connect to the database

Let's create a script that connects to our database:

from sqlalchemy import create_engine

eng = create_engine('postgresql://postgres:postgres@localhost:5432/school')

Try running it to see if everything is ok!

3.Create our tables mappings

We are going to start with the teacher table, since it is the simplest one:

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

eng = create_engine('postgresql://postgres:postgres@localhost:5432/school')

# creates a new base class from which all mapped classes should inherit
Base = declarative_base()


class Teacher(Base):
    __tablename__ = "teacher"

    # the first argument is the column's actual name 
    # (if the column and the created field have the same name, it is not necessary to specify it)
    # the second its type 
    # and we are defining it as a primary key
    id = Column("teacher_id", Integer, primary_key=True)

    #here we are defining it as "not null"
    name = Column("name", String, nullable=False)


Session = sessionmaker(bind=eng)
ses = Session()

teacher = Teacher(name='the new teacher')
ses.add(teacher)
ses.commit()

# finding the created teacher based on the returned id
rs = ses.query(Teacher).filter(Teacher.id == teacher.id)

for teacher in rs:
    print(teacher.name)

If you run this script, it should print the name of the teacher we created.

Now, let's create the course mapping, specifying the many to one teacher relationship:

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

eng = create_engine('postgresql://postgres:postgres@localhost:5432/school')

# creates new base class from which all mapped classes should inherit
Base = declarative_base()


class Teacher(Base):
    __tablename__ = "teacher"

    id = Column("teacher_id", Integer, primary_key=True)
    name = Column("name", String, nullable=False)


class Course(Base):
    __tablename__ = "course"

    id = Column("course_id", Integer, primary_key=True)
    name = Column("name", String, nullable=False)

    #here we are defining teacher_id as a FK
    teacher_id = Column(Integer, ForeignKey('teacher.teacher_id'))

    #here we are defining the teacher as a relationship
    teacher = relationship(Teacher, primaryjoin=teacher_id == Teacher.id)


Session = sessionmaker(bind=eng)
ses = Session()

teacher = Teacher(name='the new teacher')
ses.add(teacher)

#here we create a course with a relationship to the teacher we just created
course = Course(name='math', teacher=teacher)
ses.add(course)
ses.commit()

rs = ses.query(Course).filter(Course.id == course.id)
for course in rs:
    print('Course Name: %s, Teacher Name: %s' % (course.name, course.teacher.name))


>>Course Name: math, Teacher Name: the new teacher

Now, let's create the student mapping. We are going to add the many to many relationship, for this we are mapping "student_course", our relationship table, as a secondary table:

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

eng = create_engine('postgresql://postgres:postgres@localhost:5432/school')

# creates new base class from which all mapped classes should inherit
Base = declarative_base()


class Teacher(Base):
    __tablename__ = "teacher"

    id = Column("teacher_id", Integer, primary_key=True)
    name = Column("name", String, nullable=False)


#here we are mapping the relationship table
student_course = Table(
    'student_course', Base.metadata,
    Column('student_id', Integer, ForeignKey('student.student_id')),
    Column('course_id', Integer, ForeignKey('course.course_id'))
)


class Student(Base):
    __tablename__ = "student"

    id = Column("student_id", Integer, primary_key=True)
    name = Column("name", String, nullable=False)

    #referencing the relationship table as secondary
    courses = relationship("Course", secondary=student_course)


class Course(Base):
    __tablename__ = "course"

    id = Column("course_id", Integer, primary_key=True)
    name = Column("name", String, nullable=False)
    teacher_id = Column(Integer, ForeignKey('teacher.teacher_id'))
    teacher = relationship(Teacher, primaryjoin=teacher_id == Teacher.id)

    #here we reference the relationship table as viewonly, as we only want one side of the
    #relationship saving it to avoid conflicts
    students = relationship("Student", secondary=student_course, viewonly=True)


Session = sessionmaker(bind=eng)
ses = Session()

teacher = Teacher(name='the new teacher')
ses.add(teacher)

math_course = Course(name='math', teacher=teacher)
ses.add(math_course)

english_course = Course(name='english', teacher=teacher)
ses.add(english_course)

student = Student(name="new student on the block", courses=[math_course, english_course])
ses.add(student)

ses.commit()

rs = ses.query(Student).filter(Student.id == student.id)
for student in rs:
    print('Student name: %s' % student.name)
    for course in student.courses:
        print('Course Name: %s, Teacher Name: %s' % (course.name, course.teacher.name))

>> Student name: new student on the block
>> Course Name: math, Teacher Name: the new teacher
>> Course Name: english, Teacher Name: the new teacher

With this process, everytime we add a student to a course, a record is saved in the student_course relationship table. We can access the list of the courses a student enrolled and the list of students that are enrolled in a course.

4.Conclusion

This is a simple example of using sqlalchemy as orm library to map tables in Python. As you can see, it is very easy and straightforward. In the following posts we are going to explore more possibilities.