As I begin to write this article I can't help but feel an overwhelming sense of imposter syndrome. This topic of Object-Relational Mapping or ORM is one I never thought in a couple weeks was something I could reiterate or even explain to the masses. Yet, here I am. At the computer, writing this blog to all my aspiring (w)right coders, explaining this very cool technique. I will not lie, this one really had my head spinning at first, just like every new concept I learn. ORM was different though! Once I had figured out how to break it down into bite sized pieces of information, it actually started to click! Or should I say, relate? Nonetheless, here we go y'all into the magical world of ORM in Python.
Object-Relational Mapping (ORM) is a technique that enables a software engineer to connect with a relational database, such as MySQL, PostgreSQL, or SQLite, via an object-oriented programming language, like Python. In other words, ORM allows developers to manipulate database tables and records as if they were Python objects and collections.
ORM is an essential concept in software engineering since it streamlines database operations. Traditionally, working with databases in a programming language entails writing difficult SQL queries that retrieve and manipulate the data. This approach can be time-consuming, error-prone, and challenging to manage, particularly for large and complex systems. ORM tackles these challenges by making intuitive and simple databases.
One of the most popular ORM libraries in Python is SQLAlchemy. SQLAlchemy is a full-featured ORM that offers a high-level interface for working with databases. It supports a variety of database engines, including SQLite, PostgreSQL, and MySQL, and has a robust query API for accessing and modifying data. We've done a lot of talking so far but let's see this magic ORM in action !
THE MAGIC ORM AWAITS !
To utilize SQLAlchemy, you must first create your database schema by building Python classes that represent the database tables. Each class attribute represents a column in the database, and you may add features like primary keys, foreign keys, and data types. A foreign key is a column or columns in a database that are linked to a column in a different table. A primary key is a column in a table that is used as a unique identifier for each row, and used in the table’s primary index. (Technically, a primary key can be made up of multiple columns, but for our purposes here let’s think of it as a single column).
Here's an example.
In this example, we define/create a User class to represent a users table in a SQLite database. We then construct an engine object to represent the database connection and use the Base.metadata.create_all() method to create the users table if it does not already exist.
We can then build a new User object, add it to the database session, and commit the transaction to save the data in the database. ORM generates the required SQL INSERT statement and executes it against the database.
ORM makes it straightforward to retrieve data from the database. We can utilize the query API to filter and sort data, and ORM will automatically convert our Python code to SQL queries.
Here's an example.
In this example, we utilize the session.query() method to retrieve all User objects whose names begin with the letter "J". We then iterate through the results, printing each user's name and email. ORM generates and executes the required SQL SELECT statement on the database.
To summarize, ORM is an effective strategy for interacting with databases in Python. It makes it easier to retrieve and manipulate data, minimizing the amount of repeated code needed, and allows developers to work with databases more intuitively. Using ORM in Python programs can help you save time, decrease errors, and increase maintainability. With this technique there is no stopping the magic of manipulating data and finding balance with SQL in Python! I encourage you to go and try this very technique to see what happens, but more importantly to see what you learn! If everything goes to plan don't be a stranger, come back, comment "Ta-Da✨" and share your experience! I can't wait to hear, happy coding!