Implementing a Basic ORM for PostgreSQL in Python

  • Python
  • Gabriele Romanato

An ORM (Object-Relational Mapper) allows you to interact with the database using Python objects instead of writing SQL queries manually. In this article, we’ll see how to create a simple ORM from scratch for PostgreSQL using the psycopg2 library.

Requirements

Make sure you have installed:

  • Python 3.7+
  • PostgreSQL
  • psycopg2
pip install psycopg2

1. Connecting to the Database

Let's start by creating a class that manages the database connection.

import psycopg2

class Database:
    def __init__(self, dsn):
        self.dsn = dsn
        self.conn = psycopg2.connect(dsn)
        self.conn.autocommit = True

    def execute(self, query, params=None):
        with self.conn.cursor() as cur:
            cur.execute(query, params)

    def fetchall(self, query, params=None):
        with self.conn.cursor() as cur:
            cur.execute(query, params)
            return cur.fetchall()

    def fetchone(self, query, params=None):
        with self.conn.cursor() as cur:
            cur.execute(query, params)
            return cur.fetchone()

2. Defining the Base Model

Now let’s create a base class for the models, from which the tables will inherit.

class Model:
    db = None  # To be set externally

    @classmethod
    def create_table(cls):
        fields = []
        for name, type_ in cls.__annotations__.items():
            sql_type = 'SERIAL PRIMARY KEY' if name == 'id' else 'TEXT'
            fields.append(f"{name} {sql_type}")
        table = cls.__name__.lower()
        sql = f"CREATE TABLE IF NOT EXISTS {table} ({', '.join(fields)});"
        cls.db.execute(sql)

    def save(self):
        fields = [f for f in self.__annotations__ if f != 'id']
        values = [getattr(self, f) for f in fields]
        placeholders = ', '.join(['%s'] * len(values))
        sql = f"INSERT INTO {self.__class__.__name__.lower()} ({', '.join(fields)}) VALUES ({placeholders}) RETURNING id;"
        result = self.db.fetchone(sql, values)
        self.id = result[0]

    @classmethod
    def all(cls):
        sql = f"SELECT * FROM {cls.__name__.lower()};"
        rows = cls.db.fetchall(sql)
        instances = []
        for row in rows:
            obj = cls()
            for key, val in zip(cls.__annotations__.keys(), row):
                setattr(obj, key, val)
            instances.append(obj)
        return instances

3. Usage Example

Now we can define a model and use it to interact with the database.

class User(Model):
    id: int
    name: str
    email: str

# Database connection
db = Database("dbname=test user=postgres password=secret")
Model.db = db

# Table creation
User.create_table()

# Data insertion
u = User()
u.name = "Mario Rossi"
u.email = "mario@example.com"
u.save()

# Query
users = User.all()
for user in users:
    print(user.id, user.name, user.email)

Conclusion

We have built a minimal ORM that allows mapping Python objects to a PostgreSQL table. Although very simple, this approach demonstrates the fundamental concepts behind ORMs like SQLAlchemy or Django ORM. It’s recommended to extend this base with support for more precise data types, constraints, relationships, and validations.

Back to top