Object Relational Mapping in Python

Object Relational Mapping in Python THIS ARTICLE IS STILL IN EDITING MODE

Introduction

This article introduces the advantages and disadvantages of Object Relational Mapping (ORM) and the popular Python ORM solutions. This article uses SQLAlchemy, one of the common packages for ORM, to demonstrate how to use ORM in Python.

What is ORM

ORM is a programming technique for mapping objects to relational databases. There is a significant difference between object-oriented and relational databases. Relational databases are organized in rows and columns where each row has a unique key, and each column has a unique name. This way, you have a very structured database. An object-oriented database relies on objects which contain data and methods. For example, an object of Leuphana University might contain data about the staff employed, the total number of students, the number of courses offered, yearly budget spending, etc. But it can also contain methods, such as a formula to calculate the average monthly spending on chemistry laboratory equipment. These two datatypes therefore follow different logics and are not fully compatible, even though information from one database sometimes needs to be accessible from the other (for more information and comparison see here).

Popular ORM Solutions in Python

ORM creates an intermediate layer between the developer and the database, converting the data into object entities in Python, which shields the differences between different databases while making it very easy for the developer to manipulate the data in the database to use the advanced object-oriented features.

Many components in Python provide ORM support, each with a slightly different application field, but the theoretical principles of database manipulation are the same. The following describes some of the more well-known ORM frameworks for Python databases.

• SQLAlchemyis the most mature ORM framework in Python and has a lot of resources and documentation. As a result, SQLAlchemy is considered to be the de facto ORM standard for Python.
• Django ORM comes with the Django framework for simple or moderately complex database operations. However, Django ORM is based on complex query operations transformed into SQL statements, which are more tedious than those written directly in SQL or generated with SQLAlchemy.
• Peewee is a lightweight ORM, which is small and flexible. Peewee is based on the SQLAlchemy kernel development, and the entire framework consists of only one file. Peewee provides access to various databases, such as SQLite, MySQL, and PostgreSQL, for small sites with simple functionality.
• Storm is a medium-sized ORM library that requires developers to write DDL statements for data tables and can not generate their table definitions directly from the data table class definition.
• SQLObject is a Python ORM that maps objects between SQL databases and Python. Thanks to its Ruby on Rails-like ActiveRecord model, it is becoming increasingly popular in the programming community. It includes a Python-object-based query language, which makes SQL more abstract and thus provides substantial database independence for applications.

Pros and Cons of ORM

Pros:

• Database details are shielded from developers so that developers do not have to deal with SQL statements, which improves development efficiency.
• Facilitates database migration. SQL-based data access layers often spend much time debugging SQL statements when changing databases because of the nuanced differences in SQL statements between each database. ORM provides a SQL-independent interface, and the ORM engine handles the differences between databases, so no code changes are required when migrating databases.
• Applying techniques such as cache optimization can sometimes improve the efficiency of database operations.

Cons:

• ORM libraries are not lightweight tools and require much effort to learn and set up
• For complex queries, ORM is either inexpressible or less performant than native SQL.
• ORM abstracts away the database layer, and developers need help understanding the underlying database operations and customizing some particular SQL.

Applying Methods in Python with SQLAlchemy

The following sections introduce the basic syntax of ORM in Python, using SQLAlchemy as an example. SQLAlchemy ORM presents a method of associating user-defined Python classes with database tables and instances of those classes (objects) with rows in their corresponding tables. An application can be built using ORM alone.

Create a Model

SQLAlchemy ORM provides a way to relate user-defined python classes to tables in the database. The developer manipulates the database similarly to the python class (argument, method). Usually, the first step in ORM operations is to describe/declarative the database tables as python classes. A table (python class) must have at least the __tablename__ attribute and at least one column with a primary key field.

from sqlalchemy import *
from sqlalchemy.ext.declarative import *
from sqlalchemy.orm import *

engine = create_engine("sqlite:///test.db")
metadata = MetaData()
Base = declarative_base()

class Individ(Base):
    __tablename__ = 'individ'
    metadata = metadata
    id = Column(Integer, primary_key=True)
    name = Column(String)
    addresses = relationship("Address", backref="individ")

class Address(Base):
    __tablename__ = 'address'
    metadata = metadata
    id = Column(Integer, primary_key=True)
    email = Column(String)
    individ_id = Column(Integer, ForeignKey('individ.id'))
    individs = relationship("Individ",backref ="address")
metadata.create_all(engine)

First, we need to install the necessary packages. Then we create a database engine called "test.db" which is stored in the current working directory. We then settle the metadata to contain meta-information about the tables we will create. The Base command determines the structure of the database in a way that it is attributive. This means we can assign attributes to each column in our tables, such as a name. We then create the two tables, individ and address. For both, we first define the name of the table and settle the same metadata. We then create an id column as an integer for both tables. For the address table, we create another string column with the e-mail information and individ ID information which has its own key. We then set the relationship between the two tables and make sure with the "backref" specification that the relationship is also reversed. Lastly, we create the relational database using all the information from above. In the following, we will look at how to create read, update, and delete connections between the database we have created and ORM.

Create, Read, Update, and Delete- the Four Elements of ORM

Create

We create an instance of individ and address, which are "Hannah" and "Hannah@gmail.com" and set the attribute name for an e-mail for individ and address respectively. We also set that the person with the given address belongs to the person with the individ ID 1 (which in this case is Hannah). We can therefore treat the instances in the relational database as objects. We then create a database using the sessionmaker function and add the attributes with "session.add_all()" (we could also only add one attribute with "session.add()"). With the commit command, we confirm the changes made to the database.

individ = Individ()
individ.name = "Hannah"

address = Address()
address.email = "Hannah@gmail.com"
address.individ_id = 1

Session = sessionmaker(engine)
session = Session()
session.add_all([individ, address])
session.commit()

Read

We first again start a session to connect to the database. Then we make the query to retrieve the address of the person with the ID 2, so the address of the second individ. The first command makes sure that the first result of the query is retrieved. Since we have only created one ID, we should get the result "none".

Session = sessionmaker(engine)
session = Session()
result = session.query(Address).filter(Address.id==2).first()

Update

To update the database, we again start the session to connect to the database. We again query for the address with ID 2. But this time, we update the e-mail address by adding "update({"email": "Hannah+1@gmail.com"}. Note that e-mail is an attribute of the address we have defined above. We then finalize the update with "session.commit". This update only works when ID 2 already exists.

Session = sessionmaker(engine)
session = Session()
result = session.query(Address).filter(Address.id==2).update({"email": "Hannah+1@gmail.com"})
session.commit()

Delete

To delete an attribute, we again query an address, this with ID after, we have stated the session. This retrieved address we call the result. This result is then deleted using "session.delete(result)". Lastly, we confirm the changes made with "session.commit".

Session = sessionmaker(engine)
session = Session()
result = session.query(Address).filter(Address.id==1).first()
session.delete(result)
session.commit()

References

• Copeland, Rick, and Jason Myers. Essential Sqlalchemy. O'Reilly, 2016.
• “The Python SQL Toolkit and Object Relational Mapper.” SQLAlchemy, https://www.sqlalchemy.org/.

The author of this entry is XX. Edited by Milan Maushart