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")