An object-relational mapper commonly referred to as ORM is a code library that automates the transfer of data stored in a relational database into objects that are more frequently used in an application code. Using an object-oriented paradigm, object Relational Mapping allows developers to write queries for accessing and manipulating data by using their preferred programming language (in this case, that is python) instead of SQL.
In this article, we will be discussing the features of ORM and some python ORMs that can be used to create, read, update and delete data and schemas in the database with a Python code.
Object-relational Mappers (ORM)
ORM libraries provide a high-level abstraction upon a relational database that allows a developer to write the code in the language of his choice instead of SQL to create, read, update and delete data and schemas in their database. Developers can use the programming language they are more experienced or comfortable with to work with databases instead of writing SQL statements.
For instance, without an ORM a developer would have to write the following SQL statement to retrieve every row from the PRODUCTS table where the porductType column is capital goods:
SELECT * FROM PRODUCTS WHERE productType=capitalgoods;
The equivalent Django ORM (a python ORM) query would look like the following Python code:
# Obtain everyone in the capital goods product type and assign them to prod variable prod = Products.objects.filter(productType=capitalgoods)
The feature to write Python code instead of SQL can significantly speed up the web application development process, especially at the start of a project. Although many software developers and programmers may not have a problem switching back and forth between languages, it is still relatively easier to create a prototype or start a web application using a single programming language.
Although it is not advised, ORM can also be used to switch an application between various relational databases. For example, a developer could work with SQLite for local development and MySQL in production. A production application could be then later shifted from MySQL to PostgreSQL with some minor tweaks.
Python ORM Libraries
There are several ORM libraries written in Python, Following are the most stable and prominent ones among them:
- The Django ORM
- Pony ORM
- Peewee ORM
The Django ORM
The Django web framework offers its own built-in object-relational mapping module, which is referred to as “the Django ORM”.
The Django ORM works quite well for simple to medium-complexity database operations. However, the developers often complain about Django ORM being very difficult than just writing the codes in raw SQL or using SQLAlchemy. The ORM is coupled closely with Django so replacing the default ORM with SQLAlchemy is currently the only solution for this.
SQLAlchemy is widely considered one of the best ORM libraries for python because of its simplicity, speed and various other features that other ORM libraries do not offer. It also allows writing Python code to map data from the database to the applications’ Python objects and SQL is not required at all to create or maintain the database. It can be used with Flask, Pyramid and Django (not officially).
Pony ORM is another open-source Python ORM available under the Apache 2.0 license. It is much like other Python ORM libraries available out there but Pony makes it much easier to write complex SQL codes in python. It is also considered the best ORM in terms of speed. The biggest limitation in using Pony is no support for migration yet.
Peewee is relatively simpler and easier than most ORM libraries including SQLAlchemy. This makes it an ideal ORM for new developers who are just getting started. Peewee can be implemented with almost any web framework but being a small ORM, it makes it hard to be used in bigger projects.
SQLObject is an object-relational manager that provides an object interface to your database. It has been around for a long time as it is in active open-source development for over 14 years, since before 2003 but still there are not that many tutorials available for it. Still, it can be a good option for developers looking for an open-source ORM for their projects.
Database connector libraries
Python ORM libraries do not access have direct access to relational databases. In fact, the low-level access is typically provided by another library called a database connector. There are various data connector libraries available for different databases such as psycopg (for PostgreSQL) or MySQL-python (for MySQL).
The table below shows the ORMs with their compatible connector libraries and relational databases:
| Database Connector|
|Compatible Relational database||SQLite|
Drawbacks of using an ORM
As discussed before, there are numerous advantages of using python ORM libraries but it also comes with some downsides.
Following are some of the most prominent disadvantages of using a python ORM:
- Reduced performance
- Difficult learning curve
- Impedance mismatch
- Shifting complexities
The biggest concern associated with any higher-level abstraction is the potential reduction in performance. When using the ORM libraries, the drop in performance comes from the translation of application code (python code) into a corresponding SQL statement which may not be tuned properly.
Difficult learning curve
Most python ORM libraries are often very easy to try but it is not that easy to master them. For instance, a beginner using the Django ORM might not know about various functions and how they can be correctly used. There are various performance features offered by every ORM but requires time to learn them all. It can seem a good idea to invest time in learning an ORM but it may be better to just learn SQL instead and how to write stored procedures.
The term “impedance mismatch” is commonly used in conjunction with ORM. Impedance mismatch refers to all the problems that are faced while moving data between relational tables and application objects. It is because the way a developer uses objects is quite different from how the data is stored and joined in relational tables.
The code written for working with the data of the application has to be saved somewhere. Before ORM libraries were used, the stored procedures in the database were used to encapsulate the database logic. Now with an ORM, the data manipulation code is stored within the python codebase of the application.
The addition of this data handling logic in the application codebase is not such an issue for application design, but it does increase the total amount of Python code instead of splitting code between the application and the database stored procedures.
Wrapping it up
In this article, we discussed the python ORM libraries and how they can be used to connect object-oriented programs written in python to relational databases without the need to use SQL. It is a great tool to work with if you wish to work exclusively on Python. We listed some of the popular ORM tools available for python along with some of the most prominent drawbacks to be considered before starting to work with ORM tools.
Also Read: Guide on How Xperti Helps You Find Python Jobs in the U.S.