Python and ORM

Python is widely used as a general purpose, high-level programming language. It was originally designed by Guido van Rossom in 1991, and developed by the Python Software Foundation. The main focus of Python at it's creation was readability, which means the cost of maintaining programs within this language will be more manageable and reliable for the long term. The reason Guido chose to develop Python because he thought that development in other languages at the time was too slow. Today, I want to talk about how what ORM is, and why it can help speed up application development.

An Object-relational mapper (ORM) is a library that automates the transfer of data into database tables through objects that are used in code. They are useful because they allow developers to write Python code instead of SQL to make create, read, update, and delete data from a database. The main benefit of this is that a developer wouldn't need to switch between languages when creating an application, they can just continue in Python and I feel like that makes it a very powerful tool, as the more experience with Python a developer has, the more they can just continue using it over any other language. For example, here is an example of a Python class:

from sqlalchemy import create_engine, Column, Integer, String
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.orm import sessionmaker

Base = declarative_base()

class User(Base):
    __tablename__ = 'users'

    id = Column(Integer, primary_key=True)
    username = Column(String)
    email = Column(String)

engine = create_engine('sqlite:///users.db')
Session = sessionmaker(bind=engine)
session = Session()

new_user = User(username='john_doe', email='[e-mail]')
session.add(new_user)
session.commit()

In this block of Python code, an SQL database is created, and an initial user is already added. It is pretty simple to interpret, the 'users' table will have 3 columns: id, username, and email. Almost instantaneously a new user can be generated, and the ability to generate more users is readily available all a few lines.

Another benefit of using Python for SQL is how many database backends it can support. This code above would only need a couple minor changes and it will be able to switch from SQLite support to MySQL or any other database backend for example.

from sqlalchemy import create_engine, Column, Integer, String
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.orm import sessionmaker

Base = declarative_base()

class User(Base):
    __tablename__ = 'users'

    id = Column(Integer, primary_key=True)
    username = Column(String)
    email = Column(String)

mysql_username = 'your_username'
mysql_password = 'your_password'
mysql_host = 'localhost'  # Change to your MySQL host
mysql_db_name = 'your_database_name'

connection_string = f'mysql+mysqlconnector://{mysql_username}:{mysql_password}@{mysql_host}/{mysql_db_name}'

engine = create_engine(connection_string)
Session = sessionmaker(bind=engine)
session = Session()

new_user = User(username='john_doe', email='[e-mail]')
session.add(new_user)
session.commit()

Here is an example of code that serves the same purpose as above but with support for MySQL. The majority of code is the same, which goes to show how adaptable Python code really is.

Another really powerful benefit of Python is relationship handling. Relationship handling in SQLAlchemy refers to how relationships are managed between tables in a database model. One record in a parent table can be associated with multiple records in a child table. This is called a one-to-many relationship. Another relationship two tables can have with each other is called a many-to-many relationship, and that is when both the parent and child table can share multiple records in the other table. Although managing multiple different records and the possibility of duplicates in those records sounds like it can get messy and confusing fast, SQLAlchemy can handle these relationships with a few lines of code. For example, without Python, you would need to create two separate tables with their own primary keys and other fields. Then, create a middle table to link both main tables together, this table contains keys that reference both primary keys of the two main tables. Afterwards, you would need to manually establish the relationships between the middle table and both main tables. Whenever a connection needs to be made from the two main tables, a third record needs to be created from the middle table to connect them. With the possibility of duplicate and multiple entries, visualizing this process can get very messy and confusing pretty quickly. Thankfully, this process can be automated with a few lines a Python code, here is an example:

association_table = Table(
    'user_roles', Base.metadata,
    Column('user_id', Integer, ForeignKey('users.id')),
    Column('role_id', Integer, ForeignKey('roles.id'))
)

class User(Base):
    __tablename__ = 'users'
    id = Column(Integer, primary_key=True)
    username = Column(String)
    email = Column(String)
    roles = relationship('Role', secondary=association_table, back_populates='users')

class Role(Base):
    __tablename__ = 'roles'
    id = Column(Integer, primary_key=True)
    name = Column(String)
    users = relationship('User', secondary=association_table, back_populates='roles')

In this example, a user is able to have many roles, and a role can have many users. This code will establish the relationships between the two tables without needing to manually enter in anything. The keys between the tables will be established. This process without a ORM will definitely be more messy, time consuming, and could potentially reach a point where it'll be too complex to work with. In conclusion, Python's purpose is more readability and simplicity, and even with SQL it provides just that. Python makes SQL easier to manage and navigate.