The Beginner Guide to SQLAlchemy - Python SQL Toolkit and ORM

Introduction

Before explaining the ORM let’s break down SQLAlchemy and its layer of abstraction. SQLAlchemy is the most popular open-source library for working with relational databases from Python. It is one of the ORM libraries that provides an interface for using object-oriented programming to interact with a database.

Note:

Using an ORM to interact with your database is one of many useful approaches for how you can have a layer of abstraction in your web application allowing it to interact with databases easily. Additionally, there are numerous query builder libraries you can use that are somewhere between talking to a database directly (with a database driver library like pyscopg2) and using an ORM. An ORM is considered to be the highest possible level of abstraction you can add to a web application for database management. Query Builder libraries are right there in the middle Well this article from LogRocket explains why you shouldn’t an ORM. (Source: Udacity)

SQLAlchemy various features :

It features function query-based construction which allows SQL clauses to be written in Python.
The SQLAlchemy ORM library features automatic caching, caching collections, and references between objects once initially loaded.
Avoid writing raw SQL.
Layer of Abstraction

In computer science, an abstraction layer is a generalization of a conceptual model or algorithm, away from any specific implementation. In SQLAlchemy, layers of abstraction are libraries that provide various ways of interacting with the database.

Without SQLAlchemy we will have to use a low-level driver like pyscopg2 to connect with a database which will look like this.

import psycopg2
conn = psycopg2.connect('dbname=example')
cursor = conn.cursor()

cur = conn.cursor()
// Write your sql expressions here

cur.close()
conn.close()

The above is how we connect with psycopg2 to write a SQL expression and send it to the database. But with SQL Alchemy and its layer of abstraction, it will look more like this.

SQLALchemy lets you traverse through all 3 layers of abstraction to interact with your database.

Can stay on the ORM level
Can dive into database operations to run customized SQL code specific to the database, on the Expressions level.
Can write raw SQL when needed on the Engine but could just a DBAPI in that case instead.

Let's explain each level of abstraction

Dialect: When using SQLAlchemy, we can forget generally about the database system we are using. Which makes it easier to switch out the database system whenever we need to. Dialect makes it easy to do this. Dialect is the system SQLAlchemy uses to communicate with various types of DBAPI implementations and databases.

Connection Pooling: With a connection pool the opening and closing of connections and which connection you are using when your executing statements within a session are completely abstracted away from you. This leads to:

Connections are easily reused after they started. This avoids the problem of closing and opening connections every time we wanted to make changes to our database
It fixes the problem of dropped connection.

Without a connection pooling and just using a DBAPI to connect to our database, it will look like this

import psycopg2

conn = psycopg2.connect('dbname=example')

cursor = conn.cursor()

# Open a cursor to perform database operations
cur = conn.cursor()


cur.execute("DROP TABLE IF EXISTS list;")

cur.execute("""
  CREATE TABLE list (
    id serial PRIMARY KEY,
    description VARCHAR NOT NULL
  );
""")

# commit, so it does the executions on the db and persists in the db
conn.commit()

cur.close()
conn.close()

So, a connection pool is a technique used to maintain long-running connections in memory for efficient re-use, as well as management of the total number of simultaneous connections an application might use.

Engine: This is the lowest abstraction of interacting with a database it is more like using the DBAPI directly. It is one of the main 3 layers of interacting with a database.

The Engine is the starting point for any SQLAlchemy application. It’s the “home base” for the actual database and its DBAPI is delivered to the SQLAlchemy application through a connection pool and a Dialect, which describes how to talk to a specific kind of database/DBAPI combination.

The general structure can be illustrated as follows:

Creating an engine is as simple as this :

from sqlalchemy import create_engine
engine = create_engine('postgresql+psycopg2://teslim:postgres@localhost:5432/mydatabase')
// 'database+driver://username:password@localhost:5432/dbname'

SQL Expression: If we don’t want to send raw SQL to the engine, SQL expressions allow us to use python objects to compose SQL Expressions. SQL Expressions still involves using and knowing SQL to interact with the database. SQLAlchemy ORM: This is the highest layer of abstraction. An ORM (Object-Relational Mapper) is a way of querying and manipulating databases by using an object-oriented paradigm. Lets you compose SQL expressions by mapping python classes of objects to tables in the database and also uses the Engine and SQL Expression layer to interact with the database.

Moreover, SQLAlchemy is split into two libraries:

SQLAlchemy Core
SQLAlchemy ORM (Object Relational Mapping library). SQLALchemy ORM is offered as an optional library, so you don't have to use the ORM in order to use the rest of SQLAlchemy.
- The ORM uses the Core library inside
- The ORM lets you map from the database schema to the application's Python objects
- The ORM persists objects into corresponding database tables

In this below example I will write a SQL expression and show you how to write it in python

CREATE TABLE department (
    id INTEGER PRIMARY KEY,
    name VARCHAR(30)
)
CREATE TABLE employee (
    id INTEGER PRIMARY KEY,
    name VARCHAR(30),
    dep_id INTEGER REFERENCES department(id)
)

And in python using object-oriented programming

from flask_sqlalchemy import SQLAlchemy

db = SQLAlchemy()

// Define a class named Department
class Department(db.Model):
// Create a table named department
   _tablename_ = 'department'
// Table query
   id =  Column(Integer, primary_key=True)
   name = Column(String(30))

class Employee(db.Model):
    __tablename__ = 'employee'
    id = Column(Integer, primary_key=True)
    name = Column(String(30))
    dep_id = Column(Integer, ForeignKey('department.id'))
    department = relationship("Department")

Teslim's blog

Teslim's blog

The Beginner Guide to SQLAlchemy- Python Library for ORM

Introduction

Layer of Abstraction

Let's explain each level of abstraction

Reference