Object-Relational Mapping (ORM)

Problem: Relational databases store data in tables; object-oriented programs manipulate objects.

• Tables: rows and columns
• Objects: instances of classes with attributes and methods

Challenge: Converting between objects in memory and rows in a database is tedious and error-prone.

Say we have:

class Student:
def init(self, netid, name, major):
self.netid = netid
self.name = name
self.major = major

How do we store and retrieve Student objects from a table Students(netid, name, major) without writing lots of SQL?

Object-Relational Mapping (ORM):

• A programming technique that connects objects with relational database tables.
• Automatically maps:
  • Classes ↔ Tables
  • Object attributes ↔ Table columns
  • Object instances ↔ Table rows

Benefits:

• Reduces boilerplate SQL
• Keeps code Pythonic / object-oriented
• Supports relationships (one-to-one, one-to-many, many-to-many)
• Supports query abstraction via Python expressions

How ORM Works

ORM Concept	Database Equivalent
Class	Table
Instance	Row
Attribute	Column
Relationship	Foreign Key / Join

Relationships:

• One-to-many: e.g., a Professor teaches many Courses
• Many-to-many: e.g., Students enrolled in many Courses

Short Tutorial

pip install sqlalchemy

from flask import Flask, render_template, request
from flask_sqlalchemy import SQLAlchemy
import os

app = Flask(__name__)

# Configure SQLAlchemy connection
app.config['SQLALCHEMY_DATABASE_URI'] = 'mysql+pymysql://tweninge:newpassword@localhost/tweninge'
app.config['SQLALCHEMY_TRACK_MODIFICATIONS'] = False

db = SQLAlchemy(app)

# ORM Model
class AgeEntry(db.Model):
    __tablename__ = 'age_entries'
    id = db.Column(db.Integer, primary_key=True)
    age = db.Column(db.Integer, nullable=False)
    created_at = db.Column(db.DateTime, server_default=db.func.now())

# Home route
@app.route("/")
def home():
    return 'hello web'

# Index route
@app.route("/index", methods=['GET'])
def index():
    userinput_age = request.args.get('age', None)
    if userinput_age is None:
        return 'no age'
    else:
        # Insert new entry using ORM
        new_entry = AgeEntry(age=int(userinput_age))
        db.session.add(new_entry)
        db.session.commit()
        
        # Query all entries using ORM
        rows = AgeEntry.query.all()
        
        return render_template('index.html', data=userinput_age, dbrows=rows)

if __name__ == '__main__':
    # Create tables if they don't exist
    with app.app_context():
        db.create_all()
        
    app.debug = True
    app.run(host='0.0.0.0', port=5000)

and then you’ll need to change the template too.