Introduction to ORMs and Building a Basic ORM

Overview

This lecture introduced Object-Relational Mappers (ORMs), focusing on how they map Python objects to relational database tables, and walked through the process of building a simple ORM using Python and SQLite through test-driven development.

Introduction to ORMs

• ORM stands for Object-Relational Mapper: it maps in-memory objects (Python classes) to relational database tables.
• Relational databases organize data in tables; common examples are MySQL, PostgreSQL, SQLite, SQL Server, and Oracle.
• A mapper translates between Python objects and database tables.
• Building an ORM is complex due to differences between object-oriented and relational paradigms.

Key ORM Responsibilities

• Creating a database and defining tables.
• Creating and inserting data, managing foreign keys, handling data types.
• Advanced features include caching, transactions, performance tuning, error handling, and logging (not covered here).

Building a Basic ORM

• Used test-driven development: write tests first, implement code to pass tests.
• Defined a Database class and connected to a SQLite database.
• Created a Column class for table fields and mapped Python types to SQLite types (int → INTEGER, str → TEXT, bool → INTEGER, etc.).
• Implemented Table class with class methods to generate SQL for table creation, including handling primary keys and foreign keys.
• Used Python's property, @classmethod, and __getattribute__ for dynamic attribute access and class-instance logic.
• ORM inserts objects by converting attributes to SQL fields and parameterizing queries to prevent SQL injection.
• Used SQLite’s lastrowid to set the primary key after inserting a row.
• Provided methods for fetching all records or a specific record by ID, using SQL SELECT statements and dynamic object construction.

Key Concepts & Implementation Patterns

• Mapping Python data types to SQL data types is crucial for data integrity.
• Foreign keys reference other tables; require special handling since you can't directly embed objects in relational tables.
• Fetching and saving data use SQL templates and dynamic construction of fields and values.
• Used Python’s reflection (inspect module) and magic methods for generic table and field handling.
• Parameterized SQL queries to prevent SQL injection.

What Was Not Covered

• Complex features like joins, advanced querying, and transaction management were mentioned but not implemented.

Key Terms & Definitions

• ORM (Object-Relational Mapper) — A tool or code that connects Python objects with relational database tables.
• Foreign Key — A database field that creates a link between two tables by referencing the primary key of another table.
• Primary Key — A unique identifier for a record in a database table.
• SQL Injection — A security vulnerability where untrusted input is executed as SQL code, potentially compromising the database.

Action Items / Next Steps

• Review provided code and slides on GitHub for full implementation.
• Experiment with extending the ORM—add features like joins or more flexible querying.
• Practice using SQLite and Python’s sqlite3 module.
• Do not use this basic ORM in production; use established ORMs like SQLAlchemy or Django ORM for real applications.